[SOLVED] Mass SQL import (singles are ok?)

[MadTechie]

OK, bascially i have some code that imports alot of data into the MySQL database,

 

Now this is fine when i use the INSERT INTO one record at a time.. but if i do 2 records ie

 

INSERT INTO `PB2` (`field1`,`field2`) VALUES ('111' ,'222');

INSERT INTO `PB2` (`field1`,`field2`, `field3`) VALUES ('333' ,'444', '555');

 

in which case i get the 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 '; INSERT INTO `PB2` (`field1`,`field2

on line 1

 

so it would seam the ; is causing the problem.. but even when i do them one by one it has the ;, AND if i copy the SQL statement that is echoed with the error and run it in PMA (phpMyAdmin) it runs without error!..

 

the only thing i can think of is a limit set by the host.. ?

 

any ideas?

 

i truely hope its something stupid and not the host..

[XaeroDegreaz]

Are you using PMA to do all of the importing? If so, it's smart and you don't need to actually put the ;'s in there.

 

Otherwise, your script there looks sound.

[MadTechie]

Only using the PMA for testing and reviewing the data, basically i couldn't see a problem so i used PMA to "attempt" the import so i see the problem more clearly.. yet their was none.. unless PMA corrected it for me!

[lemmin]

This should help:

http://dev.mysql.com/doc/refman/5.1/en/c-api-multiple-queries.html

[Barand]

AFAIK you'd need mysqli to execute multiple statements.

 

But you could

INSERT INTO `PB2` (`field1`,`field2`, `field3`) VALUES 
('333' ,'444', '555') ,
('111' ,'222', null) ,
('666','777','888') ;

[MadTechie]

Woohoo Thanx all,

 

mysqli seams to of done it,

 

just need to build it into my main code now

 

heres my working sample code (incase someone need to do the same)

<?php
test();

function test()
{
require_once "../conf.inc.php";
$mysqli = new mysqli(DBS_HOST, DBS_USER, DBS_PASS, DBS_DB);

/* check connection */
if (mysqli_connect_errno())
{
	printf("Connect failed: %s\n", mysqli_connect_error());
	exit();
}

echo "sending...<br />";
/* execute multiple statements */
$status = mysqli_multi_query($mysqli,"
INSERT INTO `PB2` (`LFT`) VALUES ('test1' );
INSERT INTO `PB2` (`LFT`) VALUES ('test2' );
INSERT INTO `PB2` (`LFT`) VALUES ('test3' );
") or die(mysqli_error());
echo "sent...<br />status:";
var_dump($status);
echo "<br />close connection<br />";
/* close connection */
$mysqli->close();

}

 

oh an you can use the insert like Barand says but for my project i couldn't without a few too many changes.