Advanced MySQL Query & Tab-Delimited Value
6 posts by 3 authors in: Forums > CMS Builder
Last Post: August 24, 2012 (RSS)
By Perchpole - August 24, 2012
Here's a brain teaser...
I'm trying to construct an advanced MySQL query for a list in the editor.
I need to test whether any of the numbers in a tab-delimited string match a selected number.
I've tried all the usual methods...
SELECT * FROM `<?php echo $TABLE_PREFIX ?>table` WHERE 'string' LIKE '%number%'
SELECT * FROM `<?php echo $TABLE_PREFIX ?>table` WHERE 'string' LIKE '%\tnumber\t%'
...but nothing seems to work.
Have I got it the wrong way around?
:0/
Perchpole
Re: [Perchpole] Advanced MySQL Query & Tab-Delimited Value
By Jason - August 24, 2012
Is "string" a multi value field in the database, or a string that was already taken from the database?
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] Advanced MySQL Query & Tab-Delimited Value
By Perchpole - August 24, 2012
The "string" was formed as a result of a multi-select list - created in another table/editor.
As I understand it, this would create a tab-delimited string of values.
Perch
Re: [Perchpole] Advanced MySQL Query & Tab-Delimited Value
By Dave - August 24, 2012
SELECT * FROM `<?php echo $TABLE_PREFIX ?>table` WHERE 'string' LIKE '%\tnumber\t%'
The next step to debug it is to double check your values, try this:
SELECT 'string', '%\tnumber\t%'
Replacing string and number with whatever you are using in your actual query. Then view-source on the list output and see what values mysql is getting. And maybe post your actual query if you can.
Hope that helps,
interactivetools.com
Re: [Dave] Advanced MySQL Query & Tab-Delimited Value
By Perchpole - August 24, 2012
Still no joy. What I failed to mention is that the number in this instance is chosen via the preceding list option in the editor.
This then triggers the following list to refresh - changing the values in the list. The full code should be:
SELECT * FROM `<?php echo $TABLE_PREFIX ?>table` WHERE 'string' LIKE '%\t<?php echo $ESCAPED_FILTER_VALUE ?>\t%'When I change the value in the firt menu, the second menu shows the "Loading..." message - but is always empty.
:0/
Perch
Re: [Perchpole] Advanced MySQL Query & Tab-Delimited Value
By Dave - August 24, 2012
Does it work if you hard code a number you know exists in the database and then change the preceding list? For example:
SELECT * FROM `<?php echo $TABLE_PREFIX ?>table` WHERE 'string' LIKE '%\t123\t%'
And what do you see when you change the query to this:
SELECT '%\tnumber\t%', 'string' FROM `<?php echo $TABLE_PREFIX ?>table`
Also, If 'string' is a fieldname make sure you don't have quotes around it.
Let me know, thanks.
interactivetools.com