Order by category

Question

Result i want:

campaign 1
  offer 1.1
  offer 1.2
  offer 1.3
  (and so forth)

campaign 2
  offer 2.1
  offer 2.2
  offer 2.3
  (and so forth)

These are my tables:

campaign (tablename)
  campaign_id (used)
  campaign_name

offer (tablename)
  offer_id
  offer_parentid (uses category_id find which category the item is supposed to be paired 
with.)
  offer_name

i have little experience with using two tables but i find it useful for this kind of thing. my problem is i've tried every aspect of php i know, and i can't get the results i want. this is my final result after several hours of blood, sweat and tears:

$get_campaign = mysql_query("SELECT * FROM campaign ORDER BY campaign_id DESC")
  or die(mysql_error());

$get_offers = mysql_query("SELECT * FROM offers JOIN campaign ON     offers.offer_parentid=campaign.campaign_id ORDER BY campaign_id DESC ")
  or die(mysql_error());

while($row = mysql_fetch_array($get_campaign)){
echo $row['campaign_name']."</br>";
while($row = mysql_fetch_assoc($get_offers)){
  echo $row['offer_name']."</br>";
    }
}

the result becomes:

Campaign 1
 offer 1.1
 offer 1.2
 offer 2.1
 offer 2.2
campaign 2

Thanks for any help i can get

You should stop using `mysql_*` functions. They're being deprecated. Instead use [PDO](http://php.net/manual/en/book.pdo.php) (supported as of PHP 5.1) or [mysqli](http://php.net/manual/en/book.mysqli.php) (supported as of PHP 4.1). If you're not sure which one to use, [read this SO article](http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons). — Matt, Aug 06 '12 at 19:19
Please, don't use `mysql_*` functions to write new code. They are no longer maintained and the community has begun [deprecation process](http://goo.gl/KJveJ). See the *[red box](http://goo.gl/GPmFd)*? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide which, [this article](http://goo.gl/3gqF9) will help you. If you pick PDO, [here is good tutorial](http://goo.gl/vFWnC). — Madara's Ghost, Aug 06 '12 at 19:19
Take a look at the result from your second query. This may be all you need to loop through. — Matt, Aug 06 '12 at 19:20
I'm gonna check on these articles, and see if i can make something of them, Thanks alot! — DarkDomination, Aug 06 '12 at 19:55

davidethell · Answer 1 · 2012-08-06T20:12:35.000

You need to make your SQL for your second query conditional on the first query:

$get_campaign = mysql_query("SELECT * FROM campaign ORDER BY campaign_id DESC")
  or die(mysql_error());


while($row = mysql_fetch_array($get_campaign)){
    echo $row['campaign_name']."</br>";
    $get_offers = mysql_query("SELECT * FROM offers WHERE campaign_id = " . $row['campaign_id'] . " ORDER BY campaign_id DESC ")
  or die(mysql_error());

    while($offerRow = mysql_fetch_assoc($get_offers)){
        echo $offerRow['offer_name']."</br>";
    }
}

Note that I'm assuming the primary key column in your campaign table is "id". You could also do this in one query with a join and just have a single loop, but depending on the size of your data and the amount you really return from the first table two loops is preferable for performance.

campaign_id is primary, so is offer_id, which one should $row['id'] be? — DarkDomination, Aug 06 '12 at 19:47

arnoudhgz · Answer 2 · 2012-08-06T20:18:02.000

You can use one query:

$query = 'SELECT c.campaign_name, o.offer_name
FROM campaign c
  INNER JOIN offers o ON (o.offer_parentid = c.campaign_id)
ORDER BY c.campaign_id';

Now loop through your results:

$campaign = '';
while($row = mysql_fetch_array($query)){
  // only display the campaign name when its not $campaign
  if ($row['campaign_name'] != $campaign){
    echo $row['campaign_name']."</br>";
    $campaign = $row['campaign_name'];
  }
  echo $row['offer_name']."</br>";
}

Note: I must say you use poorly choosen column names. If you refer to the campaign table in the offer table you should choose something like offers.campaign_id. The reason is that you know at this moment what you need, but when you get more tables and columns it would be hard to choose the right column connections between the tables without looking in your PHP code first. It could save you (and others that are working with your code) a lot of time.

@amoudhgz, explain to him why the column names are poorly chosen. I agree with you, but it helps for teaching purposes to explain it so we can all benefit from the knowledge. — davidethell, Aug 06 '12 at 20:13

Order by category

2 Answers2