Solved

Combining mulitple MYSQL rows into one php variable?

Posted on 2007-04-10
10
370 Views
Last Modified: 2013-12-12
Using MYSQL
the following select statement renders a multiple rowed response (if there is more then one address_rs in the br_addresses table (MtoM)). Currently I am getting as many records as addresses and I only want ONE record with all the addresses.

select statment:
SELECT cat_name, `ad_offer`, coupon_cat_id, ad_dis, ad_sub, coupon_id, logo_file, address_rs, l_phone_a, l_phone_b
FROM ((br_coupons LEFT JOIN catagories ON
catagories.cat_id = br_coupons.coupon_cat_id) LEFT JOIN br_logos ON
br_coupons.logo_id = br_logos.logo_id) LEFT JOIN br_addresses ON
br_coupons.ad_id_address = br_addresses.ad_id
WHERE ad_id = 9
ORDER BY catagories.cat_name, br_coupons.coupon_id

I think I need to define a variable that gathers all the addresses and use that in the record.
I am a PHP newble and ANY help would be great!
0
Comment
Question by:kpandelakis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 35

Expert Comment

by:Raynard7
ID: 18886154
Hi,

I'm not sure the specifics of what you want to achieve but I would suggest that group_concat() which combines multiple rows into one would be what you are after,

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
0
 

Author Comment

by:kpandelakis
ID: 18886224
Thanks for such a quick responce. I have tried group_concat() and it just didnt produce.
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 18886250
what exactly did you try?

I think what you want is to have multiple addresses from many rows into one ,

if you tried

select cat_name, group_concat(address_rs) from
(
SELECT cat_name, `ad_offer`, coupon_cat_id, ad_dis, ad_sub, coupon_id, logo_file, address_rs, l_phone_a, l_phone_b
FROM ((br_coupons LEFT JOIN catagories ON
catagories.cat_id = br_coupons.coupon_cat_id) LEFT JOIN br_logos ON
br_coupons.logo_id = br_logos.logo_id) LEFT JOIN br_addresses ON
br_coupons.ad_id_address = br_addresses.ad_id
WHERE ad_id = 9
ORDER BY catagories.cat_name, br_coupons.coupon_id
) x
group by 1

does this work?

to combine rows into one is not part of sql syntax, group_concat is mysql's way of combining them, I'd suggest that you may need to play with it a little more.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 2

Expert Comment

by:taveirne
ID: 18886296
eh i skimmed this quickly but i would question your overall algorithm/architecture/approach here if you're getting data you don't expect and then trying to cram it into something.  could you explain your data model some more like what the br/ad tables are, why multiple addresses ?
0
 

Author Comment

by:kpandelakis
ID: 18886427
Well, when I put any group_concat in SQLyog it prouduces an error.
Error Code : 1064
You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '(address_rs) from
(
SELECT cat_name, `ad_offer`, coupon_cat_i
So I just figured that my version of MYSQL (4.0.20 ) didn't like it.
so I thought that handling it through PHP would be cleaner.

My schema is such, I have one table (br_advertisers) that has many "advertisers" (ad_id = pk); each advertiser MAY have MANY addreses, which brings me to table 2 (br_addresses)  (MtoM - ad_id;address_rs (street address);l_phone_a (local phone for THAT address);l_phone_b(toll-free phone for THAT address). Some "Advertisers" have coupon/s hence table 3 (br_coupons) (coup_id(pk);ad_id(to pull the address through br_addresses;ad_offer;ad_sub; ..among others that define the coupon). I also have a br_ad_coup table (M2M) (ad_id,coup_id) that stores which coupon is shown with which advertiser (must be broken out, for some advertisers accept other advertiser's coupons) This table is not really relavant, but wanted to give you  a better picture. If you need me to further explain the br_logos and catagories tables I will, just let me know.

The road I was thinking about taking is query MYSQL the addresses needed, do some fancy PHP string replacements (maybe- this is where I am at a loss) and just print those puppies.
Here is where I am:
$db->query("SELECT concat_ws('•',l_phone_baddress_rs,l_phone_a,l_phone_b)FROM ".$market_abbr."_addresses WHERE coupon_id = ".$coupon);
$articles = array();
while ($db->next_record())
{
      $a = array();
      $a["address_rs"] = $db->f("address_rs");
      $a["l_phone_a"] = $db->f("l_phone_a");
      $a["l_phone_b"] = $db->f("l_phone_b");
      $articles[] = $a;
}
GOAL = to have them formatted as such:
address_rs &#8226;(HTMLbullet) l_phone_a &#8226; (HTMLbullet) l_phone_b <br> (then a line break between addresse when needed)
Your thoughts?
0
 

Author Comment

by:kpandelakis
ID: 18886444
WHOOPS! posted wrong working PHP: i mean...
$db->query("SELECT concat_ws('&#8226;',l_phone_baddress_rs,l_phone_a,l_phone_b) as addresses FROM ".$market_abbr."_addresses WHERE coupon_id = ".$coupon);
$addresses = array();
while ($db->next_record())
{
      $a = array();
      $a["addresses"] = $db->f("addresses");
      $addresses[] = $a;
}
0
 

Author Comment

by:kpandelakis
ID: 18886448
WHOOPS2 I must be getting tired.
$db->query("SELECT concat_ws('&#8226;',address_rs,l_phone_a,l_phone_b)as addresses FROM ".$market_abbr."_addresses WHERE coupon_id = ".$coupon);
$addresses = array();
while ($db->next_record())
{
      $a = array();
      $a["addresses"] = $db->f("addresses");
      $addresses[] = $a;
}
0
 
LVL 35

Accepted Solution

by:
Raynard7 earned 500 total points
ID: 18886458
hi, that version of mysql does not even take proper sql - i'd suggest that if you are doing this with php then you create an object that takes all your records - then have an array of these objects - when you get to a new row check if the data is already there, if so then the addresses are added into the object where it has an array of addresses within the object otherwise a new object is created - then create a tostring method of the object that displays the data as you wish.
0
 

Author Comment

by:kpandelakis
ID: 18886496
I have the data for all the coupons (minus the addresses) formatted and looking great. I thought that I would do this seperate query, format it and toss it in as an added variable. I have the variable placed, I just need help formatting the array.
I think.. does this sound viable?
0
 

Author Comment

by:kpandelakis
ID: 18886827
I got it to work!
This is the meat and bones of it:
$many_addresses = "";
$SQL = "SELECT concat_ws('&nbsp;&#8226;&nbsp;',address_rs,l_phone_a,l_phone_b)as addresses FROM ".$market_abbr."_addresses WHERE ad_id = ".$coupon;

$db->query($SQL);
while ($db->next_record()){
         if($many_addresses != "") $many_addresses .= "<br>";
          $many_addresses .= $db->f("addresses");
      }

      $db->close();
used $many_addresses to populate.

Raynard7: I'll still hook you up with pts!
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question