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'>
<tr>
<th>Apples</th>
</tr>";
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.
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.
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;
}
var_dump($calc2['apples'])
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
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.
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.
