[RESOLVED] Query With Calculations

transfield

Hello,

My query statement is listed below:-

$query="SELECT id, area_1, address, price, price*3 AS secret_formula FROM average_income WHERE (area_1 like '%$drop_down_box_1%') AND (area_1 like '%$drop_down_box_2%')";

My question is:-
How do I total up(sum up) all the prices in the secret_formula column & divide them by the number of records from this query & display the answer in another column?

Here's an example of what I want to achieve in case you did not understand my question:-

secret_formula column(after the query is done)
5454
6565
878
2323
9809
121

another column(sum of all values in secret_formula column divided by number of rows)
4191.666667

Thanks a lot for your advice.

gardnc

$reslut = $mysql_query($query);

$rows = mysql_num_rows($result);

$sum = 0;

while ($rs = mysql_fetch_array($result)) {

 $sum += $rs['secret_formula'];

}

$total = $sum/$rows;

herve

You can do that with SQL :

SELECT SUM((price*3))/COUNT(*) AS mean FROM average_income WHERE ....;

bradgrafelman

Note that if you have large tables, that's probably going to add some strain in the SQL server regardless of indeces.

transfield

Thank you for your advice, bradgrafelman. I have 41,000 records in my database & I hope thats not too big. I appreciate your advice.

Hello herve, this code gives me an error message. The message is :-
Warning: mysql_result(): area_1 not found in MySQL result index 3 in /home/dgrgroup/public_html/query_results.php on line 65
Warning: mysql_result(): address not found in MySQL result index 3 in /home/dgrgroup/public_html/query_results.php on line 66
Warning: mysql_result(): price not found in MySQL result index 3 in /home/dgrgroup/public_html/query_results.php on line 67
Warning: mysql_result(): secret_formula not found in MySQL result index 3 in /home/dgrgroup/public_html/query_results.php on line 68

What am I doing wrong here? My full code is listed below.

Thanks for your reply, gardnc. I have not tried this yet because I don't know where to implement this code(I don't understand the logic of this code). Could you show me how to implement this code? I appreciate your guidance.

Warm Regards to all of you.

<head>
<title>Untitled Document</title>
</head>
<body>
<?php
$username="abc123";
$password="abc123";
$database="abc123";
$host="localhost";
mysql_connect ("$host","$username","$password");
mysql_select_db($database) or die( "Where's the database man?");
$drop_down_box_1 = $_POST['drop_down_box_1'];
$drop_down_box_2 = $_POST['drop_down_box_2'];
$Text_Box_1 = $_POST['Text_Box_1'];
$Text_Box_2 = $_POST['Text_Box_2'];
$Text_Box_3 = $_POST['Text_Box_3'];
$Text_Box_4 = $_POST['Text_Box_4'];
$Text_Box_5 = $_POST['Text_Box_5'];
$Text_Box_6 = $_POST['Text_Box_6'];
$Text_Box_7 = $_POST['Text_Box_7'];
$Text_Box_8 = $_POST['Text_Box_8'];
$submit2 = $_POST['submit2'];

if ($Text_Box_1 == '') $Text_Box_1 = '0x0x0x0';
if ($Text_Box_2 == '') $Text_Box_2 = '0x0x0x0';
if ($Text_Box_3 == '') $Text_Box_3 = '0x0x0x0';
if ($Text_Box_4 == '') $Text_Box_4 = '0x0x0x0';
if ($Text_Box_5 == '') $Text_Box_5 = '0x0x0x0';
if ($Text_Box_6 == '') $Text_Box_6 = '0x0x0x0';
if ($Text_Box_7 == '') $Text_Box_7 = '0x0x0x0';
if ($Text_Box_8 == '') $Text_Box_8 = '0x0x0x0';

switch($_POST['R1'])
{
case 'and':
$query="SELECT SUM((price*3))/COUNT(*) AS mean FROM average_income WHERE (area_1 like '%$drop_down_box_1%') AND (area_1 like '%$drop_down_box_2%')"; 
break;
case 'or':
$query="SELECT SUM((price*3))/COUNT(*) AS mean FROM average_income WHERE (area_1 like '%$drop_down_box_1%') OR (area_1 like '%$drop_down_box_2%')";
break;
default:
$query="SELECT id, area_1, address, price, price*3 AS secret_formula FROM average_income WHERE (area_1 = '$drop_down_box_1')";
}

