| | |
populate select list from multiple Mysql tables
Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
Thread Solved |
•
•
Join Date: Jun 2008
Posts: 62
Reputation:
Solved Threads: 3
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.
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.
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.
php Syntax (Toggle Plain Text)
<?php $sql1 = "Select * from table1"; $result1 = mysql_query($sql1); $sql2 = "Select * from table2"; $result2 = mysql_query($sql2); ?> <select name="options_tab"> <?php //First show all of the from table 1 while($row1 = mysql_fetch_array($result1)) { ?> <option value="<?php echo $row1[0]; ?>"><?php echo $row1[1];?></option> <?php } ?> <?php //Now show all of the from table 2 while($row2 = mysql_fetch_array($result2)) { ?> <option value="<?php echo $row2[0]; ?>"><?php echo $row2[1];?></option> <?php } ?> </select>
•
•
Join Date: Jun 2008
Posts: 62
Reputation:
Solved Threads: 3
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.
I may need something similar for the parent category, not sure right the moment though.
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.
html Syntax (Toggle Plain Text)
<select name="category"> <option value="25">cat 25 name</option> <option value="30">cat 30 name</option> <option value="35">cat 35 name</option> <option value="40">cat 40 name</option> <option value="45">cat 45 name</option> </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.
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...
If you are looking for some different result, then you can change the sql query but the basic idea remains the same...
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...
php Syntax (Toggle Plain Text)
<?php $sql1 = "Select table2.categoryID,table2.categoryName from table1,table2 where table1.categoryID=table2.categoryID and table1.item_info=2"; //2 is some value of the item coresponding to which the categories are to be chosen.. $result1 = mysql_query($sql1); ?> <select name="options_tab"> <?php //First show all of the from table 1 while($row1 = mysql_fetch_array($result1)) { ?> <option value="<?php echo $row1[0]; ?>"><?php echo $row1[1];?></option> <?php } ?> </select>
If you are looking for some different result, then you can change the sql query but the basic idea remains the same...
![]() |
Other Threads in the PHP Forum
- Previous Thread: Are there any "new this week/out this week" scripts
- Next Thread: create date??
| Thread Tools | Search this Thread |
ajax apache api array arrays beginner binary broken cache cakephp checkbox class cms code confirm cron curl customizableitems database date display dynamic echo email error external file files folder form forms forum function functions google header headmethod howtowriteathesis href htaccess html iframe image include insert integration ip java javascript joomla limit link login loop mail malfunction menu method mlm multiple mysql neutrality oop paypal pdf php phpmysql play problem query question radio random recursion regex remote root script search select server sessions sms soap source space sql syntax system table tutorial update upload url validator variable video web xml youtube





