formatted output using php and mysql

Question

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

Welcome to Stackoverflow.To be more clear with question.

Nagama Inamdar
– Nagama Inamdar

2015-02-13 04:56:54 +00:00
Commented Feb 13, 2015 at 4:56 — Nagama Inamdar
– Nagama Inamdar, Commented Feb 13, 2015 at 4:56

Alex · Accepted Answer · 2015-02-13 05:39:56Z

1

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

edited Feb 13, 2015 at 5:39

answered Feb 13, 2015 at 5:11

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

junaid khan Over a year ago

does not display any records, empty table appears. only the header are shown.

Alex Over a year ago

so check my names? I have no idea about your real table structure. does offername belongs to logs table? because my changes do nothing new, there is no filter no WHERE conditions, so that can be only names of fields that break the result

junaid khan Over a year ago

logs table ::::::: userid offername amount uamount offertitle date I only need to pick username from other table rest is same. I have edited the question again with more codes i am using

Alex Over a year ago

so I can see 6 fields you try to output, but in your data samles you use only 4 and that is strange. so is offername usually empty? what type is set for this column? try to change offername in my query to offertitle

junaid khan Over a year ago

after change still same, in my question, i displayed sample data.offertitle is empty some times marked as N/A, but offername is always filled up.

|

logsv · Accepted Answer · 2015-02-13 05:57:49Z

0

use DISTINCT Modifiers.
DISTINCT  logs.*

answered Feb 13, 2015 at 5:57

logsv

5546 silver badges17 bronze badges

Collectives™ on Stack Overflow

formatted output using php and mysql

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related