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.

    I would then need to create a new SELECT statement each time I add a new table though right?

      It should be possible to build the query dynamically. However, the solution I would use would be to not have separate tables. Instead, I would have one table that included a date or year column that I could use to select and order the desired records.

        Thanks NogDog,

        I used the UNION approach and it seems to be working. I agree with you on the tables. Sadly this is how the web guys have it set up 🙁

        Have a great weekend.

        SC

          Both of you beat me to it.. Hit the web guys with a stick as they deserve it unless they have a very good reason 🙂

          query SHOW TABLES.

          The bigger question should be what happens if you need to alter the table format in the future and how that will effect everything. It could be a real learning experience for someone.

          If there is the data postfix then you can get a list of the tables, loop through them and search for tables with a 4 digit year and the post fix then you could generate the sql. Until you have to change the schema in the future and then things start falling apart everywhere else that needs to refer to them in a more precise fashion. It's the potential very heavy cost of splitting up data like this which can cause other people to literally scream if they ever have to go near it.

            Write a Reply...