Solved

Combining mulitple MYSQL rows into one php variable?

Posted on 2007-04-10
10
367 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
Comment Utility
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
Comment Utility
Thanks for such a quick responce. I have tried group_concat() and it just didnt produce.
0
 
LVL 35

Expert Comment

by:Raynard7
Comment Utility
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
 
LVL 2

Expert Comment

by:taveirne
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:kpandelakis
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now