CODEWITHSUNDEEP
phpforeach
Stoosh
Stoosh

Reputation: 2429

Removing last word from FOREACH loop

I'm building a basic function, which builds out Mysql WHERE clauses based on how many are in the array.

$array = array('id' => '3', 'name' => 'roger'); 
$sql = "SELECT * FROM table WHERE ";

foreach ($array as $k => $v) {
    $sql .= $k . ' = ' . $v . ' AND ';
}

which will output

SELECT * FROM table WHERE id = 3 AND name = roger AND

However obviously I don't want that last AND, how do I go about removing it from the string?

Thanks

Upvotes: 4

Views: 819

Answers (5)

codaddict
codaddict

Reputation: 455302

I would do it this way:

$sql = "SELECT * FROM table WHERE 1=1 "; 
// add "AND x=y" for every pair of key, value pair in the array.    
foreach ($array as $k => $v) 
    $sql .= ' AND ' . $k . ' = ' . $v;

I've added a 1=1 to the where clause so that your query will be valid even if the array $array is empty.

Upvotes: 1

Casey Chu
Casey Chu

Reputation: 25463

You could do

$sql = substr($sql, 0, -5);

But perhaps the more elegant solution is

$array = array('id' => '3', 'name' => 'roger'); 
$clauses = array();

foreach ($array as $k => $v)
    $clauses[] = $k . ' = ' . $v;

$sql = "SELECT * FROM table WHERE " . implode(' AND ', $clauses);

Upvotes: 12

David
David

Reputation: 7153

Reformulate the question. You're trying to put an AND after every clause except the last. It would be easier to put an AND before every clause except the first.


$first = true;
foreach ($array as $k => v) {
    if (!$first) $sql .= ' AND ';
    $first = false;
    sql .= $k . ' = ' . $v;
}

Maybe not the easiest way in this case (other people mentioned using substr). However, I've found it a good tool to remember in general for situations like this.

Upvotes: 0

Amy B
Amy B

Reputation: 17977

$array = array('id' => '3', 'name' => 'roger'); 
$sql = "SELECT * FROM table WHERE ";

foreach ($array as $k => $v) {
    $sql .= $k . ' = ' . $v . ' AND ';
}

$sql = substr(trim($sql), 0, -3);

Upvotes: 2

jps
jps

Reputation: 11605

$sql = trim($sql, ' AND ');

Upvotes: 0

Related Questions