How to query multiple tables using PHP script

[learingperl01]

Hello everyone wondering if someone could please shed some light/point me in the right direction. I have a DB that has multiple tables...a table for each day. We do not have merge tables so if I need to query a range of tables over 5 days I have to run the same exact query five times but changing the "from" table. Wondering if there is anyway to script this in PHP so that it can be read from a file which has all the tables that need to get looped through the query. I am still pretty new to PHP so not sure how to do something this advanced wondering if some one could PLEASE give me an example on how to accomplish this. I was wondering if the following below would be possible where the "from" field is populated using a $variable

$sql = "select name, date, id, t_id, s_id, app from ($domlist_str);";

 

[vacunita]

It certainly is possible.

If you have a file with a list of tables to loop through you could do something like:

$tables=file("filewithelistoftables.txt");

foreach ($tables as $table){

$mysqlquery="SELECT *FROM " . $table . "WHERE somefield=somevalue";

$res=mysql_query($mysqlquery);

}

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[josel]

vacunita,

I think your sample will query each instance or each table but the end result will be that which was returned by the last query. Don't you think?

learningpearl,

As vacunita suggests, if you run an for/each loop, you should be able to dynamically run a query through each of your tables. I do wonder what kind of naming convention you used. If your tables are not named in a fashion such that allows their names to be structured dynamically, you may want to run a query for each table.

Either way, you still did not let us know if you intend to manipulate this data individually or if you want to store it in an array and then sort it from there.

I think that if you use vacunita sample and replace = with .=, you will manage to concatenate your results into a single array.

Knowing what you intend to do with data helps determined the best SQL Query syntax ... I think ...

NOTE: Like you, I am not an expert but I dare suggest and learn along side you

Regards,


Jose Lerebours


KNOWLEDGE: Something you can give away endlessly and gain more of it in the process! - Jose Lerebours

 

[vacunita]

It was just an example showing how to use a variable as the value for a tablename, obviously incomplete as it will replace $res with the next result handle as it loops through the table names.

As for your comment, since $res is a result handle, there's no real info there so you can't really concatenate anything. You'd need to call mysql_fetch_array or fetch_assoc to actually get the data out.

Now depending on what it is the OP wants to do with that data, they can insert everything into an array and then manipulate it or whatever.

$tables=file("filewithelistoftables.txt");

foreach ($tables as $table){

$mysqlquery="SELECT *FROM " . $table . "WHERE somefield=somevalue";

$res=mysql_query($mysqlquery);

[green]\\Do whatever with the data for each table thats being looped. store it in an array, or echo it out or whatever.  [green]

}

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

the ideal solution, i'd guess, would be a union query across the tables, since the datasets are otherwise identical.
btw - crazy idea to split the tables...

$tables=file("filewithelistoftables.txt");
foreach ($tables as $table){
 $sql[] = " Select * from $table ";
}
$query = implode(' UNION ', $sql);
$result = mysql_query($query) or die (mysql_error());
while ($row=mysql_fetch_assoc($result)){
 echo "<pre>".print_r($row, true)."</pre>";
}

 

[learingperl01]

Thanks for all the replies not sure what the best way to do this would be. Regarding the "UNION" example which was recommended would this give me all results from all tables, even duplicate entries? Reading on the UNION command I seems as though it does not report dups.

I don't intend to manipulate the data, I just want to be able to run the same query but through a range of tables and get the results that I am querying for.

Example:
Select * from table 1 where uid = 'rsc2204';
Select * from table 2 where uid = 'rsc2204';
Select * from table 3 where uid = 'rsc2204';

Stored in a file I plan on having table1, table2, table3.

I tried the following query but I only get results from the last table in the "filewithlistoftables.txt"

Not sure what I am doing wrong. Thanks again for all the help everyone.

$tables = file($argv[1]);
foreach ($tables as $table){
$mysqlquery = "select * from $table where = '22443'";
}
$result = mysql_query($mysqlquery) or die (mysql_error());
while ($row=mysql_fetch_assoc($result)){
echo "<pre>".print_r($row, true)."</pre>";
}

 

[jpadie]

you can't have duplicates across a day because by definition the date will be different. to get rid of duplicates within a day, use select Distinct

and for your query - surely you need a column qualifier in your where clause?

$mysqlquery = "select * from $table where [red]col[/red] = '22443'";

 

[learingperl01]

Thanks for the reply here is the code forgot to put in the correct query earlier but I do have the correct query in the script:

$tables = file("filewithelistoftables.txt");
foreach ($tables as $table){
$mysqlquery = "select * from $table where u_id = '22443'";
}
$result = mysql_query($mysqlquery) or die (mysql_error());
while ($row=mysql_fetch_assoc($result)){
echo "<pre>".print_r($row, true)."</pre>";
}

Now here is my table structure and also the entries in my text file which is the file the scripts grabs the DB names from. The tables are broken down by dayofmonth and each table only contains data for that day.
sdd_20070812
sdd_20070813
sdd_20070814
sdd_20070815

In my text file I have all the tables names listed above. Now when I run the script I only get results from table sdd_20070815. Now if I delete table (sdd_20070815) from the text file I get results from table sdd_20070814 only. Not sure why it's not reading all the entries in the file just the last.

Anyone have any ideas? Thanks for all the help once again.

 

[jpadie]

what is the output from this script?

echo "<pre";
print_r(file("filewithelistoftables.txt"));

 

[learingperl01]

Thanks for the reply once again here are the results of the test query you recommended. The query returned the correct number/entries in the file. So now the question is why the script from above is only displaying results for the last entry in the file which is sdd_20070717

[quote
echo "<pre";
print_r(file("filewithelistoftables.txt"));
[/quote]

<preArray
(
[0] => sdd_20070713

[1] => sdd_20070714

[2] => sdd_20070715

[3] => sdd_20070716

[4] => sdd_20070717

)



Once again here is the code that I am trying, the query is printing out the timestamp so in theory I should get more than one date in the output but that is not the case I only get results for the last entry in the file.....

$tables = file("filewithelistoftables.txt");
foreach ($tables as $table){
$mysqlquery = "select timestamp from $table where u_id = '22443'";
}
$result = mysql_query($mysqlquery) or die (mysql_error());
while ($row=mysql_fetch_assoc($result)){
echo "<pre>".print_r($row, true)."</pre>";
}

thanks again for the help.

 

[vacunita]

You need to include the while loop inside the for each, so its done for each of the tables in the file.:

$tables = file("filewithelistoftables.txt");
foreach ($tables as $table)[red]{[/red]
$mysqlquery = "select timestamp from $table where u_id = '22443'";

 $result = mysql_query($mysqlquery) or die (mysql_error());
 while ($row=mysql_fetch_assoc($result)){
          echo "<pre>".print_r($row, true)."</pre>";
  }
[red]}[/red]

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

isn't timestamp a reserved word?

try

select [red]`[/red]timestamp[red]`[/red] from $table where u_id = '22443'";