Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
Toneboy
Forum Contributor
Posts: 102 Joined: Wed Jul 31, 2002 5:59 am
Location: Law, Scotland.
Contact:
Post
by Toneboy » Mon Oct 07, 2002 7:10 pm
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?
hob_goblin
Forum Regular
Posts: 978 Joined: Sun Apr 28, 2002 9:53 pm
Contact:
Post
by hob_goblin » Mon Oct 07, 2002 7:12 pm
$sql = mysql_query("SELECT * FROM adminviews ORDER BY pageviews DESC");
is obviously returning 0 results
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Mon Oct 07, 2002 7:13 pm
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
Toneboy
Forum Contributor
Posts: 102 Joined: Wed Jul 31, 2002 5:59 am
Location: Law, Scotland.
Contact:
Post
by Toneboy » Mon Oct 07, 2002 7:51 pm
Thanks Volka, still getting the error though, just on a different line now.
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Mon Oct 07, 2002 10:12 pm
on another line?
!¿ but the changes shouldn't alter the linenumber ?!
mikeq
Forum Regular
Posts: 512 Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland
Post
by mikeq » Tue Oct 08, 2002 3:49 am
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....
Toneboy
Forum Contributor
Posts: 102 Joined: Wed Jul 31, 2002 5:59 am
Location: Law, Scotland.
Contact:
Post
by Toneboy » Tue Oct 08, 2002 6:01 am
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)){
...
<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");
... might work?
twigletmac
Her Royal Site Adminness
Posts: 5371 Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK
Post
by twigletmac » Tue Oct 08, 2002 6:12 am
What exactly is the error you are now getting?
Mac
mikeq
Forum Regular
Posts: 512 Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland
Post
by mikeq » Tue Oct 08, 2002 7:09 am
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)){
...
<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");
... 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.
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Tue Oct 08, 2002 7:39 am
doh, that sounds very much like the reason
that's why indents are so important
Toneboy
Forum Contributor
Posts: 102 Joined: Wed Jul 31, 2002 5:59 am
Location: Law, Scotland.
Contact:
Post
by Toneboy » Tue Oct 08, 2002 7:14 pm
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.
mikeq
Forum Regular
Posts: 512 Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland
Post
by mikeq » Wed Oct 09, 2002 5:20 am
Pleasure