PHP查询停止LIKE停止记录重复? [英] PHP query stop LIKE stop records duplicating?

问题描述

我已经建立了一个带有look"列的产品表，在该列中它可以在多个范围内，例如1,6"，所有逗号分隔在表列中.

I have set up up a products table with a "look" column, in this column it can be in multiple ranges such as "1,6" all comma separated in the table column.

我遇到的问题是，如果将一个范围添加到1,6,8"中，该范围似乎也显示在 10 和 11 中，我猜这是因为它在列中包含1".

The issue I am having is that if a range is added into "1,6,8" the range appear to show in 10 and 11 also which I guess is because it consists "1" in the column.

我的代码是:

$query_RSsec = "SELECT * FROM look WHERE seolink = '$_REQUEST[range]'";
$RSsec = mysql_query($query_RSsec, $swede) or die(mysql_error());
$row_RSsec = mysql_fetch_assoc($RSsec);
$totalRows_RSsec = mysql_num_rows($RSsec);

$query_RSrange = "SELECT ranges.*, ranges.name as rname, ranges.seolink as rseo, ranges.image as rimage, category.*, category.name as cname, category.seolink as cseo FROM (ranges LEFT JOIN category ON ranges.category = category.id) WHERE look LIKE '%$row_RSsec[id]%' ORDER BY ranges.name ASC";
$RSrange = mysql_query($query_RSrange, $swede) or die(mysql_error());
$row_RSrange = mysql_fetch_assoc($RSrange);
$totalRows_RSrange = mysql_num_rows($RSrange);
$use = 'yes';

然后返回这些我有以下代码:

And then to return these I have the following code:

<?php do {
          if (strlen($row_RSrange['rname']) > 10) {
                  $name = substr(str_replace('&','&amp;',$row_RSrange['rname']),0,10) . '...';
          } else {
              $name = str_replace('&','&amp;',$row_RSrange['rname']); 
          }
          ?>

<li><a title="<?php echo str_replace('&','&amp;',$row_RSrange['rname']); ?>" href="/products/<?php echo $row_RSrange['cseo']; ?>/<?php echo $row_RSrange['rseo']; ?>"><img src="/prod-images/category/<?php echo $row_RSrange['rimage']; ?>" alt="<?php echo str_replace('&','&amp;',$row_RSrange['rname']); ?>" title="<?php echo str_replace('&','&amp;',$row_RSrange['rname']); ?>" width="127" height="127" /><br />
        <?php echo $name; ?></a></li>
        <?php } while ($row_RSrange = mysql_fetch_assoc($RSrange)); ?>

推荐答案

WHERE FIND_IN_SET('$row_RSsec[id]',look) <> 0

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

只是为了好玩，这是您使用 MySQLi 的完整代码:

Just for fun, here is your entire code using MySQLi :

$swede = new mysqli('localhost', 'my_user', 'my_password', 'my_db');

try {
    $RSsec = $swede->query( "
                            SELECT id FROM look 
                            WHERE seolink = '{$_REQUEST['range']}'
                            ");
} catch (mysqli_sql_exception $e) { 
    echo $e->message;
}

$RSsecTotalRows = $RSsec->num_rows;

foreach($RSsec as $id => $RSsecID) {
    try 
    {
        $RSrange = $swede->query( "
        SELECT 
            ranges.*, ranges.name AS rname, ranges.seolink AS rseo, 
            ranges.image AS rimage, 
            category.*, category.name AS cname, category.seolink AS cseo 
        FROM ranges 
        LEFT JOIN category ON ranges.category = category.id 
        WHERE FIND_IN_SET('$RSsecID',look) <> 0
        ORDER BY ranges.name ASC
        ");

        $RSrangeTotalRows = $RSrange->num_rows;

        foreach($RSrange as $RSrangeRow) {
            $name = htmlspecialchars($RSrangeRow['rname']);
            $name = (strlen($name) > 10 ) ? substr_replace($name,"...",10) : $name;
            $href_url = "/products/{$RSrangeRow['cseo']}/{$RSrangeRow['rseo']}";
            $img_url = "/prod-images/category/{$row_RSrange['rimage']}";
?>

<li>
    <a title="<? echo $name; ?>" 
        href="<? echo $href_url; ?>">
        <img src="<? echo $img_url; ?>" 
        alt="<? echo $name; ?>" 
        title="<? echo $name; ?>" 
        width="127" height="127" />
        <br />
        <? echo $name; ?>
    </a>
</li>

<?          
        }
    } catch (mysqli_sql_exception $e) { 
        echo $e->message;
    }
}

这篇关于PHP查询停止LIKE停止记录重复?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！