[SOLVED] Inserting multiple rows with one query

Bifter · January 12, 2009

Hi All,

Im having trouble getting this to work.

The variables; are passed from index.php to insert-records.php, all data goes across fine, however its getting a way of inserting the data from insert-records into the DB that im having trouble with, possibly need to use UNION - however i cannot figure out how this will work as the amount of rows to enter can be anywhere from 1 to 200+.

index.php has a dropdown box, where the user selects the amount of rows to insert.

index.php

<?php
$num = 1+$_GET["num-mac"];
$i=0;
while ($i < $num) {
echo "<tr><td><div align=\"center\">";
echo "<input name=\"row\" type=\"hidden\" value=\"" . $i . "\">";
echo "<input name=\"row-total\" type=\"hidden\" value=\"" . $num . "\">";
echo "<input type=\"hidden\" id=\"DPC_TODAY_TEXT\" value=\"today\">";
echo "<input type=\"hidden\" id=\"DPC_BUTTON_TITLE\" value=\"Open calendar...\">";
echo "<input type=\"hidden\" id=\"DPC_MONTH_NAMES\" value=\"['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']\">";
echo "<input type=\"hidden\" id=\"DPC_DAY_NAMES\" value=\"['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat']\">
        <input type=\"text\" name=\"" . $i . "date\" id=\"" . $i . "date\" datepicker=\"true\" datepicker_format=\"YYYY-MM-DD\"/>
        </div></td>";
echo "<td><div align=\"center\"><select name=\"" . $i . "company\" id=\"" . $i . "company\">";
$sql="SELECT id,company FROM users order by company ASC";
$result =mysql_query($sql);
while ($data=mysql_fetch_assoc($result)){
echo "<option value =\"" . $data['company'] . "\" >" . $data['company'] . "</option>";
}
echo "</select><div></td>";
echo "<td><div align=\"center\"><select name=\"" . $i . "product\" id=\"" . $i . "product\">";
$sql="SELECT id,model FROM product order by model ASC";
$result =mysql_query($sql);
while ($data=mysql_fetch_assoc($result)){
echo "<option value =\"" . $data['model'] . "\" >" . $data['model'] . "</option>";
}
echo "</select></div></td>";
echo "<td><div align=\"center\"><input type=\"text\" name=\"" . $i . "serial\" id=\"" . $i . "serial\"></div></td>";
echo "<td><div align=\"center\"><input type=\"text\" name=\"" . $i . "mac\" id=\"" . $i . "mac\"></div></td>";
echo "<td>$i</td>";
echo "<div></tr>";
$i++;
}
?>

insert-records.php

<?php
$con = mysql_connect("localhost", "ipendpoi_Bifter", "Bollocks");
$i = $_GET[row];
$num = $_GET[row-total];
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
  while ($i < $num) {
  mysql_select_db("ipendpoi_mac", $con);$sql="INSERT INTO details (date, company, product, serial, mac)
VALUES
('$i . $_GET[date]','$i . $_GET[company]','$i . $_GET[product]','$i . $_GET[serial]','$i . $_GET[mac]')";if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
  }

echo $num . "record/s added" . "< >< >" . "<a href=\"http://www.ipendpoints.co.uk/mac/\">Back";mysql_close($con)
?>

I hope i have explained everything, however if I have missed something, please let me know.

Thanks.

Mchl · January 12, 2009

INSERT INTO details (date, company, product, serial, mac)
VALUES
(values for row1),
(values for row2),
(values for row3),
...
(values for rown)

Bifter · January 12, 2009

INSERT INTO details (date, company, product, serial, mac)
VALUES
(values for row1),
(values for row2),
(values for row3),
...
(values for rown)

Hi Mchl,

Thanks for the reply, but how would I get the INSERT to loop until all rows are inserted? and avoid emtpy rows being inserted

Ben.

Mchl · January 12, 2009

You have to be smart, when creating a query ;P

mysql_select_db("ipendpoi_mac", $con);
$sql = "INSERT INTO details (date, company, product, serial, mac)
VALUES ";

while ($i < $num) {
  if(/*check if row not empty*/) {
    $sql .= "('$i . $_GET[date]','$i . $_GET[company]','$i . $_GET[product]','$i . $_GET[serial]','$i . $_GET[mac]'),";
  }
}

$sql = substr($sql,0,-1);  //this will remove last comma

if (!mysql_query($sql,$con)) {
  die('Error: ' . mysql_error());
}

Bifter · January 12, 2009

I get the following error:

Error: Query was empty

when the code executes.

Mchl · January 12, 2009

That's strange. $sql certainly should not be empty at this point.

Change

