A previously unseen error message

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Toneboy
Forum Contributor
Posts: 102
Joined: Wed Jul 31, 2002 5:59 am
Location: Law, Scotland.
Contact:

A previously unseen error message

Post by Toneboy »

Just got the following message when I tried to load a page:

Warning: 3 is not a valid MySQL result resource in /home/sites/site48/web/admin/adminsleaguetable.php on line 69

A bit baffling as the script had only processed the first entry, not got as far as three yet. Searched on MySql.com for an answer but saw nothing. Anyway, the page as a whole is as follows (as you can probably tell, it is meant to produce a league table based on admin pageviews):

Code: Select all

<? $pagetitle='Admins League Table';
require('auth.php');
include("$DOCUMENT_ROOT/templates/headeradmin.php");?>

<table width="600" border="0">
<tr>
<td valign="top" align="right">
Current time:
<? echo date ("l jS F\, Y\, g:ia"); ?>
<br>
Current timestamp:
<?
echo date ("U");
?>
</td>
</tr>
</table>


<table>
<tr>
<td>
Position
</td>
<td>
Name
</td>
<td>
Log-ins
</td>
<td>
Pageviews
</td>
<td>
Notes
</td>
</tr>


<?php

$position = 1;
$lastrelegationposition = 5;

$p1 = "#ffd700";
$p2 = "#fff68f";
$pmid = "#e0e0e0";
$prel = "#98fb98";
$blank = "#FFFFFF";

// Database constants
define("DATABASE_HOST", "***");
define("DATABASE_USER", "***");
define("DATABASE_PASSWORD", "***");
define("DATABASE_NAME", "***");

// Establish connection since we are going to use the database in every screen
$dbLink = mysql_connect(DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD);
if(!$dbLink) {
        print "Unable to connect to the database, please contact Sysadmin asap.";
} else {
   $dbUse = mysql_select_db(DATABASE_NAME, $dbLink);
}

 // SQL QUERY
 $sql = mysql_query("SELECT * FROM adminviews ORDER BY pageviews DESC");

	//LOOP TO GET IT OUT OF THE DB - *NOTICE THE WAY I AM BREAKING OUT OF PHP, INTO HTML*
	while($news = mysql_fetch_array($sql)){


if ($position == 1) {
echo "
<tr>
<td bgcolor="$p1">$position</td>
<td bgcolor="$p1">$newsїRealName]</td>
<td bgcolor="$p1">$newsїVisits]</td>
<td bgcolor="$p1">$newsїpageviews]</td>
<td bgcolor="#FFFFFF">League Leader</td>
</tr>";
$position++;
} else if ($position < 4) {
echo "
<tr>
<td bgcolor="$p2">$position</td>
<td bgcolor="$p2">$newsїRealName]</td>
<td bgcolor="$p2">$newsїVisits]</td>
<td bgcolor="$p2">$newsїpageviews]</td>
<td bgcolor="$blank">Champions League</td>
</tr>";
$position++;
} else if ($position > $lastrelegationposition) {
echo "
<tr>
<td bgcolor="$pmid">$position</td>
<td bgcolor="$pmid">$newsїRealName]</td>
<td bgcolor="$pmid">$newsїVisits]</td>
<td bgcolor="$pmid">$newsїpageviews]</td>
<td bgcolor="$blank"></td>
</tr>";
$position++;
} else {
echo "
<tr>
<td bgcolor="$prel">$position</td>
<td bgcolor="$prel">$newsїRealName]</td>
<td bgcolor="$prel">$newsїVisits]</td>
<td bgcolor="$prel">$newsїpageviews]</td>
<td bgcolor="$blank">Relegation candidate</td>
</tr>";
$position++;
}
	mysql_free_result($sql);
	mysql_close($dbLink);
}
echo "</table>";
include("$DOCUMENT_ROOT/templates/footeradmin.php");?>
Line 69 is this:

Code: Select all

