I’ve been searching online for hours trying to figure out how to get this to work and everytime i submit my code, it comes up blank. Everything works except for my query for the average of a column.
Can anyone help me as to what I’m doing wrong?
[code]$query2 = “select avg(rating) as rating from contractors where license = “.$license.””;
$res = mysql_query($query2);
$average = mysql_result($res);
[/code]
What did you do to try to debug it? Were there any error messages?
When running queries, sometime the error is with the database. So syntactically you might have things correct as far as PHP is concerned, so you might not get an error there, however you have to “Request” the errors from mysql using the mysql_error() or mysql_errno() functions.
Also, do you have error reporting shut of in the PHP.INI so that errors don’t display? (This is more and more common, since the error messages can give valuable information to a would be hacker. So admins shut off error reporting). If this is the case then you need to look in your error log to see what errors you might be getting.
Finally try and run the query manually. I use SQLyog ( http://www.webyog.com/en/ ) and phpMyadmin ( http://www.phpmyadmin.net/home_page/index.php ) both will allow you to run manual queries to see if you are getting the expected results.
As a final note, if that code snippet is ALL the database section, then I see much more wrong. You have no connection string (mysql_connect) , you have no database selection (mysql_select_db) which is required since you are only using the mysql_query (no mysql_select_db is needed if you change the mysql_query to mysql_db_query however you still need to define the database being used). It also appears that your mysql_result statement is lacking a parameter. You have supplied the resource id ($res) but no row which is required.
[php]
// Connection String
$link = mysql_connect(‘example.com’, ‘username’, ‘password’);
// Just a normal query
$query2 = “select avg(rating) as rating from contractors where license = “.$license.””;
// send the query to the specified database as a single step
$res = mysql_db_query(‘DbName’, $query2);
// Get the result. Since it’s an aggregate query, there should only be ONE row.
//So we get row 1
$average = mysql_result($res, 1);
[/php]
php.net references
http://us3.php.net/manual/en/function.mysql-connect.php
http://us3.php.net/manual/en/function.m … -query.php
http://us3.php.net/manual/en/function.mysql-result.php
http://us3.php.net/manual/en/function.mysql-query.php
http://us3.php.net/manual/en/function.m … ect-db.php
Thanks so much for the response. I ran the code manually in phpMyAdmin and it returned the correct result. I’m not sure where to find my PHP.INI file, or how to make it so that it shows errors, because I adjusted my code and it’s still showing up blank. Here is the full code:
[code]<?php
$id=$_GET[‘id’];
$lastname=$_GET[‘name’];
$license=$_GET[‘license’];
@ $db = new mysqli('localhost','uname','pword','silverskye_com_-_main');
if (mysqli_connect_errno())
{
echo 'Error: Could not connect to database. Please try again later.';
exit;
}
$query2 = "select avg(rating) as rating from contractors where license = ".$license."";
$res = mysql_db_query('silverskye_com_-_main', $query2);
$average = mysql_result($res, 1);
$info = "select * from contractors where id = ".$id."";
$total = $db->query($info);
$line = $total->fetch_assoc();
echo'<h3>Contractor Name: '.$line['firstname'].' '.$line['lastname'].'</h3>
License Number: '.$line['license'].'<br>
Average Rating: '.$average.'
<p>
<table border="0">
<tr>
<td>Address:</td><td> '.$line['address'].'</td></tr><tr>
<td> </td><td> '.$line['city'].', '.$line['state'].' '.$line['zipcode'].'
</td>
</tr>
</table><p>';
$query = "select comments, rating from contractors where license = ".$license."";
$result = $db->query($query);
$num_results = $result->num_rows;
echo '<table cellpadding="3" cellspacing="1" border="0" bgcolor="#666666" width="630">
<tr>
<td bgcolor="#D2DBF6">Contractor Feedback</td>
</tr>';
for ($i=0; $i <$num_results; $i++)
{
$row = $result->fetch_assoc();
echo'<tr><td bgcolor="#FFFFFF"><strong>Contractor Rating:</strong> '.$row['rating'].'<p>
<strong>Comments: </strong>'.$row['comments'].'</td></tr>';
}
echo '</table>';
echo '<p style="margin-top: 30px;"><strong>Add Comments About This Contractor</strong><p>
<form action="submitcontractor.php" method="post">
<input type="hidden" name="firstname" value="'.$line['firstname'].'">
<input type="hidden" name="lastname" value="'.$line['lastname'].'">
<input type="hidden" name="license" value="'.$line['license'].'">
<input type="hidden" name="address" value="'.$line['address'].'">
<input type="hidden" name="city" value="'.$line['city'].'">
<input type="hidden" name="state" value="'.$line['state'].'">
<input type="hidden" name="zipcode" value="'.$line['zipcode'].'">
<table cellpadding="0" cellspacing="0" border="0">
<tr>
<td>Rating: </td>
<td colspan="2">1<input type="radio" name="rating" value="1"> 2<input type="radio" name="rating" value="2"> 3<input type="radio" name="rating" value="3"> 4<input type="radio" name="rating" value="4"> 5<input type="radio" name="rating" value="5"> 6<input type="radio" name="rating" value="6"> 7<input type="radio" name="rating" value="7"> 8<input type="radio" name="rating" value="8"> 9<input type="radio" name="rating" value="9"> 10<input type="radio" name="rating" value="10">
</td>
</tr>
<tr>
<td> </td><td align="left" style="font-size: 10px;">Worst</td><td align="right" style="font-size: 10px;">Best</td>
</tr>
<tr>
<td colspan="3"> </td>
</tr>
<tr>
<td valign="top">Comments: </td><td colspan="2"><textarea name="comments" rows="6" cols="40"></textarea></td>
</tr>
<tr>
<td colspan="3"> </td>
</tr>
<tr>
<td colspan="3"><input type="submit" value="Submit"></form></td>
</tr>
</table>';
$result->free();
$db->close();
?>[/code]if the screen is returning blank… Then I suspect that your PHP.INI IS suppressing errors from being displayed.
If you are on a windows system you can use explorer to search for php.ini On a *nix system, you can use slocate to find it.
Then search the file for display_errors and set it to equal On Also look to see what your error_reporting is set to. Mine is set to error_reporting = E_ALL & ~E_NOTICE. (all errors except notices).
For changes to take effect you will need to restart your webserver.
You can also put at the top of the pages.
error_reporting(“E_ALL”);
This will force the parser to display errors.
I’ve tried to show the errors, and it doesn’t return anything. Here is my restructured code:
[php]<?php
// include function files for this application
require_once(‘bookmark_fns.php’);
session_start();
//create short variable names
$email = $_POST[‘email’];
$password = $_POST[‘password’];
if ($email && $password)
// they have just tried logging in
{
try
{
login($email, $password);
// if they are in the database register the user id
$_SESSION[‘valid_user’] = $email;
}
catch(Exception $e)
{
// unsuccessful login
do_html_header(‘Problem:’);
echo ‘You could not be logged in.
You must be logged in to view this page.’;
do_html_url(‘login.php’, ‘Login’);
do_html_footer();
exit;
}
}
error_reporting(“E_ALL”);
do_html_header();
if ($_SESSION['valid_user'])
{
display_user_menu();
check_valid_user();
}
?>
<?php $id=$_GET['id']; $lastname=$_GET['name']; $license=$_GET['license'];
@ $db = new mysqli('localhost','smurf','hapuna32','silverskye_com_-_main');
if (mysqli_connect_errno())
{
echo 'Error: Could not connect to database. Please try again later.';
exit;
}
$info = "select * from contractors where id = ".$id."";
$total = $db->query($info);
$line = $total->fetch_assoc();
echo'<h3>Contractor Name: <font color="#3333FF">'.$line['firstname'].' '.$line['lastname'].'</font></h3>
License Number: '.$line['license'].'<br>
<table border="0">
<tr>
<td>Address:</td><td> '.$line['address'].'</td></tr><tr>
<td> </td><td> '.$line['city'].', '.$line['state'].' '.$line['zipcode'].'
</td>
</tr>
</table><p>';
$query2 = "select avg(rating) as rating from contractors where license = ".$license."";
$res = mysql_db_query('silverskye_com_-_main', $query2);
$average = mysql_result($res, 1);
echo 'Average Rating: '.$average.'<p>';
$query = "select comments, rating, email from contractors where license = ".$license."";
$result = $db->query($query);
$num_results = $result->num_rows;
echo '<table cellpadding="3" cellspacing="1" border="0" bgcolor="#666666" width="620">
<tr>
<td bgcolor="#D2DBF6">Contractor Feedback</td>
</tr>';
for ($i=0; $i <$num_results; $i++)
{
$row = $result->fetch_assoc();
echo'<tr><td bgcolor="#FFFFFF"><strong>Posted By:</strong> <a href="mailto:'.$row['email'].'">'.$row['email'].'</a><br /><strong>Contractor Rating:</strong> '.$row['rating'].'<p>
<strong>Comments: </strong>'.$row['comments'].'</td></tr>';
}
echo '</table>';
echo '<p style="margin-top: 30px;"><strong>Add Comments About This Contractor</strong><p>
<form action="submitcontractor.php" method="post">
<input type="hidden" name="firstname" value="'.$line['firstname'].'">
<input type="hidden" name="lastname" value="'.$line['lastname'].'">
<input type="hidden" name="license" value="'.$line['license'].'">
<input type="hidden" name="address" value="'.$line['address'].'">
<input type="hidden" name="city" value="'.$line['city'].'">
<input type="hidden" name="state" value="'.$line['state'].'">
<input type="hidden" name="zipcode" value="'.$line['zipcode'].'">
<input type="hidden" name="user_email" value="'.$_SESSION['valid_user'].'">
<table cellpadding="0" cellspacing="0" border="0">
<tr>
<td valign="top">Comments: </td><td colspan="2"><textarea name="comments" rows="6" cols="40"></textarea></td>
</tr>
<tr>
<td colspan="3"> </td>
</tr>
<tr>
<td>Rating: </td>
<td colspan="2">1<input type="radio" name="rating" value="1"> 2<input type="radio" name="rating" value="2"> 3<input type="radio" name="rating" value="3"> 4<input type="radio" name="rating" value="4"> 5<input type="radio" name="rating" value="5"> 6<input type="radio" name="rating" value="6"> 7<input type="radio" name="rating" value="7"> 8<input type="radio" name="rating" value="8"> 9<input type="radio" name="rating" value="9"> 10<input type="radio" name="rating" value="10">
</td>
</tr>
<tr>
<td> </td><td align="left" style="font-size: 10px;">Worst</td><td align="right" style="font-size: 10px;">Best</td>
</tr>
<tr>
<td colspan="3"> </td>
</tr>
<tr>
<td colspan="3"><input type="submit" value="Submit"></form></td>
</tr>
</table>';
$result->free();
$db->close();
do_html_footer();
?>[/php]
Here is the html page being displayed:
http://ww.silverskye.com/feedback/contractor.php?id=14&name=Taylor&license=89234293523
If you look at the page, everything works except for the average. It shows up blank. I ran the query in php my admin and it worked fine, so I’m not sure what is still wrong. Any help?
Admin Edit: Changed CODE tags to PHP for syntax highlighting. Additionally removed COLOR tags from code section. Both the CODE and PHP tags do not allow other phpBB tags inside the CODE and PHP tags (respectively). The CODE and PHP tags are akin to the tags in html except that the PHP tags allow for PHP Syntax highlighting.
try changing
[php]
$average = mysql_result($res, 1);
[/php]
to
[php]
$average = mysql_result($res, 0);
[/php]
Rows start at 0 (zero) and not 1 (one) http://us2.php.net/manual/en/function.mysql-result.php
To get an error (if one exists) you would have to use the mysql_error() function to retrieve the SQL errors. Syntactically there is no PHP error so none is shown.