Need some help with multiple PHP/MySQL INSERT statements

[Jack]

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,$pass) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
$add_review = "INSERT INTO $table
values('','$rname','$umeal','$ucomment','$uname')" ;
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?

[Garp]

"Jack" <no*****@please.com> wrote in message
news:5f2Ac.32619$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)");

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,$pass) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
$add_review = "INSERT INTO $table
values('','$rname','$umeal','$ucomment','$uname')" ;
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?

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

Untested, etc.

Garp

[Michael Austin]

Jack wrote:

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,$pass) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
$add_review = "INSERT INTO $table
values('','$rname','$umeal','$ucomment','$uname')" ;
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?

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]

Garp wrote:

"Jack" <no*****@please.com> wrote in message
news:5f2Ac.32619$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)");

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,$pass) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
$add_review = "INSERT INTO $table
values('','$rname','$umeal','$ucomment','$uname' )";
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?

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

Untested, etc.

Garp

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]

"Jack" <no*****@please.com> wrote in message
news:NR6Ac.103571$3x.83114@attbi_s54...

Garp wrote:

"Jack" <no*****@please.com> wrote in message
news:5f2Ac.32619$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)");

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,$pass) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
$add_review = "INSERT INTO $table
values('','$rname','$umeal','$ucomment','$uname' )";
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?

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

Untested, etc.

Garp

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.

The "leading semicolon" came from the end of the first SQL statement.
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?

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]

Jack wrote:

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:

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 - sp**@koivi.com
PHP POSTERS: Please use comp.lang.php for PHP related questions,
alt.php* groups are not recommended.

[Jack]

Virgil Green wrote:

"Jack" <no*****@please.com> wrote in message
news:5f2Ac.32619$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.txt and enter it as a separate entry?
If you've already got a file full of SQL, just read it all in and
execute it
in one go:
mysql_query(join('\n',file("add2db.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 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?

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.

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($listFile));
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($buffer) 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("COMMIT");

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]

Jack wrote:

Virgil Green wrote:

"Jack" <no*****@please.com> wrote in message
news:5f2Ac.32619$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.txt and enter it as a separate entry?

If you've already got a file full of SQL, just read it all in and

execute it

in one go:
mysql_query(join('\n',file("add2db.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
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?

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.

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:

$cnt = 0; $listFile = "add2db.txt";
if (!($fp = fopen($listFile, "r")))
exit("Unable to open the input file, $listFile.");
$buffer = fread($fp, filesize($listFile));
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("COMMIT");
printf("Records added: %d\n", $cnt);
}

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($buffer) 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("COMMIT");

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?

[Jack]

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($listFile));
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("COMMIT");
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

Any other suggestions?

[Michael Austin]

Jack wrote:

Michael Austin wrote:

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?

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]

"Jack" <no*****@please.com> wrote in message
news:gmmAc.65836$HG.44761@attbi_s53...

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($listFile));
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("COMMIT");
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

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($buffer);
would work to create the array you need.

- Virgil

[Garp]

"Jack" <no*****@please.com> wrote in message
news:gmmAc.65836$HG.44761@attbi_s53...

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($listFile));
fclose($fp);
Don't need to do this, this is what file() does.
$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());
You're sending the array as the query, ITYM mysql_query($v).
if (mysql_affected_rows() >= 1) {
$cnt++;
}
else {
print("Database Error!");
die(mysql_error());
}
mysql_query("COMMIT");
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

Any other suggestions?

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]

Virgil Green wrote:

"Jack" <no*****@please.com> wrote in message
news:gmmAc.65836$HG.44761@attbi_s53...

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($listFile));
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("COMMIT");
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

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)

I tried using the mysql_query(join('\n',file("add2db.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.

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($buffer);
would work to create the array you need.

I know but when I tried changing the mysql_query($buffer) 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,$pass) 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($listFile));
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("COMMIT");
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]

"Jack" <no*****@please.com> wrote in message
news:S%pAc.66275$HG.38048@attbi_s53...

Virgil Green wrote:

"Jack" <no*****@please.com> wrote in message
news:gmmAc.65836$HG.44761@attbi_s53...

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($listFile));
> 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("COMMIT");
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

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)

I tried using the mysql_query(join('\n',file("add2db.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.

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($buffer);
would work to create the array you need.

I know but when I tried changing the mysql_query($buffer) 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,$pass) 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($listFile));
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("COMMIT");
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?

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]

On Thu, 17 Jun 2004 13:30:41 GMT, Justin Koivisto <sp**@koivi.com>
wrote:

Jack wrote:

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:

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.

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 )

[Jack]

Garp wrote:

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.

THANK YOU GARP!! That works perfectly! Now I'll go back and rewrite the
way the add2db.txt gets written to conform with this. I appreciate your
help (as well as Michael's and Virgil's) in getting this working.

Then I'll move on to tackle calling this script with a submit button
instead of having it execute on loading. But I can do that... I think.
;) LOL!