473,735 Members | 9,917 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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,$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?

Jul 17 '05 #1
15 7390

"Jack" <no*****@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)");

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?


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
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,$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?

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.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)");

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?

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


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.1035 71$3x.83114@att bi_s54...
Garp wrote:
"Jack" <no*****@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)");

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?

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


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.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
execute it
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 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($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?

Jul 17 '05 #7
Jack wrote:
Virgil Green wrote:
"Jack" <no*****@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

execute it
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
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($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);
}

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?

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($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

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

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

Similar topics

7
33311
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', '$e')"; I want to insert 5 rows at a time in the database, but it only inserts every 5th record. For example: 1. AA
6
2237
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 product in the database with that ID (primary key), just UPDATE the price, and if there is no product with that ID it should do an INSERT for that item. Now I've tried it in several ways and I can't seem to get it to execute
5
3492
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, because the dates must have quotes on each side. I just don't know how make the dates right. Well I'll just show you the code and some insert statements it generates. Could anyone please help me?
0
1295
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 active. Restricted such that if there are items active for the subcategory and the parent category *only* the items of the subcategory get returned and if there are only items active for the parent category they get returned. The parent category will...
3
4604
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 communication overhead. If I send a batch multiple statements separated by ; or ; + newline I get syntax errors pointing at the start of the next statement.. If I fire each statement one at a time with the ; at the end there's no problem.
4
16756
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 customers; I can execute each statement individually but get the 'you have an error in
3
17920
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 insert the data would do the trick. But, unfortunately it works only properly for 1 record. This is what I do (suggestions are more than welcome) Create a new query, skip all the wizard-stuff and choose sql-view. Then I past my dozen sql commands...
2
8258
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 wondering if maybe I'm hitting a limit on character length per line on some insert statements that I'm trying. Here's the error messages: execute failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server...
9
1604
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 names using a Soundex function. It reads through a table of about 20,000 records and matches it with a table of about 9,000 records. That is all done in code. The tables are stored in SQL Server 2005. Soundex is a numerical formula to...
0
8962
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8785
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9327
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8201
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6747
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4559
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4822
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2739
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2188
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.