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.

Recommended Answers

All 4 Replies

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

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

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.

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

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

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

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.