die('Error: ' . mysql_error());

to

die('Error: ' . mysql_error()) ." Query: $sql";

Bifter · January 12, 2009

Exactly the same error

Mchl · January 12, 2009

Well then... some debugging code needs to be inserted then

mysql_select_db("ipendpoi_mac", $con);
$sql = "INSERT INTO details (date, company, product, serial, mac)
VALUES ";
echo $sql;

while ($i < $num) {
  if(/*check if row not empty*/) {
    $sql .= "('$i . $_GET[date]','$i . $_GET[company]','$i . $_GET[product]','$i . $_GET[serial]','$i . $_GET[mac]'),";
  }
}
echo $sql;

$sql = substr($sql,0,-1);  //this will remove last comma
echo $sql;

if (!mysql_query($sql,$con)) {
  die('Error: ' . mysql_error());
}

Bifter · January 12, 2009

The following is returned:

INSERT INTO details (date, company, product, serial, mac) VALUES INSERT INTO details (date, company, product, serial, mac) VALUES INSERT INTO details (date, company, product, serial, mac) VALUESError: 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 2

Mchl · January 12, 2009

Let me guess...

if(/*check if row not empty*/) {

You left my comment here instead of putting an actual condition?

Bifter · January 12, 2009

Nope I took the whole If statement out, as I want to get rest working first....the code im using is:

<?php
$i = $_GET[row];
$num = $_GET[row-total];
mysql_select_db("ipendpoi_mac", $con);
$sql = "INSERT INTO details (date, company, product, serial, mac)
VALUES ";
echo $sql;
while ($i < $num) {
    $sql .= "('$i . $_GET[date]','$i . $_GET[company]','$i . $_GET[product]','$i . $_GET[serial]','$i . $_GET[mac]'),";
  }
echo $sql;

$sql = substr($sql,0,-1);  //this will remove last comma
echo $sql;

if (!mysql_query($sql,$con)) {
  die('Error: ' . mysql_error());
}

echo $num . "record/s added" . "< >< >" . "<a href=\"http://www.ipendpoints.co.uk/mac/\">Back";mysql_close($con)
?>

Mchl · January 12, 2009

This would indicate, that

while ($i < $num)

is never true.

Bifter · January 12, 2009

Maybe we can go back a couple of steps, I have posted all the relevent code, below - I cant work out where this is going wrong:

index.php

<?php
$num = 1+$_GET["num-mac"];
$i=0;
while ($i < $num) {
echo "<tr><td><div align=\"center\">";
echo "<input name=\"row\" type=\"hidden\" value=\"" . $i . "\">";
echo "<input name=\"row-total\" type=\"hidden\" value=\"" . $num . "\">";
echo "<input type=\"hidden\" id=\"DPC_TODAY_TEXT\" value=\"today\">";
echo "<input type=\"hidden\" id=\"DPC_BUTTON_TITLE\" value=\"Open calendar...\">";
echo "<input type=\"hidden\" id=\"DPC_MONTH_NAMES\" value=\"['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']\">";
echo "<input type=\"hidden\" id=\"DPC_DAY_NAMES\" value=\"['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat']\">
        <input type=\"text\" name=\"" . $i . "date\" id=\"" . $i . "date\" datepicker=\"true\" datepicker_format=\"YYYY-MM-DD\"/>
        </div></td>";
echo "<td><div align=\"center\"><select name=\"" . $i . "company\" id=\"" . $i . "company\">";
$sql="SELECT id,company FROM users order by company ASC";
$result =mysql_query($sql);
while ($data=mysql_fetch_assoc($result)){
echo "<option value =\"" . $data['company'] . "\" >" . $data['company'] . "</option>";
}
echo "</select><div></td>";
echo "<td><div align=\"center\"><select name=\"" . $i . "product\" id=\"" . $i . "product\">";
$sql="SELECT id,model FROM product order by model ASC";
$result =mysql_query($sql);
while ($data=mysql_fetch_assoc($result)){
echo "<option value =\"" . $data['model'] . "\" >" . $data['model'] . "</option>";
}
echo "</select></div></td>";
echo "<td><div align=\"center\"><input type=\"text\" name=\"" . $i . "serial\" id=\"" . $i . "serial\"></div></td>";
echo "<td><div align=\"center\"><input type=\"text\" name=\"" . $i . "mac\" id=\"" . $i . "mac\"></div></td>";
echo "<td>$i</td>";
echo "<div></tr>";
$i++;
}
?>

insert-records.php

<?php
$con = mysql_connect("localhost", "", "") or die(mysql_error());
$i = $_GET[row];
$num = $_GET[row-total];
mysql_select_db("ipendpoi_mac", $con);
$sql = "INSERT INTO details (date, company, product, serial, mac)
VALUES ";
echo $sql;
while ($_GET[row] + 1 < $num) {
    $sql .= "('$i . $_GET[date]','$i . $_GET[company]','$i . $_GET[product]','$i . $_GET[serial]','$i . $_GET[mac]'),";
  }
echo $sql;
$sql = substr($sql,0,-1);  //this will remove last comma
if (!mysql_query($sql,$con)) {
  die('Error: ' . mysql_error());
}
echo $sql;
echo "record/s added" . "< >< >" . "<a href=\"http://www.ipendpoints.co.uk/mac/\">Back";mysql_close($con)
?>

The varibles get passed as follows:

{URL}insert-records.php?row=0&row-total=1&0date=2009-01-12&0company=IPTEL+Ltd&0product=7906&0serial=casdcsas&0mac=dasfdsavfcv

I really appriaciate you helping me on this,

Mchl · January 12, 2009

$_GET[row] + 1 = 0 + 1 = 1

$num = $_GET[row-total] = 1

which means

($_GET[row] + 1 < $num) == FALSE (1 < 1)

so the loop doesn't start at all

Oh... and row-total is not the best name for variable. Use row_total perhaps.

sasa · January 12, 2009

try

<?php
$con = mysql_connect("localhost", "", "") or die(mysql_error());
$i = 0;
$num = $_GET[row-total];
mysql_select_db("ipendpoi_mac", $con);
$sql = "INSERT INTO details (date, company, product, serial, mac)
VALUES ";
//echo $sql;
while ($i < $num) {
    $sql1[] = "('" . $_GET[$i.'date']."','".$_GET[$i.'company']."','".$_GET[$i.'product']."','".$_GET[$i.'serial']."','".$_GET[$i.'mac']."')";
  }
$sgl .= implode(", \n", $sql1);
echo $sql;
//$sql = substr($sql,0,-1);  //this will remove last comma
if (!mysql_query($sql,$con)) {
  die('Error: ' . mysql_error());
}
echo $sql;
echo "record/s added" . "< >< >" . "<a href=\"http://www.ipendpoints.co.uk/mac/\">Back";mysql_close($con)
?>

Bifter · January 13, 2009

If i use sasa's code i get the following error:

Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 17 bytes) in /home/ipendpoi/public_html/mac/insert-records.php on line 11

