Mass INSERT

[tgavin]

I'm used to inserting into mysql one at a time using foreach()[code=php:0]
foreach($emails as $email) {
$query = "INSERT INTO table(email) VALUES ('$email')";
}[/code] OR selecting from one table and inserting into another from within mysql queries.

Well, now I'm looking at doing one massive insert in a few mysql queries where I'm comparing between tables. Like so:[code=php:0]$query = "CREATE TEMPORARY TABLE subscribers_temp(email VARCHAR(60)) TYPE=HEAP";
$query = "INSERT INTO subscribers_temp(email) VALUES ('$email')";
$query = "INSERT IGNORE subscribers(email) SELECT email FROM subscribers_temp";
$query = "
INSERT IGNORE list_subscribers
(ls_list_id,ls_sub_id)
SELECT $ls_id, subscribers.id
FROM subscribers
INNER JOIN subscribers_temp
ON subscribers.email = subscribers_temp.email";[/code]

I know this sounds really stupid, but I'm not sure how to go about inserting. I have the queries, but $email and $ls_id both return arrays. I'm completely stuck on stupid right now.

[fert]

http://us2.php.net/manual/en/function.mysql-query.php

[Demonic]

Try this:

[code]
$query .= "CREATE TEMPORARY TABLE subscribers_temp(email VARCHAR(60)) TYPE=HEAP";
$query .= "INSERT INTO subscribers_temp(email) VALUES ('$email')";
$query .= "INSERT IGNORE subscribers(email) SELECT email FROM subscribers_temp";
$query .= "
INSERT IGNORE list_subscribers
(ls_list_id,ls_sub_id)
SELECT $ls_id, subscribers.id
FROM subscribers
INNER JOIN subscribers_temp
ON subscribers.email = subscribers_temp.email";
[/code]

Might help when adding to the query instead of giving it a new value .= not = :)

Not sure if thats the full problem I just had a glance.

[Hypnos]

[code=php:0]mysql_query($query);[/code]

[artacus]

mysql_query is only going to let you do 1 query statement at a time. Its a security feature. So you need to run mysql_query($query) for every statement.
But what you can do to speed it up is:
[code]
foreach ($emails as $email) {
  $sql = ($sql) ? "$sql,\n($email)" : "($email)";
}
$query = "INSERT INTO table (email) VALUES $sql";
mysql_query($query);[/code]

[tgavin]

Thank you for all of your replies.

I didn't put mysql_query(); into my post because it would have been a waste of space. I figured it was a given that that would be needed. I just wanted to show everybody the actual queries used.

These replies put me right back to doing a query for every single email address I want to insert. I already know how to do that.

The problem is taking all of the input and inserting it at once into a mysql_query() and letting mySQL handle it, instead of one at a time with php.

[anatak]

maybe build one query with all the values you want to insert

out of the mysql manual
INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

this will insert 3 rows.

I hope this can help you get into the right direction.
anatak

[tgavin]

That's what I want to do, but don't know how to put/format the data into the one query. I keep getting arrays...

[anatak]

ah ok,
you will have to use php to build your query.


$query = "INSERT INTO table (email) VALUES
foreach($emails as $email) {
$query = $query . " ('$email'),"
}
then you have to remove the last , with substring() and after that put a ; after the query.
that way you will have all the values
echo the query to your screen to make sure before you try this.

anatak

[tgavin]

[code=php:0]$query = "INSERT INTO subscribers (email) VALUES ";
foreach($emails as $email) {
$query = $query . " ('$email'),";
}
$query = rtrim($query,',');
$sql = mysql_query($query,$conn) or die(mysql_error());[/code]

Perfect! Thank you! :)