Hobbyist_PHPer Posted April 27, 2011 Share Posted April 27, 2011 Hi, I am trying to insert an entry into an access log when someone views a record, but I can not, for the life of me, understand why it will not work... Here's my code... $query = mysql_query("SELECT * FROM Clients WHERE ClientID = '$_GET[clientid]'"); while ($row = mysql_fetch_assoc($query)) { $log_text = ''; $log_text = 'Client '.$row['ClientID'].' - '.$row['ClientFirstName'].' '.$row['ClientLastName'].'\'s information was accessed from the database.'; mysql_query("INSERT INTO AccessLogs VALUES ('','$_SESSION[userID]','',NOW(),'$log_text','$_SERVER[REMOTE_ADDR]')"); Quote Link to comment https://forums.phpfreaks.com/topic/234893-insert-statement-not-working-inside-of-while-loop-from-select-query/ Share on other sites More sharing options...
Pikachu2000 Posted April 27, 2011 Share Posted April 27, 2011 And what about it doesn't work? Quote Link to comment https://forums.phpfreaks.com/topic/234893-insert-statement-not-working-inside-of-while-loop-from-select-query/#findComment-1207129 Share on other sites More sharing options...
Hobbyist_PHPer Posted April 27, 2011 Author Share Posted April 27, 2011 It just doesn't insert into the database The other piece of code that uses that AccessLog INSERT statement, that you helped me with, works perfectly... $query = mysql_query("SELECT * FROM Clients WHERE ClientID = '$_GET[clientid]'"); $arr1 = mysql_fetch_assoc($query); $theStrippedBusinessPhoneNo = stripPhone($_POST['ClientBusinessNum']); $theStrippedHomePhoneNo = stripPhone($_POST['ClientHomeNum']); $theStrippedMobilePhoneNo = stripPhone($_POST['ClientMobileNum']); $theStrippedFaxPhoneNo = stripPhone($_POST['ClientFaxNum']); $theStrippedSSN = stripSSN($_POST['ClientSSN']); $ClientDOBComplete = $_POST['ClientDOBYear'].'-'.$_POST['ClientDOBMonth'].'-'.$_POST['ClientDOBDay']; $query = mysql_query("UPDATE Clients SET ClientFirstName = '$_POST[ClientFirstName]', ClientMiddleName = '$_POST[ClientMiddleName]', ClientLastName = '$_POST[ClientLastName]', ClientMaidenName = '$_POST[ClientMaidenName]', ClientBusinessNum = '$theStrippedBusinessPhoneNo', ClientBusinessExt = '$_POST[ClientBusinessExt]', ClientHomeNum = '$theStrippedHomePhoneNo', ClientMobileNum = '$theStrippedMobilePhoneNo', ClientFaxNum = '$theStrippedFaxPhoneNo', ClientEmail = '$_POST[ClientEmail]', ClientAddress = '$_POST[ClientAddress]', ClientCity = '$_POST[ClientCity]', ClientState = '$_POST[ClientState]', ClientZipCode = '$_POST[ClientZipCode]', ClientSSN = '$theStrippedSSN', ClientDOB = '$ClientDOBComplete', ClientTaxIDNum = '$_POST[ClientTaxIDNum]', ClientEmployer = '$_POST[ClientEmployer]', ClientOccupation = '$_POST[ClientOccupation]', ClientNotes = '$_POST[ClientNotes]' WHERE ClientID = '$_GET[clientid]'"); $results = mysql_query($query); $query = mysql_query("SELECT * FROM Clients WHERE ClientID = '$_GET[clientid]'"); $arr2 = mysql_fetch_assoc($query); $before = array_diff($arr1, $arr2); $after = array_diff($arr2, $arr1); $log_text = ''; foreach($before as $k => $v) { $log_text .= "$k was changed from {$before[$k]} to {$after[$k]},"; } mysql_query("INSERT INTO AccessLogs VALUES ('','$_SESSION[userID]','',NOW(),'$log_text','$_SERVER[REMOTE_ADDR]')"); Quote Link to comment https://forums.phpfreaks.com/topic/234893-insert-statement-not-working-inside-of-while-loop-from-select-query/#findComment-1207140 Share on other sites More sharing options...
Maq Posted April 27, 2011 Share Posted April 27, 2011 Put an or die(mysql_error()) at the end of your insert line. It's probably just failing. Quote Link to comment https://forums.phpfreaks.com/topic/234893-insert-statement-not-working-inside-of-while-loop-from-select-query/#findComment-1207142 Share on other sites More sharing options...
gizmola Posted April 27, 2011 Share Posted April 27, 2011 You are not checking the result of your mysql_query for the insert. Chances are you have a syntax error. It's also best practice on inserts, to include the column list: INSERT INTO AccessLogs (col1, col2...etc) VALUES ... You can then exclude columns that you don't need from the values list, and also be assured that you will not have an error. You can also include the single quotes around array keys simply by specifying a php block around those values. The code is clearer, and PHP does not have to try and resolve constants. mysql_query("INSERT INTO AccessLogs VALUES ('','{$_SESSION['UserID']}', '', NOW(), '$log_text',' {$_SERVER['REMOTE_ADDR']}')"); Quote Link to comment https://forums.phpfreaks.com/topic/234893-insert-statement-not-working-inside-of-while-loop-from-select-query/#findComment-1207144 Share on other sites More sharing options...
Hobbyist_PHPer Posted April 27, 2011 Author Share Posted April 27, 2011 Ah, it's the apostrophe Quote Link to comment https://forums.phpfreaks.com/topic/234893-insert-statement-not-working-inside-of-while-loop-from-select-query/#findComment-1207146 Share on other sites More sharing options...
Hobbyist_PHPer Posted April 27, 2011 Author Share Posted April 27, 2011 You are not checking the result of your mysql_query for the insert. Chances are you have a syntax error. It's also best practice on inserts, to include the column list: INSERT INTO AccessLogs (col1, col2...etc) VALUES ... You can then exclude columns that you don't need from the values list, and also be assured that you will not have an error. You can also include the single quotes around array keys simply by specifying a php block around those values. The code is clearer, and PHP does not have to try and resolve constants. mysql_query("INSERT INTO AccessLogs VALUES ('','{$_SESSION['UserID']}', '', NOW(), '$log_text',' {$_SERVER['REMOTE_ADDR']}')"); K, thanks, will take your advice and make my code better Quote Link to comment https://forums.phpfreaks.com/topic/234893-insert-statement-not-working-inside-of-while-loop-from-select-query/#findComment-1207148 Share on other sites More sharing options...
gizmola Posted April 27, 2011 Share Posted April 27, 2011 Yeah for text data you always want to run mysql_real_escape_string() on it before trying to insert or update. Quote Link to comment https://forums.phpfreaks.com/topic/234893-insert-statement-not-working-inside-of-while-loop-from-select-query/#findComment-1207149 Share on other sites More sharing options...
Muddy_Funster Posted April 28, 2011 Share Posted April 28, 2011 Yeah for text data you always want to run mysql_real_escape_string() on it before trying to insert or update. doesn't that have the potential to cause errors if magic_quotes is enabled? Quote Link to comment https://forums.phpfreaks.com/topic/234893-insert-statement-not-working-inside-of-while-loop-from-select-query/#findComment-1207570 Share on other sites More sharing options...
Pikachu2000 Posted April 28, 2011 Share Posted April 28, 2011 Checking for magic_quotes should be part of the escaping routine, using get_magic_quotes_gpc. If it returns true, apply stripslashes() prior to escaping. (If you're unable to simply turn magic_quotes off) Quote Link to comment https://forums.phpfreaks.com/topic/234893-insert-statement-not-working-inside-of-while-loop-from-select-query/#findComment-1207612 Share on other sites More sharing options...
Hobbyist_PHPer Posted April 28, 2011 Author Share Posted April 28, 2011 So all in all, what's the best way to insert POST data into the db? Quote Link to comment https://forums.phpfreaks.com/topic/234893-insert-statement-not-working-inside-of-while-loop-from-select-query/#findComment-1207627 Share on other sites More sharing options...
Pikachu2000 Posted April 28, 2011 Share Posted April 28, 2011 There's no single right answer that covers all types of data, but generally speaking you should: - Trim leading and trailing spaces - Validate that the data matches what you'd expect it to be. i.e. people's names don't contain numeric characters, etc. - Meets minimum and maximum length/value expected. i.e. a telephone number wouldn't be 3 digits, nor would it be 36. - Data that that is expected to be numeric should be cast as the appropriate numeric data type. - String data must be properly escaped One caveat: If the data will be hashed (such as with MD5, SHA1, etc.) before insertion into the database, it should not be escaped, and in some cases not trimmed. Quote Link to comment https://forums.phpfreaks.com/topic/234893-insert-statement-not-working-inside-of-while-loop-from-select-query/#findComment-1207647 Share on other sites More sharing options...
Hobbyist_PHPer Posted April 28, 2011 Author Share Posted April 28, 2011 There's no single right answer that covers all types of data, but generally speaking you should: - Trim leading and trailing spaces - Validate that the data matches what you'd expect it to be. i.e. people's names don't contain numeric characters, etc. - Meets minimum and maximum length/value expected. i.e. a telephone number wouldn't be 3 digits, nor would it be 36. - Data that that is expected to be numeric should be cast as the appropriate numeric data type. - String data must be properly escaped One caveat: If the data will be hashed (such as with MD5, SHA1, etc.) before insertion into the database, it should not be escaped, and in some cases not trimmed. Thank you very much for that... That is a lot of great information that I will start integrating into my code... Quote Link to comment https://forums.phpfreaks.com/topic/234893-insert-statement-not-working-inside-of-while-loop-from-select-query/#findComment-1207664 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.