Using an array to create a query

[gdow]

Admittedly, I am probably doing something very strange. I am self-taught at PHP and do many things a little unorthodox. Please bear with me.

 

I have a database with seperate tables with similar-type data in each of the tables representing each of the 50 states in addition to the Canadian provinces. It's a long story why these tables are simply not combined as one table, but they're not.

 

I have been creating queries that I call, "Free Text queries" that hunt through each of the tables for the search term the user enters. I am using Union Select in the queries to get this done, but as another state comes on long, this code gets very lengthy. Instead, I would like to use an array with the name of the table represented as the variables. I would then like the program to loop the array through the Union Select portion of the query until all the tables have been queried.

 

My strategy has been to create the first part of the query like:

 

$query_first = "SELECT DISTINCT `ID`, ORrpt.`freq`, ORrpt.`offset`, ORrpt.`pl`,

            ORrpt.`loc`, ORrpt.`call`, ORrpt.`state_id`, counties.`county_name`, ORrpt.`use`

            FROM ORrpt

            INNER JOIN counties ON ORrpt.county_id = counties.county_id

            AND ORrpt.state_id = counties.state_id

            WHERE ORrpt.`op_status` NOT LIKE '4' AND (`freq` LIKE '%$keyword%' OR `loc` LIKE '%$keyword%'

            OR `call` LIKE '%$keyword%' OR `use` LIKE '%$keyword%'

            OR `affil` LIKE '%$keyword%' OR `features` LIKE '%$keyword%'

            OR`coverage` LIKE '%$keyword%' OR `sponsor` LIKE '%$keyword%' OR

            `system` LIKE '%$keyword%' OR

            `comments` LIKE '%$keyword%' OR `nets` LIKE '%$keyword%' OR

            `gps` LIKE '%$keyword%' AND ORrpt.`state_id` LIKE '$state_id')";

 

Then create a middle with a FOREACH loop like:

 

$tables = array("AKrpt","AZrpt","COrpt","CTrpt","IDrpt","MTrpt","NMrpt","NVrpt","ORrpt","WArpt","WYrpt","ABrpt","BCrpt","SKrpt","MBrpt","YTrpt","CArpt");

foreach ($tables as $table)

{

 

$query_midI = "UNION SELECT DISTINCT `ID`, $table.`freq`, $table.`offset`, $table.`pl`,

            $table.`loc`, $table.`call`, $table.`state_id`, counties.`county_name`, $table.`use`

            FROM $table

            INNER JOIN counties ON $table.county_id = counties.county_id

            AND $table.state_id = counties.state_id

            WHERE $table.`op_status` NOT LIKE '4' AND (`freq` LIKE '%$keyword%' OR `loc` LIKE '%$keyword%'

            OR `call` LIKE '%$keyword%' OR `use` LIKE '%$keyword%'

            OR `affil` LIKE '%$keyword%' OR `features` LIKE '%$keyword%'

            OR`coverage` LIKE '%$keyword%' OR `sponsor` LIKE '%$keyword%' OR

            `system` LIKE '%$keyword%' OR

            `comments` LIKE '%$keyword%' OR `nets` LIKE '%$keyword%' OR

            `gps` LIKE '%$keyword%' AND $table.`state_id` LIKE '$state_id')";

}

 

 

And end portion of the query:

 

$query_end = "ORDER BY `freq`, `loc` ASC";

 

 

And finally put it all together with:

 

$query = mysql_query ("$query_first $query_midF $query_end")

 

 

Everything about the query seems to work except that it only queries on 'ORrpt' (from $query_first) and 'CArpt' (the last array value). It is not echoing any of the other array vaiables, so the query is useless.

 

Any thoughts on how I fix this. I haven't been able to find anything on looping a an array through a query. It's usually the other way around.

[sasa]

try

foreach ($tables as $table)
{

$query_midI[] = "UNION SELECT DISTINCT `ID`, $table.`freq`, $table.`offset`, $table.`pl`,
             $table.`loc`, $table.`call`, $table.`state_id`, counties.`county_name`, $table.`use`
             FROM $table
             INNER JOIN counties ON $table.county_id = counties.county_id
             AND $table.state_id = counties.state_id
             WHERE $table.`op_status` NOT LIKE '4' AND (`freq` LIKE '%$keyword%' OR `loc` LIKE '%$keyword%'
             OR `call` LIKE '%$keyword%' OR `use` LIKE '%$keyword%'
             OR `affil` LIKE '%$keyword%' OR `features` LIKE '%$keyword%'
             OR`coverage` LIKE '%$keyword%' OR `sponsor` LIKE '%$keyword%' OR
             `system` LIKE '%$keyword%' OR
             `comments` LIKE '%$keyword%' OR `nets` LIKE '%$keyword%' OR
             `gps` LIKE '%$keyword%' AND $table.`state_id` LIKE '$state_id')";
}
$query_midI = implode(' ', $query_midI);

[gdow]

Sorry for not using the "code" button above.

 

I tried the solution and got 

Fatal erro:  [] operator not supported for strings in

.

 

This is a new error for me, so I am researching that.

 

Thanks for trying. I know it's really close.

[gdow]

IT'S WORKING!!!!

 

 

Thanks for all the help. I can now ice my forehead!

 

- Garrett