Concat a string with a field value in a Join condition


#1

Trying to select some values from an Opencart database. I can’t seem to think of the right syntax for this query.
There is a table called oc_url_alias which is a look-up for URL rewriting, it looks like:-

url_alias_id | query      | keyword
_____________________________________
12345        |product=123 | url-text

I’m trying to get the values of the keyword column, I don’t see any reference to the url_alias_id in any other table, so have to use the product ID number which follows in the string product= (123 in the example data).

This is what i have for the query so far:-

			$fields = 'oc_product.product_id AS ocid,
				oc_product.sku AS id,
				oc_product_description.name AS title,
				meta_description AS description,
				oc_url_alias.keyword,
				oc_product.image' ;
			
			$query = "SELECT $fields FROM oc_product
				LEFT JOIN oc_product_description ON oc_product.product_id = oc_product_description.product_id
				LEFT JOIN oc_url_alias ON oc_url_alias.query = CONCAT('product_id=', `oc_product.product_id`)
				WHERE oc_product.status = '1'" ;

This gives an error that oc_product.product_id is an unknown column. It works if I replace oc_product.product_id in the CONCAT with an actual number, so it can’t be too far off.

So how do I correctly append the product ID number to the string in the comparison of the join?


#2

remove the backticks

correct syntax would be

`oc_product`.`product_id`

but you don’t need no steenkin backticks


#3

That worked, thank you.

But I’m sure I tried it yesterday without backticks during the process of “trying different random things”. :thinking:
Maybe not, or I had something else wrong at the time.