andrew has asked for the wisdom of the Perl Monks concerning the following question:

alright alot in the while function here but my code doesnt work I need to get the results from where category is equal to somethng, here u can see what I mean 
      $sth = $dbh->prepare(
    "SELECT * FROM `items`
    WHERE
      category = '$id' 
    AND
      description LIKE '%$search%'
    OR
      itemid LIKE '%$search%'
    OR
      longdescription LIKE '%$search%' LIMIT $offset, $limit");
     $sth->execute or die $dbh->errstr;

[download]

Replies are listed 'Best First'.
Re: MYSQL while function
by gryphon (Abbot) on Sep 07, 2002 at 00:10 UTC

    Greetings andrew,

    First off, there's really not enough information in your post to help me/us figure out what's wrong and let you know. What are some of the example data sets that fill your variables like $search and $id?

    Next, and very important: FOR THE LAST TIME, LEARN TO USE PLACEHOLDERS AND USE THEM! Several different people have told you about placeholders on several different occations. Please, please use them. In your SQL above, let's say you want $search to be "andrew's sql". Well, the way you have it coded now, badness because the single quote in $search will get interpreted by your database to be the end if the $search string. You'll get a SQL error. Using placeholders saves you headaches.

    $sth = $dbh->prepare(q{
  SELECT * FROM items
  WHERE category = ?
  AND description LIKE ?
  OR itemid LIKE ?
  OR longdescription LIKE ?
  LIMIT ?, ?
});
$sth->execute(
  $id, 
  '%' . $search . '%', 
  '%' . $search . '%', 
  '%' . $search . '%', 
  $offset, $limit
) or die $dbh->errstr;

    [download]

    So try the above code to see if it helps. If not, then post back some additional code or information about your specific situation.

    gryphon
    code('Perl') || die;

[reply]
[d/l]
Re: MYSQL while function
by greywolf (Priest) on Sep 07, 2002 at 03:04 UTC
    You might want to try bracketing your where clauses to ensure you are testing for the conditions you really want. It never hurts to be explicit with your logic. For example:
    WHERE category = '$id' AND (description LIKE '%$search%' OR itemid LIK
+E '%$search%' OR longdescription LIKE '%$search%')
is different from
WHERE (category = '$id' AND description LIKE '%$search%') OR itemid LI
+KE '%$search%' OR longdescription LIKE '%$search%'

    [download]
    That being said, you really should try to implement some of the suggestions from other monks if you are going to post more than 1 question on essentially the same problem.

    mr greywolf
[reply]
[d/l]
Re: MYSQL while function
by blokhead (Monsignor) on Sep 07, 2002 at 06:14 UTC
    Apart from what others have mentioned, consider removing those backticks in the first line of the SQL query. No reason to quote the table name -- it may not even be legal. And if it were legal, backticks would probably not be the right way to quote.

    blokhead

[reply]

Back to Seekers of Perl Wisdom