Getting arrays into database

kiss_the_robot

I have an array that looks like this:

$_POST['prods']= 'avacados_jun,avacados_july,avacados_aug,onions_apr,onions_may,lemons_may,lemons_june,greenbellpeppers_june';

I'm trying to get this into a db so that for each distinct vegetable, it inserts a new row, and puts the months for that record in the same row, like so:

Vegetable | Months

avacados | jun, july, aug

onions | apr, may

lemons | may, june

greenbellpeppers | june

What I've done so far follows, I'm just having a hard time figuring out how to structure my loops in concert with a MySQL query to achieve the above result:

$prods = $_POST['prods'];
$prod_months = explode(',', $prods);

$products = array();
$new_product = array();

for ($i=0; $i < sizeof($prod_months); $i++) {
  $products = explode('_',$prod_months[$i]);
  $new_product[$products[0]] = $products[1];
	 if (array_key_exists($products[0], $new_product)) {
	    array_push($new_product, $products[1]);
	 }

}

If I echo things out with the following:

foreach ($new_product as $k => $v) {

if (!is_numeric($k)) {	
	echo '<br />' .$k . ' | ';
} else {
	echo $v . ' ';
} // end non-numeric key check
}

I get something that looks close to what I want, so how would I work in a MySQL query?

Krik

foreach ($new_product as $k =>  $v) {

sql="
    INSERT INTO `Vegetables` ( `Vegetable`, `Months` ) 
    VALUES ( '$k', '$v' )
";
mysql_query($sql);
}

NogDog

And I'll throw in the obligatory database normalization suggestion here that you might want to have a separate month-to-vegetable table here for that many-to-one relationship. 😉

Krik

I would agree with NogDog.

If you notice I had to edit my post. I had overlooked your need to have multiple months per vegetable and so, originally, I cut out your entire first code sample and simplified it. But as you do have multiple months per vegetable you need to have those 10 additional (and unnecessary) lines of code.

kiss_the_robot

NogDog;10961934 wrote:
And I'll throw in the obligatory database normalization suggestion here that you might want to have a separate month-to-vegetable table here for that many-to-one relationship. 😉

Do you mean have one table that stores only vegetable names and one that stores only months?

Krik

Vegetables table

Unique_Id | Vegetable
------------------------------------------------------------
 1 | avacados
------------------------------------------------------------
 2 | onions 
------------------------------------------------------------
 3 | lemons
------------------------------------------------------------
 4 | greenbellpeppers

Months table

Unique_Id | Months
------------------------------------------------------------
 1 | jan
------------------------------------------------------------
 2 | feb
------------------------------------------------------------
 3 | mar
------------------------------------------------------------
 4 | apr
------------------------------------------------------------
 5 | may
------------------------------------------------------------
 6 | june
------------------------------------------------------------
 7 | july
------------------------------------------------------------
 8 | aug
------------------------------------------------------------
 9 | sept
------------------------------------------------------------
10 | oct
------------------------------------------------------------
11 | nov
------------------------------------------------------------
12 | dec

Vegetable_Month table

Unique_Id | Veg_Id | Month_Id
------------------------------------------------------------
 1 | 1 | 6
------------------------------------------------------------
 2 | 1 | 7
------------------------------------------------------------
 3 | 1 | 8
------------------------------------------------------------
 4 | 2 | 4
------------------------------------------------------------
 5 | 2 | 5
------------------------------------------------------------
 6 | 3 | 5
------------------------------------------------------------
 7 | 3 | 6
------------------------------------------------------------
 8 | 4 | 6

One table has all the vegetables names the other has all the a months and the last ties them both together This is handy if you realize that, for example onions, need to be in june also you simply add another row to the Vegetable_Month table

[CODE]

9 | 2 | 6
[/CODE]

Or a better example might be if you suddenly need to have the the fully qualified names or the month number for each month

Unique_Id | Month | Name | Number
------------------------------------------------------------
 1 | jan | January | 01
------------------------------------------------------------
 2 | feb | February | 02
------------------------------------------------------------
 3 | mar | March | 03
------------------------------------------------------------
 4 | apr | April | 04
------------------------------------------------------------
 5 | may | May | 05
------------------------------------------------------------
 6 | june | June | 06
------------------------------------------------------------
 7 | july | July | 07
------------------------------------------------------------
 8 | aug | August | 08
------------------------------------------------------------
 9 | sept | September | 09
------------------------------------------------------------
10 | oct | October | 10
------------------------------------------------------------
11 | nov | November | 11
------------------------------------------------------------
12 | dec | December | 12

You then just have to change the 12 records in the Month table verse the hundreds that could be in the Vegetable_Month table.

And if your php is setup correctly the change has no effect on your existing code. But you can easily change any place that you need to use the new info.

The idea allows for maximum flexibility. Many web designers run into trouble when the site they made has grown a lot and then need to add new features and/or data storage and handling. And because of how they originally set everything up they are forced to basically start over.

The idea we are suggesting is to start out on the right foot. It may seem quite odd if you have not done a lot of database work but believe me years form now you maybe very ecstatic that you set it up this way.

nevvermind

@: wtf question - how did you "draw" that schemata? I like it. 😃

Krik

how did you "draw" that schemata?

If you talking about the db table examples I gave. I actually copied and pasted the db table "kiss the robot" has in his first post. I just used them inside the "CODE" bb tags to take advantage of the <pre> tags that are used for the displaying code.

If I can't build a <table> in the forums I just have to use the tools that are available.

NogDog

PS: Just to take Krik's example to the end use, you would use a JOIN when doing the queries. E.g., if you want the month names for tomatoes:

SELECT name FROM month_table AS mon
INNER JOIN month_to_vegetable AS m2v ON mon.unique_id = m2v.month_id
INNER JOIN vegetable_table AS veg ON veg.unique_id = m2v.vegetable_id 
WHERE veg.vegetable = 'tomato'

More info on database normalization