Trying to count the number of rows in a result set after query

[evillazydwarf]

Hi guys,

apologies if this is something silly i'm still quite new to MySQL so here goes...

the user fills out this form to sign up to the website, the form checks the database to see if the username has already been taken with the code:

$conn = mysql_connect("localhost:3306", "root", "********")
									or die ("Error With Connection");
		echo("connected<br><br>");
		$db_sel = mysql_select_db("game",$conn)
									or die ("Error With Database");
		$check = "select * from users where 'username' = '$username'";
		$db_sel = mysql_query($check,$conn) 
				or die (mysql_error());

i then try to count the rows using:

$num = mysql_num_rows($db_sel) or die ('DEATH');

and get DEATH every time, i tried using mysql_error() but it doesn't out put anything??

i'm not sure whether this is an error with my code or an error with my server, any input would be very helpful.

Thanks
Ben

 

[towerbase]

This is the MySQL forum so your code example shouldn't include code from other languages (PHP ?). How do we know what's in variables such as $username ?

If you have this kind of problem it is usually a good idea to echo the SQL statement that is causing the problem so you can copy and paste it into something like MySQL Query Browser and see what result set, if any, MySQL returns

I would guess that you don't need the quote marks around username. So your SQL statement should look something like:

select * from users where username = 'Fred'

Andrew
Hampshire, UK

 

[guelphdad]

are you using your results or only trying to count the rows?

If you are only counting then count in your sql:

select count(*) from users where 'username' = '$username'

also are you trying to see if a username exists before adding it?

 

[evillazydwarf]

thanks for taking the time to help guys,

the SQL statement comes out as you said Andrew

select * from users where username = 'Fred'

and i am only interested in the count at this point so i will try the select count... idea but this code should still work should it not?

also all this code i've shown is inside

if (isset($username))
{ 
}

it just seems to be the mysql_num_rows statement it doesn't like, im starting to thing my server may be configured incorrectly or something as i trimmed it back to the most simple example i could and it still wouldnt work.

Another interesting point, if you submit the form with the starting values, i.e. username = username and password = password

it all works fine and counts the rows in the db (18 at this moment in time) not sure if that helps or hinders at all.

 

[vacunita]

Are you sure that whatever is in $username is an exact match to a row in your DB?

If $username contains "john", is there a row with "john" exactly like that as a user name?

If you do

select * from users where username [red]LIKE[/red] '[red]%[/red]Fred[red]%[/red]'

do you get results?

If you remove the [red]Or die['Death'][/red] and echo $num is there a number there?

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[evillazydwarf]

I've swapped the "=" for "LIKE" in the SQL statement and it seems to work ??

no idea why...

 

[guelphdad]

the difference between select * and select count(*) is that the first retrieves all the data from your table and the second counts the rows in the table. There is a significant speed difference that is magnified as the number of rows in the table grows.

that is whay I was asking if you were using the data or only after the count.

I've seen people use:

select *
mysql_count_rows()

which is pretty much a resource waste.