asked on

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!

Raynard7

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

kpandelakis

ASKER

Thanks for such a quick responce. I have tried group_concat() and it just didnt produce.

Raynard7

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.

taveirne

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 ?

kpandelakis

ASKER

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 •(HTMLbullet) l_phone_a • (HTMLbullet) l_phone_b <br> (then a line break between addresse when needed)
Your thoughts?

kpandelakis

ASKER

WHOOPS! posted wrong working PHP: i mean...
$db->query("SELECT concat_ws('•',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;
}

kpandelakis

ASKER

WHOOPS2 I must be getting tired.
$db->query("SELECT concat_ws('•',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;
}

ASKER CERTIFIED SOLUTION

Raynard7

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kpandelakis

ASKER

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?

kpandelakis

ASKER

I got it to work!
This is the meat and bones of it:
$many_addresses = "";
$SQL = "SELECT concat_ws(' • ',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!