Member Avatar for jakizak

PHPMYSQL - How do you perform a double SELECT query or check against multiple fields?

Does anyone know how this can be achieved? What i want is from a search page that has multiple search areas such as name / description / functions and be able to type multiple search queries in to a form and it narrow it down in mysql query.

My initial thought was something along these lines:

$query = mysql_query("SELECT * FROM `products` WHERE `name` LIKE '%$s1%' AND SELECT * FROM `products` WHERE `functions` LIKE '%$s3%'");

but that returns the following error:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /----/--------/------_----/------/getdatatest.php on line 110

Much help appreciated!

Edited by jakizak because: n/a
  • 3 Contributors
  • 4 Replies
  • 710 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by raju_boini525

Dani AI

Generated

Your original error came from trying to put a second SELECT inside the WHERE clause — MySQL expects field comparisons there, not another query. That made mysql_query() return false and then mysql_num_rows() produced the warning. The two basic choices for multi-field search are: require every filled field to match (AND), or match any filled field (OR). Use parentheses if you ever mix them.

As pointed out, replacing AND with OR changes the logic; as suggested, build the WHERE dynamically so empty form fields are ignored. Below is a safe, modern pattern (PDO) that demonstrates both ideas and avoids the deprecated mysql extension and SQL injection:

// $pdo is a PDO connection
$filters = [];
$params  = [];
$op = 'OR'; // change to 'AND' if you want all filled fields to match

if (strlen(trim($qName))) {
  $filters[] = "name LIKE :name";
  $params[':name'] = '%' . trim($qName) . '%';
}
if (strlen(trim($qFunctions))) {
  $filters[] = "functions LIKE :functions";
  $params[':functions'] = '%' . trim($qFunctions) . '%';
}
$sql = 'SELECT * FROM products' . ($filters ? ' WHERE (' . implode(" $op ", $filters) . ')' : '');
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

Notes and troubleshooting tips:

  • Use prepared statements (PDO/mysqli) — never interpolate user input directly into SQL.
  • Leading wildcards (%term) prevent index use; for larger tables use FULLTEXT (MATCH(...) AGAINST(...)) or an external search engine if you need advanced relevance and speed.
  • If users can enter % or _ literally, escape them before building the LIKE pattern.
  • Add LIMIT/offset for paging and test queries with known data to verify AND vs OR behavior.

This approach keeps the logic clear, fixes the original syntax problem, and is safer and future-proof compared to the old mysql_ functions.

Recommended Answers

All 4 Replies

Member Avatar for pritaeas 
$query = mysql_query("SELECT * FROM `products` WHERE `name` LIKE '%$s1%' AND  `functions` LIKE '%$s3%'");
Member Avatar for jakizak 
$query = mysql_query("SELECT * FROM `products` WHERE `name` LIKE '%$s1%' AND  `functions` LIKE '%$s3%'");

I tried this (Thanks BTW) and it didn't return the result it should have. Not sure if that query type works, I read somewhere on the Internet that it may involve JOIN or GROUP something, i personally have never heard of them :D

Member Avatar for pritaeas

Replace AND with OR

Member Avatar for raju_boini525 
if(($name!="")&(isset($name))&($name<>"select")) 
		{
			$condition=$condition."namelike '%$name%' and ";
		}
		if(($function!="")&(isset($vptype))ype<>"select")) {
			$condition=$condition."function like '%$function%' and ";
		}
if(($description!="")&(isset($description))ype<>"select")) {
			$condition=$condition."description like '%$description%' and ";
		}
		$condition=substr($condition,0,-4);

mysql_query("select * from tablename where (".$condition.")");

try like this. it will help you.

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.