MySQL query using SELECT field replace

Databases
1

Hi guys,

I need help with an equation that I’m trying to resolve here. I need to construct a query that would output the result highlighted with RED given the url value highlighted with BLUE, using the “name1” match from field_one as a reference.

| field_one | field_two
| name0_url | http://siteX.com
| name1_url | http://siteY.com
| name2_url | http://siteZ.com
| name0_num | 2
| name1_num | 2
| name2_num | 2
|

My understanding is that my query should contain something like:

SELECT field_two,
REPLACE(field_one, ‘url’, ‘num’) as number
WHERE…

I also have the feeling I could somehow make use of the following statement:

IF(REPLACE(field_one, ‘_url’, ‘’) = REPLACE(field_one, ‘_num’, ‘’), 1, 0) AS matches

Please give me some idea or point out the right direction, thanks a lot!

2

SELECT value2.field_two
FROM table_name as value1
LEFT JOIN table_name AS value2 ON (REPLACE(value1.field_one, ‘_url’, ‘’) = REPLACE(value2.field_one, ‘_num’, ‘’))
WHERE value1.field_two = ‘http://siteY.com’

3

the right direction is this –

foo num url
 0   2  http://siteX.com  
 1   2  http://siteY.com 
 2   2  http://siteZ.com

if you could give something more specific than “field” “name” and “num” we could help more

what kind of application is this?

4

Hi r937, thanks for being so responsive as usual. It’s a WordPress query, I’m trying to play with values inside the “wp_postmeta” table, and I got it to work, thanks!

5

none of that made any sense… except the “i got it to work” part :wink:

6

I knew you’d say that :slight_smile:

Ok, I’m using the Advanced Custom Fields plugin together with the Repeater add-on for a client licensing platform. Licenses are per domain, so I’m storing sets of “Domain Name” + “Expiry Date” user meta values in the database. Now, to be able to check if we’re past an expiry date I had to use the following formula (assuming that ACF is storing the data as follows:

| user_id | meta_key … | meta_value … |
| 1 … | license_0_domain | http://domain0.com |
| 1 … | license_0_expiry | 1402312804 … |
| 1 … | license_1_domain | http://domain1.com |
| 1 … | license_1_expiry | 1402312806 … |
| 1 … | license_2_domain | http://domain2.com |
| 1 … | license_2_expiry | 1402312808 … |

My goal was to check the expiry date of any given license by the domain name with just one sql query which I managed to work out soon after posting this thread (I wish it also were an optimal way of doing it, please correct me if wrong):

SELECT domain.user_id
FROM $wpdb->usermeta as domain
LEFT JOIN $wpdb->usermeta AS expiry ON ( REPLACE( domain.meta_key, '_domain', '' ) = REPLACE( expiry.meta_key, '_expiry', '' ))
	WHERE domain.meta_value = '$domain'
	AND expiry.meta_value > UNIX_TIMESTAMP(NOW())

Hope this is making more sense now…
Thanks again :wink:

7

yes it does make sense now – next time, just go with the real names :wink:

by the way, you want INNER JOIN instead of LEFT JOIN

8

It was my understanding that by simplifying the variables, a solution would be easier to work on, will keep your note in mind though :slight_smile:

An INNER JOIN will retrieve all records from both tables that have matching values for whatever column you’re joining them on. Records from either table that don’t match will not be retrieved. A LEFT JOIN will retrieve all records from the left (first) table, and only the records from the second table with matching values for the joining column, filling in any empty spaces with NULL values.
Great, so I does look like I need INNER JOIN indeed, thanks again!