Ad:

PHP Discussion Thread
Marked Solved
Views: 2906

Dec 17th, 2008

populate select list from multiple Mysql tables

Expand Post »

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.

PomonaGrange

Reputation Points: 10

Solved Threads: 3

Junior Poster in Training

Offline

67 posts
since Jun 2008

Permalink

Dec 18th, 2008

Re: populate select list from multiple Mysql tables

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.

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>
<?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>

sikka_varun

Reputation Points: 11

Solved Threads: 12

Junior Poster in Training

Offline

94 posts
since Dec 2008

Permalink

Dec 18th, 2008

Re: populate select list from multiple Mysql tables

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.

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>
<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.

PomonaGrange

Reputation Points: 10

Solved Threads: 3

Junior Poster in Training

Offline

67 posts
since Jun 2008

Permalink

Dec 18th, 2008

Re: populate select list from multiple Mysql tables

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...

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>
<?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...

sikka_varun

Reputation Points: 11

Solved Threads: 12

Junior Poster in Training

Offline

94 posts
since Dec 2008

Permalink

Dec 19th, 2008

Re: populate select list from multiple Mysql tables

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.

PomonaGrange

Reputation Points: 10

Solved Threads: 3

Junior Poster in Training

Offline

67 posts
since Jun 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: Are there any "new this week/out this week" scripts

Next Thread in PHP Forum Timeline: create date??

DaniWeb Message

Cancel Changes

User Name
Password