Looping and comparing data from two tables

[dreamer]

I tried posting in the database-forum earlier, but now I that this might be a better place 🙂

Below you will see my code, but as it is today it only checks if $data2 is
set, and if it is it's displayed as a link.
But I have another table containing info on some of the companies, and I
want the script to check if the orgnum-field exists in both tables and only
then display it as a link, otherwise the companyname will be plain text.
Can anyone give me a hint ore two on how to get this done?

<?PHP
$columns = 2;
$db = mysql_connect("localhost", "user", "pass");
mysql_select_db("database",$db);

$query = "SELECT company, orgnum FROM table1 ORDER BY company";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
$rows = ceil($num_rows / $columns);

while($row = mysql_fetch_array($result)) {
    $data[] = $row['company'];
    $data2[] = $row['orgnum'];
}

echo "<table>\n";

for($i = 0; $i < $rows; $i++) {

echo "<td>\n";

for($j = 0; $j < $columns; $j++) {
   if(isset($data[$i + ($j * $rows)])) {
           if(empty($data2[$i + ($j * $rows)])) {
           echo "<td>" . ucwords(strtolower($data[$i + ($j * $rows)])) .
"</td>\n";
       }else{
               echo "<td><a href=\"companyinfo.php?company=" . $data2[$i +
($j * $rows)] . "\">" . ucwords(strtolower($data[$i + ($j * $rows)])) .
"</a></td>\n";
       }
               echo "<td width=\"5\">" . "</td>\n";
    }
 }
    echo "</tr>\n";
}
echo "</table>\n";
?>

-Thanks-
Jørn

[Weedpacket]

The query would be SELECT DISTINCT orgnum FROM table2 (I'm guessing the DISTINCT is unnecessary, but you know your db better than I do 🙂) Fetch all those orgnums into an array, $companies_with_links, say.

The short answer from here is to use [man]in_array[/man]. If you want to know whether to give a company a link or not, see if its orgnum is in the $companies_with_links array or not.

[dreamer]

Well, that sounds easy enough, but it leeds to another problem.
Maby this is basic stuff, but I mostly just learn as I go and I've never extracted from two querys befor. How do I do this?
Do I have to do somthing like this?
$query = "SELECT blablabla";
$query2 = "SELECT blablabla";

$result = mysql_query($query);
$result2 = mysql_query($query2);

And so on...

[jayant]

Originally posted by dreamer
Well, that sounds easy enough, but it leeds to another problem.
Maby this is basic stuff, but I mostly just learn as I go and I've never extracted from two querys befor. How do I do this?
Do I have to do somthing like this?
$query = "SELECT blablabla";
$query2 = "SELECT blablabla";

$result = mysql_query($query);
$result2 = mysql_query($query2);

And so on...

ya this is fine

[dreamer]

Ok, but how do I include $result2 into the loop?

[jayant]

Using:-
while($row = mysql_fetch_array($result) && $row2 = mysql_fetch_array($result2))

OR

while($row = mysql_fetch_array($result) || $row2 = mysql_fetch_array($result2))

Personally i will prefer the above one.

[Weedpacket]

Extracting from two queries is really not much different from extracting from one; you just do it twice.

I'd actually run two separate loops, myself; one to get the orgnums in table2 (putting them into an array), and one to get companies and orgnums from table1.

$companies_with_links=array();
$query = 'select distinct orgnum from table2';
$results = mysql_query($query);
while($row = mysql_fetch_array($results)
  $companies_with_links[]=$row['orgnum'];

Then do your query on table1 and loop through the results of that, pretty much as you're already doing. Whenever you want to see if a company should have a link or not, use in_array() to see if its orgnum is in $companies_with_links.

Eg.

$columns = 2;

$query = "SELECT company, orgnum FROM table1 ORDER BY company";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
$rows = ceil($num_rows / $columns);

for($i=0; $i<$num_rows; $i++)
{	$data[$i] = mysql_fetch_array($result);
}

echo "<table>\n";

for($i = 0; $i < $rows; $i++)
{
	echo "<td>\n";
	for($j = 0; $j < $columns; $j++)
	{
		$index = $i+$j*$rows;
		if($index>=$num_rows)
			break;
		$company = $data[$index]['company'];
		$orgnum = $data[$index]['orgnum'];
		if(!in_array($orgnum, $companies_with_links))
		{
			echo "<td>" . ucwords(strtolower($company)) . "</td>\n";
		}else
		{
			echo "<td><a href=\"companyinfo.php?company=" . $orgnum . "\">" .
				ucwords(strtolower($company)) . "</a></td>\n";
		}
		echo "<td width=\"5\">" . "</td>\n";
	}
	echo "</tr>\n";
}
echo "</table>\n";

which includes a couple of other simplifications.

[dreamer]

Thanks guys, I've learned a few new things from you 🙂
But I went for a sollution with a LEFT JOIN-query.