ellegua220

the where clause was out of place i think??

There is an error in this update statement. Here is the error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE user.userid = 209 set user.username='ttttt', email='akegncnc@ial.com', fir' at line 1 here is the code: $query = sprintf("UPDATE user LEFT JOIN parent_user ON parent_user.userid = user.userid LEFT JOIN parent ON parent.parent_id = parent_user.parent_id WHERE user.userid = $userid set user.username='$username', email='$email', first_name='$first_name', last_name='$last_name', password='$password',phone='$phone', street_address='$street_address', other_address='$other_address', city='$city', state='$state', zipcode='$zipcode', type='$type', school='$school', school_district='$school_district',double_gui='$double_gui',status_id=$status_id,notes='%s',parent.parent_first_name='$parent_first_name',parent.parent_last_name='$parent_last_name' where userid=$userid", mysql_real_escape_string($notes, $mysql_link));

thanks!

can someone break this statement down? what are the u and pu and p? is that shorthand for user, parent_user and parent? if so, what if you had 2 tables with the same first letter? also can you break down the join for me with an example data set? > $query="SELECT * FROM user u LEFT JOIN parent_user pu ON pu.userid = > u.userid LEFT JOIN parent p ON p.parent_id = pu.parent_id WHERE > u.userid = $userid"; >

i don't see it? what do you mean? Undefined variable: parent_first_name in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 531 Notice: Undefined variable: paren_last_name in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 531 However I do know now that the row is the position in the select statement. What if the select is a wildcard such as in this select: $query="SELECT * FROM user u LEFT JOIN parent_user pu ON pu.userid = u.userid LEFT JOIN parent p ON p.parent_id = pu.parent_id WHERE u.userid = $userid"; this one is working except i am getting a user id and parent_user_id in the parent first and last name fields.. all othr fields are in place... where is this defined?

