CODEWITHSUNDEEP
phpmysql
user319940
user319940

Reputation: 3317

Looping through an array and querying data

Basically what I'm trying to achieve is go through an array and do a query based on that data - e.g. array of names bob, bill, ben and query a database table based on all of the items in the array - so SELECT * FROM table WHERE name="$name".

The code I have is:

<?php 
 session_start(); 
 $array = $_SESSION['basket'];
  foreach  ($array = $_SESSION['basket'] as $value);

 $query = "SELECT * FROM catalogue WHERE plantname='$value'";
 $result = mysql_query($query) or die("Query failed : " . mysql_error()); 

echo "<table>\n"; 
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { 
   echo "\t<tr>\n"; 
   foreach ($line as $col_value) { 
       echo "\t\t<td>$col_value</td>\n"; 
   } 
   echo "\t</tr>\n"; 
} 
echo "</table>\n"; 

 ?>

but this is only displaying the last item it should be picking out of the query, any help is much appreciated.

Upvotes: 1

Views: 136

Answers (4)

colinmarc
colinmarc

Reputation: 2471

To get everything for all the possible $values, which is what I think you're trying to do, you want something like this:

SELECT * FROM catalogue WHERE plantname IN ('value1', 'value2')

Which can be accomplished with something like:

$query = "SELECT * FROM catalogue WHERE plantname IN ('" . implode($array, "', '") . "')";

...without hitting the database multiple times.

Upvotes: 2

Compeek
Compeek

Reputation: 909

UPDATED: Moved query outside of loop.

<?php
    session_start();
    $array = $_SESSION['basket'];
    $query = "SELECT * FROM catalogue WHERE plantname IN ('" . implode("', '", $array) . "')";
    $result = mysql_query($query) or die("Query failed : " . mysql_error());
    echo "<table>\n";
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
        echo "\t<tr>\n";
        foreach ($line as $col_value) {
            echo "\t\t<td>$col_value</td>\n";
        }
        echo "\t</tr>\n";
    }
    echo "</table>\n";
?>

Upvotes: 1

SnatchFrigate
SnatchFrigate

Reputation: 372

There are a number of issues in your code.

Firstly you have already assigned to $array so you only need to do:

foreach($array as $value)

Secondly you shouldn't be running queries inside a loop, as the basket gets bigger the more queries are executed on every page request.

Why not push the row into $_SESSION['basket'] then you can just loop $_SESSION['basket'] to show it rather than running loads of queries, it will make your code execute faster and you'll right less code.

Also be careful with the other answers as they haven't considered security as database input should be escaped.

Upvotes: 0

Marek Karbarz
Marek Karbarz

Reputation: 29324

The problem is with this line

foreach  ($array = $_SESSION['basket'] as $value);
    $query = "SELECT * FROM catalogue WHERE plantname='$value'";

First is the semicolon - it shouldn't be there. Also notice that you have no { } around your foreach statement, so you build a query for each item in your session variable, but execute it only for the last one.

Upvotes: 2

Related Questions