php search query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

Re: php search query

Post by chris98 »

Would I have to include multiple if statements and multiple select statements to make sure I am covered with all options, or would there be an easier way?

I.E.

Code: Select all

if (empty($file))
{
SELECT COUNT(*) AS id FROM downloads WHERE name LIKE :file AND difficulty = :difficulty AND estates = :estates AND missions = :missions AND balanced = :balance AND category = :category
}
if (empty($author))
{
SELECT COUNT(*) AS id FROM downloads WHERE username LIKE :user AND difficulty = :difficulty AND estates = :estates AND missions = :missions AND balanced = :balance AND category = :category
}.etc.etc
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php search query

Post by Celauran »

Why not loop through your POST array and build your query string that way?
User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

Re: php search query

Post by chris98 »

How could I do that?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: php search query

Post by Christopher »

Code: Select all

foreach ($_POST as $key => $value) {
     // do something here
}
(#10850)
User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

Re: php search query

Post by chris98 »

So would something like this do the trick?

Code: Select all

foreach ($_GET as $ps => $row) {
SELECT COUNT(*) AS id FROM downloads WHERE name LIKE :file AND difficulty = :difficulty AND estates = :estates AND missions = :missions AND balanced = :balance AND category = :category
}
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php search query

Post by Celauran »

No. If you're running a query inside a loop, you're definitely doing it wrong. Use the loop to build your query string. Something along these lines:

Code: Select all

$columns = array('file', 'username', 'difficulty', 'estates', 'missions', 'balanced', 'category');
$query = "SELECT COUNT(id) AS count FROM downloads WHERE ";
foreach ($_POST as $key => $value) {
	if (in_array($key, $columns)) {
		$query .= "{$key} = :{$key} AND ";
	}
}
$query = rtrim($query, 'AND ');
Post Reply