Ok. aparently I can't use var_dump on the variables from the select statment $parent.parent_last_name=parent.parent_last_name; var_dump($parent.parent_last_name); was causing an error and giving the white screen. why? so now it's working a little better. I am still gettting: Notice: Undefined variable: parent_first_name in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 531 Notice: Undefined variable: parent_last_name in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 531 I am not sure how to assign the joined table values to variables. The user table is assigned with an array of some kind. how do I reference the other tables?: if ($row = mysql_fetch_row($result)) { $userid = $row[0]; $username = $row[1]; $email = $row[2]; $first_name = $row[3]; $last_name = $row[4]; //$parent_last_name = $row[5]; $password = $row[5]; // no it didn't print "<tr><td> will this print next to the password??????</td></tr>"; $phone = $row[6]; $street_address = $row[7]; $other_address = $row[8]; $city = $row[9]; $state = $row[10]; $zipcode = $row[11]; $type = $row[12]; $school = $row[13]; $school_district = $row[14]; $date_registered = $row[15]; $double_gui = $row[16]; $status_id = $row[17]; $notes = $row[18]; //$parent_user.userid =$row[19]; if ($type == "S") { $student_selected = "selected"; } elseif ($type == "T") { $tutor_selected = "selected"; } elseif ($type == "A") { $admin_selected = "selected"; } elseif ($type == "K") { $tech_selected = "selected"; }

I also tried this: $query = "SELECT user.userid, user.username, user.email, user.first_name, user.last_name, user.password, user.phone, user.street_address, user.other_address, user.city, user.state, user.zipcode, user.type, user.school, user.school_district, DATE_ADD(user.date_registered, INTERVAL $time_diff HOUR),user.double_gui,user.status_id,user.notes, parent_user.userid, parent.parent_first_name, parent.parent_last.name FROM user JOIN parent_user ON user.userid=parent_user.userid JOIN parent ON parent_user.parent_id=parent.parent_id";

no its still not working. I thought the join would give parent_first_name it's value but I'm don't know how to write it correctly: $query = "SELECT user.userid, user.username, user.email, user.first_name, user.last_name, user.password, user.phone, user.street_address, user.other_address, user.city, user.state, user.zipcode, user.type, user.school, user.school_district, DATE_ADD(user.date_registered, INTERVAL $time_diff HOUR),user.double_gui,user.status_id,user.notes, parent_user.userid, parent.parent_first_name, parent.parent_last.name FROM user INNER JOIN parent_user ON user.userid=parent_user.userid AND parent_user.parent_id=parent.parent_id";

I have taken care of all of the errors except the 2 fields that i want to assign with the joins... Notice: Undefined variable: parent_first_name in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 525 Notice: Undefined variable: parent_last_name in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 525 here is my code now. Please have a look and let me know if i did anything wrong: <html> <head> <title>User Admin</title> <link rel="STYLESHEET" href="../style.css" type="text/css"> </head> <body> <center> <h1>User Admin</h1> </center> <script language="php"> // display errors and warnings added by mp ini_set('display_errors', 1); include("../db.php"); // declarations added by mark palmer to stop the warnings once the display errors statement was added $action=''; $userid=''; $search=''; $status_search=''; $status_not=''; $result=''; $selected=''; $tutor_selected=''; $admin_selected=''; $tech_selected=''; /* $action = $_GET['action']; $userid = $_GET['userid']; $search = $_GET['search']; */ // checking to see if variables were set before assigning them with $_GET added by mark palmer to stop the warnings once the display errors statement was added if (isset($_GET['action'])) {$action = $_GET['action'];} if (isset($_GET['userid'])) {$userid = $_GET['userid'];} if (isset($_GET['search'])) {$search = $_GET['search'];} if (isset($_GET['status_search'])) {$status_search = $_GET['status_search'];} if (preg_match('/[^\d]/', $status_search) > 0) { die("invalid status search id"); } if (isset($_GET['status_not'])) {$status_not = $_GET['status_not'];} if (preg_match('/[^\d]/', $status_not) > 0) { die("invalid status search id"); } if ($action == "delete") { if ($userid == "") { die("<p class=highlight>no userid specified</p>"); } if (preg_match('/[^\d]/', $userid) > 0) { die("<p class=highlight>invalid userid</p>"); } $query = "DELETE FROM user WHERE userid=$userid"; $result = mysql_query($query, $mysql_link) or die(mysql_error()); $query = "DELETE FROM student_session WHERE userid=$userid"; $result = mysql_query($query, $mysql_link) or die(mysql_error()); $query = "DELETE FROM session_login WHERE userid=$userid"; $result = mysql_query($query, $mysql_link) or die(mysql_error()); //below added by mark palmer to delete information from the new parent and parent_user tables when a user, future session and logins are deleted above. /*$query = "DELETE FROM parent_user WHERE userid=$userid"; AT THE SAME TIME OR BEFORE IT NEEDS TO PULL OUT ALL OF THE PARENT_ID WHERE USERID=$USERID SO IT CAN DELETE FROM PARENT ID WHERE PARENTID=$PARENTID $result = mysql_query($query, $mysql_link) or die(mysql_error()); $query = "DELETE FROM student_session WHERE userid=$userid"; $result = mysql_query($query, $mysql_link) or die(mysql_error()); $query = "DELETE FROM WHERE userid=$userid"; $result = mysql_query($query, $mysql_link) or die(mysql_error());*/ include("updatepasswd.php"); include("updateconfig.php"); print "<p class=highlight>User deleted</p>"; print "<meta http-equiv='Refresh' content='2; url=useradmin.php'>"; } elseif ($action == "update" || $action == "insert") { // error checking preg_match('/[\047\042],varible') searches for single and double quotes if (preg_match('/[^\d]/', $userid) > 0) { die("<p class=highlight>invalid userid</p>"); } $username = $_GET['username']; if (preg_match('/[^\w\.@ ]/', $username) > 0) { die("<p class=highlight>invalid username: must only contain letters, digits, spaces, dots, underscores and @</p>"); } $email = $_GET['email']; if ($email != "") { if (! (preg_match('/@/', $email))) { die("<p class=highlight>invalid email address</p>"); } if (! (preg_match('/\./', $email))) { die("<p class=highlight>invalid email address</p>"); } if (preg_match('/[ \047\042\|<>]/', $email) > 0) { die("<p class=highlight>invalid email address</p>"); } } $first_name = $_GET['first_name']; if (preg_match('/[\047\042]/', $first_name) > 0) { die("<p class=highlight>invalid first name</p>"); } $last_name = $_GET['last_name']; if (preg_match('/[\047\042]/', $last_name) > 0) { die("<p class=highlight>invalid last name</p>"); } //next group of lines below added by Mark Palmer // working $parent_first_name=$_GET['parent_first_name']; //var_dump($parent_first_name); if (preg_match('/[\047\042]/', $parent_first_name) > 0) { die("<p class=highlight>invalid parent last name</p>"); } $parent_last_name=$_GET['parent_last_name']; //var_dump($parent_last_name); if (preg_match('/[\047\042]/', $parent_last_name) > 0) { die("<p class=highlight>invalid parent last name</p>"); } //var_dump($parent_first_name); //var_dump($parent_last_name); //print("<tr><td>next</td></tr>"); // previous lines added by mark pamer $password = $_GET['password']; if (preg_match('/[\047\042]/', $password) > 0) { die("<p class=highlight>invalid password</p>"); } $phone = $_GET['phone']; if (preg_match('/[^ \d\.\/\(\)\+\-]/', $phone) > 0) { die("<p class=highlight>invalid phone number</p>"); } $street_address = $_GET['street_address']; if (preg_match('/[\047\042]/', $street_address) > 0) { die("<p class=highlight>invalid street_address</p>"); } $other_address = $_GET['other_address']; if (preg_match('/[\047\042]/', $other_address) > 0) { die("<p class=highlight>invalid other_address</p>"); } $city = $_GET['city']; if (preg_match('/[\047\042]/', $city) > 0) { die("<p class=highlight>invalid city</p>"); } $state = $_GET['state']; if (preg_match('/[^a-zA-Z]/', $state) > 0) { die("<p class=highlight>invalid state</p>"); } $zipcode = $_GET['zipcode']; if (preg_match('/[\047\042]/', $zipcode) > 0) { die("<p class=highlight>invalid zipcode</p>"); } $type = $_GET['type']; if ($type != "T" && $type != "A" && $type != "S" && $type != "K") { die("<p class=highlight>invalid type</p>"); } $school = $_GET['school']; if (preg_match('/[\047\042]/', $school) > 0) { die("<p class=highlight>invalid school</p>"); } $school_district = $_GET['school_district']; if (preg_match('/[\047\042]/', $school_district) > 0) { die("<p class=highlight>invalid school_district</p>"); } $double_gui = $_GET['double_gui']; if ($double_gui != "Y") { $double_gui = "N"; } $notes = $_GET['notes']; $status_id = $_GET['status_id']; if (preg_match('/[^0-9]/', $status_id) > 0) { die("<p class=highlight>invalid status</p>"); } if ($status_id == "") { $status_id = "null"; } if ($email == "") { die("<p class=highlight>email cannot be blank</p>"); } if ($username == "") { die("<p class=highlight>username cannot be blank</p>"); } if ($first_name == "") { die("<p class=highlight>first name cannot be blank</p>"); } if ($last_name == "") { die("<p class=highlight>last name cannot be blank</p>"); } if ($street_address == "") { die("<p class=highlight>street_address cannot be blank</p>"); } if ($city == "") { die("<p class=highlight>city cannot be blank</p>"); } if ($state == "") { die("<p class=highlight>state cannot be blank</p>"); } if ($zipcode == "") { die("<p class=highlight>zipcode cannot be blank</p>"); } if ($password == "") { die("<p class=highlight>password cannot be blank</p>"); } if ($type == "") { die("<p class=highlight>type cannot be blank</p>"); } if ($action == "insert") { // adding a new user if ($username == "") { die("<p class=highlight>username cannot be blank</p>"); } if ($company_id == "") { $company_id = "null"; } // david is using the sprintf to do some error checking. It is inserting all of the fields into the table user. it uses and string arg %s to get the esc checked field $notes $query = sprintf("insert into user values(null, '$username', '$email', '$first_name', '$last_name', '$password', '$phone', '$street_address', '$other_address', '$city', '$state', '$zipcode', '$type', '$school', '$school_district', current_timestamp, '$double_gui', $status_id, '%s')",mysql_real_escape_string($notes, $mysql_link)); mysql_query($query, $mysql_link) or die(mysql_error()); $user_id= mysql_insert_id(); // var_dump($query); // added by mark palmer inserts the null (for the pk) parents first and last name int the parent table $query2 = sprintf("insert into parent values(null,'$parent_first_name','$parent_last_name')"); //var_dump($query2); mysql_query($query2,$mysql_link) or die(mysql_error()); // next line gets the parent id automatically created by after the insert into parents $parent_id= mysql_insert_id(); var_dump($parent_id); var_dump($user_id); $query3= sprintf("insert into parent_user values(null, '$parent_id','$user_id')"); var_dump($query3); mysql_query($query3,$mysql_link) or die(mysql_error()); if ($status_id != 'null' && $status_id != '') { $uid = mysql_insert_id($mysql_link); $changed_by = $_SERVER['REMOTE_USER']; $changed_by_userid = 'null'; if ($changed_by != "") { $query = "select userid from user where username='$changed_by'"; $result = mysql_query($query, $mysql_link) or die(mysql_error()); if ($result) { if ($row = mysql_fetch_row($result)) { $changed_by_userid = $row[0]; } } } $query = "insert into user_status values(null, $status_id, $uid, current_timestamp, $changed_by_userid)"; mysql_query($query, $mysql_link) or die(mysql_error()); } print "<p class=highlight>User added</p>"; print "<a href=\"useradmin.php?action=add\">Add More Users</a><br>"; print "<a href=\"useradmin.php\">Back to User List</a><br>"; } else { if ($userid == "") { die("<p class=highlight>no userid specified</p>"); } // get old status so we can see if it changed $query = "select status_id from user where userid=$userid"; $result = mysql_query($query, $mysql_link) or die(mysql_error()); if((!result) || (! mysql_num_rows($result))) { die("<p class=highlight>error getting status of user userid=$userid from db</p>"); } if ($row = mysql_fetch_row($result)) { $old_status = $row[0]; } $query = sprintf("UPDATE user set username='$username', email='$email', first_name='$first_name', last_name='$last_name', password='$password', phone='$phone', street_address='street_address', other_address='$other_address', city='$city', state='$state', zipcode='$zipcode', type='$type', school='$school', school_district='$school_district',double_gui='$double_gui',status_id=$status_id,notes='%s' where userid=$userid", mysql_real_escape_string($notes, $mysql_link));; ; mysql_query($query, $mysql_link) or die(mysql_error()); if ($status_id != 'null' && $status_id != '' && $status_id != $old_status) { $changed_by = $_SERVER['REMOTE_USER']; $changed_by_userid = 'null'; if ($changed_by != "") { $query = "select userid from user where username='$changed_by'"; $result = mysql_query($query, $mysql_link) or die(mysql_error()); if ($result) { if ($row = mysql_fetch_row($result)) { $changed_by_userid = $row[0]; } } } $query = "insert into user_status values(null, $status_id, $userid, current_timestamp, $changed_by_userid)"; mysql_query($query, $mysql_link) or die(mysql_error()); } print "<p class=highlight>User details updated</p>"; print "<meta http-equiv='Refresh' content='2; url=useradmin.php'>"; } include("updatepasswd.php"); include("updateconfig.php"); } elseif ($action == "edit" || $action == "add") { if ($action == "edit") { if ($userid == "") { die("<p class=highlight>no userid specified</p>"); } if (preg_match('/[^\d]/', $userid) > 0) { die("<p class=highlight>invalid userid</p>"); } // get details from db $query = "SELECT userid, username, email, first_name, last_name, password, phone, street_address, other_address, city, state, zipcode, type, school, school_district, DATE_ADD(date_registered, INTERVAL $time_diff HOUR),double_gui,status_id,notes from user where userid=$userid"; //$query = "SELECT user.userid, user.username, user.email, user.first_name, user.last_name, user.password, user.phone, user.street_address, user.other_address, user.city, user.state, user.zipcode, user.type, user.school, user.school_district, DATE_ADD(user.date_registered, INTERVAL $time_diff HOUR),user.double_gui,user.status_id,user.notes FROM user INNER JOIN parent_user ON user.userid=parent_user.userid; //$query="SELECT * FROM user u LEFT JOIN parent_user pu ON pu.userid = u.userid LEFT JOIN parent p ON p.parent_id = pu.parent_id WHERE u.userid = $userid" var_dump($userid); //$result = mysql_query($query, $mysql_link) or die("Query failed: $query\n" . mysql_last_error()); $result = mysql_query($query, $mysql_link); if((!$result) || (! mysql_num_rows($result))) { die("<p class=highlight>error getting details of user userid=$userid from db</p>"); } if ($row = mysql_fetch_row($result)) { $userid = $row[0]; $username = $row[1]; $email = $row[2]; $first_name = $row[3]; $last_name = $row[4]; //$parent_last_name = $row[5]; $password = $row[5]; // no it didn't print "<tr><td> will this print next to the password??????</td></tr>"; $phone = $row[6]; $street_address = $row[7]; $other_address = $row[8]; $city = $row[9]; $state = $row[10]; $zipcode = $row[11]; $type = $row[12]; $school = $row[13]; $school_district = $row[14]; $date_registered = $row[15]; $double_gui = $row[16]; $status_id = $row[17]; $notes = $row[18]; if ($type == "S") { $student_selected = "selected"; } elseif ($type == "T") { $tutor_selected = "selected"; } elseif ($type == "A") { $admin_selected = "selected"; } elseif ($type == "K") { $tech_selected = "selected"; } } } // the line right below prints above the commented out print "where will this print????" line below. //print "<tr><td> really? where will this print??????</td></tr>"; print "<form>"; // when not commented the below line prints right above the table for editing a user //print "<tr><td> where will this print??????</td></tr>"; print "<table>"; if ($action == "edit") { print "<input type=hidden name=action value=update>"; print "<input type=hidden name=userid value='$userid'>"; } else { print "<input type=hidden name=action value=insert>"; } // action is not edit then has to be add mp print "<tr><td>Username:</td><td><input type=text maxlength=40 name=username value=\"$username\"> (REQUIRED)</td><tr>"; print "<tr><td>Status:</td><td><select name=status_id><option value=\"\"></option>"; // get statuses from db $query = "SELECT status_id,description from status"; $result = mysql_query($query, $mysql_link); if(!result) { die("<p class=highlight>error getting status info from db</p>"); } while ($row = mysql_fetch_row($result)) { $sid = $row[0]; $desc = $row[1]; $selected = ""; if ($sid == $status_id) { $selected = "selected"; } print "<option $selected value=\"$sid\">$desc</option>"; } print "</select><input type=submit onclick=\"window.open('statusadmin.php', 'status','width=600,height=600,scrollbars=yes'); return false\" value=\"ADD\"></td>"; if ($userid != "") { print "<td style='padding-left: 50' rowspan=100 valign=top><iframe src=\"statuslog.php?id=$userid\"></iframe></td>"; } print "</tr>"; print "<tr><td>Email: </td><td><input type=text maxlength=80 name=email value=\"$email\"> (REQUIRED)</td><tr>"; // next 2 lines below have been altered by mark palmer. I have added parent first name and last name in different positions. well first i added parent first and alst name then the last version is the reorder of the input fields // print "<tr><td>First Name:</td><td><input type=text maxlength=40 name=first_name value=\"$first_name\"> (REQUIRED)</td><td> </td><td>Parent First Name</td><td><input type=text maxlength=40 name=parent_first_name value=\"$parent_first_name\"> (REQUIRED)</td><tr>"; // print "<tr><td>Last Name:</td><td><input type=text maxlength=40 name=last_name value=\"$last_name\"> (REQUIRED)</td><td> </td><td>Parent Last Name</td><td><input type=text maxlength=40 name=parent_last_name value=\"$parent_last_name\"> (REQUIRED)<tr>"; print "<tr><td> First Name: </td><td><input type=text maxlength=40 name=first_name value=\"$first_name\"> (REQUIRED)</td><td> </td><td>Last Name:</td><td><input type=text maxlength=40 name=last_name value=\"$last_name\"> (REQUIRED)</td><tr>"; print "<tr><td>Parent First Name</td><td><input type=text maxlength=40 name=parent_first_name value=\"$parent_first_name\"> (REQUIRED)</td><td> </td><td>Parent Last Name</td><td><input type=text maxlength=40 name=parent_last_name value=\"$parent_last_name\"> (REQUIRED)</td><tr>"; print "<tr><td>Password:</td><td><input type=text maxlength=40 name=password value=\"$password\"> (REQUIRED)</td><tr>"; print "<tr><td>Phone:</td><td><input type=text maxlength=20 name=phone value=\"$phone\"></td><tr>"; print "<tr><td>Street Address:</td><td><input type=text maxlength=80 name=street_address value=\"$street_address\"> (REQUIRED)</td><tr>"; print "<tr><td>Other Address:</td><td><input type=text maxlength=80 name=other_address value=\"$other_address\"></td><tr>"; print "<tr><td>City:</td><td><input type=text maxlength=40 name=city value=\"$city\"> (REQUIRED)</td><tr>"; print "<tr><td>State:</td><td><input size=2 type=text maxlength=2 name=state value=\"$state\"> (REQUIRED)</td><tr>"; print "<tr><td>Zipcode:</td><td><input type=text maxlength=10 name=zipcode value=\"$zipcode\"> (REQUIRED)</td><tr>"; print "<tr><td>Type:</td><td><select name=type><option value='S' $student_selected>Student</option><option value='T' $tutor_selected>Tutor</option><option value='A' $admin_selected>Admin</option><option value='K' $tech_selected>Tech</option></select>"; print "<tr><td>School:</td><td><input type=text maxlength=80 name=school value=\"$school\"></td><tr>"; print "<tr><td>School District:</td><td><input type=text maxlength=80 name=school_district value=\"$school_district\"></td><tr>"; $checked = ""; if ($double_gui == "Y") { $checked = "checked"; } print "<tr><td>Use Double GUI:</td><td><input type=checkbox name=double_gui value=\"Y\" $checked></td><tr>"; print "<tr><td>Notes:</td><td><textarea name=\"notes\">$notes</textarea></td></tr>"; print "</table>"; //var_dump("$first_name"); //var_dump("$parent_first_name"); if ($action == "add") { print "<p><input type=submit value=\"Add User\"></p>"; } else { print "<p><input type=submit value=\"Update User Details\"></p>"; } print "</form>"; print "<hr>"; print "<p>Upload multiple students from CSV file:</p>\n"; print "<form enctype='multipart/form-data' action='csvupload.php' method='POST'>\n"; print "<input type='hidden' name='MAX_FILE_SIZE' value='100000000'>\n"; print "Choose a file to upload: <input name='csvfile' type='file'><br>\n"; print "<input type='submit' value='Upload CSV File'>\n"; print "</form>\n"; } elseif ($action == "") { $extra_tables = ""; $qual = ""; if ($userid != "") { if (preg_match('/[^\d]/', $userid) > 0) { die("<p class=highlight>invalid userid</p>"); } $qual = "where userid=$userid"; } if ($search != "") { $search = mysql_real_escape_string($search, $mysql_link); $qual = "where (username like '%$search%' or last_name like '%$search%' or email like '%$search%')"; } if ($status_search != "") { $extra_tables = ",user_status"; $qual = "where user_status.status_id=$status_search and user.userid=user_status.userid"; } // get list of all users // prints the table of all the user in the system. mp $query = "SELECT distinct user.userid, username, email, first_name, last_name, password, phone, street_address, other_address, city, state, zipcode, type, school, school_district, DATE_FORMAT(DATE_ADD(date_registered, INTERVAL $time_diff HOUR), '%m/%d/%y %l:%i%p'),double_gui,status.description,notes from (user$extra_tables) left join status on status.status_id=user.status_id $qual order by username"; $result = mysql_query($query, $mysql_link) or die(mysql_error()); // the print statement below prints the table for each varibale. need to add a column here before you fetch a new varible or print a new varible. or it will just copy over something. print "<table border=1><th>Userid</th><th>Username</th><th>Email</th><th>First Name</th><th>Last Name</th><th>Password</th><th>Phone</th><th>Street Address</th><th>Other Address</th><th>City</th><th>State</th><th>Zipcode</th><th>Type</th><th>School</th><th>School District</th><th>Sessions Attended</th><th>Date Registered</th><th>Double GUI</th><th>Status</th><th>Notes</th><th>DELETE</th><th>EDIT</th></tr>"; if($result && mysql_num_rows($result)) { while ($row = mysql_fetch_row($result)) { $userid = $row[0]; $username = $row[1]; $email = $row[2]; $first_name = $row[3]; $last_name = $row[4]; $password = $row[5]; $phone = $row[6]; $street_address = $row[7]; $other_address = $row[8]; $city = $row[9]; $state = $row[10]; $zipcode = $row[11]; $type = $row[12]; if ($type == "S") { $type = "Student"; $student_selected = "selected"; } elseif ($type == "T") { $type = "Tutor"; $tutor_selected = "selected"; } elseif ($type == "A") { $type = "Admin"; $admin_selected = "selected"; } elseif ($type == "K") { $type = "Tech"; $tech_selected = "selected"; } $school = $row[13]; $school_district = $row[14]; $date_registered = $row[15]; $double_gui = $row[16]; $status_desc = $row[17]; $notes = $row[18]; if ($double_gui != "Y") { $double_gui = "N"; } if ($status_not != "") { $query2 = "select * from user_status where status_id=$status_not and userid=$userid"; $result2 = mysql_query($query2, $mysql_link) or die(mysql_error()); if($result2 && mysql_num_rows($result2)) { // ignore this one continue; } } print "<tr>"; print "<td>$userid</td>"; print "<td>$username</td>"; print "<td>$email</td>"; print "<td>$first_name</td>"; print "<td>$last_name</td>"; print "<td>$password</td>"; print "<td>$phone</td>"; print "<td>$street_address</td>"; print "<td>$other_address</td>"; print "<td>$city</td>"; print "<td>$state</td>"; print "<td>$zipcode</td>"; print "<td>$type</td>"; print "<td>$school</td>"; print "<td>$school_district</td>"; print "<td><a href=\"listlogins.php?userid=$userid\">Sessions</a></td>"; print "<td>$date_registered</td>"; print "<td>$double_gui</td>"; print "<td>Current status: $status_desc<br><button onclick=\"window.open('statuslog.php?id=$userid', 'statuslog$userid', 'width=600, height=600,scrollbars=yes')\">Show Log</button></td>"; print "<td>$notes</td>"; print "<td><form><input type=\"hidden\" name=\"action\" value=\"delete\"><input type=\"hidden\" name=\"userid\" value=\"$userid\"><input type=submit value=\"Delete\"></td></form>"; print "<td><form><input type=\"hidden\" name=\"action\" value=\"edit\"><input type=\"hidden\" name=\"userid\" value=\"$userid\"><input type=submit value=\"Edit\"></form>"; print "</tr>"; } } else { print "<tr><td colspan=19>No users</td></tr>"; } print "</table>"; print "<form><input type=\"hidden\" name=\"action\" value=\"add\"><input type=submit value=\"Add New User\"></form>"; } </script> <a href=index.html>Back to Admin Home</a> </body> </html>

I have changed the query back to : $query = "SELECT userid, username, email, first_name, last_name, password, phone, street_address, other_address, city, state, zipcode, type, school, school_district, DATE_ADD(date_registered, INTERVAL $time_diff HOUR),double_gui,status_id,notes from user where userid=$userid"; the first set of errors: Notice: Undefined index: action in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 13 Notice: Undefined index: userid in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 14 Notice: Undefined index: search in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 15 Notice: Undefined index: status_search in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 16 Notice: Undefined index: status_not in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 21 then when i click on edit these errors: Notice: Undefined index: search in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 15 Notice: Undefined index: status_search in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 16 Notice: Undefined index: status_not in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 21 string(2) "57" Notice: Use of undefined constant result - assumed 'result' in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 385 Notice: Undefined variable: parent_first_name in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 488 Notice: Undefined variable: parent_last_name in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 488 Notice: Undefined variable: tutor_selected in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 501 Notice: Undefined variable: admin_selected in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 501 Notice: Undefined variable: tech_selected in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 501

I ran it again after changing the result statement back to its original form. one error is the number of rows it is looking for or finding. at line 384. this is the if statement... are there any tools like a work bench that could help me debug php? here are the errors i get after changing the result statement: Notice: Undefined index: search in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 15 Notice: Undefined index: status_search in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 16 Notice: Undefined index: status_not in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 21 string(2) "24" Notice: Undefined variable: query in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 383 Notice: Use of undefined constant result - assumed 'result' in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 384 Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 384 error getting details of user userid=24 from db

oldmatt what does the u do after user: user u btherl the error display shows this before the edit button: Notice: Undefined index: action in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 13 Notice: Undefined index: userid in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 14 Notice: Undefined index: search in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 15 Notice: Undefined index: status_search in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 16 Notice: Undefined index: status_not in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 21 this is with the select statment commented out so this is just listing of all the users. I haven't even clicked on the edit button when i click on the edit button i get these errors. Notice: Undefined index: search in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 15 Notice: Undefined index: status_search in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 16 Notice: Undefined index: status_not in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 21 string(2) "94" Notice: Undefined variable: query in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 381 Notice: Undefined variable: query in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 381 Fatal error: Call to undefined function mysql_last_error() in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 381 here is code for the whole file. I am so lost. thanks for your help!: <html> <head> <title>User Admin</title> <link rel="STYLESHEET" href="../style.css" type="text/css"> </head> <body> <center> <h1>User Admin</h1> </center> <script language="php"> ini_set('display_errors', 1); include("../db.php"); $action = $_GET['action']; $userid = $_GET['userid']; $search = $_GET['search']; $status_search = $_GET['status_search']; if (preg_match('/[^\d]/', $status_search) > 0) { die("invalid status search id"); } $status_not = $_GET['status_not']; if (preg_match('/[^\d]/', $status_not) > 0) { die("invalid status search id"); } if ($action == "delete") { if ($userid == "") { die("<p class=highlight>no userid specified</p>"); } if (preg_match('/[^\d]/', $userid) > 0) { die("<p class=highlight>invalid userid</p>"); } $query = "DELETE FROM user WHERE userid=$userid"; $result = mysql_query($query, $mysql_link) or die(mysql_error()); $query = "DELETE FROM student_session WHERE userid=$userid"; $result = mysql_query($query, $mysql_link) or die(mysql_error()); $query = "DELETE FROM session_login WHERE userid=$userid"; $result = mysql_query($query, $mysql_link) or die(mysql_error()); //below added by mark palmer to delete information from the new parent and parent_user tables when a user, future session and logins are deleted above. /*$query = "DELETE FROM parent_user WHERE userid=$userid"; AT THE SAME TIME OR BEFORE IT NEEDS TO PULL OUT ALL OF THE PARENT_ID WHERE USERID=$USERID SO IT CAN DELETE FROM PARENT ID WHERE PARENTID=$PARENTID $result = mysql_query($query, $mysql_link) or die(mysql_error()); $query = "DELETE FROM student_session WHERE userid=$userid"; $result = mysql_query($query, $mysql_link) or die(mysql_error()); $query = "DELETE FROM WHERE userid=$userid"; $result = mysql_query($query, $mysql_link) or die(mysql_error());*/ include("updatepasswd.php"); include("updateconfig.php"); print "<p class=highlight>User deleted</p>"; print "<meta http-equiv='Refresh' content='2; url=useradmin.php'>"; } elseif ($action == "update" || $action == "insert") { // error checking preg_match('/[\047\042],varible') searches for single and double quotes if (preg_match('/[^\d]/', $userid) > 0) { die("<p class=highlight>invalid userid</p>"); } $username = $_GET['username']; if (preg_match('/[^\w\.@ ]/', $username) > 0) { die("<p class=highlight>invalid username: must only contain letters, digits, spaces, dots, underscores and @</p>"); } $email = $_GET['email']; if ($email != "") { if (! (preg_match('/@/', $email))) { die("<p class=highlight>invalid email address</p>"); } if (! (preg_match('/\./', $email))) { die("<p class=highlight>invalid email address</p>"); } if (preg_match('/[ \047\042\|<>]/', $email) > 0) { die("<p class=highlight>invalid email address</p>"); } } $first_name = $_GET['first_name']; if (preg_match('/[\047\042]/', $first_name) > 0) { die("<p class=highlight>invalid first name</p>"); } $last_name = $_GET['last_name']; if (preg_match('/[\047\042]/', $last_name) > 0) { die("<p class=highlight>invalid last name</p>"); } //next group of lines below added by Mark Palmer // working $parent_first_name=$_GET['parent_first_name']; //var_dump($parent_first_name); if (preg_match('/[\047\042]/', $parent_first_name) > 0) { die("<p class=highlight>invalid parent last name</p>"); } $parent_last_name=$_GET['parent_last_name']; //var_dump($parent_last_name); if (preg_match('/[\047\042]/', $parent_last_name) > 0) { die("<p class=highlight>invalid parent last name</p>"); } //var_dump($parent_first_name); //var_dump($parent_last_name); //print("<tr><td>next</td></tr>"); // previous lines added by mark pamer $password = $_GET['password']; if (preg_match('/[\047\042]/', $password) > 0) { die("<p class=highlight>invalid password</p>"); } $phone = $_GET['phone']; if (preg_match('/[^ \d\.\/\(\)\+\-]/', $phone) > 0) { die("<p class=highlight>invalid phone number</p>"); } $street_address = $_GET['street_address']; if (preg_match('/[\047\042]/', $street_address) > 0) { die("<p class=highlight>invalid street_address</p>"); } $other_address = $_GET['other_address']; if (preg_match('/[\047\042]/', $other_address) > 0) { die("<p class=highlight>invalid other_address</p>"); } $city = $_GET['city']; if (preg_match('/[\047\042]/', $city) > 0) { die("<p class=highlight>invalid city</p>"); } $state = $_GET['state']; if (preg_match('/[^a-zA-Z]/', $state) > 0) { die("<p class=highlight>invalid state</p>"); } $zipcode = $_GET['zipcode']; if (preg_match('/[\047\042]/', $zipcode) > 0) { die("<p class=highlight>invalid zipcode</p>"); } $type = $_GET['type']; if ($type != "T" && $type != "A" && $type != "S" && $type != "K") { die("<p class=highlight>invalid type</p>"); } $school = $_GET['school']; if (preg_match('/[\047\042]/', $school) > 0) { die("<p class=highlight>invalid school</p>"); } $school_district = $_GET['school_district']; if (preg_match('/[\047\042]/', $school_district) > 0) { die("<p class=highlight>invalid school_district</p>"); } $double_gui = $_GET['double_gui']; if ($double_gui != "Y") { $double_gui = "N"; } $notes = $_GET['notes']; $status_id = $_GET['status_id']; if (preg_match('/[^0-9]/', $status_id) > 0) { die("<p class=highlight>invalid status</p>"); } if ($status_id == "") { $status_id = "null"; } if ($email == "") { die("<p class=highlight>email cannot be blank</p>"); } if ($username == "") { die("<p class=highlight>username cannot be blank</p>"); } if ($first_name == "") { die("<p class=highlight>first name cannot be blank</p>"); } if ($last_name == "") { die("<p class=highlight>last name cannot be blank</p>"); } if ($street_address == "") { die("<p class=highlight>street_address cannot be blank</p>"); } if ($city == "") { die("<p class=highlight>city cannot be blank</p>"); } if ($state == "") { die("<p class=highlight>state cannot be blank</p>"); } if ($zipcode == "") { die("<p class=highlight>zipcode cannot be blank</p>"); } if ($password == "") { die("<p class=highlight>password cannot be blank</p>"); } if ($type == "") { die("<p class=highlight>type cannot be blank</p>"); } if ($action == "insert") { // adding a new user if ($username == "") { die("<p class=highlight>username cannot be blank</p>"); } if ($company_id == "") { $company_id = "null"; } // david is using the sprintf to do some error checking. It is inserting all of the fields into the table user. it uses and string arg %s to get the esc checked field $notes $query = sprintf("insert into user values(null, '$username', '$email', '$first_name', '$last_name', '$password', '$phone', '$street_address', '$other_address', '$city', '$state', '$zipcode', '$type', '$school', '$school_district', current_timestamp, '$double_gui', $status_id, '%s')",mysql_real_escape_string($notes, $mysql_link)); mysql_query($query, $mysql_link) or die(mysql_error()); $user_id= mysql_insert_id(); // var_dump($query); // added by mark palmer inserts the null (for the pk) parents first and last name int the parent table $query2 = sprintf("insert into parent values(null,'$parent_first_name','$parent_last_name')"); //var_dump($query2); mysql_query($query2,$mysql_link) or die(mysql_error()); // next line gets the parent id automatically created by after the insert into parents $parent_id= mysql_insert_id(); var_dump($parent_id); var_dump($user_id); $query3= sprintf("insert into parent_user values(null, '$parent_id','$user_id')"); var_dump($query3); mysql_query($query3,$mysql_link) or die(mysql_error()); if ($status_id != 'null' && $status_id != '') { $uid = mysql_insert_id($mysql_link); $changed_by = $_SERVER['REMOTE_USER']; $changed_by_userid = 'null'; if ($changed_by != "") { $query = "select userid from user where username='$changed_by'"; $result = mysql_query($query, $mysql_link) or die(mysql_error()); if ($result) { if ($row = mysql_fetch_row($result)) { $changed_by_userid = $row[0]; } } } $query = "insert into user_status values(null, $status_id, $uid, current_timestamp, $changed_by_userid)"; mysql_query($query, $mysql_link) or die(mysql_error()); } print "<p class=highlight>User added</p>"; print "<a href=\"useradmin.php?action=add\">Add More Users</a><br>"; print "<a href=\"useradmin.php\">Back to User List</a><br>"; } else { if ($userid == "") { die("<p class=highlight>no userid specified</p>"); } // get old status so we can see if it changed $query = "select status_id from user where userid=$userid"; $result = mysql_query($query, $mysql_link) or die(mysql_error()); if((!result) || (! mysql_num_rows($result))) { die("<p class=highlight>error getting status of user userid=$userid from db</p>"); } if ($row = mysql_fetch_row($result)) { $old_status = $row[0]; } $query = sprintf("UPDATE user set username='$username', email='$email', first_name='$first_name', last_name='$last_name', password='$password', phone='$phone', street_address='street_address', other_address='$other_address', city='$city', state='$state', zipcode='$zipcode', type='$type', school='$school', school_district='$school_district',double_gui='$double_gui',status_id=$status_id,notes='%s' where userid=$userid", mysql_real_escape_string($notes, $mysql_link));; ; mysql_query($query, $mysql_link) or die(mysql_error()); if ($status_id != 'null' && $status_id != '' && $status_id != $old_status) { $changed_by = $_SERVER['REMOTE_USER']; $changed_by_userid = 'null'; if ($changed_by != "") { $query = "select userid from user where username='$changed_by'"; $result = mysql_query($query, $mysql_link) or die(mysql_error()); if ($result) { if ($row = mysql_fetch_row($result)) { $changed_by_userid = $row[0]; } } } $query = "insert into user_status values(null, $status_id, $userid, current_timestamp, $changed_by_userid)"; mysql_query($query, $mysql_link) or die(mysql_error()); } print "<p class=highlight>User details updated</p>"; print "<meta http-equiv='Refresh' content='2; url=useradmin.php'>"; } include("updatepasswd.php"); include("updateconfig.php"); } elseif ($action == "edit" || $action == "add") { if ($action == "edit") { if ($userid == "") { die("<p class=highlight>no userid specified</p>"); } if (preg_match('/[^\d]/', $userid) > 0) { die("<p class=highlight>invalid userid</p>"); } // get details from db // $query = "SELECT userid, username, email, first_name, last_name, password, phone, street_address, other_address, city, state, zipcode, type, school, school_district, DATE_ADD(date_registered, INTERVAL $time_diff HOUR),double_gui,status_id,notes from user where userid=$userid"; //$query = "SELECT userid, username, email, first_name, last_name, password, phone, street_address, other_address, city, state, zipcode, type, school, school_district, DATE_ADD(date_registered, INTERVAL $time_diff HOUR),double_gui,status_id,notes FROM user INNER JOIN parent_user ON user.userid=parent_user.userid; var_dump($userid); $result = mysql_query($query, $mysql_link) or die("Query failed: $query\n" . mysql_last_error()); //$result = mysql_query($query, $mysql_link); if((!result) || (! mysql_num_rows($result))) { die("<p class=highlight>error getting details of user userid=$userid from db</p>"); } if ($row = mysql_fetch_row($result)) { $userid = $row[0]; $username = $row[1]; $email = $row[2]; $first_name = $row[3]; $last_name = $row[4]; //$parent_last_name = $row[5]; $password = $row[5]; // no it didn't print "<tr><td> will this print next to the password??????</td></tr>"; $phone = $row[6]; $street_address = $row[7]; $other_address = $row[8]; $city = $row[9]; $state = $row[10]; $zipcode = $row[11]; $type = $row[12]; $school = $row[13]; $school_district = $row[14]; $date_registered = $row[15]; $double_gui = $row[16]; $status_id = $row[17]; $notes = $row[18]; if ($type == "S") { $student_selected = "selected"; } elseif ($type == "T") { $tutor_selected = "selected"; } elseif ($type == "A") { $admin_selected = "selected"; } elseif ($type == "K") { $tech_selected = "selected"; } } } // the line right below prints above the commented out print "where will this print????" line below. //print "<tr><td> really? where will this print??????</td></tr>"; print "<form>"; // when not commented the below line prints right above the table for editing a user //print "<tr><td> where will this print??????</td></tr>"; print "<table>"; if ($action == "edit") { print "<input type=hidden name=action value=update>"; print "<input type=hidden name=userid value='$userid'>"; } else { print "<input type=hidden name=action value=insert>"; } // action is not edit then has to be add mp print "<tr><td>Username:</td><td><input type=text maxlength=40 name=username value=\"$username\"> (REQUIRED)</td><tr>"; print "<tr><td>Status:</td><td><select name=status_id><option value=\"\"></option>"; // get statuses from db $query = "SELECT status_id,description from status"; $result = mysql_query($query, $mysql_link); if(!result) { die("<p class=highlight>error getting status info from db</p>"); } while ($row = mysql_fetch_row($result)) { $sid = $row[0]; $desc = $row[1]; $selected = ""; if ($sid == $status_id) { $selected = "selected"; } print "<option $selected value=\"$sid\">$desc</option>"; } print "</select><input type=submit onclick=\"window.open('statusadmin.php', 'status','width=600,height=600,scrollbars=yes'); return false\" value=\"ADD\"></td>"; if ($userid != "") { print "<td style='padding-left: 50' rowspan=100 valign=top><iframe src=\"statuslog.php?id=$userid\"></iframe></td>"; } print "</tr>"; print "<tr><td>Email: </td><td><input type=text maxlength=80 name=email value=\"$email\"> (REQUIRED)</td><tr>"; // next 2 lines below have been altered by mark palmer. I have added parent first name and last name in different positions. well first i added parent first and alst name then the last version is the reorder of the input fields // print "<tr><td>First Name:</td><td><input type=text maxlength=40 name=first_name value=\"$first_name\"> (REQUIRED)</td><td> </td><td>Parent First Name</td><td><input type=text maxlength=40 name=parent_first_name value=\"$parent_first_name\"> (REQUIRED)</td><tr>"; // print "<tr><td>Last Name:</td><td><input type=text maxlength=40 name=last_name value=\"$last_name\"> (REQUIRED)</td><td> </td><td>Parent Last Name</td><td><input type=text maxlength=40 name=parent_last_name value=\"$parent_last_name\"> (REQUIRED)<tr>"; print "<tr><td> First Name: </td><td><input type=text maxlength=40 name=first_name value=\"$first_name\"> (REQUIRED)</td><td> </td><td>Last Name:</td><td><input type=text maxlength=40 name=last_name value=\"$last_name\"> (REQUIRED)</td><tr>"; print "<tr><td>Parent First Name</td><td><input type=text maxlength=40 name=parent_first_name value=\"$parent_first_name\"> (REQUIRED)</td><td> </td><td>Parent Last Name</td><td><input type=text maxlength=40 name=parent_last_name value=\"$parent_last_name\"> (REQUIRED)</td><tr>"; print "<tr><td>Password:</td><td><input type=text maxlength=40 name=password value=\"$password\"> (REQUIRED)</td><tr>"; print "<tr><td>Phone:</td><td><input type=text maxlength=20 name=phone value=\"$phone\"></td><tr>"; print "<tr><td>Street Address:</td><td><input type=text maxlength=80 name=street_address value=\"$street_address\"> (REQUIRED)</td><tr>"; print "<tr><td>Other Address:</td><td><input type=text maxlength=80 name=other_address value=\"$other_address\"></td><tr>"; print "<tr><td>City:</td><td><input type=text maxlength=40 name=city value=\"$city\"> (REQUIRED)</td><tr>"; print "<tr><td>State:</td><td><input size=2 type=text maxlength=2 name=state value=\"$state\"> (REQUIRED)</td><tr>"; print "<tr><td>Zipcode:</td><td><input type=text maxlength=10 name=zipcode value=\"$zipcode\"> (REQUIRED)</td><tr>"; print "<tr><td>Type:</td><td><select name=type><option value='S' $student_selected>Student</option><option value='T' $tutor_selected>Tutor</option><option value='A' $admin_selected>Admin</option><option value='K' $tech_selected>Tech</option></select>"; print "<tr><td>School:</td><td><input type=text maxlength=80 name=school value=\"$school\"></td><tr>"; print "<tr><td>School District:</td><td><input type=text maxlength=80 name=school_district value=\"$school_district\"></td><tr>"; $checked = ""; if ($double_gui == "Y") { $checked = "checked"; } print "<tr><td>Use Double GUI:</td><td><input type=checkbox name=double_gui value=\"Y\" $checked></td><tr>"; print "<tr><td>Notes:</td><td><textarea name=\"notes\">$notes</textarea></td></tr>"; print "</table>"; //var_dump("$first_name"); //var_dump("$parent_first_name"); if ($action == "add") { print "<p><input type=submit value=\"Add User\"></p>"; } else { print "<p><input type=submit value=\"Update User Details\"></p>"; } print "</form>"; print "<hr>"; print "<p>Upload multiple students from CSV file:</p>\n"; print "<form enctype='multipart/form-data' action='csvupload.php' method='POST'>\n"; print "<input type='hidden' name='MAX_FILE_SIZE' value='100000000'>\n"; print "Choose a file to upload: <input name='csvfile' type='file'><br>\n"; print "<input type='submit' value='Upload CSV File'>\n"; print "</form>\n"; } elseif ($action == "") { $extra_tables = ""; $qual = ""; if ($userid != "") { if (preg_match('/[^\d]/', $userid) > 0) { die("<p class=highlight>invalid userid</p>"); } $qual = "where userid=$userid"; } if ($search != "") { $search = mysql_real_escape_string($search, $mysql_link); $qual = "where (username like '%$search%' or last_name like '%$search%' or email like '%$search%')"; } if ($status_search != "") { $extra_tables = ",user_status"; $qual = "where user_status.status_id=$status_search and user.userid=user_status.userid"; } // get list of all users // prints the table of all the user in the system. mp $query = "SELECT distinct user.userid, username, email, first_name, last_name, password, phone, street_address, other_address, city, state, zipcode, type, school, school_district, DATE_FORMAT(DATE_ADD(date_registered, INTERVAL $time_diff HOUR), '%m/%d/%y %l:%i%p'),double_gui,status.description,notes from (user$extra_tables) left join status on status.status_id=user.status_id $qual order by username"; $result = mysql_query($query, $mysql_link) or die(mysql_error()); // the print statement below prints the table for each varibale. need to add a column here before you fetch a new varible or print a new varible. or it will just copy over something. print "<table border=1><th>Userid</th><th>Username</th><th>Email</th><th>First Name</th><th>Last Name</th><th>Password</th><th>Phone</th><th>Street Address</th><th>Other Address</th><th>City</th><th>State</th><th>Zipcode</th><th>Type</th><th>School</th><th>School District</th><th>Sessions Attended</th><th>Date Registered</th><th>Double GUI</th><th>Status</th><th>Notes</th><th>DELETE</th><th>EDIT</th></tr>"; if($result && mysql_num_rows($result)) { while ($row = mysql_fetch_row($result)) { $userid = $row[0]; $username = $row[1]; $email = $row[2]; $first_name = $row[3]; $last_name = $row[4]; $password = $row[5]; $phone = $row[6]; $street_address = $row[7]; $other_address = $row[8]; $city = $row[9]; $state = $row[10]; $zipcode = $row[11]; $type = $row[12]; if ($type == "S") { $type = "Student"; $student_selected = "selected"; } elseif ($type == "T") { $type = "Tutor"; $tutor_selected = "selected"; } elseif ($type == "A") { $type = "Admin"; $admin_selected = "selected"; } elseif ($type == "K") { $type = "Tech"; $tech_selected = "selected"; } $school = $row[13]; $school_district = $row[14]; $date_registered = $row[15]; $double_gui = $row[16]; $status_desc = $row[17]; $notes = $row[18]; if ($double_gui != "Y") { $double_gui = "N"; } if ($status_not != "") { $query2 = "select * from user_status where status_id=$status_not and userid=$userid"; $result2 = mysql_query($query2, $mysql_link) or die(mysql_error()); if($result2 && mysql_num_rows($result2)) { // ignore this one continue; } } print "<tr>"; print "<td>$userid</td>"; print "<td>$username</td>"; print "<td>$email</td>"; print "<td>$first_name</td>"; print "<td>$last_name</td>"; print "<td>$password</td>"; print "<td>$phone</td>"; print "<td>$street_address</td>"; print "<td>$other_address</td>"; print "<td>$city</td>"; print "<td>$state</td>"; print "<td>$zipcode</td>"; print "<td>$type</td>"; print "<td>$school</td>"; print "<td>$school_district</td>"; print "<td><a href=\"listlogins.php?userid=$userid\">Sessions</a></td>"; print "<td>$date_registered</td>"; print "<td>$double_gui</td>"; print "<td>Current status: $status_desc<br><button onclick=\"window.open('statuslog.php?id=$userid', 'statuslog$userid', 'width=600, height=600,scrollbars=yes')\">Show Log</button></td>"; print "<td>$notes</td>"; print "<td><form><input type=\"hidden\" name=\"action\" value=\"delete\"><input type=\"hidden\" name=\"userid\" value=\"$userid\"><input type=submit value=\"Delete\"></td></form>"; print "<td><form><input type=\"hidden\" name=\"action\" value=\"edit\"><input type=\"hidden\" name=\"userid\" value=\"$userid\"><input type=submit value=\"Edit\"></form>"; print "</tr>"; } } else { print "<tr><td colspan=19>No users</td></tr>"; } print "</table>"; print "<form><input type=\"hidden\" name=\"action\" value=\"add\"><input type=submit value=\"Add New User\"></form>"; } </script> <a href=index.html>Back to Admin Home</a> </body> </html>

