Re: [PHP] mysql query and maximum characters in sql statement
Hi Jim Lucas, You are correct... i want to run in the same way. but as my 2 tables, column name are different i cant run the LOAD DATA infile. And the example you mentioned for break at 100, also i thought to use in that way. but one of the column had the text type which we cant predict about the size. Thanks for the support from all of you. Now, I am inserting the rows one by one only On 5/2/08, Chris [EMAIL PROTECTED] wrote: Jim Lucas wrote: Waynn Lue wrote: Wouldn't using LOAD DATA INFILE be better than writing your own script? depends, does the data file match the table column for column? Doesn't have to. http://dev.mysql.com/doc/refman/5.0/en/load-data.html By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list: LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...); But load data infile requires extra mysql privileges. -- Postgresql php tutorials http://www.designmagick.com/ -- Regards, Sanjeev http://www.sanchanworld.com | http://webdirectory.sanchanworld.com - submit your site
Re: [PHP] mysql query and maximum characters in sql statement
Sanjeev N wrote: Hi Jim Lucas, You are correct... i want to run in the same way. but as my 2 tables, column name are different i cant run the LOAD DATA infile. If you're inserting the same data, then use LOAD DATA INFILE to load it into a temporary table, then use INSERT SELECT's to put them into the other tables. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query and maximum characters in sql statement
Waynn Lue wrote: Wouldn't using LOAD DATA INFILE be better than writing your own script? depends, does the data file match the table column for column? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query and maximum characters in sql statement
Jim Lucas wrote: Waynn Lue wrote: Wouldn't using LOAD DATA INFILE be better than writing your own script? depends, does the data file match the table column for column? Doesn't have to. http://dev.mysql.com/doc/refman/5.0/en/load-data.html By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list: LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...); But load data infile requires extra mysql privileges. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] mysql query and maximum characters in sql statement
Hi,
I have written a program which imports the tab delimited file and insert all
the line from file to the mysql line by line.
I am succeding in the above case. but problem with the above method is its
taking to too much time while inserting into the database.
The file's size will be more than 5000 lines.
Then i tried to build a string of ; seperated queries. as follows
for($i=1; $isizeof($array); $i++){
$insert_string .= insert into tablename (v1, v2. v6)
values('$array[$i][1]', '$array[$i][2]'. '$array[$i][6]');;
}
if(!empty($insert_string)){
mysql_query($insert_string, $conn) or die(query failed : .mysql_errror());
}
Its throwing error saying check the manual for right syntax.
After investigating in some sites i come to know that its problem of
limitations in query size.
I also tried with SET GLOBAL max_allowed_packet=3000;
Then also its throwing the same error.
Can anybody tell me how to fix this error and reduce the inserting time with
a single statement instead of writing more insert statements
--
Regards,
Sanjeev
http://www.sanchanworld.com
http://webdirectory.sanchanworld.com - submit your site
Re: [PHP] mysql query and maximum characters in sql statement
Sanjeev N [EMAIL PROTECTED] wrote:
Hi,
I have written a program which imports the tab delimited file and insert all
the line from file to the mysql line by line.
I am succeding in the above case. but problem with the above method is its
taking to too much time while inserting into the database.
The file's size will be more than 5000 lines.
Then i tried to build a string of ; seperated queries. as follows
for($i=1; $isizeof($array); $i++){
$insert_string .= insert into tablename (v1, v2. v6)
values('$array[$i][1]', '$array[$i][2]'. '$array[$i][6]');;
}
if(!empty($insert_string)){
mysql_query($insert_string, $conn) or die(query failed : .mysql_errror());
}
Its throwing error saying check the manual for right syntax.
After investigating in some sites i come to know that its problem of
limitations in query size.
I also tried with SET GLOBAL max_allowed_packet=3000;
Then also its throwing the same error.
Can anybody tell me how to fix this error and reduce the inserting time with
a single statement instead of writing more insert statements
Sure, check with a MySQL list via http://www.mysql.com
Otherwise, split it up into multiple inserts.
I currently have a script which reads a 550,000 CSV file and uses
?php
// read file
// for each line, do the following
while ($in != EOF)
{
$line=explode (',',$in);
$out=insert into table values('','$line[0]', .);
fwrite ($outf, $out);
}
then I have another file that
?php
//read the file line by line
//connect to db
$sql = $inLine;
mysql_query($sql) or die();
?
which all in all takes about 10 seconds to run the conversion and then the
inserts.
HTH,
Wolf
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query and maximum characters in sql statement
Sanjeev N wrote:
Hi,
I have written a program which imports the tab delimited file and insert all
the line from file to the mysql line by line.
I am succeding in the above case. but problem with the above method is its
taking to too much time while inserting into the database.
The file's size will be more than 5000 lines.
Then i tried to build a string of ; seperated queries. as follows
for($i=1; $isizeof($array); $i++){
$insert_string .= insert into tablename (v1, v2. v6)
values('$array[$i][1]', '$array[$i][2]'. '$array[$i][6]');;
}
if(!empty($insert_string)){
mysql_query($insert_string, $conn) or die(query failed : .mysql_errror());
}
Its throwing error saying check the manual for right syntax.
After investigating in some sites i come to know that its problem of
limitations in query size.
I also tried with SET GLOBAL max_allowed_packet=3000;
Then also its throwing the same error.
Can anybody tell me how to fix this error and reduce the inserting time with
a single statement instead of writing more insert statements
You are probably looking for something like this.
?php
if ( count($array) ) {
$insert_string = INSERT INTO tablename (v1, v2. v6) VALUES ;
$data = array();
foreach ( $array AS $row ){
$row_clean = array_map('mysql_real_escape_string', $row);
$data[] = ('{$row_clean[1]}', '{$row_clean[2]}',.'{$row_clean[6]}');
}
$insert_string = join(', ', $data);
mysql_query($insert_string, $conn) or die(query failed : .mysql_errror());
} else {
echo Nothing to insert;
}
?
--
Jim Lucas
Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them.
Twelfth Night, Act II, Scene V
by William Shakespeare
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query and maximum characters in sql statement
Jim Lucas wrote:
Sanjeev N wrote:
Hi,
I have written a program which imports the tab delimited file and
insert all
the line from file to the mysql line by line.
I am succeding in the above case. but problem with the above method is
its
taking to too much time while inserting into the database.
The file's size will be more than 5000 lines.
Then i tried to build a string of ; seperated queries. as follows
for($i=1; $isizeof($array); $i++){
$insert_string .= insert into tablename (v1, v2. v6)
values('$array[$i][1]', '$array[$i][2]'. '$array[$i][6]');;
}
if(!empty($insert_string)){
mysql_query($insert_string, $conn) or die(query failed :
.mysql_errror());
}
Its throwing error saying check the manual for right syntax.
After investigating in some sites i come to know that its problem of
limitations in query size.
I also tried with SET GLOBAL max_allowed_packet=3000;
Then also its throwing the same error.
Can anybody tell me how to fix this error and reduce the inserting
time with
a single statement instead of writing more insert statements
You are probably looking for something like this.
?php
if ( count($array) ) {
$insert_string = INSERT INTO tablename (v1, v2. v6) VALUES ;
$data = array();
foreach ( $array AS $row ){
$row_clean = array_map('mysql_real_escape_string', $row);
$data[] = ('{$row_clean[1]}',
'{$row_clean[2]}',.'{$row_clean[6]}');
}
$insert_string = join(', ', $data);
mysql_query($insert_string, $conn) or die(query failed :
.mysql_errror());
} else {
echo Nothing to insert;
}
?
That would work, but will probably result in a query string that is too long.
I'll redo the above to fix that.
?php
# How often do you want to insert??
$break_at = 100;
# Initialize the counter
$cnt = 0;
# Initial insert string
$insert_string = INSERT INTO tablename (v1, v2. v6) VALUES ;
# if there is data, then process, otherwise skip it.
if ( count($array) ) {
$data = array();
# Loop through data
foreach ( $array AS $row ) {
$cnt++;
# Clean the result data
$row_clean = array_map('mysql_real_escape_string', $row);
# Build data string and push it onto the data array.
$data[] = ('{$row_clean[1]}', '{$row_clean[2]}',.'{$row_clean[6]}');
# Break and insert if we are at the break point
if ( $cnt === $break_at ) {
# Reset Counter
$cnt = 0;
# Run insert
mysql_query($insert_string . join(', ', $data), $conn) or
die(query failed : .mysql_error());
# Reset data array
$data = array();
} //if
} //foreach
# This should take care of any extra that didn't get processed in the foreach
if ( count($data) ) {
# Insert remaining data
mysql_query($insert_string . join(', ', $data), $conn) or
die(query failed : .mysql_error());
} //if
} else {
echo Nothing to insert;
} //if
?
--
Jim Lucas
Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them.
Twelfth Night, Act II, Scene V
by William Shakespeare
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query and maximum characters in sql statement
Wouldn't using LOAD DATA INFILE be better than writing your own script?
On 5/1/08, Jim Lucas [EMAIL PROTECTED] wrote:
Jim Lucas wrote:
Sanjeev N wrote:
Hi,
I have written a program which imports the tab delimited file and
insert all
the line from file to the mysql line by line.
I am succeding in the above case. but problem with the above method is
its
taking to too much time while inserting into the database.
The file's size will be more than 5000 lines.
Then i tried to build a string of ; seperated queries. as follows
for($i=1; $isizeof($array); $i++){
$insert_string .= insert into tablename (v1, v2. v6)
values('$array[$i][1]', '$array[$i][2]'. '$array[$i][6]');;
}
if(!empty($insert_string)){
mysql_query($insert_string, $conn) or die(query failed :
.mysql_errror());
}
Its throwing error saying check the manual for right syntax.
After investigating in some sites i come to know that its problem of
limitations in query size.
I also tried with SET GLOBAL max_allowed_packet=3000;
Then also its throwing the same error.
Can anybody tell me how to fix this error and reduce the inserting
time with
a single statement instead of writing more insert statements
You are probably looking for something like this.
?php
if ( count($array) ) {
$insert_string = INSERT INTO tablename (v1, v2. v6) VALUES ;
$data = array();
foreach ( $array AS $row ){
$row_clean = array_map('mysql_real_escape_string', $row);
$data[] = ('{$row_clean[1]}',
'{$row_clean[2]}',.'{$row_clean[6]}');
}
$insert_string = join(', ', $data);
mysql_query($insert_string, $conn) or die(query failed :
.mysql_errror());
} else {
echo Nothing to insert;
}
?
That would work, but will probably result in a query string that is too
long.
I'll redo the above to fix that.
?php
# How often do you want to insert??
$break_at = 100;
# Initialize the counter
$cnt = 0;
# Initial insert string
$insert_string = INSERT INTO tablename (v1, v2. v6) VALUES ;
# if there is data, then process, otherwise skip it.
if ( count($array) ) {
$data = array();
# Loop through data
foreach ( $array AS $row ) {
$cnt++;
# Clean the result data
$row_clean = array_map('mysql_real_escape_string', $row);
# Build data string and push it onto the data array.
$data[] = ('{$row_clean[1]}',
'{$row_clean[2]}',.'{$row_clean[6]}');
# Break and insert if we are at the break point
if ( $cnt === $break_at ) {
# Reset Counter
$cnt = 0;
# Run insert
mysql_query($insert_string . join(', ', $data), $conn) or
die(query failed : .mysql_error());
# Reset data array
$data = array();
} //if
} //foreach
# This should take care of any extra that didn't get processed in the
foreach
if ( count($data) ) {
# Insert remaining data
mysql_query($insert_string . join(', ', $data), $conn) or
die(query failed : .mysql_error());
} //if
} else {
echo Nothing to insert;
} //if
?
--
Jim Lucas
Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them.
Twelfth Night, Act II, Scene V
by William Shakespeare
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
