Explode

[wemustdesign]

Hi,

 

I am creating a webiste about places to visit. Now each place can have different attractions, and I have stored these attractions as follows:

 

Location

Las Vegas

 

Attractions

3 | 6 | 7 | 10

 

The attractions data is the reference to the id of a certain attraction.

 

What I am looking to do is create a total of all all of the locations that have a Casino (id: 3) for example

 

I know I have to explode the Attractions but then how do I query for id=3 for every row?

 

If someone could just point me in the right direction i would be over the moon!

[AyKay47]

you would want something like this perhaps..

 

$attractions = "3 | 6 | 7 | 10";
$explode = explode(" | ", $attractions);
foreach($explode as $value){ //separate each id
  // do whatever with each id
}

 

I'm not 100% following you on this one, but the code above will split each id into a value of the $explode array, then will isolate each id with a foreach loop so you can do what you want with each...

[wemustdesign]

Hi,

 

Thanks for the reply.

 

The only problem is that there are multiple rows in the MySQL table so need to explode all of the values from the attractions column for every row and then add this all up.

 

Las Vegas 3|67|7|89

New York  3|67|89|43

LA              2|67|77|8

 

So this would some up

 

(id  3)Casinos (2)

(id 67)Cinemas (3)

 

I just can't get my head round how I would go through each row and add all these values up. Is the method I am using storing them as 56|78|3|4 the best way to do this do you think?

[AyKay47]

I would store a row for each id, stating the locations of each..so you would have a row of id 3, and the fields would be the location of that id

 

Edit: refer to Abra's post, basically same thing I am saying, with table structure examples etc...

[AbraCadaver]

Hi,

 

Thanks for the reply.

 

The only problem is that there are multiple rows in the MySQL table so need to explode all of the values from the attractions column for every row and then add this all up.

 

Las Vegas 3|67|7|89

New York  3|67|89|43

LA              2|67|77|8

 

So this would some up

 

(id  3)Casinos (2)

(id 67)Cinemas (3)

 

I just can't get my head round how I would go through each row and add all these values up. Is the method I am using storing them as 56|78|3|4 the best way to do this do you think?

 

Stop now and don't do it this way.  You need a table that stores each attraction in a separate row with the location.  Then its much easier:

 

Locations
id   name
1    Las Vegas

Attractions
id   name
1    Casino
2    Brothel

Location_Attractions
location_id    attraction_id
1                   1
1                   2

 

SELECT Locations.name FROM Locations, Locations_Attractions WHERE Locations_Attractions.attraction_id = 2

 

[wemustdesign]

Thanks for your help, I have chage my setup think this will work much better in the lonrun

[AbraCadaver]

Good idea.  Just a change to the example query (I left out the join):

 

SELECT Locations.name
FROM Locations, Locations_Attractions
WHERE Locations.id = Locations_Attractions.attraction_id 
AND Locations_Attractions.attraction_id = 2