PJ wrote:
Jim Lucas wrote:
PJ wrote:
9el wrote:
On Sat, Mar 7, 2009 at 5:37 AM, PJ <af.gour...@videotron.ca
<mailto:af.gour...@videotron.ca>> wrote:

    I've done some rethingking and this may be the direction to go:

    What I need to wind up with is something like this:

    $sql = "INSERT INTO book_categories ( book_id, category )
       VALUES( '$autoID', '$categoriesID[0]' ),
       ( '$autoID', '$categoriesID[1]' ),
       ( '$autoID', '$categoriesID[2]' ),
       ( '$autoID', '$categoriesID[3]' )
       ( '$autoID', '$categoriesID[4]' )";

    Does it make sense?
    How do I pass the values to the query?

You can run a php loop inside the insert  clause.
But if its already existing record then it woud be UPDATE clause.

Could I have a small hint as to how to do that. I'll check the manual
and search G in the meantime.  Right now, it's Friday night and I'm
about to get whammied by the wife... ;-)      have a good night & let's
hope for a brighter tomorrow, tomorrow, tomorrowwww

To do something like what you are describing above, you would do the following:


# Setup your DB connection etc...

# Build insert statement for your book
$sql = "INSERT INTO books (title, author, etc...) VALUES ('To kill a mocking bird', 
'Harper Lee', etc...)";

# Execute insert statement
if ( ( $results = mysql_query($sql, $dblink) ) !== false ) {

        # Grab last insert ID for my thread
        $last_id = mysql_insert_id($dblink);

        # Check to see if any categories were choosen
        if ( $categoriesIN ) {
                $values = array();

                # Loop through each category and build VALUES entry...
                foreach ( $categoriesIN as $k => $id ) {

                        # Build VALUES entry, plus run $id through escape 
function for sanity
                        $values[] = "( {$last_id}, ".mysql_real_escape_string($id)." 

                # Join the VALUES entries just created and separate them with a 
                $sql = "INSERT INTO book_categories ( book_id, category ) VALUES 
" . join(', ', $values);

At this point, if you echo'ed this.  You would get this

INSERT INTO book_categories ( book_id, category ) VALUES ( 1, 3 ), ( 1, 5 ), ( 
1, 7 ), etc...

This basically takes all your inserts that would be done individually and groups them into one insert call. This helps because it causes the table not to re-index a zillion times.

You will still end up with unique row entries for each category and autoID group. They will not be grouped together in one row as you suggest below.

                # Execute book 2 categories SQL entry
                if ( ( $results = mysql_query($sql, $dblink) ) === false ) {

                        # If it fails, show me why
                        echo 'Book to category entry failed: 
} else {
        # Show why my book SQL entry failed
        echo "Book insert failed: ".mysql_error($dblink);


That is about as best as can be explained.

BTW - I took away the quotes around your values in your second insert 
statement.  They are numbers and numbers should not have quotes around them.

Hopefully this works, or guides you in the direction of the answer you seek.

Interesting... but, if I understand right (and I am not wet behind the
ears but a newbie nevertheless), this would just enter a series of comma
separated values in one field (the category field; the book_id is taken
care of by an earlier insert followed by
$autoId = mysql_insert_id($result);
which is then inserted in to the book_id)

To do what you are suggesting could probably be done in a simpler way by
using the implode function... but what I am really trying to do should
give a result like the one in my original post.
Problem is: the insert is not always going to be the same length; that
is there may be 2 categories selected or 3 or 4 so the insert statement
has to be created on the fly. So, far no one has figured out a way to do
it. :-(

An alternative, and I'm going to try that now is to do a switch or case
or whatever it is and set up 3 or 4 inserts with 1 to 4 VALUES and then
populate them from the array.

Or another way may be even simpler: implode the array and save to one
field and when querying explode it. :-)

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

Reply via email to