Member Avatar for turt2live

Hello,

I have a query that needs to perform the following:

where verified=1
AND
where name is like '%$term%' AND/OR owner is like '%$term%'

I have a basic knowledge of SQL and was wondering if the above is possible.

I need the and/or because i would like it to return a result whether only a name was found, just an owner, or both.


Please help me :)

--Turt2Live

  • 3 Contributors
  • 6 Replies
  • 123 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by hielo

Dani AI

Generated

The core issue in this thread is operator precedence and safe parameter handling. As pointed out, without parentheses the OR can bypass the verified check, so group the two LIKE conditions together. Also note that using single quotes around column names turns them into string literals (the mistake in 's reply); use identifiers (unquoted or backticked) and compare verified as a number when possible. Finally, make sure your wildcard placement is correct (the % goes around the value, not before the dollar sign) as later corrected.

For safety and correctness use a prepared statement and bind a single wildcard pattern for both columns. Escape literal % and _ inside the search term before adding wildcards to avoid accidental matches. Example (PDO):

$term = trim($_GET['q']);               // validate/clean input
$term = str_replace(['%', '_'], ['\%', '\_'], $term);
$like = "%$term%";

$stmt = $pdo->prepare(
  "SELECT id, name, owner FROM items WHERE verified = 1 AND (name LIKE :p OR owner LIKE :p)"
);
$stmt->execute([':p' => $like]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

For large tables, be aware that LIKE '%term%' cannot use a normal index (leading wildcard). Consider a FULLTEXT index or an external search engine for better performance. Also check column collation if results look case-sensitive. Useful references: PDO prepared statements, MySQL LIKE operator, and MySQL full-text search.

Recommended Answers

All 6 Replies

Member Avatar for hielo

...i would like it to return a result whether only a name was found, just an owner, or both.

You don't need to AND. The OR should give you what you want even when both are true:

$sql="SELECT `name`, `owner` 
FROM Table WHERE `name` LIKE '$%term%' OR `owner` LIKE '%$term%'";
Member Avatar for turt2live

Ok, so if I were to use the following, would it still work?

$sql = "SELECT `name`,`owner` FROM table WHERE `verified`='1' AND name LIKE '$%term%' OR `owner` LIKE '%$term%'";

Member Avatar for debasisdas

try this

$sql = "SELECT 'name','owner' FROM table WHERE 'verified'='1' AND (name LIKE '$%term%' OR 'owner' LIKE '%$term%')";
Edited by debasisdas because: n/a
Member Avatar for turt2live

It works! Thank you very much :D

Member Avatar for hielo

Ok, so if I were to use the following, would it still work?

$sql = "SELECT `name`,`owner` FROM table WHERE `verified`='1' AND name LIKE '$%term%' OR `owner` LIKE '%$term%'";

No! In that specific example, the query is treated as:

...
(`verified`='1' AND name LIKE '$%term%')
OR owner LIKE '%$term%'

In your case you clearly want the OR name with owner, so you must parenthesize that expression:

...`verified`='1' AND 
(name LIKE '$%term%' OR owner LIKE '%$term%')
Member Avatar for hielo

NOTE: In most of these replies change: name LIKE '$%term%' to: name LIKE '%$term%'

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.