how do I use data in array from a drop down selection list

[futrose]

What I am trying to do is take the id's selected from a drop down list (which is populated from table ELDERCAT) and join them together with the id being edited/created from a second table ELDERS.  I am doing this by creating a 3rd table ELDERCATEGORY where the eldercatid and eldersid fields are matched up making an elder related to a category.

 

I don't know how to get the information out of the array I have created to insert into the ELDERCATEGORY table where the id is equal to the elder being created/edited.  Here is the code I have so far.

 

*note: I can make it work when only one option is selected from the dropdown (a string)  list but not when multiple options are selected (as an array).

 

index.php - code snippet to ADD new elder ELDERS table, code to edit is similar so if I can get this I can fix the edit section on my own

  include '../../includes/db.inc.php';

  //other variables
  ...
  $eldercatid = mysqli_real_escape_string($link, $_POST['eldercatid']);  //I know mysqli_real_escape_string wont work on array but not sure how to fix that

  $sql = "Insert into elders set
image = '$uploadname',
LastName = '$LastName',
HFirst = '$HFirst',
WFirst = '$WFirst',
position= '$position',
email = '$email',
email2 = '$email2',
bio = '$bio',
avail = '$avail',
orderby = '$orderby'";
  if (!mysqli_query($link, $sql))
  {
$error = 'Database error storing elder download!'. mysqli_error($link);
include '../../includes/error.php';
exit();
  }
  
$result = mysqli_query($link, 'Select id from elders order by id DESC LIMIT 1');  //selects id from elder just created in ELDERS table
if (!$result)
{
$error = 'Error fetching elder id: ' . mysqli_error($link);
include '../../includes/error.php';
exit();
}  
while($row = mysqli_fetch_array($result))
{
$id = ($row['id']);
}

  $sql = "Insert into eldercategory set
  eldercatid = '$eldercatid',
  elderid = '$id'";
  if (!mysqli_query($link, $sql))
  {
$error = 'Database error storing elder and category id!'. mysqli_error($link);
include '../../includes/error.php';
exit();
  }

 

elders_modify.php - page with drop down list passes variables back to index.php

...
<form action="?<?php htmlout($action); ?>" method="post" enctype="multipart/form-data">

<table cellspacing="5" cellpadding="1" border="0">
<tr>
<td>Staff Category:</td><td><select name="eldercatid[]" multiple>
<?php foreach ($eldercat as $ec): ?>
<option value="<?php echo $ec['id'] ;
?>" <?php if ($action == 'elder_edit')
{
foreach ($selected as $s):
if ($ec['id'] == $s['eldercatid'])
{
echo 'selected = "yes"';
}
endforeach;
}
echo '>';
echo $ec['category']; ?></option>
<?php endforeach; ?>
</select>
</td>
</tr>
...

 

Thanks for any help.

 

[Psycho]

OK, first off you do not need a while loop here since you are only getting one record

$row = mysqli_fetch_array($result))
{
    $id = ($row['id']);
}

 

You can just use this:

$row = mysqli_fetch_array($result);
$id = ($row['id']);

 

Ok, now for your specific issue. You will need to use the "other" format for INSERT queries and "build up" a single insert query to create all the records.

 

Remove this:

$eldercatid = mysqli_real_escape_string($link, $_POST['eldercatid']);

You don't use mysqli_real_escape_string() on values that should be numeric values. Instead you need to validate the value as a number and/or int value!

 

Replace this:

  $sql = "Insert into eldercategory set
  eldercatid = '$eldercatid',
  elderid = '$id'";

 

With this:

//Process each value in the array into a complete INSERT value
$values = array();
foreach($_POST['eldercatid'] as $eldercatid)
{
    $eldercatid = intval($eldercatid);
    $values[] = "('$eldercatid', '$id')";
}

//Create the SQL statemetn using implode on the values
$sql = "Insert into eldercategory (`eldercatid`, `elderid`)
        VALUES " . implode(', ', $values);

[futrose]

Thanks mjdamato for that.  Works great.  Can you show me what the UPDATE version would be for the same code?  I did something like this but it didn't work.

 

//Process each value in the array into a complete UPDATE value
$values = array();
foreach($_POST['eldercatid'] as $eldercatid)
{
    $eldercatid = intval($eldercatid);
    $values[] = "('$eldercatid', '$id')";
}

//Create the SQL statemetn using implode on the values
$implodearray = implode(', ', $values);
$sql = ("Update eldercategory where elderid = $id set $implodearray");

[futrose]

I took a look at the values that were created in the eldercategory table.  The INSERT code that you suggested only creates 1 row with an elderid and an eldercatid.  What I need it to do is if multiple options are selected create a row for each of the options selected, in the eldercategory table, set the elderid = the new elder id, and the eldercatid to one of the selected categories.  So if a staff member is selected to be part of three categories (ie Pastor, Elder, Board member) then there should be 3 rows created like so

 

elderid    eldercatid

28              2

28              3

28              7

 

anyone know how to do that?

[Psycho]

I took a look at the values that were created in the eldercategory table.  The INSERT code that you suggested only creates 1 row with an elderid and an eldercatid.  What I need it to do is if multiple options are selected create a row for each of the options selected, in the eldercategory table, set the elderid = the new elder id, and the eldercatid to one of the selected categories.  So if a staff member is selected to be part of three categories (ie Pastor, Elder, Board member) then there should be 3 rows created like so

 

The code I provided will do exactly that. You should validate 1) the data being sent in the POST variable and the query being generated. As for doign multiple UPDATES, you can't do what you are wanting in that manner. The query you are tying to generate has a single WHERE clause and you are trying to update multiple rows with different values.

 

I believe that you are only wanting to update the associated records for a particular user. So, what you really need to do when that page is submitted is to DELETE all the associated records for that user THEN add the new associations in. The alternative it to check which associations exists, delete the individual ones not in the update list and then add the ones in the update list that do not exist in the database. That is a lot more work.

 

The following script should work no matter if you are adding the associations the first time or if you are updating a users associations. I ahve also added debugging lines to the code. Just set $debug to true to see the debug info

//Process each value in the array into a complete INSERT value
$values = array();
foreach($_POST['eldercatid'] as $eldercatid)
{
    $eldercatid = intval($eldercatid);
    $values[] = "('$eldercatid', '$id')";
}

//Delete any existing records for the elder
$deleteSql = "DELETE FROM `eldercategory` WHERE `elderid` = '$id'";
$result = mysql_query($deleteSql) or die mysql_error());

//Insert new/updated values for the elder
$insertSql = "INSERT INTO `eldercategory` (`eldercatid`, `elderid`)
              VALUES " . implode(', ', $values);
$result = mysql_query($insertSql) or die mysql_error());

//Set debug to true to see debug info
$debug = false;
if($debug)
{
    echo "<b>Debug Info:</b><br>\n";
    echo "<pre>\n";
    echo "Post['eldercatid'] Data\n";
    print_r($_POST['eldercatid']);
    echo "\nDelete Query:\n{$deleteSql}";
    echo "\nInsert Query:\n{$insertSql}";
}

[futrose]

Thanks for the help with this code.  I'm pretty close to getting it.  I had a question about this line... I think I am missing a '(' somewhere.  Is that right?

 

$result = mysql_query($insertSql) or die mysql_error());

 

also I am using mysqli so I changed the line to this

 

$result = mysqli_query($insertSql) or die mysqli_error());

 

but I'm still not sure about the '(' issue.

[futrose]

Got it all sorted out.  Code is working as it should.  Thanks for your help mjdamato.  Had to adjust the line of code to

 

$result = mysqli_query($link, $deleteSql) or die(mysqli_error());