Affected rows not registering

[soycharliente]

The query works. Rows are inputted into the table. That's why I'm posting this in the PHP forum. I don't feel like it's an MySQL problem. The only way that this bit of code will work is if I change the affected rows test to equal 0 for the check.

 

Why is that?

 

<?php
$sql = "INSERT INTO `email_register` (`email`) VALUES ('{$email}')";
$result = mysql_query($sql, $conn) OR trigger_error("SQL", E_USER_ERROR);
$rows = mysql_affected_rows($result);

if ($rows == 1)
{
header("Location: thankyou.php");
exit;
} else
{
echo "ERROR.";
}
?>

[revraz]

Because INSERTed Rows are not "affected", only UPDATEd rows are.

[soycharliente]

From the manual:

Description

int mysql_affected_rows ([ resource $link_identifier ] )

 

Get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE query associated with link_identifier .

 

@revraz: Please explain to me how mysql_affected_rows doesn't apply to an INSERT statement.

[PFMaBiSmAd]

 

If you execute this on a server with error_reporting set to E_ALL and display_errors set to ON, it will probably tell you why it is failing. The parameter mysql_affected_rows() expects is not a result resource and in fact an INSERT does not return a result resource anyway.

 

Always learn php, develop php code, or debug php code on a system with error_reporting set to E_ALL and display_errors set to ON to get php to help you.

[soycharliente]

I don't really understand what you're suggesting. The query works. What errors are you talking about?

[hobeau]

Because INSERTed Rows are not "affected", only UPDATEd rows are.

 

Sorry revraz, but inserted rows definitely do return an integer to the function mysql_affected_rows that gives you the number of inserted rows into the database.

 

If you execute this on a server with error_reporting set to E_ALL and display_errors set to ON, it will probably tell you why it is failing. The parameter mysql_affected_rows() expects is not a result resource and in fact an INSERT does not return a result resource anyway.

 

Always learn php, develop php code, or debug php code on a system with error_reporting set to E_ALL and display_errors set to ON to get php to help you.

 

PFMaBiSmAd you are correct. The mysql_affected_rows function requires the handle or link variable to the mysql_connect object (in this case $conn).

 

 

charlieholder here is some code that may help:

<?php
$conn = mysql_connect('host', 'username', 'password') or die(mysql_error());
mysql_select_db('database') or die(mysql_error());
$sql = "INSERT INTO `email_register` (`email`) VALUES ('" . mysql_escape_string($email) . "')";
$result = mysql_query($sql, $conn) OR die(mysql_error());
$num_rows = mysql_affected_rows($conn);
         
echo $num_rows;
?>

 

A couple things to note here. First we escape the $email variable with the mysql_escape_string function. Very important to remember to guard against sql injection (it happens more than you think). Second, if you end your commands with 'or die(mysql_error())', this function asks mysql what the last error was and then the 'die' function stops your script and displays the error. This is not something you want to put in a production environment as it can expose sensitive information about your database, but for debugging purposes this can help.

 

One last thing, you may want to look into using PDO (PHP Data Objects) to connect to mysql instead of the mysql extensions. PHP is moving towards using PDO as it's default database abstraction layer as it can connect to many different databases and it has important features such as prepared queries etc... Just a thought. Hope this helps!!