searching for a part of a field in mySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

searching for a part of a field in mySQL

Post by deejay »

say for example I have a mySQL field that is a blog and i would like to find all blogs that contain that word how do I do it?

Thanks in advance
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

Post by deejay »

thank you for the help.

I dont really unberstand why the syntax

Code: Select all

"SELECT * FROM mainNewsItem WHERE item='*$search_keyword*'
doesn't work?

:?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Because * isn't a wildcard in MySQL but % is and you would use LIKE not = for this sort of comparison so try:

Code: Select all

SELECT * FROM mainNewsItem WHERE item LIKE '%$search_keyword%'
Look at:
http://www.mysql.com/doc/en/String_comp ... ml#IDX1209

Mac
User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

Post by deejay »

thank, but for some reason that i cant work out this doesnt work in my script. consider

this works

Code: Select all

<?php
$query = "SELECT * FROM mainNewsItem WHERE item='$search_keyword' OR header='$search_keyword' OR short_text='$search_keyword' OR text='$search_keyword' OR author='$search_keyword' ORDER BY date DESC ";

?>
albeit that only if you enter the whole field. Accoring to mySql.com this should work

Code: Select all

<?php

$query = "SELECT * FROM mainNewsItem LIKE item='%$search_keyword%' OR header='%$search_keyword%' OR short_text='%$search_keyword%' OR text='%$search_keyword%' OR author='%$search_keyword%' ORDER BY date DESC ";
?>
but returns the 'error making query' error.

I bet its something simple I'm overlooking again but cant think what.

Thanks

Deej


?>
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post by m3mn0n »

If you want to bring multiple results to a user, i suggest using AND instead of OR in your query line, maybe that is the problem? :wink:
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

You should be using LIKE instead of = as I stated before, and you also need a WHERE clause so instead of:

Code: Select all

$query = "SELECT * FROM mainNewsItem LIKE item='%$search_keyword%' OR header='%$search_keyword%' OR short_text='%$search_keyword%' OR text='%$search_keyword%' OR author='%$search_keyword%' ORDER BY date DESC ";
you should have something like:

Code: Select all

$query = "SELECT * FROM mainNewsItem WHERE item LIKE '%$search_keyword%' OR header LIKE '%$search_keyword%' OR short_text LIKE '%$search_keyword%' OR text LIKE '%$search_keyword%' OR author LIKE '%$search_keyword%' ORDER BY date DESC ";
Mac
User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

Post by deejay »

Thanks I now have this working.

In reply to Oromians post above I tried AND instead of OR and it did not give me any results as I think it was looking for the requested keywords in all fields. using OR does give multiple results. Thanks for help anyway tho :-)
Post Reply