Multiple Queries

[johnrb87]

Hi everyone

 

I have the following PHP code

 

$sql.= "INSERT INTO `data` (`info`, `write`, `date`) VALUES";
	$sql.= "(";
        for ($c=0; $c < $num; $c++) {
		$sql.= '"'.str_replace('"', "", $data[$c]).'",';
        }
	$sql.= "'".date('Y-m-d')."');";

 

If I

 

print $sql;

 

I get

 

INSERT INTO `data` (`info`, `write`, `date`) VALUES("data99","n",'2010-10-05');
INSERT INTO `data` (`info`, `write`, `date`) VALUES("data101","y",'2010-10-05');
INSERT INTO `data` (`info`, `write`, `date`) VALUES("data876","n",'2010-10-05');

 

what would I need to do to my PHP code in order for it to process each QUERY either all at once or one at a time

 

Whatever I try, it either inserts nothing, or just does the first INSERT

 

Any ideas?

 

Thanks

[johnsmith153]

Put all the lines you have shown inside the for loop and then execute the query inside the for loop at the end.

 

Change .= to just = in the first line.

 

[johnrb87]

Thanks, I tried

 

        for ($c=0; $c < $num; $c++) {
$sql= "INSERT INTO `data` (`info`, `write`, `date`) VALUES";
$sql.= "(";

$sql.= '"'.str_replace('"', "", $data[$c]).'",';

$sql.= "'".date('Y-m-d')."');
$query = mysql_query($sql);

        }

 

but that doesn't seem to do anything

[johnrb87]

any ideas?

[johnrb87]

Can anyone help? Been trying different things for last 6 hours and no luck yet

[BlueSkyIS]

your code is invalid and should not compile, certainly not run properly. do you have error_reporting turned off? If so, turn it on at least partially to get compile errors. secondly, I suggest that you check for errors whenever you perform mysql_query, for instance:

 

$sql = "INSERT INTO table_name SET some_col = 'some_val'";
mysql_query($sql) or die(mysql_error() . " IN $sql "); // Add mysql_error() to see if the query fails (or not)

[mikosiko]

What you are trying to produce as the final value for your $sql variable is this:

$sql = INSERT INTO `data` (`info`, `write`, `date`) VALUES ('data99','n','2010-10-05')
                                                         , ('data101','y','2010-10-05')
                                                         , ('data876','n','2010-10-05')";

 

therefore using concatenation from the beginning in your $sql variable is not going to work in the first code that you showed.

 

Now that you now what is the final goal just adjust your code to produce the sentence in the right way.

 

[chintansshah]

try below code,

$sql = "INSERT INTO `data` (`info`, `write`, `date`) VALUES";     for ($c=0; $c < $num; $c++) {             $sql.= "(";             $sql.= '"'.str_replace('"', "", $data[$c]).'",';             $sql.= "'".date('Y-m-d')."');";        }echo $sql;mysql_query($sql);

 

 

your code will generate the query which you can execute once only.

[anups]

$link = new mysqli("localhost", "root", "root", "phpform");
/* turn autocommit on */
$link->autocommit(TRUE);

$query = "INSERT INTO `test` (`info`, `write`, `date`) VALUES('data99','n','2010-10-05');";
$query .= "INSERT INTO `test` (`info`, `write`, `date`) VALUES('data101','y','2010-10-05');";
$query .=  "INSERT INTO `test` (`info`, `write`, `date`) VALUES('data876','n','2010-10-05')";
echo $query;
/* execute multi query */
$link->multi_query($query);

/* close connection */
$link->close();