ellegua220 Posted March 9, 2011 Share Posted March 9, 2011 Whats wrong with my php/sql? I get a blank screen when these statements are run. they are part of a larger program. the values of $userid and $parent_id are retrieved by the mysql_insert statements. The query3=sprintf(...= seems to be the problem: $userid= mysql_insert_id(); $parent_id= mysql_insert_id(); var_dump($parent_id); var_dump($userid); query3= sprintf("insert into parent_user values(null, '$parent_id','$userid')"); var_dump($query3); i have 3 tables for simplicity i will only list a few fields: 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 Additional Details $query2 = sprintf("insert into parent values(null,'$parent_first_name','$paren… this line works and is sent to mysql mysql_query($query2,$mysql_link) or die(mysql_error()); these two statments are working perfectly here is the full code: <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"> 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 where userid=$userid AND join parent_user on userid AND join parent on parent_id"; $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> MOD EDIT: . . . tags added. Link to comment https://forums.phpfreaks.com/topic/230064-sprint-problem/ Share on other sites More sharing options...
Pikachu2000 Posted March 9, 2011 Share Posted March 9, 2011 When posting code, please enclose it within the forum's . . . BBCode tags. Link to comment https://forums.phpfreaks.com/topic/230064-sprint-problem/#findComment-1184896 Share on other sites More sharing options...
ellegua220 Posted March 10, 2011 Author Share Posted March 10, 2011 do you mean add those tags? will that put them in a scroll box?? Link to comment https://forums.phpfreaks.com/topic/230064-sprint-problem/#findComment-1185255 Share on other sites More sharing options...
Pikachu2000 Posted March 10, 2011 Share Posted March 10, 2011 It will make it look like it does in your post above (I already added them there) instead of being a wall of code on the page. Link to comment https://forums.phpfreaks.com/topic/230064-sprint-problem/#findComment-1185257 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.