SitePoint Sponsor |
|
User Tag List
Results 1 to 15 of 15
Thread: Debugging query error?
-
Jun 8, 2007, 18:15 #1
- Join Date
- Aug 2004
- Location
- Cairns, Australia
- Posts
- 762
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Debugging query error?
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
?>
-
Jun 8, 2007, 18:24 #2
$result = mysql_db_query($db,"$query",$cid);
I'd imagine it's because you have quotes around the $query here.
-
Jun 8, 2007, 18:32 #3
- Join Date
- Aug 2004
- Location
- Cairns, Australia
- Posts
- 762
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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 ?
-
Jun 8, 2007, 18:39 #4
- Join Date
- Mar 2007
- Location
- Ottawa, Ontario!
- Posts
- 149
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
$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);<3php && SitePoint ?>
-
Jun 8, 2007, 18:59 #5
- Join Date
- Aug 2004
- Location
- Cairns, Australia
- Posts
- 762
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
?>
-
Jun 8, 2007, 19:02 #6
- Join Date
- Mar 2007
- Location
- Ottawa, Ontario!
- Posts
- 149
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.<3php && SitePoint ?>
-
Jun 8, 2007, 19:06 #7
- Join Date
- Aug 2004
- Location
- Cairns, Australia
- Posts
- 762
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
$max_articles = "7";
-
Jun 8, 2007, 19:09 #8
- Join Date
- Mar 2007
- Location
- Ottawa, Ontario!
- Posts
- 149
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
'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.<3php && SitePoint ?>
-
Jun 8, 2007, 19:16 #9
- Join Date
- Aug 2004
- Location
- Cairns, Australia
- Posts
- 762
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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';
-
Jun 8, 2007, 19:21 #10
- Join Date
- Mar 2007
- Location
- Ottawa, Ontario!
- Posts
- 149
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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;
Leme know.<3php && SitePoint ?>
-
Jun 8, 2007, 21:42 #11
- Join Date
- Aug 2004
- Location
- Cairns, Australia
- Posts
- 762
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Yippeeeee!
It worked, thank you so much for your help
-
Jun 8, 2007, 21:59 #12
- Join Date
- Aug 2004
- Location
- Cairns, Australia
- Posts
- 762
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
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...
-
Jun 8, 2007, 22:29 #13
- Join Date
- Jan 2002
- Location
- Australia
- Posts
- 2,634
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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 % 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?&id={$row['article_id']}" title="$title">$title</a></td>
</tr>
EOD;
}
PHP Code:$class = ($i % 2 == 0) ? 'row-a' : '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
-
Jun 9, 2007, 00:11 #14
- Join Date
- Aug 2004
- Location
- Cairns, Australia
- Posts
- 762
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
hi craniel
Thanks for the informative reply!
What does this mean:
'echo <<<EOD' & EOD; ?
-
Jun 9, 2007, 00:16 #15
- Join Date
- Aug 2000
- Location
- Philadephia, PA
- Posts
- 20,578
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Try Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more
Bookmarks