Create dynamic query

TCovert

I need to create a dynamic query, based on what fields the user puts data into, to perform the search by.

I have a form with 12 fields that the user can search by. And on any given search, the user can search using any of these, so it is impossible to write one definate select statement to accomadate this.

So, I need to be able to create the select query dynamically.

The fields are: strDateFrom, strDateTo, strKeyword, bodytext, headline, byline, section, page, keywords, series, caption, photocredit.

This is the current php I have to search using only one of these fields...I need multiple fields:

if ($strKeyword == "")
{
$strKeyword= "%";
}
else
{
$strKeyword= "%" . $strKeyword . "%";
}

if ($headline == "") 
{
	$headline= "%";
}
else
{
	$headline= "%" . $headline . "%";
}

if ($bodytext == "") 
{
	$bodytext= "%";
}
else
{
	$bodytext= "%" . $bodytext . "%";
}

if ($byline == "") 
{
	$byline= "%";
}
else
{
	$byline= "%" . $byline . "%";
}

if ($section == "") 
{
	$section= "%";
}
else
{
	$section= "%" . $section . "%";
}

if ($page == "") 
{
	$page= "%";
}
else
{
	$page= "%" . $page . "%";
}

if ($keywords == "") 
{
	$keywords= "%";
}
else
{
	$keywords= "%" . $keywords . "%";
}

if ($series == "") 
{
	$series= "%";
}
else
{
	$series= "%" . $series . "%";
}

if ($caption == "") 
{
	$caption= "%";
}
else
{
	$caption= "%" . $caption . "%";
}

if ($photocredit == "") 
{
	$photocredit= "%";
}
else
{
	$photocredit= "%" . $photocredit . "%";
}
#  SELECT FOR THE PURPOSE OF NEXT AND PREV NAV
#

$numquery = "SELECT id, pubdate, byline, headline, bodytext FROM stories WHERE bodytext LIKE '" . $strKeyword . "' AND pubdate BETWEEN '$strFrom' AND '$strTo'"; 

$numresult = mysql_query($numquery, $conn);
$numrows = mysql_num_rows($numresult);

$query = "SELECT id, DATE_FORMAT(pubdate, '%Y %m-%d'), byline, headline, bodytext FROM stories WHERE bodytext LIKE '" . $strKeyword . "' AND pubdate BETWEEN '$strFrom' AND '$strTo' ORDER BY DATE_FORMAT(pubdate, '%Y, %m, %d') AND page DESC LIMIT 2000"; 

$result = mysql_query($query, $conn);
$num_p = mysql_num_rows($result);

eoghain

You probably want to do something like this:

$query = "SELECT something FROM mytable ";
$where = "";
$sep = "WHERE";

if (isset($strKeyword)) {
   $where .= $sep . "keyword LIKE '%" . $strKeyword . "%' ";
   $sep = "AND";
}

if (isset($headline)) {
   $where .= $sep . "headline LIKE '%" . $headline . "%' ";
   $sep = "AND";
}

$query .= $where;

That will dynamically build your query for you. There are cleaner ways to do this, but this fits with the code that you are currently using.

hand

I don't know exactly what you meen. Following sample is creating dynamically a WHERE clause ...

<?php
// http://localhost/testscript.php?bodytext=bt&headline=&byline=by&section=&page=&keywords=kw&series=ser&caption=&photocredit=ph
while(list($key, $val) = each($HTTP_GET_VARS)) {
	$key = stripslashes($key); 
	$val = stripslashes($val);
	// print "$key / $val<br>";
	if ($val != "") {
		$LikeSql .= "AND $key like '%$val%' ";	
	}
}
print $LikeSql;
// result: "AND bodytext like '%bt%' AND byline like '%by%' AND keywords like '%kw%' AND series like '%ser%' AND photocredit like '%ph%'"
?>

TCovert

So, if I used this code:

$query = "SELECT something FROM mytable ";
$where = "";
$sep = "WHERE";

if (isset($strKeyword)) {
$where .= $sep . "keyword LIKE '%" . $strKeyword . "%' ";
$sep = "AND";
}

if (isset($headline)) {
$where .= $sep . "headline LIKE '%" . $headline . "%' ";
$sep = "AND";
}

$query .= $where;

I could then continue on and add all the potential fields in here, and it would only add to the query the fields that actually contain data.

How would I put in there the date selection ones?

