Select identical columns from different tables

[JStefan]

Hi All,

I got stuck in selecting some data from different tables into one query.

Here is what I would like to get.

I have tables: category subcategory sub2category sub3category and sub4category. I would like to output the values of the prodID and subname columns from all tables if any into a drop down menu. All tables have prodID and subname columns.

Here is my code:

 

<?php

if ($_POST['task'] == 60){

$sql = "SELECT t1.subname, t1.prodID, t2.subname, t2.prodID, t3.subname, t3.prodID, t4.subname, t4.prodID, t5.subname, t5.prodID FROM category AS t1, subcategory AS t2, sub2category AS t3, sub3category AS t4, sub4category AS t5";

$result = mysql_query($sql) or die(mysql_error());

if (mysql_num_rows($result) < 1) {

  $display = "<p>No data available</p>";

}

else {

  echo "<select name=\"prodID\">";

while ($items = mysql_fetch_array($result)) {

      $prodID = $items['id'];

      $catname = $items['subname'];

      //echo $catname;

      echo "<option value=\"$prodID\">$catname</option>";

    }

    echo "</select><br/><a href=\"cp.php\">Back</a> to Control Panel";

  }

  exit ();

 

}

 

?>

 

At the moment I get just the (only) value from the sub4category table into the dropdown repeated a couple of times. But if a table has no records at all the script crashes (with no mysql error message). I run mysql 5.1 and php 5.2.6 in Windows

 

Thank you for your help in advance,

Julian