insert multiple rows from array into table with unique ID

Question

I have copied and modified a script off the internet. The script originally deleted selected records from a mysql table query. I have modified the script to insert the selected records into another table with the insert into statement.

I would like to know how I can insert all the selected records from the mysql array into the other table with the same id.

The logic is simlar to that of an 'orderdetails' table. I want all products ordered to have the same ordernumber so they share a common value.

How can I modify the below script to insert all values from the array with a unique number?

<?php
mysql_connect("localhost", "user", "pass")or die("cannot connect");    
mysql_select_db("db")or die("cannot select DB");
$sql="SELECT * FROM category";
$result=mysql_query($sql);
$count=mysql_num_rows($result);
?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr><td><form name="form1" method="post">
<table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
<tr><td bgcolor="#FFFFFF">&nbsp;</td>
    <td colspan="4" bgcolor="#FFFFFF"><strong>Insert multiple rows in mysql</strong></td></tr>
<tr><td align="center" bgcolor="#FFFFFF">#</td>
    <td align="center" bgcolor="#FFFFFF"><strong>Category ID</strong></td>
    <td align="center" bgcolor="#FFFFFF"><strong>Category</strong></td></tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr><td align="center" bgcolor="#FFFFFF"><input type="checkbox" name=check[]  value="
<?php echo $rows['cat_id']; ?>"></td>
<td bgcolor="#FFFFFF"><?php echo $rows['cat_id']; ?></td>
<td bgcolor="#FFFFFF"><?php echo $rows['category']; ?></td></tr>

<?php
}
?>
<tr><td colspan="3" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td></tr>
<?php
$check=$_POST['check'];
if($_REQUEST['delete']=='Delete'){
  {
    $sql="INSERT INTO category1 (cat_id,category) 
             SELECT cat_ID, category 
             FROM category 
             WHERE cat_id='$val'";

    foreach($check as $key=>$value)
    {
      $sql="INSERT INTO category1 (cat_id,category) 
              SELECT cat_ID, category 
              FROM category 
              WHERE cat_id='$value'";
      $final = mysql_query($sql);
      if($final) {
        echo "<meta http-equiv=\"refresh\" content=\"0;URL=php.php\">";
      }
    } 
  }
}
// Check if delete button active, start this   
// if successful redirect to php.php

mysql_close();
?>
</table></form></td></tr></table>

Have you considered doing it in one statement? `INSERT ... SELECT ...`? — Mark Byers, Oct 01 '11 at 21:52
Hi Mark, I would prefer to do it in one comment definately, the shorter the better but am an absolute novice to PHP so learning my way. I am using the insert ... select ... statement in MySQL which allows me to inser the selected rows from the array. How do I modify the statement to assign all inserted records the same unique ID. I would like to use the select max(columnname) feature but am not sure how to nest this in the existing statement. any ideas?m Thanks again. — Smudger, Oct 01 '11 at 22:05
You would have to calculate the MAX in a subquery. `SELECT (SELECT MAX(...) FROM ... WHERE), other columns`. I'd post an answr but there's way too much code in your question, and not enough description of what you are trying to do, and I don't really want to read all that code. — Mark Byers, Oct 01 '11 at 22:13

score 1 · Accepted Answer · edited May 23 '17 at 10:34

You code has several issues:

A- You have SQL-injection holes: Use mysql_real_escape_string()
B- You have possible XSS vulnerabilities: Use htmlspecialchars to escape all $vars that you echo.
C- Using select * when you're only going to use the fields catID, category is waste full. Always name the fields you select explicitly.

See:
How does the SQL injection from the "Bobby Tables" XKCD comic work?
What are the best practices for avoiding xss attacks in a PHP site
What is the reason not to use select *?

To answer your question
I would use code something like

$check = $_POST['check'];
if (is_array($check)) {
  //simple test to see if an array is multi-dimensional.
  if (count($array) != count($array, COUNT_RECURSIVE)) 
  {
     //die("multidimensional array's are not allowed");
     //insert code to reask the array, or work around the issue.
     //you really should not use `die` in production code.
  } else {
  //escape what's inside the array, not the array itself.
  $check = array_walk($check, 'mysql_real_escape_string');
  $check = "'".implode("','",$check)."'"; //1,2,3 => '1','2','3'
} else { //not an array
  $check = "'".mysql_real_escape_string($check)."'";
}
//Inserts all $check's in one go.
$sql = "INSERT INTO category1 (cat_id,category) 
          SELECT cat_ID, category 
          FROM category 
          WHERE cat_id IN ($check) ";  //$check is already quoted.

Hi @Johan, I tried inserting the code you mentioned above however I get the error message '•The website has a programming error.' when trying to openthe web page. I have replaced the last section with the code you provided. Do you have any ideas, is it possible to post the complete script that works? Thanks again for all the help. — Smudger, Oct 02 '11 at 19:13
@RyanSmith, it's not copy-pasteble code, you will need to do some rewriting yourself to actually make it work within your situation. — Johan, Oct 02 '11 at 19:44

insert multiple rows from array into table with unique ID

1 Answers1