Razoir Razoir - 6 months ago 9
MySQL Question

Output of DISTINCT and <> from SQL Query issue

I am obtaining some values from an array and making a match against these values in an SQL Query.

The code for this is as follows:

foreach($files as $ex){

$search = substr($ex,3,4);
echo $search . '<br>';
echo '<br>';

$sql = 'SELECT DISTINCT `pdb_code` FROM pdb WHERE `pdb_code` <> "' . $search . '" LIMIT 4';
}
$result = mysql_query($sql) or die(mysql_error());

while($row = mysql_fetch_array($result)){
echo 'SQL' . $row['pdb_code'] .'<br>';
$pdb[] = $row['pdb_code'];
}


The issue that I am having is that the
<>
seems not to be working.. I have even tried using the
!=
operator, but still having the same issue.

The output of
$search
from the array are :

101m

102l

102m

103l


The output of SQL from the query is still:

101m

102l

102m

103l

Answer

Your code doesn't seem that logical, as you generate numerous SQL statements and then just execute the last one.

However I assume what you want to do is take a list of files, extract a string from each file name and then list all the pdb_code values from the table which are not already in the string.

If so something like this would do it. It takes each file name, extracts the sub string and escapes it, putting the result into an array. Then it builds one query, imploding the array to use in a NOT IN clause:-

<?php 

$search_array = array();

foreach($files as $ex)
{
    $search = substr($ex,3,4);
    echo $search . '<br>';
    echo '<br>';
    $search_array[] = mysql_real_escape_string($search);
} 

if (count($search_array) > 0)
{
    $sql = "SELECT DISTINCT `pdb_code` FROM pdb WHERE `pdb_code` NOT IN ('" . implode("','", $search_array) . "') LIMIT 4";
    $result = mysql_query($sql) or die(mysql_error());

    while($row = mysql_fetch_array($result))
    {
        echo 'SQL' . $row['pdb_code'] .'<br>';
        $pdb[] = $row['pdb_code'];
    } 
}