if(isset($submit2))
{
$query="SELECT SUM((price*3))/COUNT(*) AS mean FROM average_income WHERE (address like '%$Text_Box_1%') OR (address like '%$Text_Box_2%') OR (address like '%$Text_Box_3%') OR (address like '%$Text_Box_4%') OR (address like '%$Text_Box_5%') OR (address like '%$Text_Box_6%') OR (address like '%$Text_Box_7%') OR (address like '%$Text_Box_8%')";
}

echo $query;
$result=mysql_query($query);
$num=mysql_numrows($result);
?>

<table border="0" cellspacing="2" cellpadding="2">
<tr> 
<th><strong><font size="3" face="Arial, Helvetica, sans-serif">General Location</font></strong></th>
<th><strong><font size="3" face="Arial, Helvetica, sans-serif">Full Add</font></strong></th>
<th><strong><font size="3" face="Arial, Helvetica, sans-serif">Price(Ringgit Malaysia)</font></strong></th>
<th><strong><font size="3" face="Arial, Helvetica, sans-serif">Secret Formula</font></strong></th>
</tr>
<?php
$i=0;
while ($i < $num) {
$area_1 = mysql_result($result,$i,"area_1");
$address = mysql_result($result,$i,"address");
$price = mysql_result($result,$i,"price");
$secret_formula = mysql_result($result,$i,"secret_formula");
?>

<tr> 
<td style="border-style: solid; border-width: 1" bgcolor="#CCFFFF" align="left" width="70"><font size="2" face="Arial, Helvetica, sans-serif"><? echo "$area_1"; ?></font></td>
<td style="border-style: solid; border-width: 1" bgcolor="#CCFFFF" align="left" width="500"><font size="2" face="Arial, Helvetica, sans-serif"><? echo "$address"; ?></font></td>
<td style="border-style: solid; border-width: 1" bgcolor="#CCFFFF" align="left" width="90"><font size="2" face="Arial, Helvetica, sans-serif"><? echo "$price"; ?></font></td>
<td style="border-style: solid; border-width: 1" bgcolor="#CCFFFF" align="left" width="90"><font size="2" face="Arial, Helvetica, sans-serif"><? echo "$secret_formula"; ?></font></td>
</tr>
<?php
++$i;
} 
echo "</table>";
?>
</body>
</html>

herve

Yes, it's because your get differents results from your differents queries but there :

