mysql_num_rows() not working

[Cagecrawler]

Here's a piece of code that shows when a user logs into my site:
[code]
        <?php
include("include/connect.php");
$username=$_SESSION['username'];
$sql="SELECT * FROM mail WHERE to='$username' AND read='0'";
$query=mysql_query($sql);
$numrows=mysql_num_rows($query);
$sql2="SELECT * FROM mail WHERE to='$username'";
$query2=mysql_query($sql2);
$numrows2=mysql_num_rows($query2);
echo "<table>";
echo "<tr>";
echo "<td>";
echo "Welcome ".$_SESSION['username']."!<br/>";
echo "Your last visit: ".$_SESSION['lastvisit']."<br/>";
echo "<a href=\"mail.php\" target=\"main\">Mailbox:</a> ".$numrows." unread, total ".$numrows2.".";
echo "</td>";
echo "<td width=\"100\">";
echo "<p align=\"right\"><a href=\"logout.php\">Log out</a></p>";
echo "</td>";
echo "</tr>";
echo "</table>";
        ?>[/code]
I get the following errors:
[code]Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in loginbox.php on line 8

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in loginbox.php on line 11[/code]

Any ideas why?

[Jessica]

Looks like your connection didn't work. add OR die(mysql_error) to your connection string

[Cagecrawler]

Hmm...
My connection code:
[code]
<?
//Prepare DB connection
$connection= mysql_connect($server_name,$db_username,$db_password);

//Connect to MySQL and select DB
mysql_select_db($db_name,$connection) or die(mysql_error());
?>
It's already there, so I don't know why the error isn't showing up.  I've also just got it to output $query. Result - No output.

I've used include/connect.php in other files on the site (registering and logging in) and they've worked fine...  [/code]

[fert]

you need to supress errors with @ to make die(mysql_error())

[Cagecrawler]

hmm...  I've worked out what was causing the problem, but not how to fix it.

I commented out the WHERE clause on my SQL strings, and it worked fine.  However I have no idea why that should affect it.

[trq]

Are you sure $username is populated? I dont see any call to session_start().

[Cagecrawler]

Just checked and $username is populated.  There is no session_start() because this code is an include in another script with the session_start at the beginning of that.

[trq]

Welll your query is failing for some reason and your failing to catch it. At minimum use die() and mysql_error() to do some debugging....

[code=php:0]
$query2 = mysql_query($sql2) or die(mysql_error());
[/code]

[Cagecrawler]

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to='cagecrawler'' at line 1"

It's showing it's picking up $username ok, but there is a syntax error that I can't see.

$sql2="SELECT * FROM mail WHERE to='$username'";

[corbin]

$sql2="SELECT * FROM `mail` WHERE `to`='{$username}'";

Try that... I think maybe to is a special field in mysql or something... I could be wrong though :p

[Cagecrawler]

Bingo!  Your idea didn't work, but you were right about "to" being special in mysql.  I renamed the field in my DB, and all works good now.  I also had to rename "read", apparently that is special as well.