Loop problem when trying to Query multiple MySQL Tables

[suttercain]

Hi everyone,

 

I will try and explain this as best I can. I have a database with tables separated by year (exp. 1994data, 1995data, 1996data, etc.). I am trying to perform a search that will allow the user to enter a value, iterate through the tables and echo the result. Here is the code I am using:

 

<?php
if (isset($_POST['searchdecs']) && $_POST['decs'] != '') {  //Verify thet a searchtype was selected!
require('get_connected.php');
$year_arr = array("1994","1995","1996");
        $arr_size = count($year_arr);

	//If the USER Searches by EFN
	if (isset($_POST['decs'])) {
	$decs = mysql_real_escape_string($_POST['decs']);
	$flag = 0;
              for ($i = 0; $i < $arr_size; $i++) 
              {
                //SQL Statement to SELECT EFN
                $sql = mysql_query("SELECT * FROM " .$year_arr[$i]. "data WHERE DECSFamilyName = '$decs' ORDER BY level DESC") or die(mysql_error());
		  	$total = mysql_num_rows($sql);
			if ($total > 1) {
			echo "<div class=\"results\">$total results matching $decs.</div><br />"; 
			}
			if ($total == 1) {
			echo "<div class=\"results\">$total result matching $decs.</div><br />"; 
			}
			if ($total > 0) {
			$flag = 1;
			while ($row = mysql_fetch_array($sql)) {
			include('verdevresults.php'); //The results layout
			}
		  }	
	   }
	   if ($flag == 0) {
              echo "Sorry, no records were found matching $decs.";
           }
        }
     }
?>

 

The problem I am having is that it it lumps the results by year. So it will say 25 Results found, list the results for a single year then repeat this for the next year by saying 53 results found then display the results for the next year... exp:

 

2 Results found.

Result 1 1994

Result 2 1994

 

4 Results found.

Result 1 1995

Result 2 1995

Result 3 1995

Result 4 1995

 

and so on and on... I would like it to do this:

 

6 Results found.

Result 1 1994

Result 2 1994

Result 3 1995

Result 4 1995

Result 5 1995

Result 6 1995

 

Can anyone help a brother out?

 

Thanks.

[Ken2k7]

The first thing you should do is use the while loop to call upon the num_rows of every table and then add them up. This will get all the results.

 

After that, you echo out $total, which should now have the total number of matched queries. Now you loop through to print the actual results.

[suttercain]

I tried this, but think I may be coding your suggestion wrong. Could you please give me an example?

 

Thanks.

 

SC

[suttercain]

How about a MySQL UNION? If yes, can someone show me a quick example of the PHP syantax to do a UNION?

 

Gracias.

 

SC

[Ken2k7]

<?php
if ($_POST['searchdecs'] && $_POST['decs'] != ''){
require('get_connected.php');
$year_arr = array("1994","1995","1996");
$arr_size = count($year_arr);

if ($_POST['decs']){
	$decs = mysql_real_escape_string($_POST['decs']);
	$flag = 0;
	$total = 0;
	for ($i=0; $i<$arr_size; $i++){
		$sql = mysql_query("SELECT * FROM " .$year_arr[$i]. "data WHERE DECSFamilyName = '$decs' ORDER BY level DESC") or die(mysql_error());
		$total .= mysql_num_rows($sql);
	}

	if (!$total) echo "No queries were found.";
	else {
		for ($i=0; $i<$arr_size; $i++){
			$sql = mysql_query("SELECT * FROM ".$year_arr[$i]."data WHERE DECSFamilyName='$decs' ORDER BY level DESC") or die(mysql_error());
			while ($row = mysql_fetch_assoc($sql)){
				// echo results
			}
		}
	}
}
}
?>