mysql search tables command

Hi

I have a few tables in a database. the only thing that they have in common is a column ,called part_number.

What I wish to do is search every table in turn to see which table has that ONE part number and retrieve the column heading and row data for that particular table.
Please bear in mind that all tables have different headings, (apart from part number).

I can search one table successfully and at last resort I can write the entire search code for each table, but I am sure there is a better way.

I have searched and read alot about join and subselect but can not find anything that serves my purpose.

This is part my my code

$result = mysql_query("   SELECT * FROM ipbushes 		 WHERE part_number = '$partnumber'     ") or die(mysql_error());  	
	$row = mysql_fetch_assoc($result);

Code (markup):

MAny thanks for your looking at my problem.

 

what is the structure of other tables?
I think you have problem in joining query. Visit following page for more help.
http://www.plus2net.com/sql_tutorial/sql_left_join.php

 

Yes left join would work fine, or you could just run another query inside that loop to check for the other condition.

 

Hi
Thanks for your replies.

I think I have to use a loop, since these tables have nothing in common apart from the first field called part_number.
They all have different filed names with different number of fields.
When a match is found (by the way there will only be one match) then I display the heading for that field and row that corresponds to that part number.

Thanks

 

No prob glad i helped.

 

Hi
I have got a small problem when putting it through a loop.

Without loop this code works fine, it finds the part number and displays it, but when I add a foreach loop I get error messages

Warning: Invalid argument supplied for foreach() in /var/www/vhosts/xxxl.com/httpdocs/pns.php on line 214

Warning: Invalid argument supplied for foreach() in /var/www/vhosts/xxxl.com/httpdocs/pns.php on line 231

Code (markup):

Code that works is

			$result = mysql_query("   SELECT * FROM $value		 WHERE part_number = '$partnumber'     ") or die(mysql_error());  	
			
			
	$row = mysql_fetch_assoc($result);
	
	
	echo ("<table   align='center'>");
		echo ("<tr>");
		 echo ("<td>"); 
	
	echo ("<img  src=\"http://www.xxxx.com/images/products/item1/$rowpdf[pdf].jpg\"> ");
	echo ("<img  src=\"http://www.xxxx.com/images/discounts/$rowpdf[pdf].gif\"> ");
	echo("</td>");
	echo("</tr>");
	echo("</table>");
	
	
	echo("<BR>");
		
		echo ("<table  border='1' align='center' width='620'>");
		
	
		
				echo ("<tr>");
foreach($row as $k=>$v)
{
   echo("<td>$k</td>");
	  
 }
 
 echo ("<td>"); 
 echo "pdf";
 
 
 echo("</tr>");

 echo ("<tr>");




foreach($row as $k=>$v)
{
    echo("<td>$v</td>");
	  
 }
    echo ("<td>"); 
	
	echo "<a target=\"_blank\" href=\"http://www.xxx.com/pdf/item1/
			$rowpdf[pdf].pdf\"><img  src=\"http://www.xxxl.com/images/pdflogo.gif\"> </a>";
	
			//echo (" <img src=\"http://www.xxx.com/images/pdflogo.gif\">");
	 echo ("</td>");
 echo("</tr>");
			  
		
		
	?>
	</table>

HTML:

but when I introduce a foreach loop it fails.

$rowpdf = mysql_fetch_assoc($pdf);
	
		  
		$arr=array("ipbushes", "mpbushes" , "ifbushes", "mfbushes" ,"msolidbars");
		foreach($arr as  $value)
{

		
			$result = mysql_query("   SELECT * FROM $value		 WHERE part_number = '$partnumber'     ") or die(mysql_error());  	
			
			}
	$row = mysql_fetch_assoc($result);
	
	
	echo ("<table   align='center'>");
		echo ("<tr>");
		 echo ("<td>"); 
	
	echo ("<img  src=\"http://www.xxx.com/images/products/item1/$rowpdf[pdf].jpg\"> ");
	echo ("<img  src=\"http://www.xxxl.com/images/discounts/$rowpdf[pdf].gif\"> ");
	echo("</td>");
	echo("</tr>");
	echo("</table>");
	
	
	echo("<BR>");
		
		echo ("<table  border='1' align='center' width='620'>");
		
	
		
				echo ("<tr>");
foreach($row as $k=>$v)
{
   echo("<td>$k</td>");
	  
 }
 
 echo ("<td>"); 
 echo "pdf";
 
 
 echo("</tr>");

 echo ("<tr>");




foreach($row as $k=>$v)
{
    echo("<td>$v</td>");
	  
 }
    echo ("<td>"); 
	
	echo "<a target=\"_blank\" href=\"http://www.xxxxl.com/pdf/item1/
			$rowpdf[pdf].pdf\"><img  src=\"http://www.xxxx.com/images/pdflogo.gif\"> </a>";
	
			//echo (" <img src=\"http://www.xxxxl.com/images/pdflogo.gif\">");
	 echo ("</td>");
 echo("</tr>");
			  
		
		
	?>
	</table>
	

HTML:

 

Hi

I think I know where the problem lies.

There are 5 tables in the database ( at the moment) and it searches all the tables one by one to find the part number. What happens is that if it finds the part number at the first table, it still carries on searching the remaining tables.

What I really need is a way to stop the search at the first table find and skip the rest.

the way it is operating now is the the last table search happens to be unsuccessful and the search result is empty, hence the error message.

Please help.
thanks

 

solved it.

	if (@mysql_num_rows($result)) {
			break;
			}
	}

HTML: