List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:February 23 2009 3:27pm
Subject:RE: book categories
View as plain text  

>-----Original Message-----
>From: PJ [mailto:af.gourmet@stripped]
>Sent: Sunday, February 22, 2009 2:39 PM
>To: MySql
>Subject: book categories
>Here's a tough one...
>In my library I have some 62 categories where a number of books can be
>in several categories.
>Now, to relate the "categories" table(AS c) to the "book" table (AS a),
>I assume that I need an intermediate table "book_category" (AS d)
>linking d.bookID to the b.bookID and d.categoryID to With so many
>categories and (probably) thousands of books d may become quite lengthy
>(but, I guess it's better than 62 * 100's of lengthy fields added to b).
>To enter the relationships I would add instructions on my
>addNewBooks.php form with the input as multiple choice dropdown box
>(listing the categories)...
>so far, so good (I hope)...
>Now, how do I SELECT and retrieve these categories to display on the web
>From the book_category table with a WHERE statement? If so, what then?
>CONCAT_WS the stuff to go into the html table <td>?
>I hope I'm on the right track... or am I in deep water?
[JS] We do this all of the time, and it isn't that difficult. You don't want
to use any form of CONCAT. What you want to do is SELECT all of your
categories in one fell swoop, and then loop through them to create your
dropdown. Here's a simple example, directly from our code. This would be
inside a form. (I apologize for the line-wrapping.)


      <select name="account_id" style="width: 50px;" tabindex="23">
      First, we enter a null value as the first option available in the

 			<option value=""></option>

	//	Now we pull all of the accounts from the account table

	$query = "SELECT * FROM account ORDER BY account_name;";

	//	Execute the query or die trying

	$result = mysql_query($query) or die ("Unable to execute
$query:<br/>" . mysql_error());

	//	Loop as long as we have records

	while($row_tmp = mysql_fetch_array($result)) {
  	The value of the account_id option is set to the account_id, but the
displayed information
  	is the corresponding real_name

 		<option value="<?= $row_tmp["account_id"] ?>"
 				<?= $new ? "" : (($row_tmp["account_id"] ==
			? " selected=\"selected\"" : "") ?>><?=
$row_tmp["real_name"] ?>
	} // end while


All it really does is loop through the results of the SELECT statement, and
spit out an <option...> for each record.

>Phil Jourdan --- pj@stripped
>MySQL General Mailing List
>For list archives:
>To unsubscribe:

book categoriesPJ22 Feb
  • Re: book categoriesClaudio Nanni22 Feb
    • Re: book categoriesPJ23 Feb
  • RE: book categoriesJerry Schwartz23 Feb
    • Record IDsHagen Finley23 Feb
      • Re: Record IDsMattia Merzi23 Feb
      • Re: Record IDsBrent Baisley23 Feb
      • RE: Record IDsJerry Schwartz23 Feb