FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Creating a Search Script with PHP for MySQL





Shike
I have a dilemma. I am wishing to create a search script to search my database. I have developed a semi complicated search form and wish to use it (multiple data fields, dropdowns for searching particular columns, Boolean Searching, etc) Does anyone know a good place to go to get information on this?

I, believe, that it is possible, but I am quite new at php & msql, and scripting for that matter. Any help would be appreciated.
Ironz
you can search through here to find some usefull scripts: http://www.hotscripts.com/search/10853202.html

or you can look here for a simple search:
http://arutha.co.uk/viewtut/86/


Quote:
Description: DaDaBIK is a PHP application that allows you to easily create a highly customizable front-end for a database. The strength of DaDaBIK lies in its ability of customization. For each field of a table you can choose: if the field should be included or not in the search/insert/update form; the form label; the content of the field; the input type; the possible values, also driven from another table. Other features: files uploading, checking for possible duplication during an insert, authentication, customizable graphic layout to enable embedding of the forms in your own site, export to CSV. Available in several languages.

Very good: http://www.hotscripts.com/Detailed/13114.html
Shike
Ok, the tutorials aren't cutting it.

I have a search from with 6 different "term" fields. 2 differen't AND/OR/NOT fields, and 3 different column fields.

Basicly it goes
search for term1(text field) in column1(dropdown)
AND/OR/NOT (dropdown) Term2(text field) in column2(dropdown)
AND/OR/NOT(dropdown) Term3(text field) in column(dropdown)

(ANd) Term4(dropdown) (ANd) term5(dropdown) (and) term6(dropdown)


I had thought to use $sql = "SELECT * FROM dnd WHERE $type1 LIKE '$term1' $op1 $type2 LIKE '$term2' etc.


here is a link to what I want things to look like.

Also, I want to support boolean (and/or/not) searching w/in the text fields.

Yeah, I know its askin' a lot any help would be greatly appreciated.
Philip
all u need is to know how sql statement work is.
like Shike said.

$type1="subject";
$type2="message";
$term2="Php Programming";
$term2="how to use mysql syntax";

$sql_statement_ here="SELECT * FROM table WHERE $type1 LIKE '$term1' or $type2 LIKE '$term2'";
using at mysql_open($sql_statement_ here)

that how should be works..


Shike
Philip wrote:
$type1="subject";
$type2="message";
$term2="Php Programming";
$term2="how to use mysql syntax";

$sql_statement_ here="SELECT * FROM table WHERE $type1 LIKE '$term1' or $type2 LIKE '$term2'";
using at mysql_open($sql_statement_ here)

that how should be works..


ok, That verifies what I had done. But what about having a variable for the 'or'. such as
Code:

$sql = "SELECT * FROM table WHERE $type1 LIKE 'term1' and/or/not $type2 LIKE '$term2'";


Any sggestions
Philip
or isn't a variable
it's a logical expression.


other logical expression is and

example:
select * from table where name='johan' and class='2'
select * from table where name like 'johan' and class like '2'

if using like all name have johan like johan sitepu , bardage johan will be selected.

=, <>, like, there's many more, try too search it at google.
and,or,xor many too , try too search it at google.


Shike
Philip wrote:
or isn't a variable
it's a logical expression.


other logical expression is and


Believe me, I understand this, but I want that part of the expression supplied by the form, not hard coded into the script.

[/edit]
What I truly want is for 4 logical expressions

with (as a phrase, words must exist as stated. Prefered way of indicating this is to put double quotes around the search terms)
And (all words must exist in a single record)
or (any words may exist in a single record)
Not (record does not contain words)

I want the default to be 'and' and have the option for the others.
Philip
u mean like this where (($a=$a and $b=$b) or $c=$d ) and $e>$f


deepak
Hi,

I guess you need intelligent query handling .. for example if someone enter "search OR this -nothis" then your engine should search records with SEARCH OR THIS which doesnt included NOTHIS .. .. have a look at this http://www.tsep.info/ its open source so you can implement its features into yours system. good luck.
Shike
deepak:

Thanks, I'm taking a look now.


Philip:

