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

Hi,

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

Hi, Jason -

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

Your second example should be correct:

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,
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Advanced MySQL Query & Tab-Delimited Value

By Perchpole - August 24, 2012

HI, Dave -

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