mySQL Query giving no output

[Reality15]

Hello again,

For some reason, whenever I run this section of my script on any page, it just doesn't give me anything. I don't know what's wrong with it and that's why I'm looking for some help. (login info removed)

<?php

$id = $_GET['id'] ;
$type = $_GET['type'] ;
$ip = $_SERVER['REMOTE_ADDR'] ;

$link=mysql_connect('HOST','USERNAME','PASSWORD')
or die('Could not connect: ' . mysql_error());
mysql_select_db('DATABASE') or die('Could not select database');

$result = mysql_query("SELECT AVG(rating) FROM `rating` WHERE `type` = '$type' AND `attractionid` = '$id' ");
$rows = mysql_num_rows($result); 

if($rows == 0) 
{ 
     ?>
     <td width='100%'><p align='center'><font face='Arial' size='2'>This attraction has not yet been rated!</font></td>
      <?php
} 
else 
{ 
     while ($array=mysql_fetch_array($result)or die ("Error_name1".mysql_error()) {
          extract($array);
     }
     ?>
     <td width='100%'><p align='center'><font face='Arial' size='2'>
<?php
echo $rating;
?> out of 10</font></td>
      <?php
}

mysql_close($link);

?>

Thanks,

~ Reality

[toplay]

While testing/debugging this, make sure you have error_reporting(E_ALL); at the top of your script so you can see all of PHP's errors/warnings/notices. Also, I assume you have display_errors on in the php.ini file. Otherwise, set it using ini_set('display_errors', '1'); at the top of your script too.

Put the "or die" option on the query itself to see what error you're getting back. It's probably because you're missing a "group by" clause. Maybe something like this:

$sql = "SELECT AVG(rating) FROM rating WHERE type = '$type' AND attractionid = '$id' GROUP BY rating";
$result = mysql_query($sql) or die('SQL: ' . $sql . ' Error: ' . mysql_error());

It's too confusing to have a column name the same as a table name. I would change that.

Good luck.

[Reality15]

Did all of that and it still doesn't appear. Are there any missing characters or anything like that?

[toplay]

I took a closer look at your code.

Change this:

while ($array=mysql_fetch_array($result)or die ("Error_name1".mysql_error()) {
extract($array);
}

To this:

$array = mysql_fetch_array($result);
$rating = $array[0];

🙂

[Reality15]

As much as I'd love to make a reply right now that goes "It works! It finally works! Five months later and it actually works!", it's just not displaying yet. Any other little characters or snippets of code I'm missing?

[toplay]

Post the EXACT (latest) code you're using inside this forum's PHP tags. Of course you can hide the personal DB login info.

Also, try the SQL in phpmyadmin, mysql command prompt, or similar tool to see if it works there first.

.

[Reality15]

Here is my latest current code:

                        <table border='0' width='100%' cellspacing='0' cellpadding='5'>
                          <tr>
                            <td width='100%'>
                              <p align='center'><b><font face='Arial' size='3'><u>Attraction
                              Rating</u></font></b></td>
                          </tr>
                          <tr>
<?php

$id = $_GET['id'] ;
$type = $_GET['type'] ;
$ip = $_SERVER['REMOTE_ADDR'] ;

$link=mysql_connect('HOST','USERNAME','PASSWORD')
or die('Could not connect: ' . mysql_error());
mysql_select_db('DATABASE') or die('Could not select database');

$result = mysql_query("SELECT AVG(atrating) FROM `rating` WHERE `type` = '$type' AND `attractionid` = '$id' ");
$rows = mysql_num_rows($result); 

if($rows == 0) 
{ 
     ?>
     <td width='100%'><p align='center'><font face='Arial' size='2'>This attraction has not yet been rated!</font></td>
      <?php
} 
else 
{ 
$array = mysql_fetch_array($result); 
$rating = $array[0]; 

 }
 ?>
 <td width='100%'><p align='center'><font face='Arial' size='2'>
<?php
echo $rating;
?> out of 10</font></td>
      <?php
}

mysql_close($link);

?>
                          </tr>
                          <tr>
                          </tr>
                        </table>
                        <br>
<?php
$id = $_GET['id'] ;
$type = $_GET['type'] ;
$ip = $_SERVER['REMOTE_ADDR'] ;

$link=mysql_connect('HOST','USERNAME','PASSWORD')
or die('Could not connect: ' . mysql_error());
mysql_select_db('DATABASE') or die('Could not select database');

$result = mysql_query("SELECT *  FROM `rating` WHERE `ip` = '$ip' AND `attractionid` = '$id' AND `type` = '$type' ");
$rows = mysql_num_rows($result); 

if($rows == 0) 
{ 
?>
     <form method='POST' action='rate.php'>
     <p align='center'><b><u><font face='Arial' size='3'>Your
     Rating</font></u></b><font face='Arial' size='2'><br>
     </font><select size='1' name='rating'>
     <option value='10'>10 - Great</option>
     <option value='9'>9</option>
     <option value='8'>8</option>
     <option value='7'>7</option>
     <option value='6'>6</option>
     <option value='5'>5 - OK</option>
     <option value='4'>4</option>
     <option value='3'>3</option>
     <option value='2'>2</option>
     <option value='1'>1 - Terrible</option>
     <input type='hidden' name='ip' value='<?php echo $_SERVER['REMOTE_ADDR'] ?>'>
     <input type='hidden' name='attractionid' value='<?php echo $id; ?>'>
     <input type='hidden' name='type' value='<?php echo $type; ?>'>
     </select><br>
     <input type='submit' value='Rate' name='B1'></p>
     </form>
<?php
} 
else 
{ 
     ?>
     <p align='center'><font face='Arial' size='2'>You have already rated this attraction!</font><p align='center'>&nbsp;</p>
     <?php
}

mysql_close($link);

?>

And yes, the second part of the code does work just fine. Haven't had a single problem with it yet.

[toplay]

You don't have anything from what I said in my first post here. No error reporting, no error check and no "group by".

If the code at the bottom portion is in the same script as the top, then there's no need to close the mysql link and open a new one. It's a waste of overhead.

Also, you may want to utilize cookies to determine whether someone has rated something or not and not just rely on the IP address. Since many users can come across from the same IP address (like through a proxy from an office) you'll be blocking all the rest of the people at the office from rating (when they have not rated yet).

.

[Reality15]

Sorry, that was an older version. The newer version is the EXACT same, but with the changes you mentioned. Still, not outputing as it should be. This page right here is what I get:

http://www.mirollercoast.com/testing.php?id=001&type=thrillrides

[toplay]

You're probably getting parse errors and/or mysql errors. Check your error_reporting and display_errors settings.

Do a phpinfo(); in a separate script and run it to see all the current php.ini settings. Give us the link too so we can see it.

Post your current code. There's been too many times where members say they're doing something exact, and they weren't. It's better if you have many eyes looking at your code. So, post it!

[Reality15]

Here is the full code as of right now:

<?php
error_reporting(E_ALL); 
using ini_set('display_errors', '1'); 
?>
                        <table border='0' width='100%' cellspacing='0' cellpadding='5'>
                          <tr>
                            <td width='100%'>
                              <p align='center'><b><font face='Arial' size='3'><u>Attraction
                              Rating</u></font></b></td>
                          </tr>
                          <tr>
<?php

$id = $_GET['id'] ;
$type = $_GET['type'] ;
$ip = $_SERVER['REMOTE_ADDR'] ;

$link=mysql_connect('HOST','USERNAME','PASSWORD')
or die('Could not connect: ' . mysql_error());
mysql_select_db('DATABASE') or die('Could not select database');

$sql = "SELECT AVG(`atrating`) FROM `rating` WHERE `type` = '$type' AND `attractionid` = '$id' GROUP BY `atrating`"; 
$result = mysql_query($sql) or die('SQL: ' . $sql . ' Error: ' . mysql_error());
$rows = mysql_num_rows($result); 

if($rows == 0) 
{ 
     ?>
     <td width='100%'><p align='center'><font face='Arial' size='2'>This attraction has not yet been rated!</font></td>
      <?php
} 
else 
{ 
$array = mysql_fetch_array($result); 
$rating = $array[0]; 

 }
 ?>
 <td width='100%'><p align='center'><font face='Arial' size='2'>
<?php
echo $rating;
?> out of 10</font></td>
      <?php
}

