PHP Query

[jaykappy]

I Have a query that is working fine...but I want to create a new one that is build for a search function.

I want to search for a value in multiple fields and as a wild card (any part of the field value)

 

I have been using this query:

 

 
$query = " 
SELECT 
Id,Yr,ProjectName,Department,Owner,MaplibraryName,
Description,MaplibraryPath,PageSize,Scale,LastModified,
Notes,ModifiedBy,FullPath,Path_jpg,PDF_Path,JPG_Path
FROM tbl_maplibrary
WHERE 
Department = :dept
ORDER BY 
Yr DESC
"; 

 

How do I modify it to WHERE Department CONTAINS :dept OR Field1 CONTAINS :dept OR Field2 CONTAINS :dept

 

Any help would be greatly appreciated.

Thanks

Edited March 19, 2013 by jaykappy

[fooDigi]

Might have better luck posting this in the mysql forum

http://forums.phpfreaks.com/forum/15-mysql-help/

[Jessica]

I'll move it.

 

OP: What exactly is the problem? You're unable to type the word OR?

 

Did you try using LIKE() with the Wildcard? (%)

Edited March 19, 2013 by Jessica

[fooDigi]

but you can use the '%' like

 

 

WHERE Department LIKE '%:dept%'

Edited March 19, 2013 by fooDigi

[jaykappy]

 

but you can use the '%' like

 

 

WHERE Department LIKE '%:dept%'

Thank you fooDigi....was look for the correct syntax..... 

 

So I assume I can string these together?

 

WHERE Department LIKE '%:dept%'  OR Field1 LIKE '%:dept%' OR Field2 LIKE '%:dept%'

 

Thanks for your time and resources.....

[Psycho]

Based upon a couple quick searches I did, putting the parameter between percent symbols in the prepared statement does not work. The solutions I found were to either append the percent symbols to the value being passed as the parameter OR to concat the percents in the query.

 

http://stackoverflow.com/questions/1786436/php-pdo-prepared-statement-mysql-like-query

http://board.phpbuilder.com/showthread.php?10368514-RESOLVED-mysqli-gt-prepare%28-WHERE-LIKE-%29

 

Since you are using the term multiple places, I would add the symbols to the value before passing it in as a parameter

 

 

$query = "
SELECT Id,Yr,ProjectName,Department,Owner,MaplibraryName,
       Description,MaplibraryPath,PageSize,Scale,LastModified,
       Notes,ModifiedBy,FullPath,Path_jpg,PDF_Path,JPG_Path
FROM tbl_maplibrary
WHERE Department LIKE :dept
   OR Field1 LIKE :dept
   OR Field2 LIKE :dept
ORDER BY Yr DESC";

$prep = $dbh->prepare($query);
$ret = $prep->execute(array(':dept' => "%{$department}%"));

Edited March 19, 2013 by Psycho

[Jessica]

Yeah you can't put them in the query because then it would look like '%'data'%' because the prepared statement adds the quotes for you.

[jaykappy]

Thank you once again for the help..very appreciated.....trying to learn here...

 

So If I do this:

 

 

window.location.href = "/MainPageindex.php?Value=" + $("#searchvalue").val();

 

Then in my MainPageindex.php page do this:  I DONT think that I have to correct syntax around the GET Statement

 

 

 
 
$query = " 
SELECT 
Id,Yr,ProjectName,Department,Owner,MaplibraryName,
Description,MaplibraryPath,PageSize,Scale,LastModified,
Notes,ModifiedBy,FullPath,Path_jpg,PDF_Path,JPG_Path
FROM tbl_maplibrary
WHERE 
Department LIKE :Value
OR Field1 LIKE :Value
OR Field2 LIKE :Value
ORDER BY 
Yr DESC
 
 
$stmt = $db->prepare($query); 
//$stmt->execute();
$stmt->execute(array(":Value" => "% $_GET['Value'] %" ));  

[Jessica]

Surround the $_GET in {}.

And close your query string.

Edited March 19, 2013 by Jessica

[Psycho]

 . . . and don't put extraneous spaces between the percent signs and the value - otherwise the data will only match on records where the search value exists with spaces on either side. Plus, I always trim such data from the user as well.

 

$searchValue = trim($_GET['Value']);
stmt->execute(array(":Value" => "%{$searchValue}%" )); 

[jaykappy]

fooDigi, Jessica, Psycho thank you very much...your help was very appreciated...

Credit to all 3 of you....

 

MainPageindex.php

 

 
$(".Searchbutton").click(function(){
if( !$('#searchvalue').val() ) {
alert("Please Enter a Search Value");
}
else{
alert("You are searching for: " + ($('#searchvalue').val())) ;
window.location.href = "/MainPageindexSearch.php?ItemValue=" + $("#searchvalue").val(); 
}
}); 

 

MainPageindexSearch.php

 

 

 
$query = " 
SELECT 
Id,Yr,ProjectName,Department,Owner,MaplibraryName,
Description,MaplibraryPath,PageSize,Scale,LastModified,
Notes,ModifiedBy,FullPath,Path_jpg,PDF_Path,JPG_Path
FROM tbl_maplibrary
WHERE 
Description LIKE :Values
OR MaplibraryName LIKE :Values
ORDER BY 
Yr DESC
"; 
 
$stmt = $db->prepare($query); 
$searchValue = trim($_GET['ItemValue']);
$stmt->execute(array(":Values" => "%{$searchValue}%" ));