Hello!
I have a table called Events and one of the colums is called "country". I want to be able to query events based on the user country. So eventID #1 might have "country" = US, CA, MX (multiple values), so if a user is from the US logs-in will see the eventID 1 , but also if a user from CA logs-in will see the same event.

I created a <select name=country[] multiple> with a list of countries. I would like to be able to select several countries and save them in a colum. I tested json_encode and serialize and it like better json_encode but I was wonderig what's your view?

so on my form i have someeting like this....

<select name="country[]" size="10" multiple>
<?php foreach ($countries as $key => $val) { ?>
<option value="<?=$key?>" <?php if (in_array($key, json_decode($event->country))) { echo 'selected'; } ?>><?=$val?></option>
<?php } ?>
</select>

my controller looks like this:

function save($id)
{
$event = Event::find($id);
$event->country = json_encode($input['country']);
$event->save();
}

my query looks like this

function getEvents() {
return 'Select * From events where country IN ($user->country) ';
}
Member Avatar for diafol

If you're going to save this in a DB, I wouldn't serialize - I'd use a link table:

user (user_id,...) | events (event_id,...,country_id)| countries (country_id, ...) | user_countries (uc_id, user_id, country_id)

So, maybe soemthing like...

SELECT e.event, c.country FROM events AS e 
    INNER JOIN countries AS c ON c.country_id = e.country_id 
    INNER JOIN user_countries AS uc ON uc.country_id = c.country_id 
    WHERE uc.user_id = 236   

Anway, not tested - off top of my head.

thanks diafol. that is how is have it....User, Events, Events_countries and Countries tables. But after saying someone storing json i kind of wanted to go the same way...It will save me so much coding..

Member Avatar for diafol

It will save me so much coding..

Initially maybe, but you may need to search the DB and do more complicated things than just a data dump. You'll be comprimising the flexibility to run custom queries. It may be fine for now, but your code may develop where you need to run different queries and with a json structure, you may have to dump the whole data, decode and sift throught the resulting array - or get javascript to do it for you, either way, you end up with more complicated extraction code than if you stored data in a more conventional way. My 2p. Perhaps I'm blinkered ;)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.