mysql error

[khsater]

Hey.
I'm trying to make an activation script for a website, but for some reason my sql query is giving me errors.

$sql = 'UPDATE user
		SET active = 1
		WHERE (password = "$_GET[\'hash\']") AND (timestamp = ".base64_decode($_GET[\'stamp\'].")';
$result = mysql_query ($sql) or die (mysql_error());

if( mysql_num_rows($result)==1 )
{
	echo'Activated succesfully!';
}
else
{
	echo 'There was an error.';
}

I'm pretty new to PHP and MySql, so I can't figure this one out. A link with both "hash" and a base64 encoded timestamp leads to this page. Did I screw up with the quotation marks?
Thanks for the help!

EDIT: Oh, and here's the output of mysql_error():
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home2/smcmindi/public_html/rd/activate.php on line 13
Line 13 is:
if( mysql_num_rows($result)==1 )

[MVied]

Yes, your quotations are all kinds of messed up. I'll walk you through what's wrong so you don't make the same mistake again.

$sql = 'UPDATE user
		SET active = 1
		WHERE (password = "$_GET[\'hash\']") AND (timestamp = ".base64_decode($_GET[\'stamp\'].")';
$result = mysql_query ($sql) or die (mysql_error());

if( mysql_num_rows($result)==1 )
{
	echo'Activated succesfully!';
}
else
{
	echo 'There was an error.';
}

So, you've started out with this:

$sql = '';

Therefore, every time you have another ' in the code and you're not seperating a PHP variable from the plain text of the SQL query, you need to escape it. Single quotes are very commonly used it SQL queries, so I find it's best to use double quotations to wrap them. So let's change that first.

$sql = "UPDATE user
		SET active = 1
		WHERE (password = "$_GET[\'hash\']") AND (timestamp = ".base64_decode($_GET[\'stamp\'].")";

Now then, all throughout this statement there are quotation mismatches. Let's fix them one-by-one.

WHERE (password = "$_GET[\'hash\']")

When seperating a variable from text in a PHP string, you always concatenate with a period. So this part of the statement should look like this.

WHERE (password = '".$_GET['hash']."')

Now you may be a bit confused by the single and double quote next to each other. This is so the SQL query will actually come out like this.

WHERE (password = 'userspassword')

Now note that the single quotes are not required, but I've always found it best practice to use them if you're checking for a string. Numbers are better to leave out of quotes.

The double quote with the period end the text and begin the PHP variable. You use this same method when separating text and PHP functions.

AND (timestamp = '".base64_decode($_GET['stamp'])."')";

At the end you had single quotes, but earlier we changed those to double. If you start with a single quote, continue using single quotes throughout the string. Do not mix and match. You do not need to escape the single quotes because they are not plain text, but part of a PHP variable. Also, you forget the ending ) on your PHP function. So, now let's put all of this together.

$sql = "UPDATE user
		SET active = 1
		WHERE (password = '".$_GET['hash']."') AND (timestamp = '".base64_decode($_GET['stamp'])."')";

I hope all of that made sense, and I hope you learned something from all of it.

[khsater]

Wow, that was a lot of help. I really appreciate it. It makes much more sense now, and yes, I learned a lot.

[MVied]

Excellent, I'm glad to help. Also, the mysql_num_rows error usually comes when the SQL query that it's counting from is an invalid query. If you have access to PhpMyAdmin and you see errors that you think are SQL related, try running the query through PhpMyAdmin and replace the PHP variables with something that would work and see if the query actually runs. PHP won't give you errors that are caused by invalid SQL queries, but you can test your queries out in PhpMyAdmin to trouble shoot them.

[khsater]

Well, I thought the problem was solved, but apparently it isn't. Whenever I access this page, I still get the

mysql_num_rows(): supplied argument is not a valid MySQL result resource

error. The query runs successfully in PHPMyAdmin when I manually enter the same values, but in this script, the error occurs. Don't worry about afunctions.php that really only holds the $mconn variable. Thanks again for the help.
I've included the full script in this post. Obviously, the error occurs at line 22.

<?
include "afunctions.php";
?>
<br />
<?
$hash= $_GET['hash'];
$stamp=base64_decode($_GET['stamp']);
echo $hash;
?>
<br />
<?
echo $stamp;
?>
<br />
<?
$sql = "UPDATE user
		SET active = 1
		WHERE (password = '".$hash."') 
		AND (timestamp = '".$stamp."')";
$result = mysql_query ($sql, $mconn) or die (mysql_error());

if( mysql_num_rows($result)==1 )
{
	echo'Activated succesfully!';
}
else
{
	echo 'There was an error.';
}
?>

[MVied]

Yes, the SQL query you're running will not return any rows as you are not selecting anything from the database, so the function has no rows to count. :P

Try using this.

<?
include "afunctions.php";
?>
<br />
<?
$hash= $_GET['hash'];
$stamp=base64_decode($_GET['stamp']);
echo $hash;
?>
<br />
<?
echo $stamp;
?>
<br />
<?
$sql = "UPDATE user
		SET active = 1
		WHERE (password = '".$hash."') 
		AND (timestamp = '".$stamp."')";
$result = mysql_query ($sql, $mconn) or die (mysql_error());

if( $result )
{
	echo'Activated succesfully!';
}
else
{
	echo 'There was an error.';
}
?>