mysql_fetch_array()

supplied argument is not a valid MySQL result resource in

[leon45]

Hi

I am trying to display records from a MYSQL database on multiple pages. I get the following error when I attempt to display the results

mysql_fetch_array(): supplied argument is not a valid MYSQL result in c:\intentpub\wwwroot\progen\beefsires1.php on line 238

The code is shown below. I am including a file called class.pager.php for page numbers (author: Tsigo) and this part works fine.

/* Now we use the LIMIT clause to grab a range of rows */
$result = mysql_query("SELECT sireid, sirename, sirethumbpic, comment_1,comment_2,comment_3 FROM beef".$start.", ".$limit);

If you can provide me with any help and advise, I would gratefully appreciate it

Thank You

[edited by: jatar_k at 4:30 pm (utc) on April 8, 2004]
[edit reason] removed extra code [/edit]

[barn_de]

hi leon,

i'm not sure, but i think the proble is, that there is a space ant the word "LIMIT" missing:

$result = mysql_query("SELECT sireid, sirename, sirethumbpic, comment_1,comment_2,comment_3 FROM beef".$start.", ".$limit);

so the query probably looks like this right now:

... from beef0, 10);

and it should be ... from beef limit 0, 10);

the easiest way to find out is to echo the sql query:

echo ("SELECT sireid, sirename, sirethumbpic, comment_1,comment_2,comment_3 FROM beef".$start.", ".$limit);

hope this helps

barn

[roblewis100]

Hi,

I believe you need to check your sql query after $result - although you note in your comments to now use the LIMIT clause, you haven't put this into your query, just the start and last numbers. It should read something like:

$result = mysql_query("SELECT sireid, sirename, sirethumbpic, comment_1,comment_2,comment_3 FROM beef LIMIT " . $start . "," . $limit . "");

(You may need to check how you pass the $start and $limit variables to your sql as I can't remember how this should be done correctly off the top of my head).

Rob.

[Edit] Looks like Barn got there before me, sorry. [/Edit]

[leon45]

HI Barn,

SPOT ON! When I echoed the statment it corrected this problem and created another.

The number of rows in my database are 7. The LIMIT set per page is 4. However, now I am getting 2 records on Page 1. On Page 2 I am getting 2 records but the image and data on the 2nd record is not being displayed.

I thnk the problem may lie with the FOR statement. Here is the code again so perhaps you might advise me. Thanks again for all your help

/* Find the number of rows returned from a query; Note: Do NOT use a LIMIT clause in this query */
$count = mysql_num_rows(mysql_query("SELECT sireid, sirename, sirethumbpic, comment_1,comment_2,comment_3 FROM beef"));

echo "No of Rows:".$count ;
echo "<P>";
/* Find the number of pages based on $count and $limit */
$pages = $p->findPages($count, $limit);

/* Now we use the LIMIT clause to grab a range of rows */
$result = mysql_query("SELECT sireid, sirename, sirethumbpic, comment_1,comment_2,comment_3 FROM beef LIMIT " .$start.", " .$limit);

echo ("SELECT sireid, sirename, sirethumbpic, comment_1,comment_2,comment_3 FROM beef LIMIT ".$start.", " .$limit) ;
echo "<P>";

/* Now get the page list and echo it */
/*$pagelist = $p->pageList($_GET['page'], $pages);*/
/*echo $pagelist;*/

/* Or you can use a simple "Previous ¦ Next" listing if you don't want the numeric page listing */
$next_prev = $p->nextPrev($_GET['page'], $pages);
echo $next_prev;

/* From here you can do whatever you want with the data from the $result link. */

for ($i=1; $i <= $limit; $i++)

{
//Give a name to the Fields

$row = mysql_fetch_array($result);
// echo row data here
}

?>

[edited by: jatar_k at 4:31 pm (utc) on April 8, 2004]
[edit reason] reduced code [/edit]

[ergophobe]

What is the value of $start?

You're doing

"...LIMIT $start, $limit";

if $start is 5, and $count is 7, you'll get two records.

Tom

PS, try to pare down your code a bit. The shorter your code the more replies you'll get (in general). You don't have to put in mysql_connect, all the echo statements and so on, just the parts that might be giving you trouble.

[jatar_k]

I was wondering about this

for ($i=1; $i <= $limit; $i++)
$row = mysql_fetch_array($result);

since you have already used limit in your query you should only ever get the right number of rows from the db. Therefore you could just go with something like this instead

while ($row = mysql_fetch_array($result)) {

making 1 line out of two. It will run through every result returned since you have already limited the results pulled from the db.

[ergophobe]

A few additional notes

$count = mysql_num_rows(mysql_query("SELECT sireid, sirename, sirethumbpic, comment_1,comment_2,comment_3 FROM beef"));

1. You're just getting a count, so no need to select everything. Faster and just as effective would be

$count = mysql_num_rows(mysql_query("SELECT sireid FROM beef"));

2. This is a bit more advanced but this is probably a bad idea
comment_1,comment_2,comment_3

This means that you can only have 3 comments and you always have three comments (though some are empty) for every record. Better is a table like this

comments
========
comment_id
sire_id
comment

SELECT comment FROM comments, beef
WHERE beef.sire_id = $sire_id AND beef.sire_id = comments.sire_id

or

SELECT comments.comment FROM comments
INNER JOIN beef ON comments.sire_id = beef.sire_id
AND beef.sire_id = $sire_id

(both selects should achieve the same thing unless I messed up)

Tom

[ergophobe]

from jatar_k
since you have already used limit in your query you should only ever get the right number of rows from the db.

The thing is, he should still get the same number of rows either way. Why only two rows?

Echo out $count, $start and $limit and see what you get.

[jatar_k]

I was just chopping code and noticed that and figured I would throw it in there while we were waiting for leon45 to return and answer your questions. ;)

by the way

Welcome to WebmasterWorld leon45

sorry, missed that the first time