PHP/SQL Question

scarymoon1 · 24-04-2010 1:27pm #1

I have 3 values entered into a database from a form, I am adding these values 2gether in php and I need to print out the biggest sum of these values together.

Some code below:
$fname = $_POST;
$surname = $_POST;
$student_number = $_POST;
$exam = $_POST;
$project = $_POST;
$participation = $_POST;
$totalmark = "";
$sqlquery = "INSERT INTO details VALUES ('$fname', '$surname', '$student_number', '$exam', '$project', '$participation')";
$results = mysql_query($sqlquery);

I tried this but it wont work...

$sqlquery2 = "SELECT MAX(totalmark) FROM ("SELECT exam + project + participation AS totalmark FROM details")";
$results2 = mysql_query($sqlquery2);

It doesn't work, maybe i have the quotes in the wrong places.... not sure why it wont work????

RobertFoster · 24-04-2010 2:18pm

If you're looking for the highest mark from all the entries in "details", try this:

SELECT exam + project + participation AS totalmark FROM details ORDER BY totalmark DESC LIMIT 1

ORDER BY specifies which value to order the table with.
DESC highest totalmark -> lowest totalmark
And LIMIT n limits the number of results to however many you'd like.

You were going wrong in your $sqlquery2 because FROM is meant to reference a table, and not another SELECT query.

Webmonkey · 24-04-2010 2:22pm

[php]echo mysql_error();[/php]

Might give you more insight

scarymoon1 · 24-04-2010 3:41pm

Thanks for replies.... so to print to screen id do the below right?? But I dont think I did the echo statement right.

<table width="100%" cellpadding="0" cellspacing="0">
<tr>
<td align="center"> <strong> Exam Details </strong> </td>
</tr>
<tr>
<td>
<table width="100%" cellpadding="5" cellspacing="5" border="1">
<?php
while($row = mysql_fetch_array($results2))
{
?>
<tr>
<td align"right"> Highest Mark: </td>
<td align ="left"><?php echo $row[$results2]?></td>
</tr>

Creamy Goodness · 24-04-2010 10:47pm

[php]
<?php
echo $row[$results2]
?>
[/php]

this is wrong. $results2 is an result set and will not give you the desire result you want to achieve.

usually when working with database result sets you'll be looking to do this

[php]
<?php
echo $row;

//So in robert fosters sql statement to echo the total mark would be like this.
$row;
?>
[/php]

scarymoon1 · 25-04-2010 1:14pm

thanks - that worked. one last question.... i now need to print the name of the person who has the highest mark, plus who got the lowest and average mark. will i need to do multiple_query?? Or can I do the following:

<?php // code to get final grades
$sqlquery2 = "SELECT exam + project + participation AS totalmark FROM details ORDER BY totalmark DESC LIMIT 1";
$results2 = mysql_query($sqlquery2);
$sqlquery3 = "SELECT fname, surname FROM details";
$results3 = mysql_query($sqlquery3);
?>

<body>
<center><h1> Exam Results </h1></center>

<table width="500" border="0" bgcolor="#CCCCCC">
<?php
while($row2 = mysql_fetch_array($results3))
{
?>
<tr>
<td width="36%" align="right"> Student with highest result is: </td>
<td width="64%" align="left"><?php echo $row?>
</td>
</tr>
<?php
}
?>

<?php
while($row = mysql_fetch_array($results2))
{
?>
<tr>
<td width="36%" align="right"> Student with highest result is: </td>
<td width="64%" align="left"><?php echo $row?>
</td>
</tr>
<?php
}
?>
</table>
</body>
</html>

scarymoon1 · 25-04-2010 1:44pm

sorry scrap my last post - ive figured it out

thanks for replies.

PHP/SQL Question

Comments