Ashley Sheridan wrote: > 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 > I did try something like this but I had the same mistake in the second query as I had in the first. I think this puts me in a clearer perspective. I'll get to it tomorrow morning. It's only 10:30pm here... I'm surprised that there are other crazies like me out there (24/7 birds). Really appreciate your insights. :-) -- 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