I have "AND pubdate BETWEEN '" . $strFrom . "' AND '" . $strTo . "' "at the end of this query as well. How would I dynamically add that if it was selected.

TCovert

When I use the code you supplied, I get the following error:

Warning: Supplied argument is not a valid MySQL result resource in /projects/sunarchive/htdocs_0_10/sql/search_select.php on line 217
You have an error in your SQL syntax near 'LIKE '%%' AND headline LIKE '%%' AND bodytext LIKE '%%' ' at line 1

Line 217 is: $num_p = mysql_num_rows($result);

Please help me to find the solution to this. Thanks.

eoghain

Well from what your where clause is showing you have no data inside your LIKE statements. Maybe isset() isn't the right choice for the way your code is set up. Maybe you should be using ($var != "") instead.

Also you should check the syntax for a LIKE statement in MySQL I've been using PostgreSQL for quite some time and '%name%' is a valid like search, MySQL might use a different syntax.

TCovert

So, this is the code I use now...I think we are getting closer, but it still gives me an error that I cannot nail down.

Code:

$query = "SELECT id, DATE_FORMAT(pubdate, '%Y %m-%d'), byline, headline, bodytext FROM stories ";
$where = "";
$sep = "WHERE";

if ($strKeyword != "%") {
$where .= $sep . "keyword LIKE '%" . $strKeyword . "%' ";
$sep = "AND ";
}

if ($headline != "%") {
$where .= $sep . "headline LIKE '%" . $headline . "%' ";
$sep = " AND ";
}

$query .= $where;

ERROR:
You have an error in your SQL syntax near 'LIKE '%%' AND headline LIKE '%a dearth%' ' at line 1

Please help!

eoghain

if ($strKeyword != "%") { 
   $where .= $sep . "keyword LIKE '%" . $strKeyword . "%' "; 
   $sep = "AND "; 
} 

if ($headline != "%") { 
   $where .= $sep . "headline LIKE '%" . $headline . "%' "; 
   $sep = " AND "; 
}

Why are you testing if $strKeyword and $headline = "%". If your user doesn't enter any values then they would either be not set or = "".

Maybe you should put some echo debug statements in your code to let you know what the variables are.

if ($strKeyword != "%") { 
   echo "Keyword Entry: " . $strKeyword;
   $where .= $sep . "keyword LIKE '%" . $strKeyword . "%' "; 
   $sep = "AND "; 
} 

if ($headline != "%") { 
   echo "Headline Entry: " . $headline;
   $where .= $sep . "headline LIKE '%" . $headline . "%' "; 
   $sep = " AND "; 
}

TCovert

Q: Why are you testing if $strKeyword and $headline = "%"?

A: The "%" was a mistake. I didn't mean to put that there. But, anyway I tried this code, and get the same error, I just don't see where the syntax error is coming from:

$query = "SELECT id, DATE_FORMAT(pubdate, '%Y %m-%d'), byline, headline, bodytext FROM stories ";
$where = "";
$sep = "WHERE";

if ($strKeyword != "") {
echo "Keyword Entry: " . $strKeyword;
$where .= $sep . "strkeyword LIKE '%" . $strKeyword . "%' ";
$sep = "AND ";
}

if ($headline != "") {
echo "Headline Entry: " . $headline;
$where .= $sep . "headline LIKE '%" . $headline . "%' ";
$sep = " AND ";
}

$query .= $where;

eoghain

Have you put in the debugging echo statements? Put these in and add one to output the entire query once it's been built so we can look at what has been happening. If you are getting inside your check statements then we need to know why.

jwalk76

i'm not sure if this is leading to your particular error, but i might re-write this line:

$query = "SELECT id, DATE_FORMAT(pubdate, '%Y %m-%d'), byline, headline, bodytext FROM stories WHERE bodytext LIKE '" . $strKeyword . "' AND pubdate BETWEEN '$strFrom' AND '$strTo' ORDER BY DATE_FORMAT(pubdate, '%Y, %m, %d') AND page DESC LIMIT 2000";

like this:

$query = "SELECT id, DATE_FORMAT(pubdate, '%Y %m-%d') as thedate, byline, headline, bodytext FROM stories WHERE bodytext LIKE '" . $strKeyword . "' && pubdate BETWEEN '$strFrom' AND '$strTo' ORDER BY pubdate, page DESC LIMIT 2000";

you need to give your DATE_FORMAT field a name so it can be referenced later on
also, just order the returned query by pubdate instead of your newly named DATE_FORMAT field
also, replace the last AND in your query with a comma
i replicated this as best as i could with a local database and it worked

