INSERT statement not working inside of WHILE loop from SELECT query

[Hobbyist_PHPer]

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]')");

[Pikachu2000]

And what about it doesn't work?

[Hobbyist_PHPer]

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]')");

[Maq]

Put an or die(mysql_error()) at the end of your insert line.  It's probably just failing.

[gizmola]

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']}')");

[Hobbyist_PHPer]

Ah, it's the apostrophe 

[Hobbyist_PHPer]

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

[gizmola]

Yeah for text data you always want to run mysql_real_escape_string() on it before trying to insert or update.

[Muddy_Funster]

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?

[Pikachu2000]

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)

[Hobbyist_PHPer]

So all in all, what's the best way to insert POST data into the db?

[Pikachu2000]

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.

[Hobbyist_PHPer]

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...