MySQL query using an array

Question

I'm trying to query a MySQL database using an array but I'm having trouble!

I have a table called clients, I want to be able to select 'name' from all rows whose 'sector' column is equal to $sectorlink.

I then want to put all the names into an array so I can perform my next query: select all rows from another table whose 'client' column is equal to one of the names returned from the first query. I'm doing something wrong because it returns a fatal SQL error. I'm getting confused with all the variables!

$sectorlink and $connection are the only variables that are defined outside of this code

Any suggestions?

$query1 = "SELECT name FROM clients WHERE sector = '$sectorlink'";
$clientresult = mysql_query($query1, $connection) or trigger_error("SQL", E_USER_ERROR);

while($row = mysql_fetch_array($clientresult)){

foreach($row AS $key => $value){$temp[] = '"'.$value.'"';}
$thelist = implode(",",$temp);

$query = "SELECT count(*) FROM studies WHERE client IN ($row) ORDER BY (date) desc";
$result = mysql_query($query, $connection) or trigger_error("SQL", E_USER_ERROR);

}

Answer 1

The second query should use $thelist not $row, and it should be outside of the while loop. The foreach loop is unnecessary when processing a single row. You can access the name in $row with a simple $row[0]. Something like this (untested):

$query1 = "SELECT name FROM clients WHERE sector = '$sectorlink'";
$clientresult = mysql_query($query1, $connection) or trigger_error("SQL", E_USER_ERROR);

while($row = mysql_fetch_array($clientresult)){
    $temp[] = '"'.$row[0].'"';
}

$thelist = implode(",",$temp);
$query = "SELECT count(*) FROM studies WHERE client IN ($thelist) ORDER BY (date) desc";
$result = mysql_query($query, $connection) or trigger_error("SQL", E_USER_ERROR);

Caution: Please be aware that your code is highly vulnerable to SQL injection attacks. It's fine for testing or internal development but if this code is going to be running the Fort Knox web site you're going to want to fix it up quite a bit. Just an FYI. :-)

Answer 2

Couple of things. First you have an unnecessary loop there. Try:

while (list($name) = mysql_fetch_row($clientresult)) {

    $temp[] = $name;
}

To build your temporary array.

Second, the parts of the IN clause are strings, so when you implode, you'll need to enclose each value in quotes:

$thelist = "'". implode("','", $temp) . "'";   

Lastly, in your query you are passing $row to the IN clause, you should be passing $thelist:

$query = "SELECT count(*) FROM studies WHERE client IN ($thelist) ORDER BY date desc";
$result = mysql_query($query, $connection) or trigger_error("SQL", E_USER_ERROR);

So altogether:

$query1 = "SELECT name FROM clients WHERE sector = '$sectorlink'";
$clientresult = mysql_query($query1, $connection) or trigger_error("SQL", E_USER_ERROR);

while (list($name) = mysql_fetch_row($clientresult)) {

    $temp[] = $name;
}

$thelist = "'". implode("','", $temp) . "'"; 

$query = "SELECT count(*) FROM studies WHERE client IN ($thelist) ORDER BY date desc";
$result = mysql_query($query, $connection) or trigger_error("SQL", E_USER_ERROR);

Answer 3

Try This:

$query1 = "SELECT name FROM clients WHERE sector = '$sectorlink'";
$clientresult = mysql_query($query1, $connection) or trigger_error("SQL", E_USER_ERROR);

while($row = mysql_fetch_array($clientresult)){

$client = $row['name'];

$query = "SELECT * FROM studies WHERE client='$client' ORDER BY date DESC";
$result = mysql_query($query, $connection) or trigger_error("SQL", E_USER_ERROR);

/* echo results here */

}

Answer 4

I expect you'd be better off doing this in one query with a join:

$query = "SELECT COUNT(*)  FROM `studies` INNER JOIN `clients` on studies.client = clients.name WHERE clients.sector = '$sectorlink' ORDER BY studies.date DESC";
$result = mysql_query($query, $connection) or trigger_error("SQL", E_USER_ERROR);