Create WHERE with array in PHP

Question

I would like to create a sql request if my array contains elements :

$sql = '';
$sql.= 'SELECT * FROM maisons, chambres.numero, chambres.etage, chambres.prix, chambres.charges, chambres.id';
$sql.= ' LEFT JOIN chambres';
$sql.= ' ON maisons.id = chambres.maison_id';

$sql.= ' WHERE ';

if(isset($tab))
{
    //$str_cp = implode(', ', $tab);
    //$sql.= 'maisons.commune_id IN '.$str_cp;
    if(count($tab) > 0)
    {
        for($i=0; $i<count($tab);$i++)
        {
            $sql.= 'commune_id = '.$tab[$i].' OR ';
        }
    }   
}

I'm obtaining this query :

SELECT * FROM maisons, chambres.numero, chambres.etage, chambres.prix, chambres.charges, chambres.id LEFT JOIN chambres ON maisons.id = chambres.maison_id WHERE commune_id = 8 OR

If I copy paste this query into phpmyadmin, I obtain this error :

#1142 - SELECT command denied to user 'bestinvewabi'@'10.0.120.18' for table 'numero'

By the way, do you have a better way to construct the WHERE of my query ?

Your user does not have permissions to that table. I usually start with WHERE 1=1 that I dont need and if statement I can just loop through and add AND Field = ? — kurt
– kurt, Commented Jan 3, 2016 at 9:57

Abed Alzain · Accepted Answer · 2016-01-03 09:58:22Z

5

try this code:

$sql = '';
$sql.= 'SELECT * FROM maisons, chambres.numero, chambres.etage, chambres.prix, chambres.charges, chambres.id';
$sql.= ' LEFT JOIN chambres';
$sql.= ' ON maisons.id = chambres.maison_id';

$sql.= ' WHERE 1=1 ';

if(isset($tab))
{
    if(count($tab) > 0)
    {
        $sql .= ' and commune_id in ('.implode(',', $tab).')';
    }   
}

answered Jan 3, 2016 at 9:58

Abed Alzain

7594 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Abed Alzain Over a year ago

Where do you want to use OR?

omni Over a year ago

@David Dubois you have to make sure the last OR in your query gets deleted. You can implement case handling (place the OR in the front of you WHERE statement loop and handle the FIRST for differently by using a flag) or, easier but not as clean, after the statement is finished, remove the last 3 chars from it. The error you're getting however seems to be a permission error. BTW as devpro mentioned with the changed code from Abed there's no need for the OR anymore.

devpro · Accepted Answer · 2016-01-03 10:10:41Z

1

You can use like that:

for($i=0; $i<count($tab);$i++)
 {
      $array[] = 'commune_id = '.$tab[$i];
  }

$sql .= implode(" OR ", $array);

Second solution:

for($i=0; $i<count($tab);$i++)
     {
          $array[] = $tab[$i];
      }

    $ids = implode(",", $array);
    $ids = rtrim($array,",");

    $sql .=  'commune_id IN ('.$ids.')';

edited Jan 3, 2016 at 10:10

answered Jan 3, 2016 at 10:03

devpro

16.1k3 gold badges29 silver badges39 bronze badges

2 Comments

David Dubois Over a year ago

good idea, thank you, but I obtain this SELECT maisons.*, chambres.numero, chambres.etage, chambres.prix, chambres.charges, chambres.id FROM maisons LEFT JOIN chambres ON maisons.id = chambres.maison_id WHERE ArrayORArrayORArrayORArrayORArrayORcommune_id = 8 AND etat_id = 5

David Dubois Over a year ago

Thank you. I don't have enough point to put 1 point for you, sorry.

Collectives™ on Stack Overflow

Create WHERE with array in PHP

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related