mysql_close($link);

?>
                          </tr>
                          <tr>
                          </tr>
                        </table>
                        <br>
<?php
$id = $_GET['id'] ;
$type = $_GET['type'] ;
$ip = $_SERVER['REMOTE_ADDR'] ;

$link=mysql_connect('HOST','USERNAME','PASSWORD')
or die('Could not connect: ' . mysql_error());
mysql_select_db('DATABASE') or die('Could not select database');

$result = mysql_query("SELECT *  FROM `rating` WHERE `ip` = '$ip' AND `attractionid` = '$id' AND `type` = '$type' ");
$rows = mysql_num_rows($result); 

if($rows == 0) 
{ 
?>
     <form method='POST' action='rate.php'>
     <p align='center'><b><u><font face='Arial' size='3'>Your
     Rating</font></u></b><font face='Arial' size='2'><br>
     </font><select size='1' name='rating'>
     <option value='10'>10 - Great</option>
     <option value='9'>9</option>
     <option value='8'>8</option>
     <option value='7'>7</option>
     <option value='6'>6</option>
     <option value='5'>5 - OK</option>
     <option value='4'>4</option>
     <option value='3'>3</option>
     <option value='2'>2</option>
     <option value='1'>1 - Terrible</option>
     <input type='hidden' name='ip' value='<?php echo $_SERVER['REMOTE_ADDR'] ?>'>
     <input type='hidden' name='attractionid' value='<?php echo $id; ?>'>
     <input type='hidden' name='type' value='<?php echo $type; ?>'>
     </select><br>
     <input type='submit' value='Rate' name='B1'></p>
     </form>
<?php
} 
else 
{ 
     ?>
     <p align='center'><font face='Arial' size='2'>You have already rated this attraction!</font><p align='center'>&nbsp;</p>
     <?php
}

