Multiple Tables

[eljaylarry]

Hi all,

I am trying to retrieve records from 2 tables for people who have signed up between two particualr dates, but for some reason I get an error

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource on line 21

I have two tables, table1 and table2 and the search values are submitted through a form. Both the tables are identical and have the following columns:

table1
name|signup_date

table2
name|signup_date

here is the SQL statement I used

$name = $_POST['name'];

$sql="SELECT * FROM table1, table2 WHERE name LIKE '$name' AND signup_date >= '2004-03-01' AND signup_date <= '2004-03-20' AND table1.name=table2.name";

$mysql_result=mysql_query($sql,$connection);
$num_rows=mysql_num_rows($mysql_result); .......

Any help will be greatly appreciated. The whole idea about creating two identical tables is to be able to archive the information from the first table but still be able to pull info from it and also from table2 when the query is run. I dont know, maybe my approach to the archiving might be wrong. Any help will be greatly appreciated. Thanks.

Larry

 

[cyoung]

Is there a reason that you have 2 tables with the same structure? This will cause you nothing but issues when trying to perform queries such as these. What I would do, if you are not able to change your applications to use a common table, is to create a temp table each time you need to run this query, and add the contents of each table to the temp table, and then query the temp table.

If you need to archve the data, then setup an event to copy the records to another table, or better yet, setup a replicated server.

 

[eljaylarry]

I think I will just setup an event to send all the data to another table like you suggested.

Will adding content to a temporary table when running a query make the process slow if you have maybe 10,000 records to pull up?

Thanks for the suggestions. I appreciate it

Larry