Well, I have everything more or less under control... just one little thingie... I was able to get everything working quite well... basically as I thought it should go with a little help from my friends, here on the list :-) many of my problems were small omissions and /or typos and misplaced brackets and ; The only thing (probably not) that stymies me is how to get the several categories printed out for each book . If there is only one category, everything is great... but when there are more than one category, the book listing repeats the listing of a book as many times as there are categories listed for it... fir instance, there are 10 books listed in my test table; i have the book_category table listing 1 book as having 1 category and another just 2 categories. When running the script with the category stuff commented out, I get 10 entries... with the categories on I get 11 entries with the one that has 2 categories repeated once. What should I post to analyze the problem?
Jim Lucas wrote: > 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: >>> >>> <?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. :-) >> >> >> >> > -- unheralded genius: "A clean desk is the sign of a dull mind. " ------------------------------------------------------------- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
