[SOLVED] select a bunch of items from a menu and create a record for each selection

[simon551]

It's all a matter of how you ask the question, but... I can't seem to figure this out looking through books and online. I must just not be thinking of it the same way as others.

 

This seems like a fairly basic idea. You have a list of available items with a checkbox next to each. You want to pull together all the checked items and do a bulk insert into a table. For my example, the list is between 50 and 200 items. I can create this list very easily like this:

 

  <?php do { ?>
    <tr>
      <td><?php echo $row_rs1['ID']; ?></td>
      <td><?php echo $row_rs1['name']; ?></td>
      <td>
        <input type="checkbox" name="checkbox" value="checkbox" />      </td>
      <td> </td>
    </tr>
    <?php } while ($row_rs1 = mysql_fetch_assoc($rs1)); ?>
</table>

 

Then you have a script that checks each record to see if you checked the box and if so it creates a record. I'm just stumped on how to do this. I have this insert statement. I feel like I am close I just don't know quite how to find the answer or what the question really is.

foreach (****The checkbox was selected, get the id****){

$insertSQL = ("INSERT INTO selected (ID, name) VALUES (%s, %s)",
                       ($_POST['ID'], "int"),
                       ($_POST['Name'], "varchar"));
}

 

[pocobueno1388]

Okay, try this out =]

 

  <?php 
    do { ?>
    <tr>
      <td><?php echo $row_rs1['ID']; ?></td>
      <td><?php echo $row_rs1['name']; ?></td>
      <td>
        <input type="checkbox" name="checked[]" value="<? echo $row_rs1['ID'].'.'$row_rs1['name']; ?>" />      </td>
      <td> </td>
    </tr>
    <?php } while ($row_rs1 = mysql_fetch_assoc($rs1)); ?>
</table>

 

<?php

foreach ($_POST['checked'] as $check){

$names = explode(".", $check);
$id = $names[0];
$name = $names[1];

   $insertSQL = ("INSERT INTO selected (ID, name) VALUES (%s, %s)",
                       ($id, "int"),
                       ($name, "varchar"));
}

?>

 

I didn't test it, but it should work.

[simon551]

I selected 'jones' and 'john' and it's giving me an 'unknown column 'jones'' error.

 

not sure what I'm doing wrong.

 