TCovert

eoghain:

It works more now. Something I need to know:

Does the LIKE operator look for any word contained in the given field that is similar to the one searched by?

Because I search for a word "dearth" in the field bodytext and it comes back with nothing. And I know I have a record in the database that contains this word in that field.

What do you think?

Also, how would I add, to the end of every query, the dates to search between, if a date selection was made?

Let me know, thanks.

halfelven

Hi,
I believe the most efficient way to do a search is by using FallenCrew's SQL Query Parser,
http://www.fallencrew.com
It will construct your search query and supports advance search options such as the '+','-','|' and double quotes to support phrase searches.
Cheers,
Keller

eoghain

The SQL Like operator only looks for words matching your query.

So if I had the above line in my database somewhere and I searched on '%SQL%' then I'd find that line, but if I searched for '%sql%' I wouldn't find it. Also if I happened to search for '%match%' that line would be found also even though the word is 'matching'.

As for adding a set of between dates, you'd just need to do the same thing you been doing, but you'll have to check for both a start and an end date.

if ($startDate != "" || $endDate != "") {
   if ($startDate != "" && $endDate == "") {
      $where = $sep . "entryDate > '" . $startDate . "'";
      $sep = "AND";
   }
   else if ($startDate == "" && $endDate != "") {
      $where = $sep . "entryDate < '" . $endDate . "'";
      $sep = "AND";
   }
   else if ($startDate != "" && $endDate != "") {
      $where = $sep . "entryDate BETWEEN '" . $startDate . "' AND '" . $endDate . "'";
      $sep = "AND";
   }
}

TCovert

The code works great...if you just search by date. But, if you search by date and another field at the same time, it errors. And if you just search by another field, and not date, it errors. Any suggestions?

eoghain

Do me a favor and post the final queries, plus the data that you entered to make those queries appear. The one that works as well as the ones that don't. Also post the full code so I can see what is happening. Trying to debug someone elses code without ever having seen it, or being able to run my own tests on it is very difficult.

TCovert

Here is the full code that creates the query:

$query = "SELECT id, DATE_FORMAT(pubdate, '%Y %m-%d'), byline, headline, bodytext FROM stories ";
$where = "";
$sep = "WHERE ";

if ($strKeyword != "") {
$where .= $sep . "bodytext LIKE '" . $strKeyword . "' ";
$sep = "AND ";
}

if ($headline != "") {
$where .= $sep . "headline LIKE '" . $headline . "' ";
$sep = " AND ";
}

if ($bodytext != "") {
$where .= $sep . "bodytext LIKE '" . $bodytext . "' ";
$sep = " AND ";
}

if ($byline != "") {
$where .= $sep . "byline LIKE '" . $byline . "' ";
$sep = " AND ";
}

if ($section != "") {
$where .= $sep . "section LIKE '" . $section . "' ";
$sep = " AND ";
}

if ($page != "") {
$where .= $sep . "page LIKE '" . $page . "' ";
$sep = " AND ";
}

if ($keywords != "") {
$where .= $sep . "keywords LIKE '" . $keywords . "' ";
$sep = " AND ";
}

if ($series != "") {
$where .= $sep . "series LIKE '" . $series . "' ";
$sep = " AND ";
}

if ($caption != "") {
$where .= $sep . "caption LIKE '" . $caption . "' ";
$sep = " AND ";
}

if ($strFrom != "" || $strTo != "") {
if ($strFrom != "" && $strTo == "") {
$where = $sep . "pubdate > '" . $strFrom . "'";
$sep = "AND";
}
else if ($strFrom == "" && $strTo != "") {
$where = $sep . "pubdate < '" . $strTo . "'";
$sep = "AND";
}
else if ($strFrom != "" && $strTo != "") {
$where = $sep . "pubdate BETWEEN '" . $strFrom . "' AND '" . $strTo . "'";
$sep = "AND";
}
}

$query .= $where;

$storyfile = tempnam ('/projects/sunarchive/htdocs/temp2', 'query');

$fp = fopen($storyfile,'w');
if($fp)
{
fwrite($fp,$query);
}
else
{
die('Could not open file.');
}

When I run a query using just the Date, it works, and outputs this Query string:

SELECT id, DATE_FORMAT(pubdate, '%Y %m-%d'), byline, headline, bodytext FROM stories WHERE pubdate BETWEEN '2002-07-01' AND '2002-08-30'

