MySQL code not working

Hi all,
I'm building a registration form in PHP and its all good except when I try to test the given username against one in my MySQL database, so that there are no double ups on usernames.

Now the code I want would look like this/;

$usercheck = $_POST['username'];
$test = mysql_query("SELECT username FROM login_info WHERE username = '$usercheck'");
$test2 = mysql_num_rows($test) or die("Failed at test2 construction");

//if the name exists it gives an error
if ($test2 != 0) {
die("Username already in use.");
}

PHP:

However it only works if instead of having the variable $usercheck in the WHERE clause I have an actual string.
Is there a way to do: WHERE username = variable Or is there a work around.

Thanks

 

$sql= "SELECT username FROM login_info WHERE username = '".$usercheck."'";
$test = mysql_query($sql);

Take a look if is running

 

original code should also work. what version of PHP do you use?

 

Well I tried the code you suggested jakomo but it still returns an error of: "Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/www/host.server.com/upload/register.php on line 74
Failed at test2 construction"

This is the exact code:

// checks if the username is in use
if (!get_magic_quotes_gpc()) {
$_POST['username'] = addslashes($_POST['username']) or die("user slashadd");
}
$usercheck = $_POST['username'];
$sql= "SELECT username FROM login_info WHERE username = '".$usercheck."'";
$test2 = mysql_num_rows($sql) or die("Failed at test2 construction");

//if the name exists it gives an error
if ($test2 != 0) {
die("Username already in use.");
}

PHP:

for that section.

The server I'm running this from is using PHP version 4.4.1 if that helps too.

Not sure what more I can do as I'm not that experienced with MySQL, hopefully one of you guys can help me though

 

echo $usercheck;

PHP:

--> Will let you know IF the value is being posted to the script,

Common problem with sql failing after post statments are the value wasnt posted correct

Shane

 

like mentioned before it sounds like you're not actually getting a value from the $_POST[...]

what i suggest you do is echo that whole sql variable u've created.. and if u can't immediately see the cause run it against mysql via phpMyadmin or something...

and if still no luck post it here.. i'll gladly have a look at it.

 

Ok guys I'll try echoing out the values and then I'll post back with my results, thanks.

 

OK, running this code:

$usercheck = $_POST['username'];
echo $usercheck;
$sql= "SELECT username FROM login_info WHERE username = '".$usercheck."'";
echo $sql;
$test2 = mysql_num_rows($sql) or die("Failed at test2 construction");
echo $test2;

//if the name exists it gives an error
if ($test2 != 0) {
die("Username already in use.");
}

PHP:

I get an error return of:
"bobSELECT username FROM login_info WHERE username = 'bob'
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/www/my.server.com/upload/register.php on line 76
Failed at test2 construction"

Line 76 = $test2 = mysql_num_rows...
Bob = test username I entered. So the first 'bob' is there because $usercheck is working right and the $sql, by looking at it looks like it is all good. Then nothing is echoed for $test2 which suggests to me that that is where the problem lies.

However I am new to MySQL and I don't see what else I can do.

Please save me DP!

 

Try hand-typing in a username that you know exists in the database in your WHERE statement, instead of using the $usercheck variable. Does that work?

 

Hello,

In the code you need to add (bold)

$sql= "SELECT username FROM login_info WHERE username = '".$usercheck."'";
echo $sql;

$result= mysql_query($sql);

$test2 = mysql_num_rows($result) or die("Failed at test2 construction");
echo $test2;

Hope, this must to run

Best,
Jakomo

 

Tried what was suggested. 1) When I enter a username I know is in the database it gives the message it should of "Username already in use." and echos out a "1" which is how many rows its found, so that's all correct.

2) When I put the extra

$sql= "SELECT username FROM login_info WHERE username = '".$usercheck."'";
echo $sql;

$result= mysql_query($sql);

$test2 = mysql_num_rows($result) or die("Failed at test2 construction");
echo $test2;

PHP:

$result line it still fails with an error of "Failed at test2 construction".

Sooo, I can only conclude that unless there is something that I am seriously missing and we are all blind, I can't (although I should be able to) enter a variable into the WHERE clause.

Now, does anyone else have some code which I could test which they know works and has a WHERE being used similar to what I'm doing, so that I can test.

Thanks

 

the only possible thing i can think of is that if no rows are found php interprets that as an error.. as usually if no rows are found ur $test2 variable would most likely return NULL or something that indicates it doesn't exist... which can confuse php..

try putting the or die script on the actual query.. something like:

$result= mysql_query($sql) or die("Failed at result construction");

then just leave next line as:

$test2 = mysql_num_rows($result);
echo $test2;

Now try with an user that exists and one that doesn't exist... if this works now my theory is correct...

 

fabriciogr; I fail to see the logic in your proposal, nevertheless I tried it but it still returns Failed at test2 construction. Obviously I didn't take off the or die from that section as you said because it being there or not wont effect the actual returning of values.
However your thinking about returning NULL sparked me onto adding

if (test2 == NULL)
	{ echo "NULL"; }
	if (test2 == FALSE)
	{ echo "FALSE"; }

PHP:

and taking out the or die("Failed at test2 construction");

Now when I ran the code nothing came through except an error for further down the page. So I ran the code again with a username which I know exists and what do you know? It returned "Username already exists".

So for some reason, adding the or die(""); to mysql_num_rows($result) must somehow screw it up and nothing was ever added even though the code should have worked because their was an input error further on for actually adding the entry to the DB.
Ah the joys of coding.

If I have any further problems I will make another thread because I should be OK now! Thanks for everyone's help!

PS Remember this problem for later/try it yourself

 

sorry my explanation was so bad.. i'm usually carrying over a pretty bad hangover...

anyhow.. yes.. it's what i suspected the or die on num_rows would error if no rows were found...

in other words it seems u can't use or die on a num_rows