Page 1 of 1

WHERE NOT IN Mysql statement not working.......

Posted: Thu Mar 13, 2008 2:27 pm
by kryles
Hi,

I am trying to prevent products from appearing more than once. The page currently takes all products that are of the same type as the one shown on the page. It then displays 4 of them. Right now it can show duplicate items which looks silly (think of a page like "customers who bought this also bought: ")

I decided to fix this but running into a problem with MY SQL statement. There NOT IN doesn't seem to work like I thought. Is my logic off or am I just missing out how to use the NOT IN?

Code: Select all

 
/* $rs['pName'] is assigned elsewhere in the code, but i have echoed and it is what I want it to be */
$boughtString = "' '";
for($i=0;$i<=3;$i++)
{
    echo '<td width="25%" align="center" valign="bottom">';
 
    $query = "  SELECT pName
                FROM products
                WHERE pType = '".$pType."' AND pDiscont = '1' AND pName NOT IN(".$boughtString.")";
    $result= mysql_query($query);
 
        if(strcmp($boughtString, "' '") == 0)
    {
                /*first entry */
        $boughtString = "'".$rs['pName']."'";
    }
    elseif($i == 3)
    {
                /* last entry */
        $boughtString .= ",'".$rs['pName']."'";
    }
    else
    {
                /* In between entries */
        $boughtString .= ",'".$rs['pName']."'";
    }
    print_r( $boughtString.'<br>'); //print string after each run
 

Code: Select all

 
'Expanding Wooden Fence'
'Expanding Wooden Fence','Black Palm Wallet'
'Expanding Wooden Fence','Black Palm Wallet','Expanding Wooden Fence'
'Expanding Wooden Fence','Black Palm Wallet','Expanding Wooden Fence','Colour Changing LED Candles (Set of 2)'
 
This is what the Where clause looks like at the end

Code: Select all

 
'See -Thru Shoe Store','Tungsten Carbide Edge Saw','Credit Card Case','Credit Card Case'
 
Thanks,

Re: WHERE NOT IN Mysql statement not working.......

Posted: Thu Mar 13, 2008 3:37 pm
by HCBen
"IN" or "NOT IN" are used with subqueries: http://dev.mysql.com/doc/refman/5.0/en/ ... eries.html

$boughtString is not a SQL query, so all you want to do is this:

Code: Select all

$query = "  SELECT pName FROM products WHERE pType = '".$pType."' AND pDiscont = '1' AND pName <> '".$boughtString."' ";
Hope that helps.
Ben

Re: WHERE NOT IN Mysql statement not working.......

Posted: Thu Mar 13, 2008 4:02 pm
by kryles

Code: Select all

 
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource
 
That is what I am getting now.

are you sure this works because it would look like this

Code: Select all

 
 
SELECT pID,pName,pImage
FROM products
WHERE pSection = '".$sectionID."' AND pDisplay = '1' AND pName  <> 'book,dishes,food';
 
 
I didn't think it could search through an array of items like that

Re: WHERE NOT IN Mysql statement not working.......

Posted: Thu Mar 13, 2008 5:59 pm
by HCBen
Ok, I misunderstood.

Your original select statement is correct, however, as you surmised, their's a string comparison issue with the IN expression between the pName value and the values set in the $boughtString string. What that is I'm not sure, I don't see anything obviously wrong...

However, there may be a better, alternative way to do this. Since you only want to list four items, you can achieve the same results from a single query:

Code: Select all

SELECT DISTINCT TOP 4 pName FROM products WHERE pType = '".$pType."' AND pDiscont = '1'


You can modify what DISTINCT TOP 4 products are selected by including additional columns and using an ORDER BY clause. This may be a simpler approach to what you want to do.

I hope this helps and apologize for my confusion.

Re: WHERE NOT IN Mysql statement not working.......

Posted: Thu Mar 13, 2008 6:40 pm
by kryles
yah that wont work because they are actually taken by random....not by order though....

Re: WHERE NOT IN Mysql statement not working.......

Posted: Fri Mar 14, 2008 12:19 pm
by kryles
Ok solved it. Although honestly I am not quite clear on why. Maybe it isnt even solved but it seems so :)

Code: Select all

 
<tr bordercolor="#CCCCCC" bgcolor="#FFFFFF">
 
<?php
    $boughtString = "' '";
 
    for($i=0;$i<=3;$i++)
    {
        echo '<td width="25%" align="center" valign="bottom">';
 
        $query = "  SELECT pID,pName,pImage
                FROM products
                WHERE pSection = '".$sectionID."' AND pDisplay = '1' AND pName <> 
                                '".$rs['pName']."' AND pName NOT IN(".$boughtString.")
                                ORDER BY RAND()
                LIMIT 1";
 
        $result= mysql_query($query) or die(mysql_error());
        $row = mysql_fetch_array($result);
 
        if(strcmp($boughtString, "' '") == 0)
        {
            $boughtString = "'".$row['pName']."'";
        }
        elseif($i == 3)
        {
            $boughtString .= ",'".$row['pName']."'";
        }
        else
        {
                $boughtString .= ",'".$row['pName']."'";
        }
 
        $startlink="<a href='proddetail.php?prod=" . urlencode($row['pID']) . "'>";
        $endlink="</a>";
 
        print $startlink . '<img src="' . $row["pImage"] . '" border="0" alt="' .        
                                       str_replace('"','&nbsp;',$row["pName"]) . '">' . $endlink;
        print '<br>';
        print $startlink . $row["pName"] . $endlink . $xxDot;
        print '<br>';
        print $startlink . '<img src="images/detailslib.gif" border="0" alt="' . 
                        str_replace('"','&nbsp;',$row["pName"]) . '">' . $endlink;
        echo '</td>';
    }
?>
</tr>