Yeah, I guess. I'm not completely comfortable reading the SQL query language yet.

Would that break down (in layman's terms) as

(boy OR girl) AND "Open Source" NOT Running?
Shike
Ok, I think I'm getting this down, but I'm getting an error
Quote:

Parse error: syntax error, unexpected '>' in /home/shike/domains/shike.frih.net/public_html/dndspells.php on line 227



The code in question is
Code:

 if ($numrows == 0)
  {
  echo " <h1 align='center'>Your search returned no results.</h1> ";
  echo "<p>You searched for: &quot;" . $all . "&quot;</p>";
  }


the line with the error is the first echo.
When I change it to
Code:

 if ($numrows == 0)
  {
  echo "<h1 align=\"center\">Your search returned no results.</h1>";
  echo "<p>You searched for: &quot;" . $all . "&quot;</p>";
  }


I still get the error (I believe i'm using the proper escape characters \")
any help?
ganbate
there's nothing wrong in that line.
what about the line before?
because sometimes the error is in the line before the reported line.
Philip
yap there's no wrong on your code,
may be the line before that code


Shike
I actually figured out what was wrong (at 1 am). I had forgotten to close a quote earilier.
Shike
ok, Now, I would like to attatch my file, so, here is a link to a txt version.
Philip
Shike wrote:
ok, Now, I would like to attatch my file, so, here is a link to a txt version.


what're u mean ??


Shike
I can't get the code to work properly so I am asking for help with it. I know it probably isn't all that elegant, but I would like some help getting it to work. uploading it as a txt file is the only way I can figure to get it to post w/o parsing or hiding the code.

Some discussion boards allow for file uploads to posts, Frihost has apparently turned off that feature (which I can understand, takes up more room on the server) so I uploaded it to my webspace and then linked to it.
Philip
Quote:
Some discussion boards allow for file uploads to posts, Frihost has apparently turned off that feature (which I can understand, takes up more room on the server) so I uploaded it to my webspace and then linked to it.



so where's the link ? let's help u Smile

Shike
Shike wrote:
ok, Now, I would like to attatch my file, so, here is a link to a txt version.


I put bold around the word for the link.



BTW you guys are great.
Philip
Shike wrote:
Shike wrote:
ok, Now, I would like to attatch my file, so, here is a link to a txt version.


I put bold around the word for the link.



BTW you guys are great.


which line u got the error ?
since i dont have your databases i cannot check it manually .. --"

any way at mysql_query
Code:

"SELECT cid AS $type1, $type2, $type3, components
FROM dnd WHERE
MATCH($type1) AGAINST ('-$terms1[0] -$terms1[1] -$terms1[2] -$terms1[3]' IN BOOLEAN MODE) $op1
MATCH($type2) AGAINST ('-$terms2[0] -$terms2[1] -$terms2[2] -$terms2[3]' IN BOOLEAN MODE) $op2
MATCH($type3) AGAINST ('-$terms3[0] -$terms3[1] -$terms3[2] -$terms3[3]' IN BOOLEAN MODE) AND
MATCH(components) AGAINST ('+$comp[1], +$comp[2], +$comp[3]' IN BOOLEAN MODE)
ORDER BY name, caster";


you better uses like this
select * from dnd where cid in ('$term1','$term2','#term3') order by name,caster


Marston
http://www.spoono.com/php/tutorials/tutorial.php?id=12
Shike
Philip wrote:

which line u got the error ?
since i dont have your databases i cannot check it manually .. --"


It keeps telling me I
Quote:
Parse error: syntax error, unexpected T_IF, expecting ',' or ';' in /home/shike/domains/shike.frih.net/public_html/dndspells.php on line 178
Shike
Marston wrote:
http://www.spoono.com/php/tutorials/tutorial.php?id=12


Yes, but that is actually too simple for my search. If you look at the code, you'll notice 3 searchboxes, each with a dropdown to tell it where to look. I have hard coded Booleans (AND/OR/NOT) and want the user to be able to enter boolean terms (AND/OR/NOT) there are also 3 other dropdowns which search a particular column for the specified values (Makes it easier for those values)

I wanted to be able to do all of this (and also thought I should be able to do all of this.)
Shike
Philip wrote:

you better uses like this
select * from dnd where cid in ('$term1','$term2','#term3') order by name,caster


But I want to be able to have user inputed Booleans, as well as user selected Columns.

Question:
what does 'cid' mean?
Philip
sorry but what do u mean by boolean ?
for boolean just uses like below this one

Code:
select * from table where advanced=true and beginner=false
snowboardalliance
does this help? http://www.hudzilla.org/phpbook/read.php/9_3_18
Shike
Philip wrote:
sorry but what do u mean by boolean ?
for boolean just uses like below this one

Code:
select * from table where advanced=true and beginner=false


In Library and non computer terminology a boolean search term is a connector such as AND/OR/NOT. Indicating:

And (both must be present)
OR (either can be present)
NOT (the second term cannot be present)

These would equate in MySQL as (i believe)

And "+"
Or " "
NOT "-"

Also, In most Library Catalogs and databases putting a string in double quotes (") indicates a search as a phrase (all must be present in the order specified with not words between).

The normal default for a search is an "AND" search.
Shike
snowboardalliance wrote:
does this help? http://www.hudzilla.org/phpbook/read.php/9_3_18


Thanks, this will help greatly. Smile
rohan2kool
Can anyone explain me the 'LIKE' SQL statement...
Shike
Ok, I've done some tweaking and I'm getting the page to lode (w/o it being just errors.)

http://shike.frih.net/dndspells.php is the finished product
and
http://shike.frih.net/dndspells.txt is the text file of the code.


In the "You searched for:..." part, I do not want the "AND () in components." part to show unless one of the terms was actually chosen. I thought I did that right, but apparently not.

Also, I keep getting an error on line 311 (I have lines named in notes tags).

Any Ideas???
Shike
snowboardalliance wrote:
does this help? http://www.hudzilla.org/phpbook/read.php/9_3_18



With this, it seems to me that I need to make a "FullText" index for each of my columns that I want to search. Is this True?
Shike
Shike wrote:
Ok, I've done some tweaking and I'm getting the page to lode (w/o it being just errors.)

http://shike.frih.net/dndspells.php is the finished product
and
http://shike.frih.net/dndspells.txt is the text file of the code.


1. In the "You searched for:..." part, I do not want the "AND () in components." part to show unless one of the terms was actually chosen. I thought I did that right, but apparently not.

2. Also, I keep getting an error on line 311 (I have lines named in notes tags).

Any Ideas???


Ok, I've got 1 working the way I want it. (I feel pretty good about myself there)

now when I preform a search I get this
Quote:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/shike/domains/shike.frih.net/public_html/dndspells.php on line 229


I put as a note "Problem area is here" On the line where the error is ocurring. But my guess is that something is going wrong elsewhere.

Any and all help will be appreciated.

EDIT:
For some reason it doesn't like my search. (which is now the variable $search). any help?
Shike
I got it working. Though I made some modifications to the form as well as to the script. Instead of allowing users to input booleans, I hard coded them into the search form.

http://shike.frih.net/dnd/spells.php


[edit]
There are still a few bugs. Like, how do I get a mysql statement to not allow something a search such as MATCH(name) AGAINST('+$var[1], +$var[2], ...' IN BOOLEAN MODE) NOT MATCH(name) AGAINST('+$copy[1], $copy[2]..." IN BOOLEAN MODE)

would I have to put ()'s around it? etc.?
[/edit]
Shike
Ok, I got all of it to work. Yay.
Related topics
PHP, MySQL...
ASP +PHP+MySQL Tutorials
what is your php mysql apache installer package
Need some php/mysql dlls
The Basics (php, mysql etc)
A very good PHP MySQL Tutorial
Link PHP/Mysql with Outlook Express
How to search in a MySQL - database
Free PHP/MySQL login script
php-mysql source code
PHP search function with MySQL fulltext
PHP/MySql Sending e-mail subcription confirmation to webmast
Need a Registration form PHP/Mysql with email confirmation
Search Script
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.