CODEWITHSUNDEEP
phpmysqlarrayssortingjoomla
user1278673
user1278673

Reputation:

MySQL comma separated values

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

$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 I join, there are comma separated values '1,2' and after creating an associative array where I group the services to their zones a singular service only goes to a singular zone, instead of when the value in the table is '1,2' it should be in both zones... I have a feeling it's because of 'zone.name AS zone', because then it only returns one name and if I say 'zone AS zone' it return comma separated numbers...

Here's 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 display it by:

<?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:

enter image description here

And the Zones Table:

enter image description here

Upvotes: 6

Views: 2707

Answers (1)

Jeroen van den Broek
Jeroen van den Broek

Reputation: 863

This is why people usually use link tables to deal with many-to-many relationships like this. One service can have multiple zones, one zone can have multiple services. Querying a comma separated field and using it in a join with another table is pretty tough.

You have two options, one of which involves doing it the right way, and one allows you to keep your current database model. I strongly suggest you do it the right way, but to answer your question I'll go into the other option first.

Simply separate your queries. Just get the comma separated field in your main query, then run a second query to get the zone names:

 SELECT * FROM zone WHERE id IN ($zoneIdsYouSelected)

Now, if you're working with a listing, this is probably not very performant because it means you'll have 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 the $zoneIdsYouSelected. That way you can get away with 2 queries instead if the one you were planning to achieve, as opposed to one query per line of data.

The right way involves making a link table that simply contains two fields: zone_id and service_id. If you then make a query like this, 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'll probably have to tweak that query a bit, but I hope it's clear. Keep in mind that GROUP_CONCAT is not supported by all databases but MySQL has it.

Again, both solutions can work fine and be performant, but having a comma separated field will create more problems just like this one. I strongly suggest you change your database structure. Read up on database normalization if you're not familiar with the idea of link tables.

Upvotes: 7

Related Questions