even when I change

$i = 0;

to

$i = $_GET[row];

If I echo $i and $num using:

<?php
echo "row" . $i . "<br />";
echo "rowtotal" . $num . "<br />";
?>

then I get the following result after sending two rows to the script:

row1

rowtotal2

If I send just one row then the following will echo:

row0

rowtotal1

Could this be the problem????

I have also tried changing

$i = $_GET[row]

to

$i = $_GET[row] + 1;

but this errors:

Warning: implode() [function.implode]: Invalid arguments passed in insert-records.php on line 13

INSERT INTO details (date, company, product, serial, mac) VALUES 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 2

Is there any takers....please!!!!

sasa · January 13, 2009

ups

i don't change variable $i in while loop

change while loop to

while ($i < $num) {

$sql1[] = "('" . $_GET[$i.'date']."','".$_GET[$i.'company']."','".$_GET[$i.'product']."','".$_GET[$i.'serial']."','".$_GET[$i.'mac']."')";

$i++;

}

Bifter · January 13, 2009

I get this with the last change:

INSERT INTO details (date, company, product, serial, mac) VALUES 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 2

dawsba · January 13, 2009

Can i add my 2p,

i faguly remember I wanted to upload multiple rows and for some reason that escapes me, it's denied by some reason, so i came on the work around or just uploadin an insert at a time

function q($query,$assoc=1) {
$r = @mysql_query($query);
if( mysql_errno() ) {
  $error = 'MYSQL ERROR #'.mysql_errno().' : ' . mysql_error(). '< / small>< br>< VAR>$query< /VAR>';
  echo($error); return FALSE;
}  if( strtolower(substr($query,0,6)) != 'select' ) return array(mysql_affected_rows(),mysql_insert_id());
$count = @mysql_num_rows($r);
if( !$count ) return 0;
if( $count == 1 ) {
  if( $assoc ) $f = mysql_fetch_assoc($r);
  else $f = mysql_fetch_row($r);
  mysql_free_result($r);
  if( count($f) == 1 ) {
   list($key) = array_keys($f); 
   return $f[$key];
  } else {
   $all = array();
   $all[] = $f;
   return $all;
  }
} else {
  $all = array();
  for( $i = 0; $i < $count; $i++ ) {
   if( $assoc ) $f = mysql_fetch_assoc($r);
   else $f = mysql_fetch_row($r);
   $all[] = $f;
  }
  mysql_free_result($r);
  return $all;
}
}