while ($i < $num) {
$area_1 = mysql_result($result,$i,"area_1");
$address = mysql_result($result,$i,"address");
$price = mysql_result($result,$i,"price");
$secret_formula = mysql_result($result,$i,"secret_formula");

you do as if there's always all the same columns.

for instance, if this query is executed :

$query="SELECT SUM((price*3))/COUNT(*) AS mean FROM average_income WHERE (area_1 like '%$drop_down_box_1%') AND (area_1 like '%$drop_down_box_2%')";

There will be no "area_1" column in the results (only a "mean" column)

You need to change the logic of your script.

gardnc

transfield:

Put the code right after you define the SQL query, i.e.

$query="SELECT id, area_1, address, price, price*3 AS secret_formula FROM average_income WHERE (area_1 like '%$drop_down_box_1%') AND (area_1 like '%$drop_down_box_2%')"; // Define query

$reslut = $mysql_query($query)
or die("Bad query"); // execute query

$rows = mysql_num_rows($result); // get the number of rows returned by the query

$sum = 0; // Set sum to zero
$total = 0; // Set default average

while ($rs = mysql_fetch_array($result)) { // Loop through each record
$sum += $rs['secret_formula']; // add this rows number to sum
// Anything else you want to do with each row of data
}

mysql_free_result($result); // Free the mysql result set

if ($sum > 0) { // use decision to prevent dividing by zero
$total = $sum/$rows; // Calculate the average
}

// Anything else you want to do with the compled results.

transfield

Thank you herve. Now I understand the logic. I appreciate your explanation.

Thank you for your detailed explanation, gardnc. I'll try it out.

My sincere thanks to both of you.

Roger_Ramjet

bradgrafelman wrote:
Note that if you have large tables, that's probably going to add some strain in the SQL server regardless of indeces.

No it is not. All relational databases are designed to process large data sets efficiently, and are benchmarked at 10 to 20 times faster than doing the same in code in any programming language, but especially compared to interpretted languages like php.

Transfield, this probably needs to be done qith sub-queries to make the mean column available for the calculation in each row. Check if your version supports sub-queries first, ver 4.1 or above. If it does then you can use a GROUP BY and the aggregate function AVG() in a sub-query to get your value for calculations.

$where = "(area_1 LIKE '%$drop_down_box_1%') AND (area_1 LIKE '%$drop_down_box_2%')";
$sub_query = "SELECT AVG(price*3) AS mean FROM average_income 
     WHERE " . $where . " GROUP BY price"; 

// included in an outer query

$sql = "SELECT id, area_1, address, price, price*3 AS secret_formula, mean FROM average_income, (" . $sub_query . ") AS s 
   WHERE " . $where;

Now, that probably will need some tinkering for syntax but the principle is correct.

The sub-query is going to return a table called s with a column called mean and only 1 row, that is a Subquery as Scalar Operand . The cartesian join (comma) means that this table will be joined to every row in the average_income table and so every row now has the mean available for selection.

I seperate the component parts like the WHERE clause to ensure that it is that it is the same in both the inner and the outer query, and the sub-query to make it easier to read.

It is being able to do stuff like this that makes SQL a beautifull language.

gardnc

I ran a test of both the sql query method of finding the mean, and the php coded method on a database with 50,000 records.

The SQL version is faster at almost four times faster, but still the php code takes less than .4 seconds. So both take negligible cpu resources unless the host is already overburdened.

transfield

Thanks for your reply, Roger. I appreciate your experience on this matter. I'm using php version 4.3.11 so your example should work. I will certainly experiment with it & thank you all for this learning experience.

Have a good day.

Roger_Ramjet

Gardnc, only 4 times faster? Not worth bothering about then is it? :tongue-in-cheek:

OK, my concept of good programming was formed in the '70s when every cpu cycle was very expensive and not to be wasted. These days, hardware is so cheap that we can be profligate with resources; but try it on a database with a million records or more and 4 times faster will seem like a great idea, or in an environment with a few thousand concurrent users.

gardnc

Roger,

Certainly wasn't establishing an argument over methods. I too worked with systems in the 60's with extremly limited resources. As a further simple test, I expanded the number of records to 300,000. The SQL query still took under a second and the php code took almost 6 seconds. So obviously it will scale far better by using SQL then the php intrepreter.

But the question was how to do it in php, and it was a php "newbie" forum, and I am a php programmer - not a database administrator. So chose the path that was easiest for me and what I thought was clearest for a beginning programmer to follow. I did the test just to show him that it probably was not going to be an issue since he expressed concern over his 41,000 records.

I'm glad there is so much expertise available for these forums.

Roger_Ramjet

Its OK gardnc, I was being :tongue-in-cheek: as I tried to indicate.

I'm glad that you ran these benchmark tests as I'm sure you and others reading this thread will have absorbed the lesson and will begin to see sql as an important tool that can be used to great advantage. Being able to demonstrate the scaling advantage to your customers will win you new clients and new business when the competition is doing it all in code. Remember, clients are always going to hope and expect that they will be successful and need this scaling efficiency. Even of they have not thought about it, tell them about it and they will be impressed at your knowledge and expertise. Then they will turn it against the competition for you.

Newbies take note: programming languages and paradigms come and go but SQL is here to stay. It is also the most transferable skill cos it stays the same whether you are coding in ASP, PHP, .NET, on *NIX or NT, or almost any platform and language. Indeed, I'm just reading a survey in Computer Weekly about IT skills demand and SQL is top of the list.