Combining mulitple MYSQL rows into one php variable?

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!
kpandelakisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raynard7Commented:
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
kpandelakisAuthor Commented:
Thanks for such a quick responce. I have tried group_concat() and it just didnt produce.
0
Raynard7Commented:
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
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

taveirneCommented:
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
kpandelakisAuthor Commented:
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
kpandelakisAuthor Commented:
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
kpandelakisAuthor Commented:
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
Raynard7Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kpandelakisAuthor Commented:
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
kpandelakisAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.