Solved

Combining mulitple MYSQL rows into one php variable?

Posted on 2007-04-10
10
369 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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Wordpress French and English Site 6 95
php error 27 55
MySQL Persistent Connections 10 30
JSON decode 5 12
This article discusses four methods for overlaying images in a container on a web page
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

679 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