Iqbal ha scritto:
Hi
I have a query which select the correct prefix from the lcr table, but have realised that it my be incorrect, is anyone else using something similiar for rating SELECT prefix from lcr WHERE 'sip:1234@sip.domain.com' LIKE lcr.from_uri AND '35387318220' LIKE CONCAT(lcr.prefix, '%') ORDER BY CHAR_LENGTH(lcr.prefix), lcr.priority DESC, RAND() ;
Now this returns
+--------+ | prefix | +--------+ | 353 | | 3538 | | 35387 | +--------+
Now I assume you should pick the one with the best match, i.e at the bottom, if so should the query not have ASC no desc in the query, rather than having to add another line which takes all result and sees length of each.
I do this way (this is a python prototype of a personalized cdr_mysql.so for asterisk, anyway you should catch the algorithm ...
for i in range(len(calledNum)-6): prefix = calledNum[5:i+7] queryRate = 'SELECT '+accountCode+' FROM rates WHERE prefix LIKE ''+prefix+'%' OR prefix = ''+prefix+''' dbCursor.execute(queryRate) numeroRighe = int(dbCursor.rowcount) if numeroRighe == 1: break elif numeroRighe == 0: queryRate = 'SELECT '+accountCode+' FROM rates WHERE prefix = ''+prefix[0:len(prefix)-1]+''' dbCursor.execute(queryRate) break
rate = dbCursor.fetchone() rate = rate[0]