"mysql_num_rows(): supplied argument..." common error

[Mutley]

I always have trouble resolving code when I encounter this error. Is it how I have arranged my SELECT/FROM/ORDER BY/WHERE/LIMIT?

[b]Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource on line 8[/b]
Code below:

[code]<?php
$id = $_GET['id'];
require_once("connection.php");

$sql = "SELECT team, DATE_FORMAT(date,'%d-%m-%Y') AS dstamp, kickoff, against, side, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16, p17, p18, p19, p20 FROM playerlist ORDER BY date DESC, id DESC WHERE id=".$id." LIMIT 3 ";
$result = mysql_query($sql);

if(mysql_num_rows($result)!=0) {

while(list($team, $dstamp, $kickoff, $against, $side, $p1, $p2, $p3, $p4, $p5, $p6, $p7, $p8, $p9, $p10, $p11, $p12, $p13, $p14, $p15, $p16, $p17, $p18, $p19, $p20) = mysql_fetch_row($result)) {

?>

<table width="90%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td>York vs <?=$against?>, <?=side?></td>
    <td><?=$dstamp?>, Kick-off: <?=$kickoff?></td>
  </tr>
  <tr>
    <td>#1</td>
    <td><?=$p1?></td>
  </tr>
  <tr>
    <td>#2</td>
    <td><?=$p2?></td>
  </tr>
  <tr>
    <td>#3</td>
    <td><?=$p3?></td>
  </tr>
  <tr>
    <td>#4</td>
    <td><?=$p4?></td>
  </tr>
  <tr>
    <td>#5</td>
    <td><?=$p5?></td>
  </tr>
  <tr>
    <td>#6</td>
    <td><?=$p6?></td>
  </tr>
  <tr>
    <td>#7</td>
    <td><?=$p7?></td>
  </tr>
  <tr>
    <td>#8</td>
    <td><?=$p8?></td>
  </tr>
  <tr>
    <td>#9</td>
    <td><?=$p9?></td>
  </tr>
  <tr>
    <td>#10</td>
    <td><?=$p10?></td>
  </tr>
  <tr>
    <td>#11</td>
    <td><?=$p11?></td>
  </tr>
  <tr>
    <td>#12</td>
    <td><?=$p12?></td>
  </tr>
  <tr>
    <td>#13</td>
    <td><?=$p13?></td>
  </tr>
  <tr>
    <td>#14</td>
    <td><?=$p14?></td>
  </tr>
  <tr>
    <td>#15</td>
    <td><?=$p15?></td>
  </tr>
  <tr>
    <td>#16</td>
    <td><?=$p16?></td>
  </tr>
  <tr>
    <td>#17</td>
    <td><?=$p17?></td>
  </tr>
  <tr>
    <td>#18</td>
    <td><?=$p18?></td>
  </tr>
  <tr>
    <td>#19</td>
    <td><?=$p19?></td>
  </tr>
  <tr>
    <td>#20</td>
    <td><?=$p20?></td>
  </tr>
</table>

<br />

<?
}
}

else {

echo "Error.";
}
?>[/code]

Thanks in advance.

[Zane]

Probably the most asked question there is.
yes it's your SQL query

http://www.phpfreaks.com/forums/index.php/topic,95376.0.html

You need to switch your ORDER by and WHERE in your query

i.e
the Where condition should come before ORDER

[Mutley]

I tried it, same problem...?

[code]$id = $_GET['id'];
require_once("connection.php");

$sql = "SELECT team, DATE_FORMAT(date,'%d-%m-%Y') AS dstamp, kickoff, against, side, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16, p17, p18, p19, p20 FROM playerlist WHERE id=".$id." ORDER BY date DESC LIMIT 3 ";
$result = mysql_query($sql);

if(mysql_num_rows($result)!=0) {

while(list($team, $dstamp, $kickoff, $against, $side, $p1, $p2, $p3, $p4, $p5, $p6, $p7, $p8, $p9, $p10, $p11, $p12, $p13, $p14, $p15, $p16, $p17, $p18, $p19, $p20) = mysql_fetch_row($result)) {
[/code]

[Zane]

you need to post the error it's giving you

on the $result line
put

$result = mysql_query($sql) or die("Error because:  " . mysql_error());

[Mutley]

Error because: 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 'ORDER BY date DESC LIMIT 3' at line 1

[Zane]

tell me what this gives you
[code]$result = mysql_query($sql) or die("SQL Query: {wordwrap($sql)}br />Error because:  " . mysql_error());[/code]

[Mutley]

[code]SQL Query: {wordwrap(SELECT team, DATE_FORMAT(date,'%d-%m-%Y') AS dstamp, kickoff, against, side, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16, p17, p18, p19, p20 FROM playerlist WHERE id= ORDER BY date DESC LIMIT 3 )}br />Error because: 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 'ORDER BY date DESC LIMIT 3' at line 1[/code]

Thanks for your help.

[Zane]

If you look at your query you'll notice $id is empty
that's your problem

[quote]FROM playerlist WHERE id= ORDER[/quote]

have you made sure you put the value in the URL.
I notice you're taking it from $_GET
so make sure in your URL your saying
yourplace.com/thisphppage.php?id=7

or something like that

[printf]

What's the ORDER BY doing before the WHERE...

It can only be placed after the FROM table when no WHERE is used in the query...

[Zane]

that's not true
people do it all the time.

[quote=From MySQL documentation]
The index can also be used even if the ORDER BY does not match the index exactly, as long as all of the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause. The following queries use the index to resolve the ORDER BY part
SELECT * FROM t1
  ORDER BY key_part1,key_part2,... ;
   
SELECT * FROM t1
  WHERE key_part1=constant
  ORDER BY key_part2;
   
SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 DESC;
   
SELECT * FROM t1
  WHERE key_part1=1
  ORDER BY key_part1 DESC, key_part2 DESC;
[/quote]