CODEWITHSUNDEEP
phpmysqlsql
junaid khan
junaid khan

Reputation: 17

formatted output using php and mysql

My problem details:

logs table

userid offername amount uamount offertitle date

offername are like this

xxx
yyyy
zzzz

My query:

$sqloffers=mysql_query("SELECT user.username, logs.* FROM logs 
INNER JOIN user ON user.userid = logs.userid  ORDER BY logs.offername DESC ");

to display results

<?php

      while($row = mysql_fetch_array($sqloffers))  
{

    echo "<tr> ";
    echo "<td>" .$row[username] . "</td>";
    echo "<td>" .$row[offername] . "</td>";
       echo "<td>" .$row[amount] . "</td>";
    echo "<td>" .$row[uamount] . "</td>";
       echo "<td>" .$row[offertitle] . "</td>";
        echo "<td>" .$row[date] . "</td>";

     }

     echo "</tr> " ;
?>

I want the output to be seperated by each username , aginst offername.

Right now I am getting out like this
username offername amount date
user1 xxxx xxx xxxx
user1 xxxx xxx xxxx
user1 xxxx xxx xxxx
user1 yyyy yyyy yyyy
user1 yyyy yyyy yyyy
user1 yyyy yyyy yyyy

and so on .

I want the output like this

username offername amount date
user1 xxxx xxx xxxx
user1 yyyy yyyy yyyy
user1 zzz zzz zzzz
user2 xxxx xxx xxxx
user2 yyyy yyyy yyyy
user2 zzz zzz zzzz
userN xxxx xxx xxxx
userN yyyy yyyy yyyy
userN zzz zzz zzzz

Clarification:

this table contains offers from different companies completed by users. I need out in such a way that , if user 1 completed 10 offers from offername xxx, and 5 from offername yy, then output should display user1 only 1 time against company xx, and display its count for xxx company, and so on.

I hope i have clarified the question.

Solution that worked after accepting answer:

$sqloffers=mysql_query("
SELECT user.username, logs.offername, logs.date, SUM(logs.uamount) as uamount, SUM(logs.amount) as amount ,count(*) as counter 
FROM logs
INNER JOIN user 
ON user.userid = logs.userid  
GROUP BY user.username
ORDER BY logs.offername DESC

Upvotes: 0

Views: 69

Answers (2)

logsv
logsv

Reputation: 544

use DISTINCT Modifiers.
DISTINCT  logs.*

Upvotes: 0

Alex
Alex

Reputation: 17289

Try this one, just to start:

$sqloffers=mysql_query("
SELECT user.username, logs.offername, logs.date, SUM(logs.uamount) as uamount, SUM(logs.amount) as amount ,count(*) as counter 
FROM logs 
INNER JOIN user 
ON user.userid = logs.userid  
GROUP BY user.username, logs.offername, logs.date
ORDER BY logs.offername DESC 
");

Upvotes: 1

Related Questions