php Multiple insert from recordset query

[Itnassol]

Hi Guys,

I've been struggling with this one for days, I have a table with a repeated region, when the user click any of the rows it passes the ID number of that row (ID Number is an invoice number) to the next page..

On the next page I run a select statement that uses the passed ID number, for example SELECT * FROM table1 WHERE InvoiceNo = $InNo

To this point everything works beautifully..

what I want to do next is insert that data that comes from the statement, into a table, I know how to insert using fixed values but not variable..

Any pointers would be good

Thanks

[NogDog]

The details will depend on what database extension you are using, but the preferred approach (IMO) is to use a prepared statement with a bound parameter, which will take care of any potential SQL injection issues automatically. Here are some relevant manual pages for PDO (my preference) and MySQLi:

http://php.net/manual/en/pdostatement.bindparam.php

http://php.net/manual/en/mysqli-stmt.bind-param.php

[Itnassol]

Hi,

I've been playing with some various options and have made a step forward, but now have a funny error.. here's my code..

<?php require_once('../Connections/OASDB.php');

mysql_select_db($database_OASDB, $OASD😎;

$sql = "SELECT SnPf, InvoiceNo, CustomerName FROM VSTStbl000010 WHERE SnPf = '18490'";
$result = mysql_query($sql) or die(mysql_error());

$INS = array();

while($row = mysql_fetch_assoc($result)) {
$INS[] = $row;
}

foreach( $INS as $row ) {

mysql_query("INSERT INTO phptest (InvoiceNo, CustomerName) VALUES (".$row['InvoiceNo'].", ".$row['CustomerName']."") or die(mysql_error());
}

?>

It is happy with the InvoiceNo, but stalls with a syntax error on the CustomerName...

Any help would be great..

Thanks

[Itnassol]

Syntax error now gone with changes below, but it is still not inserting to the table..

New code is...

<?php require_once('../Connections/OASDB.php');

mysql_select_db($database_OASDB, $OASD😎;

$sql = "SELECT SnPf, InvoiceNo, CustomerName FROM VSTStbl000010 WHERE SnPf = '40446'";
$result = mysql_query($sql) or die(mysql_error());

$INS = array();

while($row = mysql_fetch_assoc($result)) {
$INS[] = $row;
}

foreach( $INS as $row ) {

mysql_query("INSERT INTO phptest (InvoiceNo, CustomerName) VALUES (".$row['InvoiceNo'].", '".$row['CustomerName']."') or die(mysql_error()");
}

?>

[NogDog]

Please make use of this forum's [noparse]

...

tags (or

 or [code=html] as needed)[/noparse] to make things easier to read.

Here's my quick (and therefore likely buggy) take on how you could do it -- with the usual warning that the mysql_*() functions are deprecated, and all new code should using mysqli_*() or the PDO functions:
[code=php]
<?php require_once('../Connections/OASDB.php');

mysql_select_db($database_OASDB, $OASDB);

$sql = "SELECT SnPf, InvoiceNo, CustomerName FROM VSTStbl000010 WHERE SnPf = '40446'";
$result = mysql_query($sql) or die(mysql_error());

$sqlString = "INSERT INTO phptest (InvoiceNo, CustomerName) VALUES (%d, '%s')";
while ($row = mysql_fetch_assoc($result)) {
	$sql = sprintf(
		$sqlString,
		(int) $row['InvoiceNo'],
		mysql_real_escape_string($row['CustomerName'])
	);
	mysql_query($sql) or die("Query failed: ".mysql_error().PHP_EOL.$sql);
}

[maxxd]

Why not skip all the looping and do a SELECT INTO sql statement? Like so:

INSERT INTO phptest
	(InvoiceNo, CustomerName)
SELECT InvoiceNo, CustomerName
FROM VSTStbl000010 WHERE SnPf = '18490'