Multiple inserts revisited

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



The subject would lead you to believe that this is a "check the archives" post.
Worry not, I already did.  Besides, this is more informative than anything.

I'm working on a catalogue (menu, really) admin page from which a client will
set their daily specials.  My design calls for the user to select the day's
special items (already in the DB) with checkboxes.  Submitting the form will set
the selected items as the day's specials by inserting the item_id and date into
another table.  (This is done so that previous specials may be recalled later.
I don't see a need for it but the client requested the feature).

Regardless whether the items are inserted or simply updated, I was stuck on how
best to perform the operation.  My initial thought was to create an array of the
selected items and loop through it to insert or update the various items.
However, I don't like running nearly a dozen queries to perform the operation,
something this would entail.

My other option, as I saw it, was to loop through the items, appending value
data to the query text with each iteration.  If that seems cryptic, here's a
basic idea of what I mean.

<?
$query = "INSERT INTO specials VALUES ("
foreach ( $specials as $item ) {
    $query = substr_replace( $query, $item.", ", strlen( $query ) );
}
$query = substr_replace( $query, ")", strlen( $query )-2 );
?>

If $specials was an array with the values spec1, spec2, and spec3, the final
value of $query would be:
    INSERT INTO specials VALUES (spec1, spec2, spec3)

This provides precisely what I need and reduces things to one query.  Let it be
noted that the actual script would be a bit more complex (associative arrays)
but the general structure is there.

I know that there have been a few multiple insert questions posted to the list
lately so perhaps this will prove helpful to those who asked.  If not, than
perhaps some of you can point out flaws in my design, should there be any.
Thanks for your time.

Edward Dudlik
Becoming Digital
www.becomingdigital.com






-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux