PHP displaying results from MySQL query

Question

I am trying to wrap my head around this and i have searched google and im not sure how to go about it.

I have a table called Affiliates. In this table i have 3 fields

+---------+-----------------+-------+
| user_id | AffID           | total |
+---------+-----------------+-------+
|       1 | Aff1            |     10|
|       2 | Aff1            |     10|
|       3 | Aff2            |     5 |
|       4 | Aff2            |     5 |
+---------+-----------------+-------+

I want to echo out the following results :

Aff1 20
Aff2 10

As you can see it grouped the AffID and added the total value.

Now if i add another affiliate to the AffID for example AffID3 with a total of 10 , it must automatically just display it.

Aff1 20
Aff2 10
Aff3 10

I know i need to use groupby for the AffID , and Sum to calculate the total, but how would i echo out the results ? (in a loop?)

is there any code at all? it's not easy to help if there's nothing. — Minzkraut
– Minzkraut, Commented Jun 14, 2016 at 8:48
Yes, a loop will do just fine. There are hundreds of tutorials out there, even php manual lists some under the various MySQL APIs. So, pls do some independent digging yourself. — Shadow
– Shadow, Commented Jun 14, 2016 at 8:53
Yes, use a loop. While- or Foreach- loop are perfectly suited for this task as there are no further calculations of row count or incrementation of a counter variable necessary — Philipp
– Philipp, Commented Jun 14, 2016 at 8:53
select sum(total) as Total,AffID,user_id from Affiliates group by AffID order by user_id ASC foreach($result_array as $row) { echo $row['total']."<br/>"; echo $row['AffID']."<br/>"; echo $row['user_id ']."<br/>"; } — JYoThI
– JYoThI, Commented Jun 14, 2016 at 8:56

JYoThI · Accepted Answer · 2016-06-14 09:17:51Z

try something like this

    //db connection

     global $conn;

        $servername = "localhost";  //host name

        $username = "username"; //username

        $password = "password"; //password

        $mysql_database = "dbname"; //database name

    //mysqli prepared statement 

        $conn = mysqli_connect($servername, $username, $password) or die("Connection failed: " . mysqli_connect_error());

        mysqli_select_db($conn,$mysql_database) or die("Opps some thing went wrong");

        $stmt = $conn->prepare("select sum(total) as Total,AffID,user_id  from Affiliates  group by AffID order by user_id ASC");

                    $stmt->execute();
                    $get_result= $stmt->get_result();
                    $row_count= $get_result->num_rows;

                    if($row_count>0)
                    {
                       while($row=$get_result->fetch_assoc())
                       {

                          echo $row['user_id ']."     ".$row['AffID']."   ".$row['Total']."<br/>";


                       }


                    }
                    $stmt->close();
                     $conn->close();

Collectives™ on Stack Overflow

PHP displaying results from MySQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related