foreach ($_POST['checked'] as $check){

$names = explode(".", $check);
$id = $names[0];
$name = $names[1];

   $insertSQL = ("INSERT INTO selected (ID, name) VALUES ($id, $name)");

 

Thanks a lot for your help this far.

 

It feels like I'm getting really close now.

[teng84]

$names = explode(".", $check);//put the explode here even if i dont read you question i think its wrong

 

 

foreach ($_POST['checked'] as $check){

$id = $names[0];

$name = $names[1];

 

  $insertSQL = ("INSERT INTO selected (ID, name) VALUES ($id, $name)");

 

 

ok pls explain to me if im wrong or y your wrong with that code you have

[simon551]

I tried your suggestion, Teng. I get this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' )' at line 1

<?php
require_once('../Connections/conn_org.php'); 
mysql_select_db($database_conn_org, $conn_org);

$names = explode(".", $check);//put the explode here even if i dont read you question i think its wrong

foreach ($_POST['checked'] as $check){
$id = $names[0];
$name = $names[1];

  $insertSQL = sprintf("INSERT INTO selected (ID, name) VALUES (%s, %s)",
                       $id,
                       $name);

  $Result1 = mysql_query($insertSQL, $conn_org) or die(mysql_error());
}

?>

 

I was not getting that error with the previous code. I'm unsure about your suggestion anyway, b/c the foreach coming before the explode was in fact due to the problem where I am sending a list of items out and selecting from the list, then taking each selection and (if I'm following the logic correctly) splitting the result for an entry.

 

thanks all

[teng84]

foreach ($_POST['checked'] as $check){????

can you tell me the value you will get from array $_POST['checked'] and variable $check

 

your code do this

$check has an string containing .dots fron the array?

[ToonMariner]

if you have upto 200 items then looping through and doing an insert each time for each item is both inefficient and (where you are limited to the number of queries per page) may not result in what you actually need or even an error....

 

 

first you need to assemble the html correctly. poco is correct in that you must make the checkboxes into an array by adding the [] but in the processing script you are using the value of each checkbox to create an array where it will be beneficial both visually and in efficiency in the processing script to separate indexes from values.  So lets get the checkboxes right first..

 

 <?php while ($row_rs1 = mysql_fetch_assoc($rs1)) { ?>
   <tr>
     <td><?php echo $row_rs1['ID']; ?></td>
     <td><?php echo $row_rs1['name']; ?></td>
     <td>
       <input type="checkbox" name="checkbox[<?php echo $row_rs1['ID']; ?>]" value="<?php echo $row_rs1['name']; ?>" />      </td>
     <td> </td>
   </tr>
   <?php } ?>
</table>

 

We have now removed the need for exploding each value..

 

Now the processing script (assuming the rest of the html form is OK!) We want to run just one query to make this as efficient as possible but put ALL the id value pairs on the data base and here is who to do just that...

 

<?php

$qry = "INSERT INTO selected (ID, name) VALUES "; // the command wew will run.

// now create the values that are going in.
$values = NULL; // null string to check later.
foreach ($_POST['checked'] as $key => $check){
$values = "(" . $key . " , " . $check . "),";
}

if (!is_null($values)) // check there is something to enter into db
{
$values = substr($values, 0, -1); // strip off the last comma - otherwise you get an error.
$result = mysql_query($values);
if (mysql_affected_rows() > 0)
{
 echo "Hooray!";
}
}
?>

 

That should do the trick...

 

 

 

 

[simon551]

I'm a little lost by your post, ToonMariner...

 

This is what I have right now and it's not echoing anything back nor is it populating in the db

 

form:

<form method="post" name="selections" action="post_selectedt3.php">
<table>
  <?php 
    do { ?>
    <tr>
      <td><?php echo $row_rs1['ID']; ?></td>
      <td><?php echo $row_rs1['name']; ?></td>
      <td>
        <input type="checkbox" name="checked[]" value="<?php echo $row_rs1['ID'].'.'.$row_rs1['name']; ?>" /></td>
      <td> </td>
    </tr>
    <?php } while ($row_rs1 = mysql_fetch_assoc($rs1)); ?>
<tr>
<input type="submit" value="record selections" />
</tr>
</table>
</form>

 

post_selected3.php:

<?php
require_once('../Connections/conn_org.php'); 
mysql_select_db($database_conn_org, $conn_org);

$qry = "INSERT INTO selected (ID, name) VALUES $values"; // the command wew will run.

// now create the values that are going in.
$values = NULL; // null string to check later.
foreach ($_POST['checked'] as $key => $check){
$values = "(" . $key . " , " . $check . "),";
}

if (!is_null($values)) // check there is something to enter into db
{
$values = substr($values, 0, -1); // strip off the last comma - otherwise you get an error.
$result = mysql_query($values, $conn_org);
if (mysql_affected_rows() > 0)
{
  echo "Hooray!";
}
}
?>

[ToonMariner]

Don't be lost...

 

replace this

<form method="post" name="selections" action="post_selectedt3.php">
<table>
  <?php 
    do { ?>
    <tr>
      <td><?php echo $row_rs1['ID']; ?></td>
      <td><?php echo $row_rs1['name']; ?></td>
      <td>
        <input type="checkbox" name="checked[]" value="<?php echo $row_rs1['ID'].'.'.$row_rs1['name']; ?>" /></td>
      <td> </td>
    </tr>
    <?php } while ($row_rs1 = mysql_fetch_assoc($rs1)); ?>
<tr>
<input type="submit" value="record selections" />
</tr>
</table>
</form>

 

with this

 

<form method="post" name="selections" action="post_selectedt3.php">
<table>
  <?php 
    while ($row_rs1 = mysql_fetch_assoc($rs1)) { ?>
    <tr>
      <td><?php echo $row_rs1['ID']; ?></td>
      <td><?php echo $row_rs1['name']; ?></td>
      <td>
        <input type="checkbox" name="checked[<?php echo $row_rs1['ID']; ?>]" value="<?php echo $row_rs1['name']; ?>" /></td>
      <td> </td>
    </tr>
    <?php } ?>
<tr>
<input type="submit" value="record selections" />
</tr>
</table>
</form>

 

and replace this code

<?php
require_once('../Connections/conn_org.php'); 
mysql_select_db($database_conn_org, $conn_org);

$qry = "INSERT INTO selected (ID, name) VALUES $values"; // the command wew will run.

// now create the values that are going in.
$values = NULL; // null string to check later.
foreach ($_POST['checked'] as $key => $check){
$values = "(" . $key . " , " . $check . "),";
}

if (!is_null($values)) // check there is something to enter into db
{
$values = substr($values, 0, -1); // strip off the last comma - otherwise you get an error.
$result = mysql_query($values, $conn_org);
if (mysql_affected_rows() > 0)
{
  echo "Hooray!";
}
}
?>

 

with this

<?php
require_once('../Connections/conn_org.php'); 
mysql_select_db($database_conn_org, $conn_org);

$qry = "INSERT INTO selected (ID, name) VALUES "; // the command wew will run.

// now create the values that are going in.
$values = NULL; // null string to check later.
foreach ($_POST['checked'] as $key => $check){
$values = "(" . $key . " , " . $check . "),";
}

if (!is_null($values)) // check there is something to enter into db
{
$qry .= substr($values, 0, -1); // strip off the last comma - otherwise you get an error.
$result = mysql_query($qry, $conn_org);
if (mysql_affected_rows() > 0)
{
  echo "Hooray!";
}
}
?>

 

I do apologize - in my post I neglected to add the INSERT INTO ... to the values created in teh loop. That new code will rectify that problem.

[simon551]

I still can't get it to submit. have you tested that code? I don't see where you are getting the values into the "INSERT INTO ...VALUES (blank)" query. granted I'm new here. I copied exactly as you posted and it's not echoing anything.

[ToonMariner]

just spotted something... I suspect teh name fiedl is a text or varchar and I have not quoted the value in the query.

replace the processing script with this.

[code[<?php
require_once('../Connections/conn_org.php'); 
mysql_select_db($database_conn_org, $conn_org);

$qry = "INSERT INTO selected (ID, name) VALUES "; // the command wew will run.

// now create the values that are going in.
$values = NULL; // null string to check later.
foreach ($_POST['checked'] as $key => $check){
$values .= "(" . $key . " , '" . $check . "'),";
}

if (!is_null($values)) // check there is something to enter into db
{
$qry .= substr($values, 0, -1); // strip off the last comma - otherwise you get an error.
echo $qry;
$result = mysql_query($qry, $conn_org);
if (mysql_affected_rows() > 0)
{
  echo "Hooray!";
}
}
?>

I have added the echo $qry; line in so it should echo teh query that is going to be run...

 

If you follow teh procesing code you will see all it is doing is generating the qry string via a series of concatenating the strings.

[simon551]

THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!