populate select list from multiple Mysql tables

Thread Solved

Join Date: Jun 2008
Posts: 62
Reputation: PomonaGrange is an unknown quantity at this point 
Solved Threads: 3
PomonaGrange PomonaGrange is offline Offline
Junior Poster in Training

populate select list from multiple Mysql tables

 
0
  #1
Dec 17th, 2008
hey folks, I have a question. I have several mysql tables and I'd like to fetch data from a few of them to create a <select> <option> list. How could this be done. I can get the info from one table, but don't know how to get the data from more than one table. Hope you can understand what I;m trying to do. Thanks.
There are alot of people smarter than me, BUT at least I try to be of some help. Of coarse I'm not perfect, I need help too.
Reply With Quote Quick reply to this message  
Join Date: Dec 2008
Posts: 94
Reputation: sikka_varun is an unknown quantity at this point 
Solved Threads: 11
sikka_varun's Avatar
sikka_varun sikka_varun is offline Offline
Junior Poster in Training

Re: populate select list from multiple Mysql tables

 
0
  #2
Dec 18th, 2008
Assuming that the two tables that you have are exactly the same in their structure.. i mean, both have same fields and same order of fields..
Then you can execute first query, then second query, and append the data of second query to the first one.. See the example below...


E.g.

  1. <?php
  2.  
  3. $sql1 = "Select * from table1";
  4. $result1 = mysql_query($sql1);
  5.  
  6. $sql2 = "Select * from table2";
  7. $result2 = mysql_query($sql2);
  8. ?>
  9. <select name="options_tab">
  10. <?php
  11. //First show all of the from table 1
  12. while($row1 = mysql_fetch_array($result1))
  13. {
  14. ?>
  15. <option value="<?php echo $row1[0]; ?>"><?php echo $row1[1];?></option>
  16. <?php
  17. }
  18. ?>
  19. <?php
  20. //Now show all of the from table 2
  21. while($row2 = mysql_fetch_array($result2))
  22. {
  23. ?>
  24. <option value="<?php echo $row2[0]; ?>"><?php echo $row2[1];?></option>
  25. <?php
  26. }
  27. ?>
  28. </select>
VâRûN
---Happy to Help---
sikka_varun@yahoo.com
Reply With Quote Quick reply to this message  
Join Date: Jun 2008
Posts: 62
Reputation: PomonaGrange is an unknown quantity at this point 
Solved Threads: 3
PomonaGrange PomonaGrange is offline Offline
Junior Poster in Training

Re: populate select list from multiple Mysql tables

 
0
  #3
Dec 18th, 2008
This isn't exactly what I was looking for. I should have been more specific.
Table1 holds information such as item information and a category id number.
Table2 holds the category name and a parent category number.
Table3 holds the parent category's name.

What I need is to select distinct category id numbers from Table1 and retrieve the name of those categories from Table2.

The select field would look something like this.

  1. <select name="category">
  2. <option value="25">cat 25 name</option>
  3. <option value="30">cat 30 name</option>
  4. <option value="35">cat 35 name</option>
  5. <option value="40">cat 40 name</option>
  6. <option value="45">cat 45 name</option>
  7. </select>

I may need something similar for the parent category, not sure right the moment though.
Last edited by peter_budo; Dec 18th, 2008 at 3:27 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.
There are alot of people smarter than me, BUT at least I try to be of some help. Of coarse I'm not perfect, I need help too.
Reply With Quote Quick reply to this message  
Join Date: Dec 2008
Posts: 94
Reputation: sikka_varun is an unknown quantity at this point 
Solved Threads: 11
sikka_varun's Avatar
sikka_varun sikka_varun is offline Offline
Junior Poster in Training

Re: populate select list from multiple Mysql tables

 
0
  #4
Dec 18th, 2008
Hi...
I read your post... This thing will be a problem to do because, you do not have anythng common in the Table1 and Table2. Im not sure how much you have idea about the Database Normalization thing, but this is actually not a correct way to implement the database thing.

I know you have the parent category id in table 2, but then the parent id might not be the category of the item... I hope you get me..

So for this, first correct your tables structure...
I'll suggest you one structure..

Table1 -Perfect
- Item
- CategoryID

Table2 - Needs change
- CategoryID
- Category Name
- Parent CategoryID

If you have the above structure, then you may not even need the parent category name table (Table 3), because you can get parent category name from the table itself..

Anyways, a big lecture i suppose... I'll come to code now...
So if you implement as per the database i told you... then the following code will work...

  1. <?php
  2.  
  3. $sql1 = "Select table2.categoryID,table2.categoryName from table1,table2 where table1.categoryID=table2.categoryID and table1.item_info=2";
  4. //2 is some value of the item coresponding to which the categories are to be chosen..
  5. $result1 = mysql_query($sql1);
  6. ?>
  7.  
  8. <select name="options_tab">
  9. <?php
  10. //First show all of the from table 1
  11. while($row1 = mysql_fetch_array($result1))
  12. {
  13. ?>
  14. <option value="<?php echo $row1[0]; ?>"><?php echo $row1[1];?></option>
  15. <?php
  16. }
  17. ?>
  18. </select>

If you are looking for some different result, then you can change the sql query but the basic idea remains the same...
VâRûN
---Happy to Help---
sikka_varun@yahoo.com
Reply With Quote Quick reply to this message  
Join Date: Jun 2008
Posts: 62
Reputation: PomonaGrange is an unknown quantity at this point 
Solved Threads: 3
PomonaGrange PomonaGrange is offline Offline
Junior Poster in Training

Re: populate select list from multiple Mysql tables

 
0
  #5
Dec 19th, 2008
I guess that gives me the idea anyway. I already have the tables similar to what you specified, but I even have grandparent category id stored as well. Thanks for the help.
There are alot of people smarter than me, BUT at least I try to be of some help. Of coarse I'm not perfect, I need help too.
Reply With Quote Quick reply to this message  
Reply

This thread has been marked solved.
Perhaps start a new thread instead?
Message:



Other Threads in the PHP Forum
Thread Tools Search this Thread



About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC