Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
PHPycho
Forum Contributor
Posts: 336 Joined: Fri Jan 06, 2006 12:37 pm
Post
by PHPycho » Fri Mar 30, 2007 1:32 am
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 !!
Luke
The Ninja Space Mod
Posts: 6424 Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA
Post
by Luke » Fri Mar 30, 2007 1:39 am
I don't get it. What's "ie"?
PHPycho
Forum Contributor
Posts: 336 Joined: Fri Jan 06, 2006 12:37 pm
Post
by PHPycho » Fri Mar 30, 2007 1:52 am
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 » Fri Mar 30, 2007 2:07 am
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...)
Kieran Huggins
DevNet Master
Posts: 3635 Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:
Post
by Kieran Huggins » Fri Mar 30, 2007 2:11 am
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 » Fri Mar 30, 2007 6:58 am
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));
PHPycho
Forum Contributor
Posts: 336 Joined: Fri Jan 06, 2006 12:37 pm
Post
by PHPycho » Fri Mar 30, 2007 7:07 am
Thanks for the solution that was what i was llooking for..
Anyway thanks to all of you
Cheers
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Fri Mar 30, 2007 7:32 am
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 » Fri Mar 30, 2007 1:52 pm
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