* JamesBenson <jb@xxxxxxxxxxxxxxxxx> :
> 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
