Debugging query error?

**adammc** · Jun 8, 2007 18:15

Hi guys,

Can anyone possibly help me understand why the following query is producing:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

PHP Code:


<?



       include("../config.php");



// Connect to database

    $cid = mysql_connect($host,$username,$dbpass);

    if (!$cid) { echo("ERROR: " . mysql_error() . "\n");    }

    mysql_select_db("$db");







// define the maximum results to show (set in config file)

$max_results = $max_articles;





// Get the data from the Database

$query = "SELECT article_id, name, date from Articles LIMIT $max_results ORDER BY article_id DESC";



        $result = mysql_db_query($db,"$query",$cid);

        if (!$result) { echo("ERROR: " . mysql_error() . "\n$result\n");    }

    

    while($row=mysql_fetch_array($result)) 

    {

?>

    

<tr class="row-a">

<td class="first"><? echo("$row[date]"); ?></td>

<td><a href="article-<? echo("$row[article_id]"); ?>"><? echo("$row[name]"); ?></a></td>

</tr>





<?

    }  // end of while statement - getting results from DB

?>

Any help would be greatly appreciated

**bigalreturns** · Jun 8, 2007 18:24

$result = mysql_db_query($db,"$query",$cid);

I'd imagine it's because you have quotes around the $query here.

**adammc** · Jun 8, 2007 18:32

thanx 4 the reply

that kinda worked...

i had to remove this as well ' LIMIT $max_results ORDER BY article_id DESC' to get it to display results.

any idea what is wrong with the limit / order by ?

**t0WL337** · Jun 8, 2007 18:39

$query = 'SELECT article_id, name, date from Articles LIMIT '.$max_results.' ORDER BY article_id DESC';

$result = mysql_db_query($db.', '.$query.', '.$cid);

also maybe for $result you could do

$result = mysql_query($query);

**adammc** · Jun 8, 2007 18:59

Hi towl337

I got this error using your suggestion:
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 'ORDER BY article_id DESC' at line 1

PHP Code:


<?



//  Get Database Info

  include("../config.php");



// Connect to database

    $cid = mysql_connect($host,$username,$dbpass);

    if (!$cid) { echo("ERROR: " . mysql_error() . "\n");    }

    mysql_select_db("$db");







// define the maximum results to show (set in config file)

$max_results = $max_articles;





// Get the data from the Database

$query = 'SELECT article_id, name, date from Articles LIMIT '.$max_results.' ORDER BY article_id DESC'; 



       $result = mysql_query($query);

        if (!$result) { echo("ERROR: " . mysql_error() . "\n$result\n");    }

    

    while($row=mysql_fetch_array($result)) 

    {

?>

    

<tr class="row-a">

<td class="first"><? echo("$row[date]"); ?></td>

<td><a href="webmaster-articles/article.php&id=<? echo("$row[article_id]"); ?>"><? echo("$row[name]"); ?></a></td>

</tr>





<?

    }  // end of while statement - getting results from DB

?>

**t0WL337** · Jun 8, 2007 19:02

Okay for the LIMIT clause you need a start value and end value I think that is the problem. You only have just the $max_results in there.. hopefully someone else can elaborate on this for me.

Also can I see how you wrote the $max_results variable or rather the $max_articles variable (originally)

You could test it by changing the query to

$query = 'SELECT article_id, name, date from Articles LIMIT 0, 15 ORDER BY article_id DESC'; for example to see if that is the problem.

**adammc** · Jun 8, 2007 19:06

$max_articles = "7";

**t0WL337** · Jun 8, 2007 19:09

'SELECT article_id, name, date from Articles LIMIT 0, 7 ORDER BY article_id DESC';

'SELECT article_id, name, date from Articles LIMIT 0, '.$max_results.' ORDER BY article_id DESC';

This means we are starting from row 0 and showing the next 7 rows or whatever max results is.

You could also do ... LIMIT '.$x.', '.$y.' ORDER BY ... and have both the values as variables, and find a good way to define them. I remember creating a pagination function a while ago that pissed me off using LIMIT clause.

Leme know if it works.

**adammc** · Jun 8, 2007 19:16

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 'ORDER BY article_id DESC' at line 1

PHP Code:


$query = 'SELECT article_id, name, date from Articles LIMIT 0, '.$max_results.' ORDER BY article_id DESC';

**t0WL337** · Jun 8, 2007 19:21

Try putting the LIMIT clause last
Try this one to test again.

PHP Code:


'SELECT article_id, name, date from Articles ORDER BY article_id DESC LIMIT 0, 7';

PHP Code:


'SELECT article_id, name, date from Articles ORDER BY article_id DESC LIMIT 0, '.$max_results;

Sorry if I am wrong on this one, although I don't think I am; probably some (less)fresh blood in this thread would be good.
Leme know.

**adammc** · Jun 8, 2007 21:42

Yippeeeee!
It worked, thank you so much for your help

**adammc** · Jun 8, 2007 21:59

One last question if Im not asking too much??

PHP Code:


// Get the data from the Database

$query = 'SELECT article_id, name, date from Articles ORDER BY article_id DESC LIMIT 0, '.$max_articles; 



       $result = mysql_query($query);

        if (!$result) { echo("ERROR: " . mysql_error() . "\n$result\n");    }

    

    while($row=mysql_fetch_array($result)) 

    {

?>

    

<tr class="row-a">

<td class="first"><? echo("$row[date]"); ?></td>

<td><a href="article.php?&id=<? echo("$row[article_id]"); ?>" title="<? echo("$row[name]"); ?>"><? echo("$row[name]"); ?></a></td>

</tr>





<?

    }  // end of while statement

How would I go about alternating row classes (<tr>) ?
I would like the data to come out like:

first result = <tr class="row-a">
2nd result = <tr class="row-b">
third result = <tr class="row-a">
and so on...

**cranial-bore** · Jun 8, 2007 22:29

You really only need to put the alternative class on every second row (the others can have no class), but to do it your way do this:

PHP Code:


//Query code here
$i=0;
while($row = mysql_fetch_assoc($result))
{
  $i++;
  $class = ($i &#37; 2 == 0) ? 'row-a' : 'row-b';
  $title = htmlspecialchars($row['name'], ENT_QUOTES);

echo <<<EOD
 <tr class="$class">
    <td class="first">$row[date]</td>
    <td><a href="article.php?&amp;id={$row['article_id']}" title="$title">$title</a></td>
</tr>
EOD;
}

The main line is this one:

PHP Code:


 $class = ($i % 2 == 0) ? 'row-a' : 'row-b';

It just means if this row number is divisible by 2 (modulus of zero) then $class = 'row-a', otherwise $class = 'row-b';

The $i++ increments the loop count each time, so the row classes will alternate.

IMPORTANT NOTE: The forum is converting my percentage sign to the HTML entities. The code is supposed to have a percentage sign as the modulus operator.
i.e $i **PERCENTAGE SIGN** 2 == 0