On Sun, 2009-03-08 at 17:52 -0500, PJ wrote: > 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 > > Basically, just get the list of books (the list of 10) and do something like this: while($row = mysql_fetch_array($result)) { $query2 = "SELECT * FROM `book_categories` WHERE `id`={$row['id']}"; $result2 = mysql_query($query2); while($row2 = mysql_fetch_array($result2)) { // do stuff with categories here } } Ash www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
