Need some help with multiple PHP/MySQL INSERT statements

**Garp** · Jul 17 '05, 05:49 AM

Re: Need some help with multiple PHP/MySQL INSERT statements

"Jack" <noemail@please .com> wrote in message
news:5f2Ac.3261 9$Hg2.30648@att bi_s04...[color=blue]
> I have a text file of data in a file (add2db.txt) where the entries are
> already entered on separate lines in the following form:
>
> INSERT INTO `reviews` VALUES("", "Tony's", "Lunch", "Great atmosphere.
> Good food.", " (Harry Houdini - 03/01/2004)");
>
> INSERT INTO `reviews` VALUES("", "Le Chow Place", "Lunch", "yada yada",
> " (Herbert Hoover - 03/03/2004)");
>
> INSERT INTO `reviews` VALUES("", "Golden Dragon", "Lunch", "Exquisite.
> Good food.", " (Minnie Mouse - 01/03/2004)");
>
> etc etc
>
> These have been parsed from a review form that a client already
> submitted. What I need to do now after I have reviewed the information
> for appropriateness and grammer is to insert it into my MySQL database.
> I would write single line entries like this:
>
> // Set Mysql Variables
> $host = "localhost" ;
> $user = "root";
> $pass = "";
> $db = "rest";
> $table = "reviews";
>
>
> // Connect to Mysql, select the correct database, and run the query
> which adds the data gathered from the file into the database
>
> mysql_connect($ host,$user,$pas s) or die(mysql_error ());
> mysql_select_db ($db) or die(mysql_error ());
> $add_review = "INSERT INTO $table
> values('','$rna me','$umeal','$ ucomment','$una me')";
> mysql_query($ad d_all) or die(mysql_error ());
>
>
> But how would I write the $add_review to read each line of the
> add2db.txt and enter it as a separate entry?[/color]

If you've already got a file full of SQL, just read it all in and execute it
in one go:
mysql_query(joi n('\n',file("ad d2db.txt"));

Untested, etc.

Garp

**Michael Austin** · Jul 17 '05, 05:49 AM

Re: Need some help with multiple PHP/MySQL INSERT statements

Jack wrote:
[color=blue]
> I have a text file of data in a file (add2db.txt) where the entries are
> already entered on separate lines in the following form:
>
> INSERT INTO `reviews` VALUES("", "Tony's", "Lunch", "Great atmosphere.
> Good food.", " (Harry Houdini - 03/01/2004)");
>
> INSERT INTO `reviews` VALUES("", "Le Chow Place", "Lunch", "yada yada",
> " (Herbert Hoover - 03/03/2004)");
>
> INSERT INTO `reviews` VALUES("", "Golden Dragon", "Lunch", "Exquisite.
> Good food.", " (Minnie Mouse - 01/03/2004)");
>
> etc etc
>
> These have been parsed from a review form that a client already
> submitted. What I need to do now after I have reviewed the information
> for appropriateness and grammer is to insert it into my MySQL database.
> I would write single line entries like this:
>
> // Set Mysql Variables
> $host = "localhost" ;
> $user = "root";
> $pass = "";
> $db = "rest";
> $table = "reviews";
>
>
> // Connect to Mysql, select the correct database, and run the query
> which adds the data gathered from the file into the database
>
> mysql_connect($ host,$user,$pas s) or die(mysql_error ());
> mysql_select_db ($db) or die(mysql_error ());
> $add_review = "INSERT INTO $table
> values('','$rna me','$umeal','$ ucomment','$una me')";
> mysql_query($ad d_all) or die(mysql_error ());
>
>
> But how would I write the $add_review to read each line of the
> add2db.txt and enter it as a separate entry?
>[/color]

if you put the data in comma delimited format you can use the Mysql LOAD
command see http://dev.mysql.com/doc/mysql/en/index.html and search for
LOAD to see the exact syntax... this would be much faster than
creating a file with SQL Syntax then loading the data. The load command
works much faster than multiple insert statements.

Michael Austin.

**Jack** · Jul 17 '05, 05:49 AM

Re: Need some help with multiple PHP/MySQL INSERT statements

Garp wrote:[color=blue]
> "Jack" <noemail@please .com> wrote in message
> news:5f2Ac.3261 9$Hg2.30648@att bi_s04...
>[color=green]
>>I have a text file of data in a file (add2db.txt) where the entries are
>>already entered on separate lines in the following form:
>>
>>INSERT INTO `reviews` VALUES("", "Tony's", "Lunch", "Great atmosphere.
>>Good food.", " (Harry Houdini - 03/01/2004)");
>>
>>INSERT INTO `reviews` VALUES("", "Le Chow Place", "Lunch", "yada yada",
>>" (Herbert Hoover - 03/03/2004)");
>>
>>INSERT INTO `reviews` VALUES("", "Golden Dragon", "Lunch", "Exquisite.
>>Good food.", " (Minnie Mouse - 01/03/2004)");
>>
>>etc etc
>>
>>These have been parsed from a review form that a client already
>>submitted. What I need to do now after I have reviewed the information
>>for appropriateness and grammer is to insert it into my MySQL database.
>>I would write single line entries like this:
>>
>>// Set Mysql Variables
>>$host = "localhost" ;
>>$user = "root";
>>$pass = "";
>>$db = "rest";
>>$table = "reviews";
>>
>>
>>// Connect to Mysql, select the correct database, and run the query
>>which adds the data gathered from the file into the database
>>
>>mysql_connect ($host,$user,$p ass) or die(mysql_error ());
>>mysql_select_ db($db) or die(mysql_error ());
>>$add_review = "INSERT INTO $table
>>values('','$r name','$umeal', '$ucomment','$u name')";
>>mysql_query($ add_all) or die(mysql_error ());
>>
>>
>>But how would I write the $add_review to read each line of the
>>add2db.txt and enter it as a separate entry?[/color]
>
>
> If you've already got a file full of SQL, just read it all in and execute it
> in one go:
> mysql_query(joi n('\n',file("ad d2db.txt"));
>
> Untested, etc.
>
> Garp[/color]

I tried using that but it kept throwing an SQL error of:

You have an error in your SQL syntax near '; \nINSERT INTO `reviews`
VALUES("", "Le Chow Place", "Lunch", "yada yada - Herbert' at line 1

I have no idea where that leading semi-colon came from. But I did find
out that using phpMyAdmin's insert data from a textfile option on the
SQL page imports the add2bd.txt perfectly.

So let me amend my question to this: how can I write in a PHP script to
do what the phpMyAdmin insert data from a textfile function is doing so
I don't have to open phpMyAdmin every time I want to add the info?

**Virgil Green** · Jul 17 '05, 05:49 AM

Re: Need some help with multiple PHP/MySQL INSERT statements

"Jack" <noemail@please .com> wrote in message
news:NR6Ac.1035 71$3x.83114@att bi_s54...[color=blue]
> Garp wrote:[color=green]
> > "Jack" <noemail@please .com> wrote in message
> > news:5f2Ac.3261 9$Hg2.30648@att bi_s04...
> >[color=darkred]
> >>I have a text file of data in a file (add2db.txt) where the entries are
> >>already entered on separate lines in the following form:
> >>
> >>INSERT INTO `reviews` VALUES("", "Tony's", "Lunch", "Great atmosphere.
> >>Good food.", " (Harry Houdini - 03/01/2004)");
> >>
> >>INSERT INTO `reviews` VALUES("", "Le Chow Place", "Lunch", "yada yada",
> >>" (Herbert Hoover - 03/03/2004)");
> >>
> >>INSERT INTO `reviews` VALUES("", "Golden Dragon", "Lunch", "Exquisite.
> >>Good food.", " (Minnie Mouse - 01/03/2004)");
> >>
> >>etc etc
> >>
> >>These have been parsed from a review form that a client already
> >>submitted. What I need to do now after I have reviewed the information
> >>for appropriateness and grammer is to insert it into my MySQL database.
> >>I would write single line entries like this:
> >>
> >>// Set Mysql Variables
> >>$host = "localhost" ;
> >>$user = "root";
> >>$pass = "";
> >>$db = "rest";
> >>$table = "reviews";
> >>
> >>
> >>// Connect to Mysql, select the correct database, and run the query
> >>which adds the data gathered from the file into the database
> >>
> >>mysql_connect ($host,$user,$p ass) or die(mysql_error ());
> >>mysql_select_ db($db) or die(mysql_error ());
> >>$add_review = "INSERT INTO $table
> >>values('','$r name','$umeal', '$ucomment','$u name')";
> >>mysql_query($ add_all) or die(mysql_error ());
> >>
> >>
> >>But how would I write the $add_review to read each line of the
> >>add2db.txt and enter it as a separate entry?[/color]
> >
> >
> > If you've already got a file full of SQL, just read it all in and[/color][/color]
execute it[color=blue][color=green]
> > in one go:
> > mysql_query(joi n('\n',file("ad d2db.txt"));
> >
> > Untested, etc.
> >
> > Garp[/color]
>
> I tried using that but it kept throwing an SQL error of:
>
> You have an error in your SQL syntax near '; \nINSERT INTO `reviews`
> VALUES("", "Le Chow Place", "Lunch", "yada yada - Herbert' at line 1
>
> I have no idea where that leading semi-colon came from. But I did find
> out that using phpMyAdmin's insert data from a textfile option on the
> SQL page imports the add2bd.txt perfectly.[/color]

The "leading semicolon" came from the end of the first SQL statement.
[color=blue]
> So let me amend my question to this: how can I write in a PHP script to
> do what the phpMyAdmin insert data from a textfile function is doing so
> I don't have to open phpMyAdmin every time I want to add the info?[/color]

use file() to read the file into an array. foreach through the array, strip
off the trailing semicolon, and execute mysql_query() using each element of
the array. Watch for blank lines if you have them between your INSERT
statements in the original file.

- Virgil

**Justin Koivisto** · Jul 17 '05, 05:49 AM

Re: Need some help with multiple PHP/MySQL INSERT statements

Jack wrote:[color=blue]
> I have a text file of data in a file (add2db.txt) where the entries are
> already entered on separate lines in the following form:
>
> INSERT INTO `reviews` VALUES("", "Tony's", "Lunch", "Great atmosphere.
> Good food.", " (Harry Houdini - 03/01/2004)");
>
> INSERT INTO `reviews` VALUES("", "Le Chow Place", "Lunch", "yada yada",
> " (Herbert Hoover - 03/03/2004)");
>
> INSERT INTO `reviews` VALUES("", "Golden Dragon", "Lunch", "Exquisite.
> Good food.", " (Minnie Mouse - 01/03/2004)");
>
> These have been parsed from a review form that a client already
> submitted. What I need to do now after I have reviewed the information
> for appropriateness and grammer is to insert it into my MySQL database.
> I would write single line entries like this:[/color]

My suggestion is to get rid of the text file completely. Add another
field in the table "reviewed" and have the default value of 0. When you
review it, set the value to 1. Then in the queries that display, count,
etc., add a "WHERE display = 1" clause to the query.

--
Justin Koivisto - spam@koivi.com
PHP POSTERS: Please use comp.lang.php for PHP related questions,
alt.php* groups are not recommended.

**Jack** · Jul 17 '05, 05:49 AM

Re: Need some help with multiple PHP/MySQL INSERT statements

Virgil Green wrote:[color=blue][color=green][color=darkred]
>>>"Jack" <noemail@please .com> wrote in message
>>>news:5f2Ac.3 2619$Hg2.30648@ attbi_s04...
>>>
>>>
>>>>I have a text file of data in a file (add2db.txt) where the entries are
>>>>already entered on separate lines in the following form:
>>>>
>>>>INSERT INTO `reviews` VALUES("", "Tony's", "Lunch", "Great atmosphere.
>>>>Good food.", " (Harry Houdini - 03/01/2004)");
>>>>
>>>>INSERT INTO `reviews` VALUES("", "Le Chow Place", "Lunch", "yada yada",
>>>>" (Herbert Hoover - 03/03/2004)");
>>>>
>>>>INSERT INTO `reviews` VALUES("", "Golden Dragon", "Lunch", "Exquisite.
>>>>Good food.", " (Minnie Mouse - 01/03/2004)");
>>>>But how would I write the $add_review to read each line of the
>>>>add2db.tx t and enter it as a separate entry?
>>>
>>>
>>>If you've already got a file full of SQL, just read it all in and[/color][/color]
>
> execute it
>[color=green][color=darkred]
>>>in one go:
>>> mysql_query(joi n('\n',file("ad d2db.txt"));
>>>
>>>Untested, etc.
>>>[/color]
>>
>>I tried using that but it kept throwing an SQL error of:
>>
>>You have an error in your SQL syntax near '; \nINSERT INTO `reviews`
>>VALUES("", "Le Chow Place", "Lunch", "yada yada - Herbert' at line 1[/color][/color]
[color=blue][color=green]
>>So let me amend my question to this: how can I write in a PHP script to
>>do what the phpMyAdmin insert data from a textfile function is doing so
>>I don't have to open phpMyAdmin every time I want to add the info?[/color]
>
>
> use file() to read the file into an array. foreach through the array, strip
> off the trailing semicolon, and execute mysql_query() using each element of
> the array. Watch for blank lines if you have them between your INSERT
> statements in the original file.[/color]

Well, I think I'm making a lilttle progress but I'm still doing
something wrong. I'm using the following and it is removing the trailing
semicolon as it should:

$listFile = "add2db.txt ";
if (!($fp = fopen($listFile , "r")))
exit("Unable to open the input file, $listFile.");
$buffer = fread($fp, filesize($listF ile));
fclose($fp);
$a = array($buffer);
$a = preg_replace("( ;)", "", $a);
foreach ($a as $v) {
echo "Current value of \$a: $v.\n";
}

Everything reads and echoes fine. Each line of add2db.txt has had the ;
removed. But when I try to take this result and execute a mysql_query
with it, it throws an error. I'm using:

mysql_query($bu ffer) or die(mysql_error ());
if (mysql_affected _rows() >= 1) {
printf("Records added: %d\n", mysql_affected_ rows());
} else {
print("Database Error!");
die(mysql_error ());
}

mysql_query("CO MMIT");

But it's throwing this SQL error:

You have an error in your SQL syntax near '; INSERT INTO `tips`
VALUES("", "Le Chow Place", "Lunch", "yada yada - Herbert' at line 1

Can anyone offer me any guidance?

**Michael Austin** · Jul 17 '05, 05:49 AM

Re: Need some help with multiple PHP/MySQL INSERT statements

Jack wrote:[color=blue]
> Virgil Green wrote:
>[color=green][color=darkred]
>>>> "Jack" <noemail@please .com> wrote in message
>>>> news:5f2Ac.3261 9$Hg2.30648@att bi_s04...
>>>>
>>>>
>>>>> I have a text file of data in a file (add2db.txt) where the entries
>>>>> are
>>>>> already entered on separate lines in the following form:
>>>>>
>>>>> INSERT INTO `reviews` VALUES("", "Tony's", "Lunch", "Great atmosphere.
>>>>> Good food.", " (Harry Houdini - 03/01/2004)");
>>>>>
>>>>> INSERT INTO `reviews` VALUES("", "Le Chow Place", "Lunch", "yada
>>>>> yada",
>>>>> " (Herbert Hoover - 03/03/2004)");
>>>>>
>>>>> INSERT INTO `reviews` VALUES("", "Golden Dragon", "Lunch", "Exquisite.
>>>>> Good food.", " (Minnie Mouse - 01/03/2004)");
>>>>> But how would I write the $add_review to read each line of the
>>>>> add2db.txt and enter it as a separate entry?
>>>>
>>>>
>>>>
>>>> If you've already got a file full of SQL, just read it all in and[/color]
>>
>>
>> execute it
>>[color=darkred]
>>>> in one go:
>>>> mysql_query(joi n('\n',file("ad d2db.txt"));
>>>>
>>>> Untested, etc.
>>>>
>>>
>>> I tried using that but it kept throwing an SQL error of:
>>>
>>> You have an error in your SQL syntax near '; \nINSERT INTO `reviews`
>>> VALUES("", "Le Chow Place", "Lunch", "yada yada - Herbert' at line 1[/color][/color]
>
>[color=green][color=darkred]
>>> So let me amend my question to this: how can I write in a PHP script to
>>> do what the phpMyAdmin insert data from a textfile function is doing so
>>> I don't have to open phpMyAdmin every time I want to add the info?[/color]
>>
>>
>>
>> use file() to read the file into an array. foreach through the array,
>> strip
>> off the trailing semicolon, and execute mysql_query() using each
>> element of
>> the array. Watch for blank lines if you have them between your INSERT
>> statements in the original file.[/color]
>
>
> Well, I think I'm making a lilttle progress but I'm still doing
> something wrong. I'm using the following and it is removing the trailing
> semicolon as it should:
>[/color]
$cnt = 0;[color=blue]
> $listFile = "add2db.txt ";
> if (!($fp = fopen($listFile , "r")))
> exit("Unable to open the input file, $listFile.");
> $buffer = fread($fp, filesize($listF ile));
> fclose($fp);
> $a = array($buffer);
> $a = preg_replace("( ;)", "", $a);
> foreach ($a as $v)
> echo "Current value of \$a: $v.\n";[/color]
mysql_query($a) or die(mysql_error ());
if (mysql_affected _rows() >= 1) {
$cnt++;
}
else {
print("Database Error!");
die(mysql_error ());
}
mysql_query("CO MMIT");
printf("Records added: %d\n", $cnt);
[color=blue]
> }
>
> Everything reads and echoes fine. Each line of add2db.txt has had the ;
> removed. But when I try to take this result and execute a mysql_query
> with it, it throws an error. I'm using:
>
> mysql_query($bu ffer) or die(mysql_error ());
> if (mysql_affected _rows() >= 1) {
> printf("Records added: %d\n", mysql_affected_ rows());
> } else {
> print("Database Error!");
> die(mysql_error ());
> }
>
> mysql_query("CO MMIT");
>
> But it's throwing this SQL error:
>
> You have an error in your SQL syntax near '; INSERT INTO `tips`
> VALUES("", "Le Chow Place", "Lunch", "yada yada - Herbert' at line 1
>
> Can anyone offer me any guidance?
>[/color]

**Jack** · Jul 17 '05, 05:49 AM

Re: Need some help with multiple PHP/MySQL INSERT statements

Michael Austin wrote:
[color=blue][color=green]
>>[/color]
> $cnt = 0;
>[color=green]
>> $listFile = "add2db.txt ";
>> if (!($fp = fopen($listFile , "r")))
>> exit("Unable to open the input file, $listFile.");
>> $buffer = fread($fp, filesize($listF ile));
>> fclose($fp);
>> $a = array($buffer);
>> $a = preg_replace("( ;)", "", $a);
>> foreach ($a as $v) echo "Current value of \$a: $v.\n";[/color]
>
> mysql_query($a) or die(mysql_error ());
> if (mysql_affected _rows() >= 1) {
> $cnt++;
> }
> else {
> print("Database Error!");
> die(mysql_error ());
> }
> mysql_query("CO MMIT");
> printf("Records added: %d\n", $cnt);
>[color=green]
>> }[/color][/color]

Thanks Michael but that is throwing the following error from mySQL:

You have an error in your SQL syntax near 'Array' at line 1

and the following error log entry in Apache:

[Thu Jun 17 15:27:16 2004] [error] [client 127.0.0.1] PHP Notice: Array
to string conversion in c:\web\addtips. php on line 28

Any other suggestions?

**Michael Austin** · Jul 17 '05, 05:50 AM

Re: Need some help with multiple PHP/MySQL INSERT statements

Jack wrote:[color=blue]
> Michael Austin wrote:
>[color=green][color=darkred]
>>>[/color][/color]
>
>
> Thanks Michael but that is throwing the following error from mySQL:
> You have an error in your SQL syntax near 'Array' at line 1
> and the following error log entry in Apache:
> [Thu Jun 17 15:27:16 2004] [error] [client 127.0.0.1] PHP Notice: Array
> to string conversion in c:\web\addtips. php on line 28[/color]
[color=blue]
> Any other suggestions?
>[/color]

I didn't say it was perfect.. :) you may need to debug it and clean it
up a bit, but basically I was showing you that you were doing this in
two different loops when one was sufficient.

Here is a link for the "while" loop processing and has some array examples:

<< http://www.php.net/manual/en/control...ures.while.php >>

Michael.

**Virgil Green** · Jul 17 '05, 05:50 AM

Re: Need some help with multiple PHP/MySQL INSERT statements

"Jack" <noemail@please .com> wrote in message
news:gmmAc.6583 6$HG.44761@attb i_s53...[color=blue]
> Michael Austin wrote:
>[color=green][color=darkred]
> >>[/color]
> > $cnt = 0;
> >[color=darkred]
> >> $listFile = "add2db.txt ";
> >> if (!($fp = fopen($listFile , "r")))
> >> exit("Unable to open the input file, $listFile.");
> >> $buffer = fread($fp, filesize($listF ile));
> >> fclose($fp);
> >> $a = array($buffer);
> >> $a = preg_replace("( ;)", "", $a);
> >> foreach ($a as $v) echo "Current value of \$a: $v.\n";[/color]
> >
> > mysql_query($a) or die(mysql_error ());
> > if (mysql_affected _rows() >= 1) {
> > $cnt++;
> > }
> > else {
> > print("Database Error!");
> > die(mysql_error ());
> > }
> > mysql_query("CO MMIT");
> > printf("Records added: %d\n", $cnt);
> >[color=darkred]
> >> }[/color][/color]
>
> Thanks Michael but that is throwing the following error from mySQL:
>
> You have an error in your SQL syntax near 'Array' at line 1
>
> and the following error log entry in Apache:
>
> [Thu Jun 17 15:27:16 2004] [error] [client 127.0.0.1] PHP Notice: Array
> to string conversion in c:\web\addtips. php on line 28[/color]

Where Michael typed "mysql_query($a )" I think he meant "mysql_query($v )" to
use the current element of $a.

But why not use file()? It will place every record in the file into a
separate element of an array and then you could foreach over that array
rather than processing the $buffer like you are. Probably neither here nor
there, since your preg_replace will only be executed (though I'd probably
have just use a substring to extract all but the last character of each of
the array elements)

By the way, your example before Michael's mods failed because you attempted
to pass $buffer into the query and you needed to pass each element of the
array you created (at least tried to create) as a separate query. Actually,
withoug examining it in detail, I'm not sure if/why
$a=array($buffe r);
would work to create the array you need.

- Virgil

**Garp** · Jul 17 '05, 05:50 AM

Re: Need some help with multiple PHP/MySQL INSERT statements

"Jack" <noemail@please .com> wrote in message
news:gmmAc.6583 6$HG.44761@attb i_s53...[color=blue]
> Michael Austin wrote:
>[color=green][color=darkred]
> >>[/color]
> > $cnt = 0;
> >[color=darkred]
> >> $listFile = "add2db.txt ";
> >> if (!($fp = fopen($listFile , "r")))
> >> exit("Unable to open the input file, $listFile.");
> >> $buffer = fread($fp, filesize($listF ile));
> >> fclose($fp);[/color][/color][/color]

Don't need to do this, this is what file() does.
[color=blue][color=green][color=darkred]
> >> $a = array($buffer);
> >> $a = preg_replace("( ;)", "", $a);
> >> foreach ($a as $v) echo "Current value of \$a: $v.\n";[/color]
> >
> > mysql_query($a) or die(mysql_error ());[/color][/color]

You're sending the array as the query, ITYM mysql_query($v) .
[color=blue][color=green]
> > if (mysql_affected _rows() >= 1) {
> > $cnt++;
> > }
> > else {
> > print("Database Error!");
> > die(mysql_error ());
> > }
> > mysql_query("CO MMIT");
> > printf("Records added: %d\n", $cnt);
> >[color=darkred]
> >> }[/color][/color]
>
> Thanks Michael but that is throwing the following error from mySQL:
>
> You have an error in your SQL syntax near 'Array' at line 1
>
> and the following error log entry in Apache:
>
> [Thu Jun 17 15:27:16 2004] [error] [client 127.0.0.1] PHP Notice: Array
> to string conversion in c:\web\addtips. php on line 28
>
> Any other suggestions?[/color]

I suggest if you post the line that corresponds with those line numbers in
the errors, I might have a better chance of working it out.

Sorry my original shot didn't seem to work; I'm sure I did it once...

Garp

**Jack** · Jul 17 '05, 05:50 AM

Re: Need some help with multiple PHP/MySQL INSERT statements

Virgil Green wrote:[color=blue]
> "Jack" <noemail@please .com> wrote in message
> news:gmmAc.6583 6$HG.44761@attb i_s53...
>[color=green]
>>Michael Austin wrote:
>>
>>[color=darkred]
>>> $cnt = 0;
>>>
>>>
>>>> $listFile = "add2db.txt ";
>>>> if (!($fp = fopen($listFile , "r")))
>>>> exit("Unable to open the input file, $listFile.");
>>>> $buffer = fread($fp, filesize($listF ile));
>>>> fclose($fp);
>>>> $a = array($buffer);
>>>> $a = preg_replace("( ;)", "", $a);
>>>> foreach ($a as $v) echo "Current value of \$a: $v.\n";
>>>
>>> mysql_query($a) or die(mysql_error ());
>>> if (mysql_affected _rows() >= 1) {
>>> $cnt++;
>>> }
>>> else {
>>> print("Database Error!");
>>> die(mysql_error ());
>>> }
>>> mysql_query("CO MMIT");
>>> printf("Records added: %d\n", $cnt);
>>>
>>>
>>>>}[/color]
>>
>>Thanks Michael but that is throwing the following error from mySQL:
>>
>>You have an error in your SQL syntax near 'Array' at line 1
>>
>>and the following error log entry in Apache:
>>
>>[Thu Jun 17 15:27:16 2004] [error] [client 127.0.0.1] PHP Notice: Array
>>to string conversion in c:\web\addtips. php on line 28[/color]
>
>
> Where Michael typed "mysql_query($a )" I think he meant "mysql_query($v )" to
> use the current element of $a.
>
> But why not use file()? It will place every record in the file into a
> separate element of an array and then you could foreach over that array
> rather than processing the $buffer like you are. Probably neither here nor
> there, since your preg_replace will only be executed (though I'd probably
> have just use a substring to extract all but the last character of each of
> the array elements)[/color]

I tried using the mysql_query(joi n('\n',file("ad d2db.txt")); that Garp
suggested but it kept throwing an SQL error of:

You have an error in your SQL syntax near '; \nINSERT INTO `reviews`
VALUES("", "Le Chow Place", "Lunch", "yada yada - Herbert' at line 1

and I couldn't get it to work. It was then I experimented with importing
the file through phpMyAdmin's text file importing and found out that it
worked perfectly doing that, thus my question of a way to script that
same step using PHP rather than my having to get into the database via
phpMyAdmin every time I wanted to add the new data.

[color=blue]
> By the way, your example before Michael's mods failed because you attempted
> to pass $buffer into the query and you needed to pass each element of the
> array you created (at least tried to create) as a separate query. Actually,
> withoug examining it in detail, I'm not sure if/why
> $a=array($buffe r);
> would work to create the array you need.[/color]

I know but when I tried changing the mysql_query($bu ffer) to
mysql_query($a) , I got the array to string error.

I have tried so many different combinations by now that I can't even
begin to tell you what I haven't tried yet. I'm sure you can tell I'm no
PHP expert but I'll try anything.

I discovered using Michael's first suggestion of putting the add2db info
into a CSV file minus the quotes that this would work on my local
machine. Alas it does not work on the server (not mine) as I do not have
FILE rights so I can't use the LOAD command. That's why I'm looking for
a PHP way of doing a mysql_query to accomplish it.

So now that I've covered the history, let me once again cover the
present. Here is the code as it stands currently:

mysql_connect($ host,$user,$pas s) or die(mysql_error ());
mysql_select_db ($db) or die(mysql_error ());
$cnt = 0;
$listFile = "add2db.txt ";
if (!($fp = fopen($listFile , "r")))
exit("Unable to open the input file, $listFile.");
$buffer = fread($fp, filesize($listF ile));
fclose($fp);
$a = array($buffer);
$a = preg_replace("( ;)", "", $a);
foreach ($a as $v)
echo "Current value of \$a:<br> $v.\n";

mysql_query($v) or die(mysql_error ());
if (mysql_affected _rows() >= 1) {
$cnt++;
}
else {
print("Database Error!");
die(mysql_error ());
}
mysql_query("CO MMIT");
printf("Records added: %d\n", $cnt);

Everything works fine up to and including the echo statement. But the
mysql_query is throwing the following error:

You have an error in your SQL syntax near 'INSERT INTO `tips` VALUES("",
"Le Chow Place", "Lunch", "yada yada - Herbert Ho' at line 2

The add2db.txt looks like this:

INSERT INTO `reviews` VALUES("", "Tony's", "Lunch", "Great atmosphere.
Good food. -Harry Houdini", "2004-03-01");
INSERT INTO `reviews` VALUES("", "Le Chow Place", "Lunch", "yada yada -
Herbert Hoover ","2004-03-03");
INSERT INTO `reviews` VALUES("", "Golden Dragon", "Lunch", "Exquisite.
Good food. - Minnie Mouse", "2004-01-08");

<sigh> Any ideas what to try next?

**Garp** · Jul 17 '05, 05:50 AM

Re: Need some help with multiple PHP/MySQL INSERT statements

"Jack" <noemail@please .com> wrote in message
news:S%pAc.6627 5$HG.38048@attb i_s53...[color=blue]
> Virgil Green wrote:[color=green]
> > "Jack" <noemail@please .com> wrote in message
> > news:gmmAc.6583 6$HG.44761@attb i_s53...
> >[color=darkred]
> >>Michael Austin wrote:
> >>
> >>
> >>> $cnt = 0;
> >>>
> >>>
> >>>> $listFile = "add2db.txt ";
> >>>> if (!($fp = fopen($listFile , "r")))
> >>>> exit("Unable to open the input file, $listFile.");
> >>>> $buffer = fread($fp, filesize($listF ile));
> >>>> fclose($fp);
> >>>> $a = array($buffer);
> >>>> $a = preg_replace("( ;)", "", $a);
> >>>> foreach ($a as $v) echo "Current value of \$a: $v.\n";
> >>>
> >>> mysql_query($a) or die(mysql_error ());
> >>> if (mysql_affected _rows() >= 1) {
> >>> $cnt++;
> >>> }
> >>> else {
> >>> print("Database Error!");
> >>> die(mysql_error ());
> >>> }
> >>> mysql_query("CO MMIT");
> >>> printf("Records added: %d\n", $cnt);
> >>>
> >>>
> >>>>}
> >>
> >>Thanks Michael but that is throwing the following error from mySQL:
> >>
> >>You have an error in your SQL syntax near 'Array' at line 1
> >>
> >>and the following error log entry in Apache:
> >>
> >>[Thu Jun 17 15:27:16 2004] [error] [client 127.0.0.1] PHP Notice: Array
> >>to string conversion in c:\web\addtips. php on line 28[/color]
> >
> >
> > Where Michael typed "mysql_query($a )" I think he meant "mysql_query($v )"[/color][/color]
to[color=blue][color=green]
> > use the current element of $a.
> >
> > But why not use file()? It will place every record in the file into a
> > separate element of an array and then you could foreach over that array
> > rather than processing the $buffer like you are. Probably neither here[/color][/color]
nor[color=blue][color=green]
> > there, since your preg_replace will only be executed (though I'd[/color][/color]
probably[color=blue][color=green]
> > have just use a substring to extract all but the last character of each[/color][/color]
of[color=blue][color=green]
> > the array elements)[/color]
>
> I tried using the mysql_query(joi n('\n',file("ad d2db.txt")); that Garp
> suggested but it kept throwing an SQL error of:
>
> You have an error in your SQL syntax near '; \nINSERT INTO `reviews`
> VALUES("", "Le Chow Place", "Lunch", "yada yada - Herbert' at line 1
>
> and I couldn't get it to work. It was then I experimented with importing
> the file through phpMyAdmin's text file importing and found out that it
> worked perfectly doing that, thus my question of a way to script that
> same step using PHP rather than my having to get into the database via
> phpMyAdmin every time I wanted to add the new data.
>
>[color=green]
> > By the way, your example before Michael's mods failed because you[/color][/color]
attempted[color=blue][color=green]
> > to pass $buffer into the query and you needed to pass each element of[/color][/color]
the[color=blue][color=green]
> > array you created (at least tried to create) as a separate query.[/color][/color]
Actually,[color=blue][color=green]
> > withoug examining it in detail, I'm not sure if/why
> > $a=array($buffe r);
> > would work to create the array you need.[/color]
>
> I know but when I tried changing the mysql_query($bu ffer) to
> mysql_query($a) , I got the array to string error.
>
> I have tried so many different combinations by now that I can't even
> begin to tell you what I haven't tried yet. I'm sure you can tell I'm no
> PHP expert but I'll try anything.
>
> I discovered using Michael's first suggestion of putting the add2db info
> into a CSV file minus the quotes that this would work on my local
> machine. Alas it does not work on the server (not mine) as I do not have
> FILE rights so I can't use the LOAD command. That's why I'm looking for
> a PHP way of doing a mysql_query to accomplish it.
>
> So now that I've covered the history, let me once again cover the
> present. Here is the code as it stands currently:
>
> mysql_connect($ host,$user,$pas s) or die(mysql_error ());
> mysql_select_db ($db) or die(mysql_error ());
> $cnt = 0;
> $listFile = "add2db.txt ";
> if (!($fp = fopen($listFile , "r")))
> exit("Unable to open the input file, $listFile.");
> $buffer = fread($fp, filesize($listF ile));
> fclose($fp);
> $a = array($buffer);
> $a = preg_replace("( ;)", "", $a);
> foreach ($a as $v)
> echo "Current value of \$a:<br> $v.\n";
>
> mysql_query($v) or die(mysql_error ());
> if (mysql_affected _rows() >= 1) {
> $cnt++;
> }
> else {
> print("Database Error!");
> die(mysql_error ());
> }
> mysql_query("CO MMIT");
> printf("Records added: %d\n", $cnt);
>
>
> Everything works fine up to and including the echo statement. But the
> mysql_query is throwing the following error:
>
> You have an error in your SQL syntax near 'INSERT INTO `tips` VALUES("",
> "Le Chow Place", "Lunch", "yada yada - Herbert Ho' at line 2
>
> The add2db.txt looks like this:
>
> INSERT INTO `reviews` VALUES("", "Tony's", "Lunch", "Great atmosphere.
> Good food. -Harry Houdini", "2004-03-01");
> INSERT INTO `reviews` VALUES("", "Le Chow Place", "Lunch", "yada yada -
> Herbert Hoover ","2004-03-03");
> INSERT INTO `reviews` VALUES("", "Golden Dragon", "Lunch", "Exquisite.
> Good food. - Minnie Mouse", "2004-01-08");
>
> <sigh> Any ideas what to try next?
>[/color]

I looked up my old code; I was parsing out the INSERT stuff and repeating
the VALUES section over and over. Turn these:

INSERT INTO table VALUES ('a','b','c')
INSERT INTO table VALUES ('d','e','f')
INSERT INTO table VALUES ('g','h','i')

into this:

INSERT INTO table VALUES ('a','b','c'),( 'd','e','f'),(' g','h','i')

I *knew* I'd done it somehow.

Garp

**David Mackenzie** · Jul 17 '05, 05:50 AM

Re: Need some help with multiple PHP/MySQL INSERT statements

On Thu, 17 Jun 2004 13:30:41 GMT, Justin Koivisto <spam@koivi.com >
wrote:
[color=blue]
>Jack wrote:[color=green]
>> I have a text file of data in a file (add2db.txt) where the entries are
>> already entered on separate lines in the following form:
>>
>> INSERT INTO `reviews` VALUES("", "Tony's", "Lunch", "Great atmosphere.
>> Good food.", " (Harry Houdini - 03/01/2004)");
>>
>> INSERT INTO `reviews` VALUES("", "Le Chow Place", "Lunch", "yada yada",
>> " (Herbert Hoover - 03/03/2004)");
>>
>> INSERT INTO `reviews` VALUES("", "Golden Dragon", "Lunch", "Exquisite.
>> Good food.", " (Minnie Mouse - 01/03/2004)");
>>
>> These have been parsed from a review form that a client already
>> submitted. What I need to do now after I have reviewed the information
>> for appropriateness and grammer is to insert it into my MySQL database.
>> I would write single line entries like this:[/color]
>
>My suggestion is to get rid of the text file completely. Add another
>field in the table "reviewed" and have the default value of 0. When you
>review it, set the value to 1. Then in the queries that display, count,
>etc., add a "WHERE display = 1" clause to the query.[/color]

I'd have it so that if "reviewed" is NULL, the item has not been
reviewed. When the item is reviewed, set the "reviewed" column to the
date. Then the date can be removed from the last column. It also means
queries such as "all items reviewed since <date>", or "all items
reviewed in the last month" can be done.

I'd also create a reviewer table and make a relation between "reviews"
and "reviewer". Then I could query for all of Herbert Hoover's
reviews.

--
David ( @priz.co.uk )

Need some help with multiple PHP/MySQL INSERT statements

Need some help with multiple PHP/MySQL INSERT statements

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment