Page 1 of 1
Needing help with MySQL Insert Query for Multiselection box.
Posted: Mon May 05, 2003 2:46 pm
by WizyWyg
Im in need of some assitance in order to implement a multiselect selection box and then inserting the choices as seperate records into a table in a MySql DB.
For the form, Im pulling information from two tables, then inserting it into a third as seperate records, based on what is chosen in the multiselect box.
From table 1 , Im pulling the last record inserted.
From table 2 , Im pulling in the memberid and membername (multiselect box)
For table 3, should insert whatever is selected in the multiselect box as seperate records from table 1.
Ie, if last record was #45
Multiselect box chosen were id's 3,4,6,9 (shown name of member, but only their id will be inserted into the table)
Table 3 would result in :
45 | 3
45 | 4
45 | 6
45 | 9
My multiselect box code looks like this:
Code: Select all
<select size=10 multiple name="who">
<?php
$q="select member_id, membername from members
order by member_id asc";
$r=mysql_query($q) or die(mysql_error().$q);
while($row=mysql_fetch_array($r)) {
echo "<option value="".$rowї"member_id"]."">".$rowї"membername"]."</option>";
}
?>
</select>
The lastrecord input field value is "event_id".
How can I construct the MySQL insert query so that it loops through the chosen selections and inserts them until all are inserted?
Posted: Mon May 05, 2003 3:26 pm
by volka
you might add a hidden filed shipping the event_id.
Use name="who[]" for the select-element and you will receive an array with all selected items. You can iterate all those values with foreach() and add them one by one but I prefer to add them with one query.
Try
Code: Select all
if (isset($_POST['event_id']) && isset($_POST['who']) && count($_POST['who'] > 0))
{
$query = "INSERT INTO tablename (event_id, member_id) VALUES($_POST[event_id], ";
$query.= join("),($_POST[event_id],", $_POST['who']);
$query.= ')';
echo $query;
}
I'm a bit in a hurry right now, so no comments for the moment. Hope it helps anyway...
Posted: Mon May 05, 2003 3:49 pm
by WizyWyg
the only problem i can't understand is this
Code: Select all
$query.= join("),($_POST[event_id],", $_POST['who']);
since join() is an alias for implode() what am I joining? (") is not a value I take it.
Posted: Mon May 05, 2003 4:04 pm
by volka
"),($_POST[event_id]," is the glue string.
To illustrate this try
Code: Select all
<html><body><pre><?php
$arr = array(1,2,3,4,5,6);
echo join(',', $arr), "\n";
echo join('),(', $arr), "\n";
echo join('),(45', $arr), "\n";
echo join('),(45,', $arr), "\n";
$val = 45;
echo join("),($val,", $arr), "\n";
?></pre></body></html>
Posted: Mon May 05, 2003 4:13 pm
by WizyWyg
Okay, i was wondering because on the given snippet (i played around with it as well) but your posted one gives a bad join() error. And modifying it, gives me parse errors.
Posted: Mon May 05, 2003 4:43 pm
by volka
which version of php? OS?
Code: Select all
<html><body>
<?php
if (isset($_POST['event_id']) && isset($_POST['who']) && count($_POST['who'] > 0))
{
$query = "INSERT INTO tablename (event_id, member_id) VALUES($_POST[event_id], ";
$query.= join("),($_POST[event_id],", $_POST['who']);
$query.= ')';
echo $query;
}
?>
<form method="POST">
<input type="hidden" name="event_id" value="45" />
<select multiple="multiple" name="who[]">
<option>1</option>
<option>2</option>
<option>3</option>
<option>4</option>
<option>5</option>
<option>6</option>
</select>
<input type="submit" />
</form>
</html></body>
works fine for me
Posted: Mon May 05, 2003 5:00 pm
by WizyWyg
PHP Version 4.1.1
MySql 3.23.39
Apache server on Win 2000
Posted: Mon May 05, 2003 5:13 pm
by WizyWyg
Let me post the original code since I think Im getting some things confused: (I tested your sample and it works as well). I think that it has to do with the current code I have:
Code: Select all
//===============================
// Record Form Queries
//-------------------------------
function participant_action($sAction)
{
global $db;
global $tpl;
global $sForm;
global $sparticipantErr;
$bExecSQL = true;
$sActionFileName = "";
$sParams = "?";
$sWhere = "";
$bErr = false;
$pPKid = "";
$fldeventid = "";
$fldwho = "";
// Event begin
$sActionFileName = "admin_event.php";
$sParams .= "eventid=" . urlencode(get_param("Trn_eventid"));
// CANCEL action
if($sAction == "cancel")
{
header("Location: " . $sActionFileName . $sParams);
exit;
}
// Build WHERE statement
if($sAction == "update" || $sAction == "delete")
{
$pPKid = get_param("PK_id");
if( !strlen($pPKid)) return;
$sWhere = "id=" . tosql($pPKid, "Number");
}
// Load all form fields into variables
$fldeventid = get_param("eventid");
$fldwho = get_param("who");
// Create SQL statement
switch(strtolower($sAction))
{
case "insert":
$sSQL = "insert into participants (" .
"eventid," .
"who)" .
" values (" .
tosql($fldeventid, "Number") . "," .
tosql($fldwho, "Number") .
")";
break;
case "update":
$sSQL = "update participants set " .
"eventid=" . tosql($fldeventid, "Number") .
",who=" . tosql($fldwho, "Number");
$sSQL .= " where " . $sWhere;
break;
case "delete":
$sSQL = "delete from participants where " . $sWhere;
break;
}
// Execute SQL statement
if(strlen($sparticipantErr)) return;
if($bExecSQL)
$db->query($sSQL);
header("Location: " . $sActionFileName . $sParams);
exit;
}
Posted: Tue May 06, 2003 6:53 am
by volka
ah, there already is a function wrapping your database-access.
what does get_param() do?
Posted: Tue May 06, 2003 2:29 pm
by WizyWyg
Okay, let me post the functions that I have, sorry didn't realize that wasn't defined in the code. Forgot that the get_param is in the common file.
Code: Select all
function get_param($param_name)
{
global $HTTP_POST_VARS;
global $HTTP_GET_VARS;
$param_value = "";
if(isset($HTTP_POST_VARS[$param_name]))
$param_value = $HTTP_POST_VARS[$param_name];
else if(isset($HTTP_GET_VARS[$param_name]))
$param_value = $HTTP_GET_VARS[$param_name];
return $param_value;
}
Now, I was given this as well to define the function get_multiple which is where I think is the problem:
Code: Select all
function get_multiple($param_name)
{
global $HTTP_POST_VARS;
global $HTTP_GET_VARS;
$Where = "";
if (isset($HTTP_GET_VARS[$param_name]))
{
$array_p = $HTTP_GET_VARS[$param_name];
}
if (isset($HTTP_POST_VARS[$param_name]))
{
$array_p = $HTTP_POST_VARS[$param_name];
}
if ($array_p)
{
$size_p = count($array_p);
//echo "count numbers: ". $size_p."<br>";
for ($i = 0; $i< $size_p+1; $i++)
{
if (strlen($Where) == 0)
$Where .= " ". $param_name. "=". ToSQL($array_p[$i], "Number");
else
$Where .= " OR ".$param_name. "=". ToSQL($array_p[$i], "Number");
// echo $Where."<br>";
}
}
return $Where;
}
In the above code I posted, I was shown to do this:
Code: Select all
// Create SQL statement
switch(strtolower($sAction))
{
case "insert":
//---BEGIN Custom Code----
$members = get_multiple("who");
$number = sizeof($members);
$i = 0;
while ($i < $number)
{
$first = array_shift ($members);
$SQLinsert = "insert into participants(eventid, who) values (".$fldeventid.", ".$first.")";
$db->query($sSQLinsert);
$i++;
}
//---END Custom Code------
$sSQL = "insert into participants (" .
"eventid," .
"who)" .
" values (" .
tosql($fldeventid, "Number") . "," .
tosql($fldwho, "Number") .
")";
break;
case "update":
$sSQL = "update participants set " .
"eventid=" . tosql($fldeventid, "Number") .
",who=" . tosql($fldwho, "Number");
$sSQL .= " where " . $sWhere;
break;
case "delete":
$sSQL = "delete from participants where " . $sWhere;
break;
}
I think the problem is lying in the defined function. Any ideas?
Posted: Tue May 06, 2003 10:15 pm
by WizyWyg
volka
is it possible for me to send you the files? so you can take a look at them and see what is wrong?
Posted: Wed May 07, 2003 7:18 am
by volka
please keep your files

get_multiple() alsways returns a
where-clause string which is not feasable for
insert querries. But using the original code (without get_multiple) you might try
Code: Select all
case "insert":
$sSQL = 'insert into participants (eventid, who) values ';
$bAppened = FALSE;
foreach($fldwho as $who)
{
if ($bAppened)
$sSQL .= ',';
else
$bAppened = TRUE;
$sSQL .= '('. tosql($fldeventid, 'Number') . ',' .tosql($who, 'Number'). ')';
}
break;
If you named the selected-element who
[] get_param("who") should return an array and the loop appends
(<event_id>, <next who-id>) to the query string for each entry in
$fldwho.
About your delete and update function I'm in doubt. What can be deleted/updated how?
Posted: Wed May 07, 2003 2:51 pm
by WizyWyg
Thanks volka.
I am getting an invalid argument for foreach(), however.
Warning: Invalid argument supplied for foreach() in c:\apache\htdocs\reserve\admin_participant.php on line 213
Database error: Invalid SQL: insert into participants (eventid, who) values
MySQL Error: 1064 (You have an error in your SQL syntax near '' at line 1)
Session halted.
Are you sure you just dont want to see the pages?
Posted: Wed May 07, 2003 3:08 pm
by WizyWyg
Volka,
that's alright. GOT IT to work! thanks for putting up with me and for all your help. I had one of the fields that had an extra ; ^_^