MySQL Comma Separated Values

Question

I currently have a sql and php problem where I use this statement to get everything from services, as well as the tables associated with it:

$db = $this->getDbo(); $query = $db->getQuery(true); // Select the required fields from the table. $query->select( $this->getState( 'list.select', 'a.*' ) ); $query->from('`#__services_service` AS a'); $query->where("a.zone"); $query->select('zone.name AS zone, zone.description AS zone_description, zone.price AS zone_price, zone.interval_recommended AS interval_recommended'); $query->join('LEFT', '#__services_zones AS zone ON zone.id = a.zone'); $query->select('category.icon AS category'); $query->join('LEFT', '#__services_categories AS category ON category.id = a.category'); 

But in the zone table to which I connect, there are values ​​separated by commas "1,2", and after creating an associative array where I group services into my zones, the singular service goes only to a special zone, and not when the value in the table " 1,2 "it should be in both zones ... I have a feeling because of 'zone.name AS zone' , because then it returns only one name, and if I say 'zone AS zone' , it returns numbers separated by comma ...

This is how I sort the array:

 foreach( $this->items as $item ){ if( !isset( $zones[ $item->zone ] ) ) $zone_items[ $item->zone] = array(); $zone_items[ $item->zone ][] = $item; $zones[$item->zone] = array( 'zone' => $item->zone, 'zone_interval' => $item->interval_recommended, 'zone_description' => $item->zone_description, 'zone_price' => $item->zone_price, 'items' => $zone_items[$item->zone] ); } 

and I show it:

 <?php foreach( $zones as $zone): ?> <div class="row-fluid zones-page"> <div class="row-fluid zone-title-block"> <div class="span4 zone-name"><h1><?php echo $zone['zone']; ?></h1></div> <div class="span7 zone-description"> <?php echo $zone['zone_description']; ?> <?php if($zone['zone_interval'] == 1): ?> <span class="interval pull-right">Interval Recommended</span> <?php endif; ?> </div> </div> <?php foreach( $zone['items'] as $items ) :?> <div class="row-fluid zone-services-contain"> <div class="span1"><div class="zone-icon"><?php //echo $items->category; ?></div></div> <div class="span11 zone-services"> <h4><?php echo $items->name; ?></h4> <div><?php echo $items->description; ?></div> </div> </div> <?php endforeach; ?> <div class="row-fluid zone-amount"> only <div><?php echo 'R' . $zone['zone_price']; ?></div> </div> </div> <div class="bicycle-divider"> <div class="bicycle-icon"></div> </div> <?php endforeach; ?> 

Any help is greatly appreciated.

The Services table looks like this:

And the Zones table:

Answer 1

That's why people usually use link tables to deal with such many-to-many relationships. One service can have several zones, one zone can have several services. Querying a field separated by commas and using it in conjunction with another table is quite difficult.

You have two options, one of which assumes the correct solution, and one allows you to save the current database model. I highly recommend you do it right, but to answer your question, I will first go to another option.

Just separate your requests. Just enter a semicolon in your main query, then run the second query to get the zone names:

  SELECT * FROM zone WHERE id IN ($zoneIdsYouSelected) 

Now, if you are working with a listing, this is probably not very indicative, because it means that you will need to run one query for the list and another for each row. An alternative would be to get all the zones into an array, and then select from that using PHP based on $zoneIdsYouSelected . That way, you can slip away with two queries if you plan to achieve, as opposed to a single query per row of data.

The right way is to create a link table that simply contains two fields: zone_id and service_id . If you then make such a request, you will get all the names in one “field” in your result set:

 SELECT s.id, s.name, s.description, s.price, s.category, s.ordering, s.state, s.checked_out, s.checked_out_time, s.created_by, GROUP_CONCAT(DISTINCT z.name ORDER BY z.name ASC SEPARATOR ', ') AS zones FROM service s JOIN service_zone sz ON s.id = sz.service_id JOIN zone z ON z.id = sz.zone_id GROUP BY s.id, s.name, s.description, s.price, s.category, s.ordering, s.state, s.checked_out, s.checked_out_time, s.created_by ORDER BY s.id 

You may have to tweak this query a bit, but I hope this is clear. Keep in mind that GROUP_CONCAT is not supported by all databases, but MySQL has it.

Again, both solutions may work fine and be workable, but having a field separated by a comma will create more problems like this. I highly recommend that you change the structure of the database. Read database normalization if you are not familiar with the idea of ​​link tables.