ganesh_g1982 Posted March 13, 2006 Share Posted March 13, 2006 Hi all,Please help me to find a solution to this problme.i am having a company database, and i m using php to retrive the data fromt he tables. i am able to search on the normal way like,"select * from company where name like 'abcd%' order by name"but i have an entry in the table, "123popular". i need to search and find this..Which query i shud use for this. Alsi i would like to know, if the search string contails more than one word, what is the query to search each entry in the table matching for all the words entered by the user. Please help me!!!. TnxGanesh G Nair Quote Link to comment https://forums.phpfreaks.com/topic/4805-to-retrieve-the-strings-starting-with-numeric-digits/ Share on other sites More sharing options...
wickning1 Posted March 13, 2006 Share Posted March 13, 2006 First question: You can use the REGEX comparison:[code]SELECT * FROM company WHERE name REGEX '[[:digit:]].*'[/code]Second question: Use the MATCH AGAINST syntax, e.g.[code]SELECT * FROM company WHERE MATCH(name) AGAINST ('userinput')[/code]You'll probably want to create a fulltext index to make that fast. Quote Link to comment https://forums.phpfreaks.com/topic/4805-to-retrieve-the-strings-starting-with-numeric-digits/#findComment-16904 Share on other sites More sharing options...
ganesh_g1982 Posted March 13, 2006 Author Share Posted March 13, 2006 [!--quoteo(post=354420:date=Mar 13 2006, 11:00 AM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 13 2006, 11:00 AM) [snapback]354420[/snapback][/div][div class=\'quotemain\'][!--quotec--]First question: You can use the REGEX comparison:[code]SELECT * FROM company WHERE name REGEX '[[:digit:]].*'[/code]Second question: Use the MATCH AGAINST syntax, e.g.[code]SELECT * FROM company WHERE MATCH(name) AGAINST ('userinput')[/code]You'll probably want to create a fulltext index to make that fast.[/quote]First one its showing errors. i wish to check the names starting not only wiht one digit, 0-9. And the code you gave, i tried, its not running. error.And the second its working, but another problem also came. when i search part of a word(eg. comp) it will not display anyhting. But if i search with computer, it will display. is there any possibilie to use % % around the search string so that even i type "c", it will display computers,catridges,cameras etc. like that.Please help me on solving this. and i wish you explain the first once again. Thank you very much.Ganesh G Nair Quote Link to comment https://forums.phpfreaks.com/topic/4805-to-retrieve-the-strings-starting-with-numeric-digits/#findComment-16933 Share on other sites More sharing options...
wickning1 Posted March 13, 2006 Share Posted March 13, 2006 I thought you wanted things that started with any number. If you want to search for specific numbers, that's exactly the same as with letters. For 1, "SELECT * FROM company WHERE name LIKE '1%'". The MATCH AGAINST syntax only searches for whole words, if you just wanted a substring search, just keep using LIKE: "SELECT * FROM company WHERE name LIKE '%userinput%'" Quote Link to comment https://forums.phpfreaks.com/topic/4805-to-retrieve-the-strings-starting-with-numeric-digits/#findComment-16978 Share on other sites More sharing options...
fenway Posted March 13, 2006 Share Posted March 13, 2006 Don't you need a FULLTEXT index to use MATCH... AGAINST? Quote Link to comment https://forums.phpfreaks.com/topic/4805-to-retrieve-the-strings-starting-with-numeric-digits/#findComment-17110 Share on other sites More sharing options...
ganesh_g1982 Posted March 14, 2006 Author Share Posted March 14, 2006 hi,no, i want to retrieve all the streing starting with the numeric digits. but the first syntax is not working.ie, SELECT * FROM company WHERE name REGEX '[[:digit:]].*'do i need to modify anyhting on inside '' (after REGEX). i just changed the table name and tried. it is not working and error is,#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REGEX '[[:digit:]].*' LIMIT 0, 30' at line 1 And for the other, i wish to get a result even when a user enters a part of a word, or more than one word. Ofcourse a good search query which can take out results if there any type of a match with the search string. please advice me. Quote Link to comment https://forums.phpfreaks.com/topic/4805-to-retrieve-the-strings-starting-with-numeric-digits/#findComment-17263 Share on other sites More sharing options...
wickning1 Posted March 14, 2006 Share Posted March 14, 2006 Sorry, typo. It's REGEXP, not REGEX. Stupid SQL monkeys. Quote Link to comment https://forums.phpfreaks.com/topic/4805-to-retrieve-the-strings-starting-with-numeric-digits/#findComment-17358 Share on other sites More sharing options...
ganesh_g1982 Posted March 15, 2006 Author Share Posted March 15, 2006 hi,thanks its working. now i am able to query all string which contains numbers on it.I would like to get the assistance for the search query also. i do like to follow with like ('%') operator because on my website , there are more chances to search by characters.not full words. so if you use match against query it may not work. But still i wud like to know if the users enter 2-3 words together, how we could check it with each database entires?Any solutions?Help!!!!!ThanksGanesh G Nair Quote Link to comment https://forums.phpfreaks.com/topic/4805-to-retrieve-the-strings-starting-with-numeric-digits/#findComment-17692 Share on other sites More sharing options...
fenway Posted March 15, 2006 Share Posted March 15, 2006 What do you mean by together? You mean match ALL of the words, or match them as a phrase? Quote Link to comment https://forums.phpfreaks.com/topic/4805-to-retrieve-the-strings-starting-with-numeric-digits/#findComment-17872 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.