mysql_close($link);

?>

phpinfo(); file is at http://www.mirollercoast.com/phpinfo.php

[toplay]

You have two parse errors.

Change this:

using ini_set('display_errors', '1');

To this:

ini_set('display_errors', '1');

Change this:

}
?>
<td width='100%'><p align='center'><font face='Arial' size='2'>

To this:

 ?>
 <td width='100%'><p align='center'><font face='Arial' size='2'>

[Reality15]

Alright, this is the error I get after making said changes:

Warning: (null)(): 1 result set(s) not freed. Use mysql_free_result to free result sets which were requested using mysql_query() in Unknown on line 0

But at least it's finally displaying the script.

Also, when I include it in a page it doesn't show up. And there are no problems in the page itself because it's already being run on my server without flaws. And the include script is a common include script, no flaws.

[Rodney_H_]

edit Oops. I didn't see Toplays responses. Sorry, I just read the problem and thought I knew the answer. (silly-me)

PS. I like your site, and used to live in K-zoo for a number of years! 🙂

[Rodney_H_]

edit Oops. I didn't see Toplays responses. Sorry, I just read the problem and thought I knew the answer. (silly-me)

PS. I like your site, and used to live in K-zoo for a number of years!

[toplay]

Rodney, thanks for trying to help, however, we're way past that. You need to look at the most recent code posted because I told him/her to get rid of that already.

Change this:

else
{
?>
<p align='center'><font face='Arial' size='2'>You have already rated this attraction!</font><p align='center'>&nbsp;</p>
<?php
}

To this (added one line):

else
{
?>
<p align='center'><font face='Arial' size='2'>You have already rated this attraction!</font><p align='center'>&nbsp;</p>
<?php
mysql_free_result($result);
}

EDIT: No problem Rodney!

🙂

[Reality15]

Still gives me the same error and doesn't display on an include still. Same exact error and still blank.

Thanks for the help you two.

[toplay]

