RE: [PHP] searching a MySQL database
Well, my site currently does not search based on multiple keywords, but
rather just ONE keyword. This makes it easiers, as I offload all the work
to mySQL.
However, theoreticly, you could do the same things with a complex SQL query.
[CODE]
case 'searchform':
{
echo "Search for Article\n";
echo "\n";
echo "\n";
echo "Keyword: \n";
echo "";
echo "\n";
break;
}
case 'search':
{
$link = db_connect();
$query = "SELECT article_id,article_title,article_local,article_url FROM
Articles WHERE article_keywords LIKE'%$searchkeyword%'";
$result = mysql_query($query, $link);
echo "SQL Result:";
echo mysql_error($link);
echo "\n";
echo "Your search results:\n";
$numRows = mysql_num_rows($result);
echo "Your keyword matched $numRows articles.\n";
while ($row = mysql_fetch_assoc($result))
{
if ($row["article_local"] == 0)
{
// build link
$title = "".$row["article_title"]."";
}
else
{
// build link
$title = "http://gamedesign.incagold.com/displayarticle.php?mode=article&id=".$
row ["article_id"]."\">".$row["article_title"]."";
}
echo $title."\n";
}
break;
}
[/CODE]
- John Vanderbeck
- Admin, GameDesign (http://gamedesign.incagold.com/)
- GameDesign, the industry source for game design and development issues
> -Original Message-
> From: bill [mailto:[EMAIL PROTECTED]]
> Sent: Sunday, May 06, 2001 11:10 AM
> To: [EMAIL PROTECTED]
> Subject: Re: [PHP] searching a MySQL database
>
>
> On Sun, 6 May 2001, Jamie Saunders wrote:
>
> > Hi,
> >
> > I've set up a MySQL database and an HTML search form. I'd like
> to know how
> > to search the database with whatever it entered into the form. It only
> > needs to be a simple search, returning anything that matches the word(s)
> > entered into the input box in the form. Thanks.
>
> Yes, I would like to know how people are doing this too. I have a database
> where columns in multiple tables are searched for multiple keywords. The
> statement ends up lookup like this for the entered keyword: 'this or that'
> (the REGEXP prevents partial word matches, like 'hunk' in 'chunky') if you
> want the source for this part, email me privately...
>
> SELECT dates.date, dates.time, dates.city, dates.location, dates.contact,
> dates.phone, dates.eventid, dates.id ,events.title, events.descrip,
> events.speaker, events.attr, events.id
>
> FROM dates, events
>
> WHERE dates.eventid = events.id
> AND (
> (
> ( dates.city REGEXP '[[:<:]]this[[:>:]]' )
>OR (dates.location REGEXP '[[:<:]]this[[:>:]]' )
>OR (events.title REGEXP '[[:<:]]this[[:>:]]' )
>OR (events.descrip REGEXP '[[:<:]]this[[:>:]]' )
>OR (events.speaker REGEXP '[[:<:]]this[[:>:]]' )
>OR (events.attr REGEXP '[[:<:]]this[[:>:]]' )
> )
> OR (*note) (
> ( dates.city REGEXP '[[:<:]]that[[:>:]]' )
>OR (dates.location REGEXP '[[:<:]]that[[:>:]]' )
>OR (events.title REGEXP '[[:<:]]that[[:>:]]' )
>OR (events.descrip REGEXP '[[:<:]]that[[:>:]]' )
>OR (events.speaker REGEXP '[[:<:]]that[[:>:]]' )
>OR (events.attr REGEXP '[[:<:]]that[[:>:]]' )
> )
> )
> AND date LIKE '2001-05%'
> ORDER BY date
>
> (*note): this would have been AND if the keyword was 'this and that'
>
> is this how it is generally done or am I way off here? :)
>
> cheers,
> bill
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> To contact the list administrators, e-mail: [EMAIL PROTECTED]
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] searching a MySQL database
On Sun, 6 May 2001, Jamie Saunders wrote: > Hi, > > I've set up a MySQL database and an HTML search form. I'd like to know how > to search the database with whatever it entered into the form. It only > needs to be a simple search, returning anything that matches the word(s) > entered into the input box in the form. Thanks. Yes, I would like to know how people are doing this too. I have a database where columns in multiple tables are searched for multiple keywords. The statement ends up lookup like this for the entered keyword: 'this or that' (the REGEXP prevents partial word matches, like 'hunk' in 'chunky') if you want the source for this part, email me privately... SELECT dates.date, dates.time, dates.city, dates.location, dates.contact, dates.phone, dates.eventid, dates.id ,events.title, events.descrip, events.speaker, events.attr, events.id FROM dates, events WHERE dates.eventid = events.id AND ( ( ( dates.city REGEXP '[[:<:]]this[[:>:]]' ) OR (dates.location REGEXP '[[:<:]]this[[:>:]]' ) OR (events.title REGEXP '[[:<:]]this[[:>:]]' ) OR (events.descrip REGEXP '[[:<:]]this[[:>:]]' ) OR (events.speaker REGEXP '[[:<:]]this[[:>:]]' ) OR (events.attr REGEXP '[[:<:]]this[[:>:]]' ) ) OR (*note) ( ( dates.city REGEXP '[[:<:]]that[[:>:]]' ) OR (dates.location REGEXP '[[:<:]]that[[:>:]]' ) OR (events.title REGEXP '[[:<:]]that[[:>:]]' ) OR (events.descrip REGEXP '[[:<:]]that[[:>:]]' ) OR (events.speaker REGEXP '[[:<:]]that[[:>:]]' ) OR (events.attr REGEXP '[[:<:]]that[[:>:]]' ) ) ) AND date LIKE '2001-05%' ORDER BY date (*note): this would have been AND if the keyword was 'this and that' is this how it is generally done or am I way off here? :) cheers, bill -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] searching a MySQL database
Hi, I've set up a MySQL database and an HTML search form. I'd like to know how to search the database with whatever it entered into the form. It only needs to be a simple search, returning anything that matches the word(s) entered into the input box in the form. Thanks. Jamie Saunders Mail: [EMAIL PROTECTED] Web: http://jamie-s.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Searching a MySQL database?
> Here are some queries of the above table with their results > bases='Ft. Worth' returns record 1 > bases LIKE '%Worth%' returns record 1 > bases LIKE '%Ft.%' returns 1 & 2 > bases LIKE '%' returns all records (in this case, 1 & 2) > > See? That makes perfect sense. Thank you! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Searching a MySQL database?
> > towns = '$town'
> > ...and...
> > towns LIKE '$town'
> >
> > ...are essentially the same without the % wildcard character. Thus,
> >
> > towns = '$town'
> > ...is much different than...
> > towns LIKE '%$town%'
>
> How different are they? I'm not even sure what a wildcard is?
A wildcard is a character that can be used to represent any character (or
any set of any character). For instance, on many operating systems the *
wildcard represents what you could call "anything". So * is anything from
the empty string ('') to anything ('Toby Butzon'). '*zon', however, would
match only strings ending in 'zon'; thus, 'Toby Butzon' would match but
'Joe Smith' would not.
In SQL, the % symbol is used as the * wildcard.
Consider the following:
(id) bases
-
(1) Ft. Worth
(2) Ft. Benning
Here are some queries of the above table with their results
bases='Ft. Worth' returns record 1
bases LIKE '%Worth%' returns record 1
bases LIKE '%Ft.%' returns 1 & 2
bases LIKE '%' returns all records (in this case, 1 & 2)
See?
> And thanks ;)
Of course.
--Toby
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Searching a MySQL database?
> You've basically got it... the advantage of LIKE is that you can add > wildcards to specify what can be different... > > towns = '$town' > ...and... > towns LIKE '$town' > > ...are essentially the same without the % wildcard character. Thus, > > towns = '$town' > ...is much different than... > towns LIKE '%$town%' How different are they? I'm not even sure what a wildcard is? And thanks ;) James. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Searching a MySQL database?
You've basically got it... the advantage of LIKE is that you can add wildcards to specify what can be different... towns = '$town' ...and... towns LIKE '$town' ...are essentially the same without the % wildcard character. Thus, towns = '$town' ...is much different than... towns LIKE '%$town%' --Toby -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] Searching a MySQL database?
Can anyone tell me what I would use to query a MySQL database in a search? If the search field, was for example, a variable like "town", would the results page use something like this? : $sql = " SELECT * FROM table_name WHERE towns = \"$town\" "; I remember seeing someone post something like this: $sql = " SELECT * FROM table_name WHERE towns LIKE \"$town\" "; so if the search word is not EXACTLY like a row in the database, it may return results to partial words. Thanks in advance, James. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
