How to use $result from mysqli_query to perform calculation

Hi all,

I’ve just started a new job and have quite a bit of free time available and thought it would be a good idea to use it productively so i thought i would have a poke and prod and try to learn PHP with MySQL.

I’ve made good progress this week, connecting to db’s, creating tables, using SELECT, UPDATE, WHERE but have hit a little brick wall when i’ve tried to do the following.

$result = mysqli_query($con, "SELECT apples FROM users2 WHERE username='$_POST[user1]'
                     OR username='$_POST[user2]' ORDER BY apples DESC");

while($row = mysqli_fetch_array($result))
    echo "<table border='1'>
  echo "<tr>";
  echo "<td>" . $row['apples'] . "</td>";
  echo "</tr>";
echo "</table>";

I get what i want and expect in that i get a table with the results i asked for.

<table border=‘1’>
</tr><tr><td>10</td></tr></table><table border=‘1’>

I’ve had a brainwave/brainfart however of using $result to perform a small calculation. Such like user1’s apples minus users’2 apples then display that result, so somehow I have to get the values out of $result and work with them. If i can’t use $result from the above MySQL statement or my PHP syntax how could i do it differently?

I am grateful for any pointers in the right direction.

I hope this makes sense!

Many Thanks.

You could put the results in an array, and then do anything you want with them.

Heh, you make it sound so easy!

Thank you, that sets me onto the right track then. I’ll get swatting :wink:

Hopefully i can get somewhere and post back here.


O.k so i re-wrote the above SQL query and PHP in the hope that it would break down the process a little better, unfortunately i don’t think i am any further forward.

Here is the new code.

$result1 = mysqli_query($con, "SELECT apples FROM users2 WHERE username='$_POST[user1]'");
$result2 = mysqli_query($con, "SELECT apples FROM users2 WHERE username='$_POST[user2]'") ;

while($calc1 = mysqli_fetch_array($result1))
    echo "The first user has:" . $calc1['apples']  ;

while($calc2 = mysqli_fetch_array($result2))
    echo "The second user has:" . $calc2['apples']  ;
$final = $calc1['apples'] - $calc2['apples'] ;
     echo " The final number is  : " . $final;

If i check and use var_dump($calc2[‘apples’]) I can see that the $final line of code isn’t doing anything because var_dump shows NULL.

Now i’ll have to explain what i think is going on like a noob because i obviously don’t get a lot about php so far.

So i’d like it if i could just type ($result1 - $result2) after the MySQL statement or something like that but the $results are not INT values (even though the column they are in in MYsql is INT) so an array would seem the way to do it but that gives me 3 problems - how to access the values given in $result1 etc as int’s, how to get those values into an array in the first place to access and then do the operation of subtracting them.

I presume an array is the way to do it because my while loop works so well and returns ‘apples’ on both occasions.

I am missing something!

Could somebody point me to the PHP manual or a tutorial etc or even a plain english explanation of logical steps to follow please :wink:

Hi b9rry,

The reason that var_dump($calc2[‘apples’]) gives you NULL is that is because you’re calling mysqli_fetch_array($result2) as part of a while loop. The values of $calc2 are only available inside the loop.

What you could do, is something like this:

$result1 = mysqli_query($con, "SELECT apples FROM users2 WHERE username='$_POST[user1]'"); 
$result2 = mysqli_query($con, "SELECT apples FROM users2 WHERE username='$_POST[user2]'");

$calc1 = mysqli_fetch_array($result1);
echo "The first user has: " . $calc1['apples']; 

$calc2 = mysqli_fetch_array($result2);
echo "The second user has: " . $calc2['apples']; 

$final = $calc1['apples'] - $calc2['apples']; 
echo " The final number is : " . $final;

Even better would be to get all the data you need with only one call to the DB, which you could do by going back to your first query:

$query = mysqli_query($con, "SELECT apples FROM users2 WHERE username='$_POST[user1]' OR username='$_POST[user2]'");
$results = mysqli_fetch_all($query, MYSQLI_ASSOC);

echo "The total number of apples is " . ($results[0]['apples'] + $results[1]['apples']);

Here I’m using [fphp]mysqli_fetch_all[/fphp] to get all rows in the result as an associative array. You can access each row via a numeric index, starting at 0 - here we’ve only got two rows in the result, so I can access them using $results[0] and $results[1] respectively.

Hi fretburner,

I must say a huge thank you for posting this message. I can now see where i was going wrong. Your two examples are excellent and have answered exactly what i wanted to know.

Thank you!