using regex with mysql

[jeger003]

hello,

i have a search on my site that matches a users ISBN entered with the one in my database

 

i want to figure out a way to use regex to match isbn numbers only

 

ie

SELECT * FROM table WHERE isbn REGEX(numbers) $user_entered_isbn

 

so say that the database has an isbn stored as ISBN97345444 whatever and the user enters 97345444 in the search

i want this to be a match of numbers

 

is this possible??

 

thank you guys

[jeger003]

is there a way to get this to work to match only numbers?

 

$query_search_exact_match = mysql_query("SELECT 
							nvc_classifieds.title, 
							 nvc_classifieds.id,
							nvc_classifieds.description, 
							 nvc_classifieds.search_text, 
							 nvc_classifieds.image, 
							nvc_classifieds.date, 
							nvc_classifieds.price,
							nvc_classifieds.location_city,
							nvc_classifieds_ads_extra.name,
							nvc_classifieds_ads_extra.value,
							nvc_classifieds_ads_extra.classified_id
   
									FROM   	nvc_classifieds_ads_extra
									JOIN 	nvc_classifieds 
									ON 		nvc_classifieds.id = nvc_classifieds_ads_extra.classified_id
									WHERE  	nvc_classifieds_ads_extra.name = 'ISBN%3A'
									AND 	live=1 
									AND  	(nvc_classifieds_ads_extra.value 
									REGEXP '[0-9] $search_isbn') 
									") or die(mysql_error());

 

using REGEXP - i want it to match only numbers but It does not

 

anyone have any ideas?

[fortnox007]

I am not sure if you can place the php variable inside the regexp

Atleast if i follow what they do here:

http://www.go4expert.com/forums/showthread.php?t=2337

Hope this helps 

[.josh]

I'm not convinced you need regex for this...regex is pattern matching... it looks like you are wanting to select info from the database based on a number the user enters in...for example, you have the following:

 

[pre]

column: ISBN

------------

  row: ISBN97345444

[/pre]

 

user enters in "97345444" and this value is in $number

 

You would just do something like (simplified sql statement):

 

$sql = "SELECT * FROM TABLENAME WHERE ISBN = 'ISBN{$number}'";

[fortnox007]

'~^(ISBN)[0-9]{8,10}$~'

 

IS this maybe something for your regexp?

It must start with letters ISBN

followed by digits with a length of minimal 8 maximum 10 (lol i have no idea what the standard length is but maybe you do

 

-edit, don't look at my post, i am still new to this

[.josh]

Well in general your pattern is fine (except for the questionable range...I'm not sure how many numbers an ISBN can be either), except for I'm not 100% but I don't think SQL regex requires delimiters... but anyways, it seems to me except for the whole ISBN prefix he is looking for an exact match, not a pattern match...

[fortnox007]

Yeah, your method is better, a pattern would not be needed here