The Syntax for q is quite simple

eg: q(SQL QUERY);

eg :: < ?= q("SELECT * FROM `tbl_whoever` WHERE `id` = '$return_result[userid]' LIMIT 1;"); ?>

eg :: < ?= q("SELECT COUNT(*) FROM `tbl_whoever` WHERE `id` = '$return_result[userid]' LIMIT 1;"); ?>

$con = mysql_connect("localhost", "", "") or die(mysql_error());
$i = 0;
$num = $_GET[row-total];
mysql_select_db("ipendpoi_mac", $con);
$sql = "INSERT INTO details (date, company, product, serial, mac)
VALUES ";
//echo $sql;
while ($i < $num) {
    $sql1[] = "('" . $_GET[$i.'date']."','".$_GET[$i.'company']."','".$_GET[$i.'product']."','".$_GET[$i.'serial']."','".$_GET[$i.'mac']."')";
  }
foreach($sql1 as $k){q($k);}

Hope this is usefull, hey who knows lol

Bifter · January 13, 2009

Hi dawsba - your 2p is more than welcome!!!

I dont follow your code - is there any chance you can repost, changing my code?

Thanks,

Ben

dawsba · January 13, 2009

this should do you, i havnt tested but looks ok 2 the eye, id run a small upload b4 i did the main job

<?php
$con = mysql_connect("localhost", "ipendpoi_Bifter", "Bollocks");
function q($query,$assoc=1) {
$r = @mysql_query($query);
if( mysql_errno() ) {
  $error = 'MYSQL ERROR #'.mysql_errno().' : ' . mysql_error(). '< / small>< br>< VAR>$query< /VAR>';
  echo($error); return FALSE;
}  if( strtolower(substr($query,0,6)) != 'select' ) return array(mysql_affected_rows(),mysql_insert_id());
$count = @mysql_num_rows($r);
if( !$count ) return 0;
if( $count == 1 ) {
  if( $assoc ) $f = mysql_fetch_assoc($r);
  else $f = mysql_fetch_row($r);
  mysql_free_result($r);
  if( count($f) == 1 ) {
   list($key) = array_keys($f); 
   return $f[$key];
  } else {
   $all = array();
   $all[] = $f;
   return $all;
  }
} else {
  $all = array();
  for( $i = 0; $i < $count; $i++ ) {
   if( $assoc ) $f = mysql_fetch_assoc($r);
   else $f = mysql_fetch_row($r);
   $all[] = $f;
  }
  mysql_free_result($r);
  return $all;
}
}

mysql_select_db("ipendpoi_mac", $con);
$i = $_GET[row];
$num = $_GET[row-total];
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
$sql = "INSERT INTO details (date, company, product, serial, mac)
VALUES ";
//echo $sql;
while ($i < $num) {
    $sql1[] = "('" . $_GET[$i.'date']."','".$_GET[$i.'company']."','".$_GET[$i.'product']."','".$_GET[$i.'serial']."','".$_GET[$i.'mac']."')";
  }
foreach($sql1 as $k){q($k);}

echo $num . "record/s added" . "< >< >" . "<a href=\"http://www.ipendpoints.co.uk/mac/\">Back";mysql_close($con)
?>

Bifter · January 14, 2009

Thanks dawsba, however I get the following error with this:

Warning: Invalid argument supplied for foreach() in /home/ipendpoi/public_html/mac/insert-records.php on line 48

???

Bifter · January 14, 2009

Any ideas - anyone????

Bifter · January 14, 2009

Found out the problem, changed

$num = $_GET[row-total];

to

$num = $_GET[rowtotal];

Still getting them blasted Memory exhausted errors.....

Bifter · January 15, 2009

This was solved using the following:

$ia = 0;
$i = $_GET['row'];
$num = $_GET['rowtotal'];
mysql_select_db("", $con);
$sql = "INSERT INTO details (date, company, product, serial, mac)
VALUES ";
while ($ia < $num) {
if ($num - $ia <= 1) {
$comma = "";
}
else {$comma = ",";
}
$sql .= "('".$_GET[$ia.'date']."','".$_GET[$ia.'company']."','".$_GET[$ia.'product']."','".$_GET[$ia.'serial']."','".$_GET[$ia.'mac']."')" . $comma;
$ia++;
}
if (!mysql_query($sql,$con)) {
  die('Error: ' . mysql_error());
}

Sign In

[SOLVED] Inserting multiple rows with one query

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information