[PHP] db insert question
Im using mysql with PHP4, whats the best way to insert as many as 20 records at one time from a form? Currently im just assigning each one a variable but that is messy and takes ages, is their a way to loop over the array of form data then maybe do the same to enter it into a database? Thanks for any help. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] db insert question
Im using mysql with PHP4, whats the best way to insert as many as 20
records at one time from a form?
Currently im just assigning each one a variable but that is messy and
takes ages, is their a way to loop over the array of form data then
maybe do the same to enter it into a database?
Thanks for any help.
A generic question begs a generic answer:
foreach($formdata as $thisdata){
$result=mysql_query(insert into ... values($thisdata) where ...);
}
Hope this helps, but it would be useful to have code examples, etc so that a
more relevant answer could be rendered.
JM
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] db insert question
Thanks for your reply, your example demonstrates what i was doing but
does that do a MySQL query for every piece of data?
So 20 form values would be 20 db queries, would that not consume a lot
of resources?
Is their another way?
Thanks,
James
Jim Moseby wrote:
Im using mysql with PHP4, whats the best way to insert as many as 20
records at one time from a form?
Currently im just assigning each one a variable but that is messy and
takes ages, is their a way to loop over the array of form data then
maybe do the same to enter it into a database?
Thanks for any help.
A generic question begs a generic answer:
foreach($formdata as $thisdata){
$result=mysql_query(insert into ... values($thisdata) where ...);
}
Hope this helps, but it would be useful to have code examples, etc so that a
more relevant answer could be rendered.
JM
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] db insert question
Jim Moseby wrote:
Im using mysql with PHP4, whats the best way to insert as
many as 20
records at one time from a form?
Currently im just assigning each one a variable but that is
messy and
takes ages, is their a way to loop over the array of form data then
maybe do the same to enter it into a database?
Thanks for any help.
A generic question begs a generic answer:
foreach($formdata as $thisdata){
$result=mysql_query(insert into ... values($thisdata)
where ...);
}
Hope this helps, but it would be useful to have code
examples, etc so that a
more relevant answer could be rendered.
JM
Thanks for your reply, your example demonstrates what i was doing but
does that do a MySQL query for every piece of data?
So 20 form values would be 20 db queries, would that not
consume a lot
of resources?
Is their another way?
As far as I know, there is no way to insert 20 unique rows of data into a
MySQL table without executing 20 queries. Maybe someone else here does(?).
Perhaps drop a note over on the MySQL list, since this is really more an SQL
question than PHP. Sorry I couldn't help.
JM
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] db insert question
On Thursday 21 July 2005 15:02, Jim Moseby typed: As far as I know, there is no way to insert 20 unique rows of data into a MySQL table without executing 20 queries. Maybe someone else here does(?). Perhaps drop a note over on the MySQL list, since this is really more an SQL question than PHP. Sorry I couldn't help. Yes, MySQL supports an extended insert syntax of insert into foo (...) values (...), (...), (...). Building that kind of query is left as an excercise for the user. I believe the PEAR DB abstraction layer can accept an array to generate that kind of query. -- My mind not only wanders, it sometimes leaves completely. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] db insert question
Hi
You may try sending multiple query with one mysql_query() function call
via seperating by ;.
Regards.
-Cafer
Prş, 2005-07-21 tarihinde 10:02 -0400 saatinde, Jim Moseby yazdı:
Jim Moseby wrote:
Im using mysql with PHP4, whats the best way to insert as
many as 20
records at one time from a form?
Currently im just assigning each one a variable but that is
messy and
takes ages, is their a way to loop over the array of form data then
maybe do the same to enter it into a database?
Thanks for any help.
A generic question begs a generic answer:
foreach($formdata as $thisdata){
$result=mysql_query(insert into ... values($thisdata)
where ...);
}
Hope this helps, but it would be useful to have code
examples, etc so that a
more relevant answer could be rendered.
JM
Thanks for your reply, your example demonstrates what i was doing but
does that do a MySQL query for every piece of data?
So 20 form values would be 20 db queries, would that not
consume a lot
of resources?
Is their another way?
As far as I know, there is no way to insert 20 unique rows of data into a
MySQL table without executing 20 queries. Maybe someone else here does(?).
Perhaps drop a note over on the MySQL list, since this is really more an SQL
question than PHP. Sorry I couldn't help.
JM
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] db insert question
On Thursday 21 July 2005 15:02, Jim Moseby typed:
As far as I know, there is no way to insert 20 unique rows
of data into a
MySQL table without executing 20 queries. Maybe someone
else here does(?).
Perhaps drop a note over on the MySQL list, since this is
really more an
SQL question than PHP. Sorry I couldn't help.
Yes, MySQL supports an extended insert syntax of insert into
foo (...) values
(...), (...), (...).
Interesting! Consulting the manual, I see that you are correct. So the OP
would do something like:
$sql=insert into foo values ;
foreach($formdata as $thisdata){
$sql.=($thisdata,...,...,...),;
}
$result=mysql_query($sql);
(of course the above code is broken, because it would leave an extra comma
at the end, but this is the general idea, no?)
JM == learns something new from this list every day!
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] db insert question
* JamesBenson [EMAIL PROTECTED] :
Thanks for your reply, your example demonstrates what i was doing but
does that do a MySQL query for every piece of data?
So 20 form values would be 20 db queries, would that not consume a lot
of resources?
Not necessarily. If you use a prepared statement first, then execute it,
you'll get excellent performance. If you're interested in prepared
statements, or don't know what they are, look at DB abstraction layers
like PEAR::DB, PEAR:MDB(2), and ADODB -- they utilize them. As an
example:
$sql = INSERT INTO someTable(field1, field2, ...) VALUES (?, ?, ...);
$insert = $db-prepare($sql);
foreach ($data as $vals) {
$db-execute($insert, $vals); // Assuming each element of $data
// is an array of values
}
Basically, you prepare a SQL statement that contains placeholders for
the values. Then when executing the statement, you pass an array of
values equal to the number of placeholders in the statement. The
benefits are (a) speed, and (b) security. Regarding (b), the values are
quoted in a proper format for your RDBMS, so you don't need to worry
about escaping each and every value -- the DB abstraction layer and/or
the RDBMS takes care of it for you.
Is their another way?
Yes -- Build your SQL first, in the following format:
INSERT INTO someTable(field1, field2, ...) VALUES
('rec1Field1', 'rec1Field2', ...),
('rec2Field1', 'rec2Field2', ...),
('rec3Field1', 'rec3Field2', ...);
And some PHP code that could help create this:
// Beginning of SQL
$sql = INSERT INTO someTable(field1, field2, ...) VALUES ;
// Loop through records to insert
$INS = array();
foreach ($data as $values) {
$final = array();
// Loop through each value in the record and quote it
foreach ($values as $val) {
$final[] = sprintf('%s', mysql_real_escape_string($val));
}
// Join them with commas and enclose all in ()
$INS[] = '(' . implode(',', $final) . ')';
}
// Finally, join all the records with commas, and end the SQL with a
// semicolon
$sql .= implode(',', $INS) . ';';
$result = mysql_query($sql);
HTH...
Jim Moseby wrote:
Im using mysql with PHP4, whats the best way to insert as many as 20
records at one time from a form?
Currently im just assigning each one a variable but that is messy and
takes ages, is their a way to loop over the array of form data then
maybe do the same to enter it into a database?
Thanks for any help.
A generic question begs a generic answer:
foreach($formdata as $thisdata){
$result=mysql_query(insert into ... values($thisdata) where ...);
}
Hope this helps, but it would be useful to have code examples, etc so that a
more relevant answer could be rendered.
--
Matthew Weier O'Phinney
Zend Certified Engineer
http://weierophinney.net/matthew/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] db insert question
On Thursday 21 July 2005 15:23, Jim Moseby typed:
Yes, MySQL supports an extended insert syntax of insert into
foo (...) values
(...), (...), (...).
Interesting! Consulting the manual, I see that you are correct. So the OP
would do something like:
$sql=insert into foo values ;
foreach($formdata as $thisdata){
$sql.=($thisdata,...,...,...),;
}
$result=mysql_query($sql);
(of course the above code is broken, because it would leave an extra comma
at the end, but this is the general idea, no?)
Vaguely, yes. The trick here is that any single failure in one of the columns
can leave you with inconsistent data. Thus, use this with InnoDB table and
transactions enabled so you can rollback on failure :)
For sake of correctness, the data should be checked for validity first. Then,
assuming that all of the form data is going into the same table as the same
fields, you could push ($variable1, $variable2) into an array, and
join(',', $that_array)
Presto, no extra comma. Should use the mysql escape stuff too if not using an
abstraction layer that deals with it for you.
--
My mind not only wanders, it sometimes leaves completely.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] db insert question
Thanks for all your help, ive got it working as i needed, my db query is
built like this, works but probably not the best way of doing it:-
$sqlStart = INSERT INTO `tester` (;
$sqlMiddle = ) VALUES (;
$sqlEnd = );
$keys = array();
$values = array();
foreach($formData as $keyName = $keyValue){
array_push($keys, `$keyName`);
array_push($values, '$keyValue');
}
$keys = implode(,, $keys);
$values = implode(,, $values);
$query = $sqlStart$keys$sqlMiddle$values$sqlEnd;
$result = mysql_query($query);
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] db insert question
On Thu, July 21, 2005 6:52 am, JamesBenson said: Thanks for your reply, your example demonstrates what i was doing but does that do a MySQL query for every piece of data? So 20 form values would be 20 db queries, would that not consume a lot of resources? Is their another way? Test it on your server. 20 INSERTS is pretty much chump change, really... I mean, sure, if you're server is getting SLAMMED and you are executing this page a thousand times a day or something. Otherwise, your are really NOT looking at a lot of overhead for those 20 queries. Time It. I doubt that the prepared statements give much performance boost for only 20 queries, but, again, time it on YOUR computer. -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
