Page 1 of 1
foreach question
Posted: Tue Mar 13, 2007 4:03 pm
by $var
i would like to use multiple select to create multiple entries to a table.
my select looks like this, and it displays industries to categorize an event:
Code: Select all
<?
$q5 = 'SELECT * FROM hcw_industry ORDER BY Ind_Ttl ASC';
$resultq5 = mysql_query($q5) or die('Query Error');
$countq5 = mysql_num_rows($resultq5);
echo "<select name=\"eventInd_IndID[]\" id=\"eventInd_IndID[]\" size=\"12\" multiple=\"multiple\">";
for ($i =0; $rowq5 = mysql_fetch_assoc($resultq5); ++$i) {
$indID = $rowq5['Ind_ID'];
$indName = $rowq5['Ind_Ttl'];
echo "<option value=\"$indID\">$indName</option>";
}
echo "</select>";
?>
i know about implode(); but it doesn't seem like it would work the same here.
i feel there needs to be a foreach statement to write an INSERT statement per each selected value.
how would i use the loop to insert multiple entries into this table:
Code: Select all
$q1 = "INSERT INTO hcw_eventsInd (
EventInd_EventID,
EventInd_IndID,
EventInd_IndSubID,
EventInd_EventDate)
VALUES (
'".$eventID."',
'".$_POST["eventInd_IndID"]."',
'".$_POST["eventInd_IndSubID"]."',
'".$eventDate."')";
$result = mysql_query($q1) or die (mysql_error());
Posted: Tue Mar 13, 2007 5:07 pm
by califdon
I'm afraid I don't understand what you want to do. Are you selecting data from one table and you want to insert records with some of that data into another table?? I don't see any connection between your two code snippets. The second one uses $_POST variables. What do they have to do with the first snippet? And I still don't see what any of this has to do with the For Each syntax. Could you explain what you're trying to do?
Posted: Tue Mar 13, 2007 5:25 pm
by RobertGonzalez
It looks like you are building a multiple select list from a select query, then using the data captured in the form to populate rows in your table using the insert query. Is that right?
If so, yes, you want to use a foreach structure. You would need to loop the array passed by the select multiple, and for all items set in that array, process the insert.
Posted: Wed Mar 14, 2007 11:53 am
by $var
yes, everah! that's exactly what i want to do.
i have gotten it this far, but i am having some troubles with the final outcome:
Code: Select all
<?php
$arrINS = array();
FOREACH ($_POST['eventInd_IndID'] AS $key => $val) :
$arrINS[] = "('$eventID','$val','" . $_POST['eventInd_IndSubID'][$key] . "','".$eventDate."')";
ENDFOREACH;
$sqlINS = implode(",", $arrINS);
$q1 = "
INSERT INTO hcw_eventsInd
(EventInd_EventID, EventInd_IndID, EventInd_IndSubID, EventInd_EventDate)
VALUES $sqlINS
";
$result = mysql_query($q1) or die ("SQL Error q1: $$q1<br>" . mysql_error());
?>
this is perfectly exactly what i was looking for, but i find just 2 small errors.
example:
i multi-select 3 values. it writes 4 entries to the database.
the first 3 contain the right ID, however the 4th value returns an ID of 0.
also with this, the 4th value will be the only one to return the date.
for the other 3 values with the proper ID's the date is returned blank.
ID EID IID Date
22 35 1 0000-00-00
23 35 2 0000-00-00
24 35 0 2007-05-15
Posted: Wed Mar 14, 2007 12:28 pm
by RobertGonzalez
Before you actually insert data, try
var_dump() on your array to see what the code will see. The actual testing of the insert should come much later in your development, and at the very least, after you have tested the data that you are capturing.
After you do a
var_dump(), post back with what is dumped and what code was used to create that dump.
Posted: Wed Mar 14, 2007 3:07 pm
by $var
Code: Select all
$sqlINS = implode(",", $arrINS);
var_dump($sqlINS);
string(34) "('35','1','0',''),('35','9','','')"
Posted: Wed Mar 14, 2007 3:26 pm
by RobertGonzalez
And you are not getting any SQL errors with this process?
The only other thing I can recommend at this point is to echo everything out in the foreach loop. That is where those values are being set.
Posted: Wed Mar 14, 2007 3:43 pm
by $var
damn renegade values.
so, i sorted what was going on with the date, i think that i just had the variable placed in a weird spot, i moved it out of the loop and it's fine.
this:
Code: Select all
foreach ($_POST['eventInd_IndID'] as $key => $val) :
echo $arrINS[] = "('$eventID','$val','".$eventDate."')";
var_dump($arrINS);
endforeach;
yields this:
Code: Select all
('35','6','2007-05-15')array(1) { [0]=> string(23) "('35','6','2007-05-15')" }
('35','9','2007-05-15')array(2) { [0]=> string(23) "('35','6','2007-05-15')" [1]=> string(23) "('35','9','2007-05-15')" }
it appears there is an extra being thrown into the array(2)?
i'm very new to error checking, so i can see this, but i don't know what it means.
Posted: Wed Mar 14, 2007 3:52 pm
by RobertGonzalez
Do this and post the results back.
Code: Select all
<?php
foreach ($_POST['eventInd_IndID'] as $key => $val) {
$arrINS[] = "('$eventID','$val','".$eventDate."')";
}
var_dump($arrINS);
?>
Posted: Wed Mar 14, 2007 4:09 pm
by $var
('35','3','2007-05-15')('35','9','2007-05-15')array(2) { [0]=> string(23) "('35','3','2007-05-15')" [1]=> string(23) "('35','9','2007-05-15')" }
Posted: Wed Mar 14, 2007 5:08 pm
by RobertGonzalez
Code: Select all
<?php
$arrINS = array();
foreach ($_POST['eventInd_IndID'] AS $key => $val) {
$arrINS[] = "('$eventID','$val','{$_POST['eventInd_IndSubID'][$key]}','$eventDate')";
}
$sqlINS = implode(",", $arrINS);
$q1 = "
INSERT INTO hcw_eventsInd
(EventInd_EventID, EventInd_IndID, EventInd_IndSubID, EventInd_EventDate)
VALUES $sqlINS
";
echo $q1;
//$result = mysql_query($q1) or die ("SQL Error q1: $$q1<br>" . mysql_error());
?>
The above code is then producing this query...
Code: Select all
INSERT INTO hcw_eventsInd
(EventInd_EventID, EventInd_IndID, EventInd_IndSubID, EventInd_EventDate)
VALUES ('35','3','2007-05-15'),('35','9','2007-05-15')
which I don't understand because you are throwing four vars into that query string you are building, but you are only getting three. You query is trying to insert into four fields, but only getting three values, which should make it puke. Or at the very least, throw errors. So I am totally not sure what is going on here.
