need help with multiple list box array

[GalaxyTramp]

Hi

 

I am trying to put together the correct query for my DB but weariness is getting the better of me I think. I have a multiple list bow which posts info which I need to string together as a query.

 

The relevant form part:

$locationmenu = "<select name='location[]'  size='10' multiple='multiple' style='width:180px'>\n\t<option value=\"\">Show All</option>\n\t";
$q1 = "SELECT DISTINCT `location` from `feed_property` WHERE status = 'Available' order by location";
$r1 = mysql_query($q1) or die(mysql_error());
if(mysql_num_rows($r1) > '0')
{
    while($a1 = mysql_fetch_array($r1))
    {
        $locationmenu .= "<option value=\"$a1[location]\">$a1[location]</option>\n\t";
    }
}
$locationmenu .= "</select>\n";

 

 

and the part of the page where I am trying to construct the query:

 

$where = array();


if(!empty($_GET['ref']))
{
     
    // Allow only alphanumeric characters
    
    $param = preg_replace('/[^a-zA-Z0-9]/', '', $_GET['ref']);
    
    $param = mysql_real_escape_string($param);
     
    $where[] = "propertyref = '$param'";  
}



if(!empty($_GET['location']))

{
$param = preg_replace('/[^á é í ó ú ñ ü Á É Í Ó Ú Ü a-zA-Z0-9]/', '', $_GET['location']);

foreach ($param as $location)
{
    $where []= "location = '$param'"; //THIS IS THE PROBLEM LINE
}

}



$where = (!empty($where)) ? ' WHERE ' . implode(' AND ', $where) : '';


echo $where;

 

The $where prints as "WHERE location = 'Array' AND location = 'Array' AND location = 'Array' AND cat_id = '12' AND status = " I need to get the multiple location selections in where the 'Array' is returned. Almost there but then almost doesn't really cut it

 

All help appreciated

 

GT

[GalaxyTramp]

OK sorted the Array problem after a rest.

 

if(!empty($_GET['location']))

{

    // Allow only alphabetic and Spanish special characters
    
     $param = preg_replace('/[^á é í ó ú ñ ü Á É Í Ó Ú Ü a-zA-Z0-9]/', '', $_GET['location']);

     foreach ($param as $key => $value)
     {
       $where []= "location = '$value'";
     }
     

}

 

Thanks

[GalaxyTramp]

I now have this query but it does not work, it returns an empty result!!:  SELECT * FROM `property` WHERE location = 'town1'  AND location = 'town2'  AND cat_id = '12' AND status = 'Yes'

 

  id      |    location  |  status  |  price  | 

-----------------------------------------------------

  12    |    town1      |  Yes    |  200  |

            |                    |            |          |

  12    |    town2      |  Yes    |  400    |

            |                    |            |          |

  12    |    town3      |  Yes    |  600    |

 

 

I am obviously missing the obvious here

 

Thanks for looking

[DavidAM]

The problem is with the multiple possible values for location.

 

SELECT * FROM `property` 
WHERE location = 'town1'  AND location = 'town2'  
AND cat_id = '12' AND status = 'Yes' 

 

location cannot be "town1" AND "town2" at the same time.  You need to use OR for those, or use IN which would be easier to build:

 

SELECT * FROM `property` 
WHERE location IN ('town1', 'town2' ) 
AND cat_id = '12' AND status = 'Yes' 

 

The quickest approach to do that would be changing this section of code:

 

if(!empty($_GET['location']))
{
  $param = preg_replace('/[^á é í ó ú ñ ü Á É Í Ó Ú Ü a-zA-Z0-9]/', '', $_GET['location']);
  /* TAKE OUT THE foreach ...
  foreach ($param as $location)
  {
    $where []= "location = '$param'"; //THIS IS THE PROBLEM LINE
  }
  */
  // AND USE impode
  $where[] = 'location IN ("' . implode('", "', $param) . '")';
}

 

Note: You need to sanitize your inputs to prevent SQL injections. Have a look at mysql_real_escape_string()

[GalaxyTramp]

Yes having taken a step back it is quite obvious that location cannot be "town1" AND "town2" at the same time. Time for a few hours off so I will digest your response tommorrow. The real script does sanitize with mysql_real_escape_string()

 

Thanks

 

 

[GalaxyTramp]

OK after some shut eye I have returned to this and added the implode as per the last post. This does actually produce a workable query but it is repeating itself due to the other implode further down the page that puts the final query together.

 

I now have a query the prints as:

WHERE location IN ("town1", "town2", "town3", "town4", "town5") AND 
location IN ("town1", "town2", "town3", "town4", "town5") AND 
location IN ("town1", "town2", "town3", "town4", "town5")  AND 
location IN ("town1", "town2", "town3", "town4", "town5") AND 
location IN ("town1", "town2", "town3", "town4", "town5") AND cat_id = '12' AND status = 'Yes

 

$where = (!empty($where)) ? ' WHERE ' . implode(' AND ', $where) : ''; // This is the culprit 

 

I need to stop the location IN looping AARGH!!

 

Thanks

 

[GalaxyTramp]

Can anyone help with this please?

 

Thanks

[DavidAM]

The quickest approach to do that would be changing this section of code:

 

if(!empty($_GET['location']))
{
  $param = preg_replace('/[^á é í ó ú ñ ü Á É Í Ó Ú Ü a-zA-Z0-9]/', '', $_GET['location']);
  /* TAKE OUT THE foreach ...
  foreach ($param as $location)
  {
    $where []= "location = '$param'"; //THIS IS THE PROBLEM LINE
  }
  */
  // AND USE impode
  $where[] = 'location IN ("' . implode('", "', $param) . '")';
}

 

Did you take out the FOREACH? Unless you are adding the location phrase to your where array more than once, you should not get it in the query more than once. Post you code so we can see where the problem is.

[GalaxyTramp]

Fixed! I had indeed forgotten to remove the for each loop 

 

Thanks for your time David much appreciated

 

Regards

 

C

[GalaxyTramp]

I know have another problem. When form is submitted without selecting a value for property I need it to return all results.

 

The query prints like this:

WHERE location IN ("") AND cat_id = '12' AND status = 'Yes'

 

No results found.

 

 

Code is now:

$where = array();


if(!empty($_GET['cat_id']))

{

   // Allow only numeric characters

    $cat_id = preg_replace('/[^0-9 ]/', '', $_GET['cat_id']);  

    $param = mysql_real_escape_string($cat_id);

    $where []= "cat_id = '$param'"; 

}


if(!empty($_GET['location']))

{
       $param = preg_replace('/[^á é í ó ú ñ ü Á É Í Ó Ú Ü a-zA-Z0-9]/', '', $_GET['location']);

       $where[] = 'location IN ("' . implode('", "', $param) . '")';
}

       $where = (!empty($where)) ? ' WHERE ' . implode(' AND ', $where) : '';


$where []= "status = 'Yes'";


       echo $where;