Page 1 of 1

how to perform this query ??

Posted: Fri Mar 30, 2007 1:32 am
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 !!

Posted: Fri Mar 30, 2007 1:39 am
by Luke
I don't get it. What's "ie"?

Posted: Fri Mar 30, 2007 1:52 am
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

Posted: Fri Mar 30, 2007 2:07 am
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...)

Posted: Fri Mar 30, 2007 2:11 am
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.

Posted: Fri Mar 30, 2007 6:58 am
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));

Posted: Fri Mar 30, 2007 7:07 am
by PHPycho
Thanks for the solution that was what i was llooking for..
Anyway thanks to all of you
Cheers

Posted: Fri Mar 30, 2007 7:32 am
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."

Posted: Fri Mar 30, 2007 1:52 pm
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