RE: [PHP] searching a MySQL database
| From: | John Vanderbeck | Date: | Sun, 06 May 2001 15:15:56 +0000 |
| Subject: | RE: [PHP] searching a MySQL database | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-51623@lists.php.net to get a copy of this message | ||
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<BR><BR>\n";
echo "<FORM ACTION=$PHP_SELF METHOD=post>\n";
echo "<INPUT TYPE=hidden NAME=mode VALUE=\"search\">\n";
echo "<B>Keyword: </B><INPUT TYPE=text
NAME=searchkeyword><BR>\n";
echo "<INPUT TYPE=submit VALUE=\"Search\"<BR>";
echo "</FORM>\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 "<BR>\n";
echo "Your search results:<HR>\n";
$numRows = mysql_num_rows($result);
echo "Your keyword matched <I>$numRows</I> articles.<BR><BR>\n";
while ($row = mysql_fetch_assoc($result))
{
if ($row["article_local"] == 0)
{
// build link
$title = "<A HREF=\"".$row["article_url"]."\"
TARGET=\"_blank\">".$row["article_title"]."</A>";
}
else
{
// build link
$title = "<A
HREF=\"http://gamedesign.incagold.com/displayarticle.php?mode=article&id=".$
row ["article_id"]."\">".$row["article_title"]."</A>";
}
echo $title."<BR>\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:billt@ifelse.org]
> Sent: Sunday, May 06, 2001 11:10 AM
> To: php-general@lists.php.net
> 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: php-general-unsubscribe@lists.php.net
> For additional commands, e-mail: php-general-help@lists.php.net
> To contact the list administrators, e-mail: php-list-admin@lists.php.net
>