Need to create queries from an array

Discussion in 'PHP' started by gdowkpc, Nov 4, 2007.

0
  1. #1
    I have a different table for each state, and each table is structered the same. I have created a form that allows a visitor to multi-select several states to search on. The variable is passed as an array.

    How do I loop a query from an array?

    I have been playing with the UNION SELECT instruction using FOREACH, but only the last value in the array is queried. I can't seem to get a loop to run to process each array variable and UNION them together.

    Thx.

    - Garrett
     
    gdowkpc, Nov 4, 2007 IP
  2. Brewster

    Brewster Active Member

    Messages:
    489
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    60
    #2
    Can you post the code that you have so it is clearer to us what you want to do

    Brew
     
    Brewster, Nov 4, 2007 IP
  3. gdowkpc

    gdowkpc Peon

    Messages:
    9
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3 
    $table_count = count($table);

// User query based on county_id code
// if the number in the array = 1 than do this.
if ($table_array_count <= "1") {
$query =   mysql_query ("SELECT DISTINCT status.`url`, $table[0].`freq`, $table[0].`pl`,
            $table[0].`loc`, $table[0].`call`, $table[0].`use`, $table[0].`ID`,
            $table[0].`state_id`, states.`state_abbrev`
             FROM `$table[0]`
             INNER JOIN states ON $table[0].state_id = states.state_id
             AND $table[0].state_id = states.state_id
             INNER JOIN status ON $table[0].op_status = status.status_id
             WHERE `freq` LIKE '%$freq%' AND `freq` LIKE '$band%' AND `loc` LIKE '%$loc%'
             AND `call` LIKE '%$call%' AND `features` LIKE '%$features%'
             AND status.`status_id` LIKE '%$status_id%'
             ORDER BY `state_abbrev`, `freq` ASC")
             or die (mysql_error());
}             



// If the number in the array is greater than 1, do this plus more unions selects below it
// I wanted to put a for loop before the union select statement but couldn't because the program read it as part of the query. How to loop union selects?
// create a union select query as a variable so it can be looped as needed.

//foreach ($table as $key => $table_next) { //this is working...just need to loop it through the array now. It's only processing the last value of the array.
for($i = 1; $i < count($table); $i++) {
$table_next = $table[$i]; // and these two lines are working too, but also just running the last value of the array.
   // the problem seems to be that the final variable for below is the last value of the array. The variable name needs to be dynamic.

// variable to loop from thr arrray.
$union_query = "UNION SELECT DISTINCT status.`url`, $table_next.`freq`, $table_next.`pl`,
            $table_next.`loc`, $table_next.`call`, $table_next.`use`, $table_next.`ID`,
            $table_next.`state_id`, states.`state_abbrev`
             FROM `$table_next`
             INNER JOIN states ON $table_next.state_id = states.state_id
             AND $table_next.state_id = states.state_id
             INNER JOIN status ON $table_next.op_status = status.status_id
             WHERE `freq` LIKE '%$freq%' AND `freq` LIKE '$band%' AND `loc` LIKE '%$loc%'
             AND `call` LIKE '%$call%' AND `features` LIKE '%$features%'
             AND status.`status_id` LIKE '%$status_id%'";
}     

   

if ($table_count > "1") {

$query =   mysql_query ("SELECT DISTINCT status.`url`, $table[0].`freq`, $table[0].`pl`,
            $table[0].`loc`, $table[0].`call`, $table[0].`use`, $table[0].`ID`,
            $table[0].`state_id`, states.`state_abbrev`
             FROM `$table[0]`
             INNER JOIN states ON $table[0].state_id = states.state_id
             AND $table[0].state_id = states.state_id
             INNER JOIN status ON $table[0].op_status = status.status_id
             WHERE `freq` LIKE '%$freq%' AND `freq` LIKE '$band%' AND `loc` LIKE '%$loc%'
             AND `call` LIKE '%$call%' AND `features` LIKE '%$features%'
             AND status.`status_id` LIKE '%$status_id%'
                     
             $union_query
                         
             ORDER BY  `freq`, `state_abbrev` ASC")
             or die (mysql_error());
}
    PHP:

    Basically I want to UNION SELECT as many queries as there are variables in the array.
     
    gdowkpc, Nov 4, 2007 IP
  4. gdowkpc

    gdowkpc Peon

    Messages:
    9
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    I may be looking at an implode() statement. I am researching further.
     
    gdowkpc, Nov 4, 2007 IP
  5. gdowkpc

    gdowkpc Peon

    Messages:
    9
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    I tried this: 

    $tables = implode( "'.'", $table );
 
if ($table_count > "1") {

$query =   mysql_query ("SELECT DISTINCT status.`url`, $tables.`freq`, $tables.`pl`,
            $tables.`loc`, $tables.`call`, $tables.`use`, $tables.`ID`,
            $tables.`state_id`, states.`state_abbrev`
             FROM `$tables`
             INNER JOIN states ON $tables.state_id = states.state_id
             AND $tables.state_id = states.state_id
             INNER JOIN status ON $tables.op_status = status.status_id
             WHERE `freq` LIKE '%$freq%' AND `freq` LIKE '$band%' AND `loc` LIKE '%$loc%'
             AND `call` LIKE '%$call%' AND `features` LIKE '%$features%'
             AND status.`status_id` LIKE '%$status_id%'
                     
             
                         
             ORDER BY  `freq`, `state_abbrev` ASC")
             or die (mysql_error());
    PHP:
    But get this: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDrpt'.'NVrpt'.'UTrpt.`freq`, AKrpt'.'IDrpt'.'NVrpt'.'UTrpt.`pl`, A' at line 1
     
    gdowkpc, Nov 4, 2007 IP
  6. AmanVR

    AmanVR Guest

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Try this

    
foreach($_POST['checkedID'] as $checkID){
$row=mysql_fetch_array(mysql_query("SELECT ID FROM db_table WHERE ID=".ceil($checkID)." AND sign_up=1"));
    PHP:
    PS. make sure you make each value checked as an ID value (thats my auto incrimented value)
    And if your not using the: ".ceil($checkID)." and not just ".$checkID" on its own
     
    AmanVR, Nov 4, 2007 IP
  7. gdowkpc

    gdowkpc Peon

    Messages:
    9
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #7
    This creates a broken query.
     
    gdowkpc, Nov 4, 2007 IP
  8. gdowkpc

    gdowkpc Peon

    Messages:
    9
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #8
    Here's another example of what I'm trying to do. Instead of a bunch of if/else statements, how about dynamically inserting UNION SELECTS based on the count().

    $table_count = count($table);


// if the number in the array = 1 than do this.
if ($table_count <= "1") {
$query =   mysql_query ("SELECT DISTINCT status.`url`, $table[0].`freq`, $table[0].`pl`,
            $table[0].`loc`, $table[0].`call`, $table[0].`use`, $table[0].`ID`,
            $table[0].`state_id`, states.`state_abbrev`
             FROM `$table[0]`
             INNER JOIN states ON $table[0].state_id = states.state_id
             AND $table[0].state_id = states.state_id
             INNER JOIN status ON $table[0].op_status = status.status_id
             WHERE `freq` LIKE '%$freq%' AND `freq` LIKE '$band%' AND `loc` LIKE '%$loc%'
             AND `call` LIKE '%$call%' AND `features` LIKE '%$features%'
             AND status.`status_id` LIKE '%$status_id%'
             ORDER BY `state_abbrev`, `freq` ASC")
             or die (mysql_error());

}elseif ($table_count = "2") {

$query =   mysql_query ("SELECT DISTINCT status.`url`, $table[0].`freq`, $table[0].`pl`,
            $table[0].`loc`, $table[0].`call`, $table[0].`use`, $table[0].`ID`,
            $table[0].`state_id`, states.`state_abbrev`
             FROM `$table[0]`
             INNER JOIN states ON $table[0].state_id = states.state_id
             AND $table[0].state_id = states.state_id
             INNER JOIN status ON $table[0].op_status = status.status_id
             WHERE `freq` LIKE '%$freq%'  AND `freq` LIKE '$bands[0]%' AND `loc` LIKE '%$loc%'
             AND `call` LIKE '%$call%' AND `features` LIKE '%$features%'
             AND status.`status_id` LIKE '%$status_id%' 
                     
            UNION SELECT DISTINCT status.`url`, $table[1].`freq`, $table[1].`pl`,
            $table[1].`loc`, $table[1].`call`, $table[1].`use`, $table[1].`ID`,
            $table[1].`state_id`, states.`state_abbrev`
             FROM `$table[1]`
             INNER JOIN states ON $table[1].state_id = states.state_id
             AND $table[1].state_id = states.state_id
             INNER JOIN status ON $table[1].op_status = status.status_id
             WHERE `freq` LIKE '%$freq%' AND `freq` LIKE '$bands[0]%' AND `loc` LIKE '%$loc%'
             AND `call` LIKE '%$call%' AND `features` LIKE '%$features%'
             AND status.`status_id` LIKE '%$status_id%' 
             
             ORDER BY  `freq`, `state_abbrev` ASC")
             or die (mysql_error());
             
 
}elseif ($table_count = "3") {

$query =   mysql_query ("SELECT DISTINCT status.`url`, $table[0].`freq`, $table[0].`pl`,
            $table[0].`loc`, $table[0].`call`, $table[0].`use`, $table[0].`ID`,
            $table[0].`state_id`, states.`state_abbrev`
             FROM `$table[0]`
             INNER JOIN states ON $table[0].state_id = states.state_id
             AND $table[0].state_id = states.state_id
             INNER JOIN status ON $table[0].op_status = status.status_id
             WHERE `freq` LIKE '%$freq%'  AND `freq` LIKE '$bands[0]%' AND `loc` LIKE '%$loc%'
             AND `call` LIKE '%$call%' AND `features` LIKE '%$features%'
             AND status.`status_id` LIKE '%$status_id%' 
                     
            UNION SELECT DISTINCT status.`url`, $table[1].`freq`, $table[1].`pl`,
            $table[1].`loc`, $table[1].`call`, $table[1].`use`, $table[1].`ID`,
            $table[1].`state_id`, states.`state_abbrev`
             FROM `$table[1]`
             INNER JOIN states ON $table[1].state_id = states.state_id
             AND $table[1].state_id = states.state_id
             INNER JOIN status ON $table[1].op_status = status.status_id
             WHERE `freq` LIKE '%$freq%' AND `freq` LIKE '$bands[0]%' AND `loc` LIKE '%$loc%'
             AND `call` LIKE '%$call%' AND `features` LIKE '%$features%'
             AND status.`status_id` LIKE '%$status_id%'
             
             UNION SELECT DISTINCT status.`url`, $table[2].`freq`, $table[2].`pl`,
            $table[2].`loc`, $table[2].`call`, $table[2].`use`, $table[2].`ID`,
            $table[2].`state_id`, states.`state_abbrev`
             FROM `$table[2]`
             INNER JOIN states ON $table[2].state_id = states.state_id
             AND $table[2].state_id = states.state_id
             INNER JOIN status ON $table[2].op_status = status.status_id
             WHERE `freq` LIKE '%$freq%' AND `freq` LIKE '$bands[0]%' AND `loc` LIKE '%$loc%'
             AND `call` LIKE '%$call%' AND `features` LIKE '%$features%'
             AND status.`status_id` LIKE '%$status_id%'
                       
             ORDER BY  `freq`, `state_abbrev` ASC")
             or die (mysql_error());
 }  // ends this query        


$num_rows = mysql_num_rows($query);
IF ($num_rows == '0') {
    PHP:
    But instead of having to manually build the queries and walk the array through by using $table[0], $table[1], etc, how can I dynamically build these queries based on the number of variables in the array?
     
    gdowkpc, Nov 4, 2007 IP