Re: insert array values

PJ wrote:

Jim Lucas wrote:

PJ wrote:

9el wrote:

    
On Sat, Mar 7, 2009 at 5:37 AM, PJ <af.gourmet@videotron.ca

<mailto:af.gourmet@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: <?php # 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 comma $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: '.mysql_error($dblink); } } } 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