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]')"); 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? 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]')"); 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. 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']}')"); 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 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 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. 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? 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) 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? 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. 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... 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
Archived
This topic is now archived and is closed to further replies.