Re: insert array values

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); > > # 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. :-) -- unheralded genius: "A clean desk is the sign of a dull mind. " ------------------------------------------------------------- Phil Jourdan --- pj@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php