If statement problem (PHP)

drew22299 posted this at 19:12 — 26th March 2008.

Joined: Mar 2006

Hi,

I have tried for hours to get the PHP IF statement logic to work but can't get it to do what I want. Any suggestions?

I want to stop users from registering a username that is already in the database, for some reason, no matter where I put the {} in the If statements (at the end of the code) it either registers any username, won't register any!

$sql2 = "SELECT username FROM `users` WHERE username='$username'";
$result2 = mysql_query($sql2);
if($result2) {
echo "Cannot create username" . mysql_error();
} else {
echo "Username can be created";

$query = "INSERT INTO `users` (username, password, email)
VALUES ('$username', '$password', '$email')";
$result = mysql_query($query);
if(!$result) {
echo "Your query failed. " . mysql_error();
} else {
echo "Welcome " . $username . " You are now registered";
} // end of else
} // end of first else
}
?>

teammatt3 posted this at 19:57 — 26th March 2008.

He has: 2,102 posts

Joined: Sep 2003

mysql_query() returns TRUE (well, a resource, but it's still true) on all valid select queries even if the select query doesn't return any rows. So using that to test if a user name is already in use is no good. As long as the query syntax is correct, it returns true.

You need to do something like

<?php
$sql2 = \"SELECT COUNT(*) AS Cnt FROM `users` WHERE username='$username'\";
$result2 = mysql_query($sql2);
$row = mysql_fetch_assoc($result2);
$cnt = $row['Cnt'];

if($cnt > 0){
...
?>

You run a query that counts how many rows contain that username. Fetch it and check if the fetched result row contains a cnt (short for count) that is greater than 0.

Make sense?

PS mysql_error() is not a very good message to show to your users . I don't even think mysql_error() will show anything unless you make a syntax error in your queries.