SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Jun 2013
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face 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.

    PHP Code:

    $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.

    <table border='1'>
    <tr>
    <th>Apples</th>
    </tr><tr><td>10</td></tr></table><table border='1'>
    <tr>
    <th>Apples</th>
    </tr><tr><td>5</td></tr></table>


    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.
    Last edited by b9rry; Jun 27, 2013 at 09:29. Reason: spelling

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    You could put the results in an array, and then do anything you want with them.

  3. #3
    SitePoint Member
    Join Date
    Jun 2013
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Heh, you make it sound so easy!

    Thank you, that sets me onto the right track then. I'll get swatting ;-)

    Hopefully i can get somewhere and post back here.

    Thanks.

  4. #4
    SitePoint Member
    Join Date
    Jun 2013
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP 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 ;-)

  5. #5
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,389
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    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:
    PHP 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]'");

    $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:
    PHP Code:
    $query mysqli_query($con"SELECT apples FROM users2 WHERE username='$_POST[user1]' OR username='$_POST[user2]'");
    $results mysqli_fetch_all($queryMYSQLI_ASSOC);

    echo 
    "The total number of apples is " . ($results[0]['apples'] + $results[1]['apples']); 
    Here I'm using mysqli_fetch_all 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.

  6. #6
    SitePoint Member
    Join Date
    Jun 2013
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •