Query Two MySQL Tables

[Darwyn]

I have been working on this ALL day and cannot seem to find a good example to work from...I need to create a search function to query the columns from two separate tables: Products (modelNo, description, features) and Sizes (PartNo).Products contains the columns: modelNo, description and featuresSizes contains the columns: modelNo and PartNoSo someone should be able to search by PartNo, modelNo or any keyword in description and features.The following code works, but is not formatted correctly. (Note: I do not need to display the PartNo, only search for it and link it to the modelNo from the other table)...

<?php  if(!empty($_POST['submit'])){  if(isset($_GET['go'])){  if(preg_match("/^[  a-zA-Z0-9\.\-]+/", $_POST['product'])){  $product=$_POST['product'];  //connect  to the database  $db=mysql_connect  ("localhost", "db_name",  "password") or die ('I cannot connect to the database  because: ' . mysql_error());  //-select  the database to use  $mydb=mysql_select_db("db_name");  //-query  the database table  $sql="SELECT Products.modelNo FROM Products WHERE modelNo LIKE '%" . $product .  "%'		  UNION		SELECT Products.description FROM Products WHERE description LIKE '%" . $product .  "%'		UNION		SELECT Products.features FROM Products WHERE features LIKE '%" . $product .  "%'		UNION		SELECT Sizes.PartNo FROM Sizes WHERE PartNo LIKE '%" . $product .  "%'		";    //-run  the query against the mysql query function  $result=mysql_query($sql);  //-create  while loop and loop through result set  while($row=mysql_fetch_array($result)){		  $modelNo=$row['modelNo'];		  $description=$row['description'];		  $features=$row['features'];		  $PartNo=$row['PartNo'];		  		  			//-display the result of the array  echo "<ul>\n";  echo "<li>" . "<a  href=\"process.php?modelNo=$modelNo\">"   .$modelNo . "</a>\n\r " . $description .  "\n\r " . $features .  "</li>\n";  echo "</ul>";  }  }  else{  echo  "<p>Please enter a search query</p>";  }  }  }?>

Can anyone help?

[justsomeguy]

I think you're looking for something like this:

SELECT * FROM products WHERE   modelNo LIKE '%xxx%' OR  description LIKE '%xxx%' OR   features LIKE '%xxx%'UNION DISTINCTSELECT * FROM products WHERE modelNo IN (  SELECT modelNo FROM Sizes WHERE PartNo LIKE '%xxx%')

[ShadowMage]

I was thinking about an INNER JOIN

SELECT * FROM products INNER JOIN Sizes ON (products.modelNo = Sizes.modelNo) WHERE   products.modelNo LIKE '%xxx%' OR   products.description LIKE '%xxx%' OR   products.features LIKE '%xxx%' OR   Sizes.PartNo LIKE '%xxx%'

[justsomeguy]

It depends on the structure, that's only going to find records that have an entry in both tables. I'm not sure if that's the case or not.

[Darwyn]

I think you're looking for something like this:

SELECT * FROM products WHERE   modelNo LIKE '%xxx%' OR  description LIKE '%xxx%' OR   features LIKE '%xxx%'UNION DISTINCTSELECT * FROM products WHERE modelNo IN (  SELECT modelNo FROM Sizes WHERE PartNo LIKE '%xxx%')

Beautiful! It worked perfectly! THANK YOU, THANK YOU!Thank you too, ShadowMage.I am a happy camper!