Ashley Sheridan wrote: > On Sun, 2009-03-08 at 16:52 -0500, PJ wrote: > >> 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.gourmet@xxxxxxxxxxxx >>>>>>> <mailto:af.gourmet@xxxxxxxxxxxx>> 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 --- pj@xxxxxxxxxxxxx >> http://www.ptahhotep.com >> http://www.chiccantine.com/andypantry.php >> >> > If you want to display all the categories for each book, then maybe the > best way is to first retrieve the list of books sans category join (with > any WHERE clauses to limit the result-set how you wish) and then as > you're in the loop outputting the books, perform a quick query to > retrieve the categories for the current book. > You lose me here as I am rather primitive in my understanding of most of this. What do you mean to "retrieve the list of books sans category join? That's what I can do with no problem. Do you mean to save the results in some way to another table or view (or whatever) and then retrieve the categories separately to another table and then join that? At present, I'm echoing the list of books to an html formatted page (via php). As I looooove things complicated, I thought of adding the categories in the output but if it becomes too too much then I can just forget the categories and deal with them on category pages. I thought if might be possible to output the array (at the point just before echoing) imploded as one string or doing a subquery at thisi point; but using a subquery doesn't look too promising. > The other way I'd do it is to retrieve the full results set (the 11 > results one) and put all of that into an multi-dimensional array, such > as: > This seems a little complicated for my peanut brain, but I may have to try it. :-\ > $books = Array(); > while($row = mysql_fetch_array($result)) > { > if(!isset($books[$row['id']])) > { > $books[$row['id']] = Array(); > $books[$row['id']]['name'] = $row['name']; > // add more book info here > > // categories as an array > $books[$row['id']]['categories'] = Array(); > } > > // book categories added here in second level of array > $books[$row['id']['categories'][] = $row['book_cat']; > } > > the $row['id'] here is just the unique id of the row for the books > table. > > > Ash > www.ashleysheridan.co.uk > > > -- unheralded genius: "A clean desk is the sign of a dull mind. " ------------------------------------------------------------- Phil Jourdan --- pj@xxxxxxxxxxxxx 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
