Combo Boxes

[kungfu71186]

Basically what im doing is trying to load information from multiple tables.
So say i have table1 with some stuff and table2 with some stuff. How do i go about adding table1 and table2 stuff into one combo box.

I found this function on the net
[code]function Combo_Box($cb_name,$table_name,$order_by="",$asc="",$css_class="",$id="") {
if ($table_name) {
if($id) {
$disable = " disabled ";
}
if ($order_by) {
$order_by = " ORDER BY ".$order_by." ".$asc;
}
$sql = "SELECT * FROM ".$table_name.$order_by;
$result = mysql_query($sql);
$show_Combo_Box = ""
."<SELECT name=\"".$cb_name."\" class=\"".$css_class."\" ".$disable."> \n"
."<OPTION value=\"0\"></OPTION>\n";
WHILE ($row = mysql_fetch_array($result)) {
$selection = "";
if($id){
if($row[0] == $id){
$selection = " selected ";
}
}
$show_Combo_Box .= ""
."<OPTION value=\"".$row[0]."\" ".$selection.">"
.$row[0]
."</OPTION> \n";
} // End WHILE
$show_Combo_Box .= ""
."</SELECT>\n";
mysql_free_result($result);
echo $show_Combo_Box;
} // End if ($table_name)[/code]

But it only accepts 1 table. How can i change to accept more than 1 table? I was thinking if its possible to just do something like.

$table_name = "table1 AND table2"
or making $table_name an array then how can i loop through it until the end of the array?
Im having a hard time trying to get that to work. Any suggestions on how to do this thanks.

[pixy]

I think you might have to use JOINS, but I honestly don't understand those for the LIFE of me. You could look them up in the mySQL manual for the syntax.

[akitchin]

are you just trying to make a select box that contains all the entries from one table, as well as all the entries from another table?

[kungfu71186]

I dont think join will work, join kind of combines the columns together unless there is a way to join rows.
Note: in my case im just taking row[0] as thats all i need is the first column of all entries.
EX:
Table1:
Name, Address, Date
1,ten,0
2,two,0
3,five,0
Table2:
Name, Address, Date
4,0,0
5,0,0
6,0,0

now in the select box it will have the following options

1
2
3
4
5
6
by combining the two tables together.

[akitchin]

something like the following:

[code]<?php
echo '<select name="option">';
$tables = array('table1', 'table2');
foreach ($tables AS $tablename)
{
  $query = "SELECT name FROM $tablename ORDER BY name ASC";
  $resource = mysql_query($query) or die("Error with query ($query): ".mysql_error());
  while (list($name) = mysql_fetch_array($resource, MYSQL_NUM))
  {
    echo "<option>$name</option>";
  }
}
echo '</select>';
?>[/code]

give that a whirl.

[redarrow]

Does it work i like that code tell us cheers.

[kungfu71186]

[quote author=redarrow link=topic=100629.msg397546#msg397546 date=1152938159]
Does it work i like that code tell us cheers.
[/quote]

had to get some sleep to try it out as i still couldnt get it :p

But finally got it, table_name was defined before thats your array.

[code]
<?php
echo "<SELECT name=\"itemtype\"> \n"
."<OPTION value=\"0\"></OPTION>\n";
foreach ($table_name1 AS $tablename)
{
$sql = "SELECT * FROM ".$tablename.$order_by;
$result = mysql_query($sql);
  WHILE (list($tablename) = mysql_fetch_array($result))
  {
    $show_Combo_Box .= ""
."<OPTION value=\"".$tablename."\" ".$selection.">"
.$tablename
."</OPTION> \n";
  }
}
$show_Combo_Box .= ""
."</SELECT>\n";
mysql_free_result($result);
echo $show_Combo_Box;
?>[/code]


[code]
$name = "tablecombine"; // Name of Combo Box
$table_name = array('table1','table2');         // Name of mysql table
$order_by = ""; // Ordering (optional)
$asc_desc = "asc"; // Ascending/Descending (optional)
$style = ""; // Css Style Sheets (optional)
$id_value = ""; // Post 'id' to be selected (optional)
[/code]


[code]
$ComboBox = new Combo_Box($name,$table_name,$order_by,$asc_desc,$style,$id_value);
[/code]

Here it is as a function in case someone wants to use this.

[code]
class Combo_Box {
function Combo_Box($cb_name,$table_name,$order_by="",$asc="",$css_class="",$id="") {
    if($id) {
$disable = " disabled ";
}
echo "<SELECT name=\"".$cb_name."\" class=\"".$css_class."\" ".$disable."> \n"
."<OPTION value=\"0\"></OPTION>\n";
foreach ($table_name AS $tablename)
{
      if ($tablename) {
if ($order_by) {
$order_by = " ORDER BY ".$order_by." ".$asc;
}
$sql = "SELECT * FROM ".$tablename.$order_by;
$result = mysql_query($sql);
WHILE (list($tablename)= mysql_fetch_array($result)) {
$selection = "";
if($id){
if($tablename == $id){
$selection = " selected ";
}
}
$show_Combo_Box .= ""
."<OPTION value=\"".$tablename."\" ".$selection.">"
.$tablename
."</OPTION> \n";
} // End WHILE
} // End if ($table_name)
} // foreach
$show_Combo_Box .= ""
."</SELECT>\n";
mysql_free_result($result);
echo $show_Combo_Box;
} // End function Combo_Box
} // End class Combo_Box
[/code]

I found this function from another site, just modified it to use an array as akit pointed me in the right direction.

credits: http://www.phpclasses.org/browse/package/1894.html

[kungfu71186]

ok need some help again. working fine to get multiple tables and what not. How can i go about getting multiple columns now. example.

first,second,third

table1
1,2,4
3,5,5
2,6,8

table2
7,3,7
3,7,8
3,6,3

you have your array tables = array('table1','table2')

then it goes through for the foreach and queries them and adds them to the select box.

so..
1
3
2
7
3
3

if you use
$sql = "SELECT * FROM ".$tablename.$order_by;
with the * it will grab the first column

you can use something like
$sql = "SELECT second FROM ".$tablename.$order_by;

and it will grab the second column like
2
5
6
3
7
6

Now how can i get two columns so it will be something like
1 (2)
3 (5)
2 (6)
7 (3)
3 (7)
3 (6)

where the inside the () is the second column i need to get, so in the select box it shows the first column then the second right next to it. So those would be your choices as shown above.

Hope thats understandable. Thanks.

[kungfu71186]

anyone?

[kungfu71186]

Ok i have seem to have got it and i worked it down to 1 query now. Is this better to do one query with a bunch of unions?
This is what i have now:

[code]
<?php
class Combo_Box {
function Combo_Box($cb_name,$table_name,$order_by="",$asc="",$css_class="",$id="") {
if (sizeof($table_name) > '0'){
$i='0';
foreach ($table_name AS $tablename)
{
if($i == "0")
{
  $sql .= "SELECT column1,column2 FROM ".$tablename;
}
else
{
$sql .= " UNION SELECT column1,column2 FROM ".$tablename;
}
$i++;

}
}
else
{
echo "Specify a database";
}

if ($table_name) {
if($id) {
$disable = " disabled ";
}
if ($order_by) {
$order_by = " ORDER BY ".$order_by." ".$asc;
}
$sql .= $order_by;

$result = mysql_query($sql) or die("Error with query (".$sql."): ".mysql_error());
$show_Combo_Box = ""
."<SELECT name=\"".$cb_name."\" class=\"".$css_class."\" ".$disable."> \n"
."<OPTION value=\"0\">Select</OPTION>\n";
WHILE ($row  = mysql_fetch_array($result)) {
$selection = "";
if($id){
if($row['indexi'] == $id){
$selection = " selected ";
}
}
$show_Combo_Box .= ""
."<OPTION value=\"".$row['column1']."\" ".$selection.">"
.$row['column1']." (".$row['column2'].") "
."</OPTION> \n";
} // End WHILE
$show_Combo_Box .= ""
."</SELECT>\n";
mysql_free_result($result);
echo $show_Combo_Box;
} // End if ($table_name)
} // End function Combo_Box
} // End class Combo_Box
?>
[/code]

Is this a good way to do it or is there a better way?

[akitchin]

as far as i can tell, you don't need a UNION at all.  you simply need to select the second value from your table:

[code]$query = "SELECT column1, column2 FROM table1";
blah blah mysql_query stuff
while ($row = mysql_stuff())
{
  echo "<option>{$row['column1']} ({$row['column2']})</option>";
}[/code]

or am i missing something?  you can easily integrate that into your first code - select both columns, and chance the <option> echoed.

[kungfu71186]

its not from table1, its from table1 and table2 whatever is in the table_name array. I have to add more than two tables together and then sort them.