how to perform this query ??

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
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

how to perform this query ??

Post by PHPycho »

Hello forums!!
Suppose i had the following table (with fields and values)
table1
-------------------------
|id | name | description |
-------------------------
| 1 | 12 name1 bla1 | desc1 |
| 2 | 13 name2 bla2 | desc2 |
|...so on
--------------------------
My question is: How to perform the query if i would like to fetch the row which matches the no (which is in conjuction with the name followed by space) with the passed no ie

Code: Select all

$query  = "SELECT * FROM `table1` WHERE [no part of the name ie 12] = [passed no ie 12]";
Any suggestions , tips are warmly welcome
Thanks in advance to all of you !!
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

I don't get it. What's "ie"?
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

Post by PHPycho »

Thanks for the reply
I don't get it. What's "ie"?
ie is just the example to state that if the no 12 is passed...
Ok i will remove that ..if that confuses..
Please tell me one thing..
One more Question:
1> Is there any string function in mysql like explode in php so that i can find the first string separated by space or anyway to find the first string of the space separated string..

thanks once again for the reply
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Have you considered moving the part of the value to a separate column?

Anyway, at http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html is the page you need to consult (in this situation i believe SUBSTR is what you're looking for...)
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

I think you want the LIKE comparison operator:

Code: Select all

$query  = "SELECT * FROM `table1` WHERE `name` LIKE '12 %'";
But I agree that if you're matching this data it should be in a separate column... even if it's duplicated. Also then you'll have the added bonus of being able to index it for fast searches.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

This is a feature i find pretty cool: Creating an index on the first 10 characters of the name column:

CREATE INDEX idx_part_of_name ON customer (name(10));
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

Post by PHPycho »

Thanks for the solution that was what i was llooking for..
Anyway thanks to all of you
Cheers
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

useless trivia:

i.e. stands for id est. Latin that generally translates to "for instance."

e.g. stands for exempli gratia. Latin that generally translates to "for example."
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

feyd wrote:useless trivia:

i.e. stands for id est. Latin that generally translates to "for instance."

e.g. stands for exempli gratia. Latin that generally translates to "for example."

for the non-native english speakers :)

ie: in example
eg: example given
Post Reply