Explode question

[We Must Design]

I have a string sored in my database like this 2,1|2,5|3,6

 

I have exploded this to get the following:

 

2,1

2,5

3,6

 

I now need to explode this again and as I need to query the database with the numbers. For example:

 

2,1 needs to be exploded as this Represents 2 Parts Vodka (The 1 is the id for vodka)

 

Does anyone know how I could do this. See http://www.wemustdesign.com/cocktail/ to see what I have done so far.

 

$query="SELECT * FROM cocktails";
$result=mysql_query($query);
$num=mysql_numrows($result);

$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$name=mysql_result($result,$i,"name");
$ingredients=mysql_result($result,$i,"ingredients");
echo "id: $id | name: $name<br><br />
";

$ic=0;
$ingredients_explode=explode("|",$ingredients);
$ingredients_count=count ($ingredients_explode);
echo"count: $ingredients_count<br />";
while ($ic<$ingredients_count){

echo"$ingredients_explode[$ic]<br />";

$ic++;
}


$i++;
}

 

 

[Yesideez]

You can perform another explode in the "1,2" using:

$var1=explode(",",$var2);

[GingerRobot]

You were on the right lines, but ive cleaned up the code you had to make it a bit shorter and easier to follow (hopefully!)

 

<?php
$query="SELECT * FROM cocktails";
$result=mysql_query($query);
while($row = mysql_fetch_assoc($result)){
echo "id: $row[id] | name: $row[name]<br><br />";
$ingredients_explode=explode("|",$row[ingredients]);
foreach($ingredients_explode AS $ingredient){
	$ingredient_parts = explode(',',$ingredient);
	$parts = $ingredient_parts[0];
	$ingredient_id = $ingredient_parts[1];
	//Im not sure how youre getting that vodka is id 1 etc im guessing a database table - in which case, select from there where your id is $ingredient_id

}
}
?>

[We Must Design]

Thanks a lot, worked perfectly , thanx very much!

[We Must Design]

I am creating a database of cocktails. In the database I have 2 tables, 'Ingredients' and 'Cocktails'.

 

Cocktail X's ingredients will be stored as:

 

2,1|2,5|1,3

 

This explodes to:

 

2,1

2,5

1,3

 

and then again to:

 

2 parts orange

2 parts lemonade

1 part vodka

 

How do I create a search for this database as you are not able to just simply query the database to see which cocktails contain vodka for example?

[trq]

Much easier to design the database properly in the first place. Take a look at this tutorial on database normalization, it will make relating data much simpler, and therefore your queries will also be much easier.

[Yesideez]

I think I'd be tempted to change the way you're storing the ingredients in the database. Maybe something like:

 

2=orange,2=lemonade,1=vodka

 

That way you can do a search like this:

SELECT * FROM cocktails WHERE ingredients LIKE '%vodka%'

 

To explode this first use:

$ingredients=explode(",",$row['ingredients']);

 

Then explode this further:

$measure=explode("=",$ingredients[$index]);
echo $measure[0].' parts '.$measure[1];

Where $index could be a loop to display the ingredients of each cocktail.

 

Just a thought...

[We Must Design]

Well this is how I was going to design the database from the start but I just thought it would be better to have a seperate table for all of the ingredients.

 

Anybodyelse have any suggestions on the best way to store the ingredients?

[trq]

Did you take a look at the link I provided?

[wbartels]

The list() is very handy in combination with explode().

It can simplefy this code from:

<?php
$ingredient_parts = explode(',',$ingredient);
$parts = $ingredient_parts[0];
$ingredient_id = $ingredient_parts[1];
?>

 

to:

<?php
list($parts, $ingredient_id) = explode(',', $ingredient);
?>

[We Must Design]

Yes I have read the article. I know that it would be best to have seperate tables which I done from the stsrt. The only problem I found was linking up the 'amounts' with the ingredients.

 

If I have all of the ingredients in one table how do I store the 'amount' that is needed to make the cocktail in the second table. This is why I started using 2,3 where the first number is the number of shots needed and the second number is the id of the ingredient from another.

 

Got this working ok but just didnt know how I would create a search for the database.

[trq]

This is why I started using 2,3 where the first number is the number of shots needed and the second number is the id of the ingredient from another.

 

Why not make these two seperate fields? eg;

 

CREATE TABLE ingredients (
  id INT PRIMARY KEY AUTO INCREMENT,
  description VARCHAR(80)
);

CREATE TABLE  cocktails (
  id INT PRIMARY KEY AUTO INCREMENT,
  description VARCHAR(80)
);

CREATE TABLE cocktail_ingredients (
  id INT PRIMARY KEY AUTO INCREMENT,
  cocktail_id INT, # relates to the cocktails table.
  ingredient_id INT, # relates to the ingredients table
  parts INT
);

 

Then.. to create cocktail X, firstly we'd need to define the ingredients.

 

INSERT INTO ingredients (description) VALUES ('orange');
INSERT INTO ingredients (description) VALUES ('lemonade');
INSERT INTO ingredients (description) VALUES ('vodka');

 

These are now in the database and can also be used in other cocktails. next we need to define the name of our cocktail.

 

INSERT INTO cocktails (description) VALUES ('x');

 

Then all we need to do is insert the ingredients and parts related to our cocktail.

 

INSERT INTO cocktail_ingredients (cocktail_id, ingredient_id, part) VALUES (1,1,2);
INSERT INTO cocktail_ingredients (cocktail_id, ingredient_id, part) VALUES (1,2,2);
INSERT INTO cocktail_ingredients (cocktail_id, ingredient_id, part) VALUES (1,3,1);

 

Now... to find the name of any cocktail containing vodka.

 

SELECT cocktail.description AS cocktail_name, cocktail_ingredients.ingredient_id
FROM cocktail, cocktail_ingredients
WHERE cocktail_ingredients.ingredient_id = 3;

 

My syntax may be a little off as its been a long time since Ive used MySql, but hopefully this will give you the idea. There alot more you can do with this sort of design as well.