When I do a query using the Date and the Byline, it doesn't work, and outputs this Query string:

SELECT id, DATE_FORMAT(pubdate, '%Y %m-%d'), byline, headline, bodytext FROM stories AND pubdate BETWEEN '2002-07-01' AND '2002-08-30'

When I do a query using just the Byline and not the date, it outputs this query string:

SELECT id, DATE_FORMAT(pubdate, '%Y %m-%d'), byline, headline, bodytext FROM stories AND pubdate BETWEEN '1900-01-01' AND '2002-12-31'

Let me know if there is anything else you need. Thanks again for your help!

eoghain

Well here are your problems:

SELECT id, DATE_FORMAT(pubdate, '%Y %m-%d'), byline, headline, bodytext FROM stories AND pubdate BETWEEN '2002-07-01' AND '2002-08-30'

SELECT id, DATE_FORMAT(pubdate, '%Y %m-%d'), byline, headline, bodytext FROM stories AND pubdate BETWEEN '1900-01-01' AND '2002-12-31'

Both of these queries are formatted incorrectly as opposed to you date query:

SELECT id, DATE_FORMAT(pubdate, '%Y %m-%d'), byline, headline, bodytext FROM stories WHERE pubdate BETWEEN '2002-07-01' AND '2002-08-30'

The invalid queries are formatted like this:
SELECT
FROM
AND

Where you valid query is formatted correctly:
SELECT
FROM
WHERE

So why are these other queries not geting the WHERE part of the query built? I'll tell you where, right here:

if ($strFrom != "" || $strTo != "") { 
   if ($strFrom != "" && $strTo == "") { 
      $where = $sep . "pubdate > '" . $strFrom . "'"; 
      $sep = "AND"; 
   } 
      else if ($strFrom == "" && $strTo != "") { 
      $where = $sep . "pubdate < '" . $strTo . "'"; 
      $sep = "AND"; 
   } 
   else if ($strFrom != "" && $strTo != "") { 
      $where = $sep . "pubdate BETWEEN '" . $strFrom . "' AND '" . $strTo . "'"; 
      $sep = "AND"; 
   } 
}

You forgot the . in the .= that should appear when you are building your $where clause. You shouldn't have just cut and pasted my code, you should have tried to understand what it was doing. I wasn't giving you code that would fit into your program, just code that showed you how to do what you were asking. But since you are just cutting and pasting, replace your date code with this:

if ($strFrom != "" || $strTo != "") { 
   if ($strFrom != "" && $strTo == "") { 
      $where .= $sep . "pubdate > '" . $strFrom . "'"; 
      $sep = "AND"; 
   } 
      else if ($strFrom == "" && $strTo != "") { 
      $where .= $sep . "pubdate < '" . $strTo . "'"; 
      $sep = "AND"; 
   } 
   else if ($strFrom != "" && $strTo != "") { 
      $where .= $sep . "pubdate BETWEEN '" . $strFrom . "' AND '" . $strTo . "'"; 
      $sep = "AND"; 
   } 
}

TCovert

Well, what I do is look over your code, and see what it is doing, and then, yes, I copy and paste it into my code. Then, if something doesn't work, I try and troubleshoot it myself, looking at the Query output, etc. But, if I can't figure it out, I come and ask you for assistance.

I am learning, as I go...I am new to this. And I appreciate your help.

Anyway, that worked, thank you.

I had a question about searching for words inside of a record field.

I have a byline field, in one of the records that field is "Trent R. Covert". Now, when I do a search on that field for "Trent" it dosn't bring anything up. But, if I do a search for "Trent R. Covert" it brings the records up that have "Trent R. Covert" in the byline field.

Do you have any ideas on this? I would like to be able to search for trent and get all records that have that word in the specified field.

eoghain

I know what it's like to be new, sorry I got a little frustrated since it was such a small problem.

As for searching for just Trent, using LIKE '%Trent%' should work if it doesn't then I don't know what would help.

You might want to try forcing all of your search strings to be upper or lower case so you don't have to worry about things like 'trent', 'Trent', 'TRENT', etc.

Just use a string manipulation function like strtoupper() or strtolower() in your PHP code when adding the word to your query, and change the field that is being looked at to UPPER(fieldname) or LOWER(fieldname). I'm not positive if MySQL uses those function names, so check it out, I do know that it does have functions that perform these manipulations.