mysql_num_rows Error

[suttercain]

Hi everyone,

 

I am running the following code:

 

    
<?php
$query = mysql_query("SELECT * FROM user_comics LEFT JOIN comics
ON user_comics.comic_id = comics.comic_id
WHERE user_comics.user_id = '".mysql_real_escape_string($_GET['title'])."' AND type='Comic Book' $filter ORDER by comics.title, comics.issue_number $limit");
}
echo "Number of Rows:". mysql_num_rows($query);
if (mysql_num_rows($query) > 0) {
echo "Hello";
}
?>

 

Okay, so here is my problem. When I run the above script and there is a result of one or more, it's fine. No errors are thrown. But when the result in the mysql_num_rows is zero, I get the following:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/superman/public_html/comics/comicSearchMembers.php on line 102

Number of Rows:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/superman/public_html/comics/comicSearchMembers.php on line 103

 

I didn't think an error like this would be echoed if the result was zero... am I incorrect? Thanks.

[pocobueno1388]

It means there is something wrong with your query.

 

Put an or die(mysql_error()) at the end of your query.

[suttercain]

I added the or die(mysql_error()) to the end and I got this error:

 

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 '-25,25' at line 3

 

The above error is generated from the $limit variable which is part of the pagination.

 

Here is the extended code to include the orgin of the $limit variable.

 

<?php
$query = mysql_query("SELECT count(*) FROM user_comics WHERE user_id = '".mysql_real_escape_string($_GET['title'])."'") or die(mysql_error());

//PAGENATION
$query_data = mysql_fetch_row($query);
$numrows = $query_data[0];
$rows_per_page = 25;
$lastpage = ceil($numrows/$rows_per_page);
$pageno = (int)$pageno;
if ($pageno < 1) {
    $pageno = 1;
} else if ($pageno > $lastpage) {
    $pageno = $lastpage;
}
$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;

if (isset($_GET['title'])) {
    $query = mysql_query("SELECT * FROM user_comics LEFT JOIN comics
ON user_comics.comic_id = comics.comic_id
WHERE user_comics.user_id = '".mysql_real_escape_string($_GET['title'])."' AND type='Comic Book' $filter ORDER by comics.title, comics.issue_number $limit") or die(mysql_error());
}
echo "Number of Rows:". mysql_num_rows($query);
if (mysql_num_rows($query) > 0) {
echo "Hello!";
}
?>

 

Just a side note. The script runs fine if a result is found. If there are no results, I get the error.

[Wes1890]

Try this.. just a guess

 

<?php
if (isset($_GET['title'])) {
    $query = @mysql_query("SELECT * FROM user_comics LEFT JOIN comics
ON user_comics.comic_id = comics.comic_id
WHERE user_comics.user_id = '".mysql_real_escape_string($_GET['title'])."' AND type='Comic Book' $filter ORDER by comics.title, comics.issue_number $limit");
}
echo "Number of Rows:". mysql_num_rows($query);
if (mysql_num_rows($query) > 0) {
echo "Hello!";
}
else
{
echo "no rows...";
}
?>

 

Or this..

 

 

<?php
if (isset($_GET['title'])) {
    $query = mysql_query("SELECT * FROM user_comics LEFT JOIN comics
ON user_comics.comic_id = comics.comic_id
WHERE user_comics.user_id = '".mysql_real_escape_string($_GET['title'])."' AND type='Comic Book' $filter ORDER by comics.title, comics.issue_number $limit");
}
$totalrows = mysql_num_rows($query);

echo "Number of Rows:". $totalrows;
if ($totalrows > 0) {
echo "Hello!";
}
else
{
echo "no rows...";
}
?>

 

 

You get an error because it's trying to count nothing, because no results are found... hope those work for you.. if not... post back

[pocobueno1388]

Do this and post what you get

 

echo "SELECT * FROM user_comics LEFT JOIN comics
ON user_comics.comic_id = comics.comic_id
WHERE user_comics.user_id = '".mysql_real_escape_string($_GET['title'])."' 
AND type='Comic Book' $filter ORDER by comics.title, comics.issue_number $limit";

 

[suttercain]

The script echoed:

SELECT * FROM user_comics LEFT JOIN comics ON user_comics.comic_id = comics.comic_id WHERE user_comics.user_id = '6' AND type='Comic Book' AND title = 'Doomsday' ORDER by comics.title, comics.issue_number LIMIT -25,25

[suttercain]

Wes' second code block works if I remover the error reporting from the end of the query.

[suttercain]

I take that last post back. It does not work.

[suttercain]

When I remove the $limit variable it works.... but then the pagination no longer works.

 

$limit = LIMIT -25,25; //Gives error when result is NULL

 

Here are two links...

 

This is if the query result actually has results:

http://www.supermandatabase.com/comics/comicSearchMembers.php?title=162&username=firstadam

 

This is if there are no results... ideally I would like to display that the user has no comics in his/her collection:

http://www.supermandatabase.com/comics/comicSearchMembers.php?title=161&username=lightning

[clanstyles]

Well if its the limit, try something like:

$limit = (LIMIT -25),25;

 

also try this.

 

$limit = LIMIT - 25;

 

Then in the SQL statment just do $limit,25 since you know its goign to be out of 25.

Also, what is LIMIT? How is it being set? But for sure, try quotes.

[suttercain]

I think I fixed it...

 

I had to change the pagination results to

 

$query_data = mysql_fetch_row($query);
$numrows = $query_data[0];
if ($numrows == 0) {
$numrows = 1;
}

 

So far working, but may lead to another error...

[clanstyles]

If so just post, I have tones of weird errors, I just post. A lot of times its me just not looking. But if you see above. I have a class that just doesn't want to work. I'm asking why.

 

You have php problems though ( Cross Site Scripting )

 

http://www.supermandatabase.com/comics/comicSearchMembers.php?title=162&pageno=5&username=<h2><marquee>Owned%20By%20Styles

 

See

[atlanta]

Here try this might work..

 

<?php
$query = mysql_query("SELECT count(*) FROM user_comics WHERE user_id = '".mysql_real_escape_string($_GET['title'])."'") or die(mysql_error());

//PAGENATION
$query_data = mysql_fetch_row($query);
$numrows = $query_data[0];
$rows_per_page = 25;
$lastpage = ceil($numrows/$rows_per_page);
$pageno = (int)$pageno;
if ($pageno < 1) {
    $pageno = 1;
} else if ($pageno > $lastpage) {
    $pageno = $lastpage;
}
$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;

if (isset($_GET['title'])) {
    $query = mysql_query("SELECT * FROM user_comics LEFT JOIN comics
ON user_comics.comic_id = comics.comic_id
WHERE user_comics.user_id = '".mysql_real_escape_string($_GET['title'])."' AND type='Comic Book' $filter ORDER by comics.title, comics.issue_number $limit") or die(mysql_error());
}

if (mysql_num_rows($query) > 0) {
echo "Number of Rows:". mysql_num_rows($query);
echo "Hello!";
}
else
{
echo "No Rows or whatever message";
}
?>

 

You have to add the echo inside of the if statement so that if it is greater it will echo but if it is 0 it will jump to the else statement