help with mass INSERT

sicranmer

I have a PHP application that will need to insert multiple new rows. Instead of

for...
INSERT INTO ...
next

I am doing a "mass" insert via
for...
...
$x .= "INSERT INTO ...; "
next
$y=mysql_query($x, $link);

But this keeps failing. I know the sql is OK cause if I cut&paste into phpmyadmin it all runs fine 😕

Any ideas, please?

kevinsequeira

post ure code

reg
kevin

sicranmer

thats a tough one as the program is hundreds of lines referencing lodas of includes and classes.

Summary of the commands is attached.

Hope you can guess it from this.

Si.

kevinsequeira

this should be the format
$date_created = now();
insert into orders_status_history (orders_id, new_value, old_value, date_added, customer_notified) values (1,2,1,'$date_created',0);

when its numeric dont use ' ' in sql and use when its date or non-numeric.

reg
kevin

sicranmer

cheers but
now() is valid date/time funtion
and mysql will accept numeric surrounded by quotes.

Here is a simplified version of my problem

$sys_dbhost='server';
$sys_dbuser='user';
$sys_dbpasswd='pass';
$sys_dbname='dbname';

/
CREATE TABLE colours (
ID smallint(4) NOT NULL auto_increment,
colour_name varchar(20) NOT NULL default '',
hex_value varchar(6) NOT NULL default '',
PRIMARY KEY (ID)
) TYPE=MyISAM;
/
$conn = mysql_connect($sys_dbhost,$sys_dbuser,$sys_dbpasswd);
if (!$conn) {
echo mysql_error();
}
mysql_select_db($sys_dbname, $conn);
$qstring = 'select * from colours';
$result = mysql_query($qstring, $conn);
$cnt = mysql_num_rows($result);
echo '<BR>total rows = "'.$cnt.'"';
$qstring = "INSERT INTO colours VALUES (NULL,'aaaname', 'simons');";
$result = mysql_query($qstring, $conn);
$new_rows = mysql_affected_rows($conn);
echo '<BR>single insert rows created = "'.$new_rows.'"';
$qstring = "INSERT INTO colours VALUES (NULL,'aaaname', 'simons'); INSERT INTO colours VALUES (NULL,'aaaname', 'simons');";
$result = mysql_query($qstring, $conn);
$new_rows = mysql_affected_rows($conn);
echo '<BR>multiple insert rows created = "'.$new_rows.'"';

kevinsequeira

try this ..

$qstring = "INSERT INTO colours (color_name, hex_value) VALUES ('aaaname', 'simons')";

what errors are you getting? have u tried capturing and printing the error messages like this
if(mysql_error()){
echo mysql_error();
}

sicranmer

bum,

fixed it. Your only supposed to have multiple value sections. So its supposed to read

$qstring = "INSERT INTO colours VALUES (NULL,'aaaname', 'simons'), (NULL,'aaaname', 'simons'), (NULL,'aaaname', 'simons');";

cheers for the help

Si.