Select an ARRAY

[loren646]

Basically I have some checkboxes that store in an array, $hood.

 

i think i have to use IN but i have tried and it does not seem to work. 

 

$N = count($hood);

if(empty($hood)) 

  {

    for($i=0; $i < $N; $i++)

    {

      echo($hood[$i] . " ");

    }

  }

  $myquery = "SELECT * 

              FROM available 

              LEFT JOIN land

              ON available.building = land.building

              WHERE location =$hood";

Edited March 19, 2013 by Zane

[Psycho]

You set $N to the count of the array and then do an empty() check on the array? Then you use a for() loop to iterate through the array instead of a foreach() loop? You don't state what type of values would be in the array - which is important since you want to use the values in a query. Also, since these are checkboxes I assume you are getting the data from a $_POST variable (the checkboxes should be named as an array)

 

 

//If hood will contain integer values
$hoodAry = array_filter(array_map('intval', $_POST['checkboxListName']));
$hoodList = implode(', ', $hoodAry);


//If hood will contain string values
$hoodAry = array_filter(array_map('mysql_real_escape_string', $_POST['checkboxListName']));
$hoodList = "'" . implode("', '", $hoodAry) . "'";


$query = "SELECT *
          FROM available
          LEFT JOIN land
          ON available.building = land.building
          WHERE location IN ($hoodList)";

[loren646]

<input type="checkbox" name="hoodselect[]" value="<?php echo $result['hood']?>"><?php echo $result['hood']." "?>

 

$hood=$_POST['hoodselect'];

 

 

the values are 2 letters i.e. EV or WV 

 

-------

 

I'm deleting the loop. its just shows that the checkbox are working. 

 

 

You set $N to the count of the array and then do an empty() check on the array? Then you use a for() loop to iterate through the array instead of a foreach() loop? You don't state what type of values would be in the array - which is important since you want to use the values in a query. Also, since these are checkboxes I assume you are getting the data from a $_POST variable (the checkboxes should be named as an array)

 

 

//If hood will contain integer values
$hoodAry = array_filter(array_map('intval', $_POST['checkboxListName']));
$hoodList = implode(', ', $hoodAry);


//If hood will contain string values
$hoodAry = array_filter(array_map('mysql_real_escape_string', $_POST['checkboxListName']));
$hoodList = "'" . implode("', '", $hoodAry) . "'";


$query = "SELECT *
          FROM available
          LEFT JOIN land
          ON available.building = land.building
          WHERE location IN ($hoodList)";

[Psycho]

I've written it out a little more explicit - with some comments

 

//Trim the values
$hoodAry = array_map('trim', $_POST['hoodselect']));
//Remove empty values
$hoodAry = array_filter($hoodAry);
//Prepare for DB query
$hoodAry = array_map('mysql_real_escape_string', $hoodAry);
//Implode into comma separated list (with quotes)
$hoodList = "'" . implode("', '", $hoodAry) . "'";

$query = "SELECT *
          FROM available
          LEFT JOIN land
          ON available.building = land.building
          WHERE location IN ($hoodList)";

[loren646]

AWESOME. THANKS!

 

if none are selected would it be better to change $hoodlist to all checked or to have it not even search location IN $hoodlist? 

 

I have this now:

 

$hood=$_POST['hoodselect'];


if (empty($hood))
{
 $hood[0] = "EV";
 $hood[1] = "LES";
 $hood[2] = "CT";
 $hood[3] = "WV";
 $hood[4] = "SH";


 //Trim the values
$hoodAry = array_map('trim', $hood);
//Remove empty values
$hoodAry = array_filter($hoodAry);
//Prepare for DB query
$hoodAry = array_map('mysql_real_escape_string', $hoodAry);
//Implode into comma separated list (with quotes)
$hoodList = "'" . implode("', '", $hoodAry) . "'";
}
else
{ 
//Trim the values
$hoodAry = array_map('trim', $_POST['hoodselect']);
//Remove empty values
$hoodAry = array_filter($hoodAry);
//Prepare for DB query
$hoodAry = array_map('mysql_real_escape_string', $hoodAry);
//Implode into comma separated list (with quotes)
$hoodList = "'" . implode("', '", $hoodAry) . "'";
}
 

 

 

I've written it out a little more explicit - with some comments

 

//Trim the values
$hoodAry = array_map('trim', $_POST['hoodselect']));
//Remove empty values
$hoodAry = array_filter($hoodAry);
//Prepare for DB query
$hoodAry = array_map('mysql_real_escape_string', $hoodAry);
//Implode into comma separated list (with quotes)
$hoodList = "'" . implode("', '", $hoodAry) . "'";

$query = "SELECT *
          FROM available
          LEFT JOIN land
          ON available.building = land.building
          WHERE location IN ($hoodList)";

Edited March 23, 2013 by fenway
code tags

[Psycho]

if none are selected would it be better to change $hoodlist to all checked or to have it not even search location IN $hoodlist? 

 

Make the WHERE clause dependent upon any being selected. If none are selected do not use it. You should first process the list first to remove any invalid values.

 

 

//Get values passed in POST data
$hoodAry = isset($_POST['hoodselect']) ? $_POST['hoodselect'] : array();
//Trim the values
$hoodAry = array_map('trim', $_POST['hoodselect']);
//Remove empty values
$hoodAry = array_filter($hoodAry);

//Create WHERE caluse for query
$WHERE = ''; //Set default as empty string
if(count($hoodAry))
{
    //Prepare for DB query
    $hoodAry = array_map('mysql_real_escape_string', $hoodAry);
    //Implode into comma separated list (with quotes)
    $hoodList = "'" . implode("', '", $hoodAry) . "'";
    $WHERE = "WHERE location IN ($hoodList)";
}

$query = "SELECT *
          FROM available
          LEFT JOIN land
          ON available.building = land.building
          {$WHERE}";

Edited March 19, 2013 by Psycho