473,380 Members | 1,357 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,380 software developers and data experts.

Need some help with multiple PHP/MySQL INSERT statements

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?

Jul 17 '05 #1
15 7349

"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
Jul 17 '05 #2
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.

Jul 17 '05 #3
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?

Jul 17 '05 #4
"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
Jul 17 '05 #5
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.
Jul 17 '05 #6
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?

Jul 17 '05 #7
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?

Jul 17 '05 #8
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?

Jul 17 '05 #9
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.
Jul 17 '05 #10
"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
Jul 17 '05 #11

"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
Jul 17 '05 #12
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?

Jul 17 '05 #13

"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
Jul 17 '05 #14
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 )
Jul 17 '05 #15
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!

Jul 17 '05 #16

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: RotterdamStudents | last post by:
Hello there, i have a strange problem. I can't get php to insert multiple rows at once in a MySQL database. I use the $sql = "INSERT INTO database (a,b,c,d,e) VALUES ('$a', '$b' ,'$c', '$d',...
6
by: a-ok | last post by:
Hi, My client has a product database od around 20000 items. And it is updated every few days when he gets a catalog from the supplier. It's supposed to work like this: if there already is a...
5
by: duikboot | last post by:
Hi all, I'm trying to export a view tables from a Oracle database to a Mysql database. I create insert statements (they look alright), but it all goes wrong when I try to execute them in Mysql,...
0
by: Frank | last post by:
This one has me pretty stumped at the moment. I've tried variations with MySQL IF() statements but can't quite get it right. How can I select all of the records of the table 'items' that are...
3
by: Tim Hastings | last post by:
Hello, I am using MyODBC from VB and I want to submit a batch of insert statements in one call from my App. This is more efficient than making multiple calls from code because of the...
4
by: DG | last post by:
Hi, Can anyone advise how to execute multiple statements in a single query batch. For example- update customers set customer_name = 'Smith' where customer_name = 'Smyth'; select * from...
3
by: kristof.loots | last post by:
Hi all, Somehow I want to transfer data from an mysql database on the web to an local offline access database. Note: tables and types do not match. So, I toughed, generating sql commands to...
2
by: speralta | last post by:
My tired old eyes may be failing me, but the following insert statements look correct to me, but I can't seem to get a clean insert from a fairly large text file database into mysql. I was...
9
by: Paul | last post by:
I have a process that I want to speed up. It first was written in Microsoft Access then converted to VB.NET. I would like to hear some suggestions on how to speed it up. The process is to match...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.