while($news = mysql_fetch_array($sql)){
Does anyone have any ideas in regard to what is wrong?
User avatar
hob_goblin
Forum Regular
Posts: 978
Joined: Sun Apr 28, 2002 9:53 pm
Contact:

Post by hob_goblin »

$sql = mysql_query("SELECT * FROM adminviews ORDER BY pageviews DESC");

is obviously returning 0 results
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

try

Code: Select all

$dbUse = mysql_select_db(DATABASE_NAME, $dbLink) or die(mysql_error()); 
...
$sql = mysql_query("SELECT * FROM adminviews ORDER BY pageviews DESC", $dbLink) or die(mysql_error());
returning no records would do no harm, since $sql would be valid as well - but the while-body would not be entered
User avatar
Toneboy
Forum Contributor
Posts: 102
Joined: Wed Jul 31, 2002 5:59 am
Location: Law, Scotland.
Contact:

Post by Toneboy »

Thanks Volka, still getting the error though, just on a different line now. :cry:
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

on another line?
!¿ but the changes shouldn't alter the linenumber ?!
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

while{
...
...
<td bgcolor=\"$prel\">$news[pageviews]</td>
<td bgcolor=\"$blank\">Relegation candidate</td>
</tr>";
$position++;
}
mysql_free_result($sql);
mysql_close($dbLink);
}

So should you be freeing the mysql result within the while loop, or should you maybe do it after the while loop, hhmmmm....
User avatar
Toneboy
Forum Contributor
Posts: 102
Joined: Wed Jul 31, 2002 5:59 am
Location: Law, Scotland.
Contact:

Post by Toneboy »

Okay, let me see if I have understood you correctly here. Are you saying that ending the page like this:

Code: Select all

while($news = mysql_fetch_array($sql)){ 
...
&lt;tr&gt; 
&lt;td bgcolor="$prel"&gt;$position&lt;/td&gt; 
&lt;td bgcolor="$prel"&gt;$news&#1111;RealName]&lt;/td&gt; 
&lt;td bgcolor="$prel"&gt;$news&#1111;Visits]&lt;/td&gt; 
&lt;td bgcolor="$prel"&gt;$news&#1111;pageviews]&lt;/td&gt; 
&lt;td bgcolor="$blank"&gt;Relegation candidate&lt;/td&gt; 
&lt;/tr&gt;"; 
$position++; 
} 
} 
   mysql_free_result($sql); 
   mysql_close($dbLink); 

echo "&lt;/table&gt;"; 
include("$DOCUMENT_ROOT/templates/footeradmin.php");
... might work?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

What exactly is the error you are now getting?

Mac
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

Toneboy wrote:Okay, let me see if I have understood you correctly here. Are you saying that ending the page like this:

Code: Select all

while($news = mysql_fetch_array($sql)){ 
...
&lt;tr&gt; 
&lt;td bgcolor="$prel"&gt;$position&lt;/td&gt; 
&lt;td bgcolor="$prel"&gt;$news&#1111;RealName]&lt;/td&gt; 
&lt;td bgcolor="$prel"&gt;$news&#1111;Visits]&lt;/td&gt; 
&lt;td bgcolor="$prel"&gt;$news&#1111;pageviews]&lt;/td&gt; 
&lt;td bgcolor="$blank"&gt;Relegation candidate&lt;/td&gt; 
&lt;/tr&gt;"; 
$position++; 
} 
} 
   mysql_free_result($sql); 
   mysql_close($dbLink); 

echo "&lt;/table&gt;"; 
include("$DOCUMENT_ROOT/templates/footeradmin.php");
... might work?
Yes, your code was initially giving $sql a resource id of 3, it was getting to the end of the first iteration in the while loop, freeing the resource, then back to the start of the while, trying to fetch the next record and voila no resource to use because it had been released.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

doh, that sounds very much like the reason 8O
that's why indents are so important
User avatar
Toneboy
Forum Contributor
Posts: 102
Joined: Wed Jul 31, 2002 5:59 am
Location: Law, Scotland.
Contact:

Post by Toneboy »

mikeq wrote:Yes, your code was initially giving $sql a resource id of 3, it was getting to the end of the first iteration in the while loop, freeing the resource, then back to the start of the while, trying to fetch the next record and voila no resource to use because it had been released.
Mike, thanks for the help. I edited the code in line with your advice and it is now working nicely. :)
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

Pleasure :)
Post Reply