Like I've said already, it's best if you remove the close to the database and second connect and DB selection. You only need to connect and select DB once (as long as both tables you're accessing is in the same D😎. The DB close is optional (PHP will do it at end of script for you anyway).

If you want to keep it the same, then just put a mysql_free_result($result); before each mysql_close($link) you have.

.

[Reality15]

Now for the part that's really got me puzzled as to why it's not working, the page that the file is included on. Here are the current two files as they stand right now (the one with the include is viewthrilltest.php and the one we've been working on is testing.php)

Viewthrilltest.php

<?php
	include "skin_header.php"; 
     $type = "thrillrides";

//CONNECT TO THE DATABASE
$link=mysql_connect('HOST','USERNAME','PASSWORD')
or die('Could not connect: ' . mysql_error());
mysql_select_db('DATABASE') or die('Could not select database');

$id = $_GET['id'] ;

//GET ALL OF THE ENTRIES FROM ONE CATEGORY IN A DIFFERENT ORDER
$result = mysql_query("SELECT * FROM `thrillrides` WHERE `id` = '$id' ");
if (!$result) { 
echo mysql_error(); 
exit; 
}
while ($array = mysql_fetch_array($result)) {
extract($array);
}

//DISCONNECT FROM THE DATABASE
mysql_close($link);
?>

             <tr>
              <td width="100%" height="100%" valign="top">
              <table border="0" width="100%" cellspacing="0" cellpadding="5" height="189">
                <tr>
                  <td width="100%" valign="top" height="176" colspan="2">
                    <p align="center"><font face="Arial" size="3"><b>
<?php echo"$name"; ?>
<br>
<?php echo"</b></font><font face='Arial' size='2'>$opened - $model</font></p>";
      echo"<p align='center'><img border='0' src='attractions/thrillrides/$image.gif' width='400' height='300'></p>";
?>
                      </td>
                    </tr>
                    <tr>
                      <td width="100%" valign="top" colspan="2">
                        <img border="0" src="images/bar.gif" width="425" height="25">
                      </td>
                    </tr>
                    <tr>
                      <td width="33%" valign="top">
<?php echo"<font face='Arial' size='2'>$description</font>";
	echo"<p align='center'><font face='Arial' size='2'>You must be $heightrequirement to ride this ride</font>";
?>
                      </td>
                      <td width="67%" valign="top">
                        <table border="0" width="100%" cellspacing="0">
                          <tr>
                            <td width="100%" colspan="2" bgcolor="#000000">
                              <table border="0" width="100%" bgcolor="#EF9E20" cellspacing="0" cellpadding="3">
                                <tr>
                                  <td width="100%">
                                    <p align="center"><font face="Arial"><b>Statistics</b></font></td>
                                </tr>
                              </table>
                            </td>
                          </tr>
                          <tr>
                            <td width="50%"><font face="Arial" size="2">Year
                              Built:</font></td>
                            <td width="50%"><font face="Arial" size="2"><?php echo"$opened"; ?></font></td>
                          </tr>
<?php
if(!$manufacturer) { 
}
if ($manufacturer) {
?>
                          <tr>
                            <td width="50%"><font face="Arial" size="2">Manufacturer:</font></td>
                            <td width="50%"><font face="Arial" size="2"><?php echo"$manufacturer"; ?></font></td>
                          </tr>
<?php
}
if(!$model) { 
}
if ($model) {
?>
                          <tr>
                            <td width="50%"><font face="Arial" size="2">Model:</font></td>
                            <td width="50%"><font face="Arial" size="2"><?php echo"$model"; ?></font></td>
                          </tr>
<?php
}
if(!$height) { 
}
if ($height) {
?>
                          <tr>
                            <td width="50%"><font face="Arial" size="2">Height:</font></td>
                            <td width="50%"><font face="Arial" size="2"><?php echo"$height"; ?>
                              feet</font></td>
                          </tr>
<?php
}
if(!$speed) { 
}
if ($speed) {
?>
                          <tr>
                            <td width="50%"><font face="Arial" size="2">Speed:</font></td>
                            <td width="50%"><font face="Arial" size="2"><?php echo"$speed"; ?>
                              mph</font></td>
                          </tr>
<?php
}
if(!$capacity) { 
}
if ($capacity) {
?>
                          <tr>
                            <td width="50%"><font face="Arial" size="2">Capacity:</font></td>
                            <td width="50%"><font face="Arial" size="2"><?php echo"$capacity"; ?></font></td>
                          </tr>
<?php
}
?>
                        </table>
                      </td>
                    </tr>
                    <tr>
                      <td width="100%" valign="top" colspan="2">
                        <img border="0" src="images/bar.gif" width="425" height="25">
                      </td>
                    </tr>
                    <tr>
                      <td width="50%" valign="top">
                        <p align="center"><a href="http://gallery.mirollercoast.com/
<?php
echo $image;
?>
                        " target="_blank"><img border="0" src="images/photogallery.gif" width="150" height="75"></a>
                      </td>
                      <td width="50%" valign="top">
<?php
include "testing.php?id=$id&type=$type";
?>
                      </td>

<?php
include "skin_footer.php";
?>

Testing.php

<?php
error_reporting(E_ALL); 
ini_set('display_errors', '1'); 
?>
                        <table border='0' width='100%' cellspacing='0' cellpadding='5'>
                          <tr>
                            <td width='100%'>
                              <p align='center'><b><font face='Arial' size='3'><u>Attraction
                              Rating</u></font></b></td>
                          </tr>
                          <tr>
<?php

$id = $_GET['id'] ;
$type = $_GET['type'] ;
$ip = $_SERVER['REMOTE_ADDR'] ;

$link=mysql_connect('HOST','USERNAME','PASSWORD')
or die('Could not connect: ' . mysql_error());
mysql_select_db('DATABASE') or die('Could not select database');

$sql = "SELECT AVG(`atrating`) FROM `rating` WHERE `type` = '$type' AND `attractionid` = '$id' GROUP BY `atrating`"; 
$result = mysql_query($sql) or die('SQL: ' . $sql . ' Error: ' . mysql_error());
$rows = mysql_num_rows($result); 

if($rows == 0) 
{ 
     ?>
     <td width='100%'><p align='center'><font face='Arial' size='2'>This attraction has not yet been rated!</font></td>
      <?php
mysql_free_result($result); 
} 
else 
{ 
$array = mysql_fetch_array($result); 
$rating = $array[0]; 
     ?>
     <td width='100%'><p align='center'><font face='Arial' size='2'>
<?php
echo $rating;
?> out of 10</font></td>
      <?php
}

?>
                          </tr>
                          <tr>
                          </tr>
                        </table>
                        <br>
<?php
$id = $_GET['id'] ;
$type = $_GET['type'] ;
$ip = $_SERVER['REMOTE_ADDR'] ;

$result = mysql_query("SELECT *  FROM `rating` WHERE `ip` = '$ip' AND `attractionid` = '$id' AND `type` = '$type' ");
$rows = mysql_num_rows($result); 

if($rows == 0) 
{ 
?>
     <form method='POST' action='rate.php'>
     <p align='center'><b><u><font face='Arial' size='3'>Your
     Rating</font></u></b><font face='Arial' size='2'><br>
     </font><select size='1' name='rating'>
     <option value='10'>10 - Great</option>
     <option value='9'>9</option>
     <option value='8'>8</option>
     <option value='7'>7</option>
     <option value='6'>6</option>
     <option value='5'>5 - OK</option>
     <option value='4'>4</option>
     <option value='3'>3</option>
     <option value='2'>2</option>
     <option value='1'>1 - Terrible</option>
     <input type='hidden' name='ip' value='<?php echo $_SERVER['REMOTE_ADDR'] ?>'>
     <input type='hidden' name='attractionid' value='<?php echo $id; ?>'>
     <input type='hidden' name='type' value='<?php echo $type; ?>'>
     </select><br>
     <input type='submit' value='Rate' name='B1'></p>
     </form>
<?php
mysql_free_result($result); 
} 
else 
{ 
?> 
<p align='center'><font face='Arial' size='2'>You have already rated this attraction!</font><p align='center'>&nbsp;</p> 
<?php 
mysql_free_result($result); 
} 
mysql_close($link);

?>

If you can get this to work I just might love you forever.

[Rodney_H_]

Reality15:

I don't think you need to pass parameters/values to an include file via the q-string:

<?php
include "testing.php?id=$id&type=$type";
?>

You can just use: $id and $type in your 'Testing.php' file directly.

*Addition: I went over your code:

<?php
include "skin_header.php"; 
$type = "thrillrides";
$id = (isset($_GET['id'])) ? (trim($_GET['id'])) : 001;

// Connect to sql
$link = mysql_connect($host,$user,$password)
     or die('Could not connect: ' . mysql_error());

// Select db
$db = mysql_select_db($database,$link)
     or die('Could not select database');

// Query Statement
$sql = "SELECT * FROM $type WHERE id = '$id'";

$result = mysql_query($sql)
     or die('Could not execute query: ' . mysql_error());

// if no result
if (!$result) {
	echo 'Attraction not found in database.';
	exit; 
}

$row = mysql_fetch_array($result);
extract($row);
?>

<tr>
  <td width="100%" height="100%" valign="top">
     <table border="0" width="100%" cellspacing="0" cellpadding="5" height="189">
        <tr>
          <td width="100%" valign="top" height="176" colspan="2">
           <p align="center"><font face="Arial" size="3"><b><?php echo $name; ?></b></font>
			<br><font face='Arial' size='2'><?php echo $opened . ' - ' . $model; ?></font></p>
			<p align='center'><img border='0' src='http://mirollercoast.com/attractions/thrillrides/<?php echo $image; ?>.gif' width='400' height='300'></p>
			</td>
          </tr>
          <tr>
            <td width="100%" valign="top" colspan="2">
              <img border="0" src="http://mirollercoast.com/images/bar.gif" width="425" height="25">
             </td>
           </tr>
           <tr>
           <td width="33%" valign="top">
		     <font face='Arial' size='2'><?php echo $description; ?></font>
			 <p align='center'><font face='Arial' size='2'>You must be <?php echo $heightrequirement; ?> to ride this ride</font></p>
			</td>
			<td width="67%" valign="top">
			  <table border="0" width="100%" cellspacing="0">
				<tr>
			  	   <td width="100%" colspan="2" bgcolor="#000000">
					 <table border="0" width="100%" bgcolor="#EF9E20" cellspacing="0" cellpadding="3">
					 	<tr>
					  	  <td width="100%">
					 		<p align="center"><font face="Arial"><b>Statistics</b></font>
					 	  </td>
					 	 </tr>
                     </table>
                   </td>
                  </tr>
                  <tr>
                    <td width="50%"><font face="Arial" size="2">Year Built:</font></td>
                    <td width="50%"><font face="Arial" size="2"><?php echo $opened; ?></font></td>
                  </tr>
					<?php
					if (!empty($manufacturer)) {
					?>
                   <tr>
                     <td width="50%"><font face="Arial" size="2">Manufacturer:</font></td>
                     <td width="50%"><font face="Arial" size="2"><?php echo $manufacturer; ?></font></td>
                   </tr>
					<?php
					}
					if(!empty($model)) {
					?>
                    <tr>
                      <td width="50%"><font face="Arial" size="2">Model:</font></td>
                      <td width="50%"><font face="Arial" size="2"><?php echo $model; ?></font></td>
                    </tr>
					<?php
					}
					if(!empty($height)) {
					?>
                   <tr>
                     <td width="50%"><font face="Arial" size="2">Height:</font></td>
                     <td width="50%"><font face="Arial" size="2"><?php echo $height; ?> feet</font></td>
                   </tr>
					<?php
					}
					if(!empty($speed)) {
					?>
                    <tr>
                      <td width="50%"><font face="Arial" size="2">Speed:</font></td>
                      <td width="50%"><font face="Arial" size="2"><?php echo $speed; ?> mph</font></td>
                    </tr>
					<?php
					}
					if(!empty($capacity)) {
					?>
                    <tr>
                      <td width="50%"><font face="Arial" size="2">Capacity:</font></td>
                      <td width="50%"><font face="Arial" size="2"><?php echo $capacity; ?></font></td>
                    </tr>
					<?php } ?>
                  </table>
                </td>
              </tr>
              <tr>
                <td width="100%" valign="top" colspan="2">
                   <img border="0" src="http://mirollercoast.com/images/bar.gif" width="425" height="25">
                </td>
              </tr>
              <tr>
                <td width="50%" valign="top">
                  <p align="center"><a href="http://gallery.mirollercoast.com/<?php echo $image; ?>" target="_blank"><img border="0" src="http://mirollercoast.com/images/photogallery.gif" width="150" height="75"></a>
                 </td>
                 <td width="50%" valign="top">
				<?php
				mysql_close();
				include "testing.php";
				?>
				</td>				
				<?php
				include "skin_footer.php";
				?>