$query = "SELECT userid, username, email, first_name, last_name, password, phone, street_address, other_address, city, state, zipcode, type, school, school_district, DATE_ADD(date_registered, INTERVAL $time_diff HOUR),double_gui,status_id,notes FROM user JOIN parent_user USING(userid) JOIN parent USING(parent_id) where userid=$userid; this is the select statement that I am using. pthberl: I changed the result statment to: $result = mysql_query($query, $mysql_link) or die("Query failed: $query\n" . mysql_last_error()); I am still just getting a blank white screen. no error message. I am using 3 tables: user, parent_user, parent. I used joins I think so at least! here: and no i didn't understand that concept madlittlemods... FROM user JOIN parent_user USING(userid) JOIN parent USING(parent_id) where userid=$userid;

In php and sql how can i use multiple tables to populate a form for updating user information? Here is a simplified table structure: user userid PK first_name last_name parent parent_id PK parent_first_name parent_last name parent_user parent_user_id PK parent_id FK userid FK Here is what I have tried. I need to use the new table parent and parent_user to populate parent_first_name and parent_last_name. I think i need to JOIN the info in the select statement: Here is the select statement I was trying to use but its not working: $query = "SELECT user.userid, user.username, user.email, user.first_name, user.last_name, user.password, user.phone, user.street_address, user.other_address, city, state, zipcode, type, school, school_district, DATE_ADD(user.date_registered, INTERVAL $time_diff HOUR),user.double_gui,user.status_id,use… parent.parent_first_name from user, parent_user, parent where user.userid=$userid and parent_user.userid=$userid and parent.parent_id=$userid"; I also tried this: $query = "SELECT userid, username, email, first_name, last_name, password, phone, street_address, other_address, city, state, zipcode, type, school, school_district, DATE_ADD(date_registered, INTERVAL $time_diff HOUR),double_gui,status_id,notes FROM user JOIN parent_user USING(userid) JOIN parent USING(parent_id) where userid=$userid; this is the original working code that just uses the user table: query = "SELECT userid, username, email, first_name, last_name, password, phone, street_address, other_address, city, state, zipcode, type, school, school_district, DATE_ADD(date_registered, INTERVAL $time_diff HOUR),double_gui,status_id,notes from user where userid=$userid"; $result = mysql_query($query, $mysql_link); if((!result) || (! mysql_num_rows($result))) { die("<p class=highlight>error getting details of user userid=$userid from db</p>"); } if ($row = mysql_fetch_row($result)) { $userid = $row[0]; $username = $row[1]; $email = $row[2]; $first_name = $row[3]; $last_name = $row[4]; //$parent_last_name = $row[5]; $password = $row[5]; // no it didn't print "<tr><td> will this print next to the password??????</td></tr>"; $phone = $row[6]; $street_address = $row[7]; $other_address = $row[8]; $city = $row[9]; $state = $row[10]; $zipcode = $row[11]; $type = $row[12]; $school = $row[13]; $school_district = $row[14]; $date_registered = $row[15]; $double_gui = $row[16]; $status_id = $row[17]; $notes = $row[18]; if ($type == "S") { $student_selected = "selected"; } elseif ($type == "T") { $tutor_selected = "selected"; } elseif ($type == "A") { $admin_selected = "selected"; } elseif ($type == "K") { $tech_selected = "selected"; } } } print "<form>"; print "<table>"; if ($action == "edit") { print "<input type=hidden name=action value=update>"; print "<input type=hidden name=userid value='$userid'>"; } else { print "<input type=hidden name=action value=insert>"; } // action is not edit then has to be add mp print "<tr><td>Username:</td><td><input type=text maxlength=40 name=username value=\"$username\"> (REQUIRED)</td><tr>"; print "<tr><td>Status:</td><td><select name=status_id><option value=\"\"></option>";

do you mean add those tags? will that put them in a scroll box??