Concatenate string with field value in MySQL
PhpMysqlSqlConcatenationPhp Problem Overview
I have the need to concatenate a string with a field value in a MySQL query in order to LEFT JOIN two tables.
Table one has a column called "category_id" with numeric values, such as 61, 78, 94 and such.
Table two has a column called "query" which refers to a request route mechanism, and it has values such as "product_id=68", "category_id=74", "manufacturer_id=99" and so on.
So in my query I require to join the tables when ever a concatenated string derived from a set string and the value of the "category_id" column matches the query field.
My SQL statement is currently:
SELECT * FROM tableOne
LEFT JOIN tableTwo
ON tableTwo.query = 'category_id=' + tableOne.category_id
I have tried using the || operator instead of the + operator, but still no luck. Is it possible to do this in MySQL, or have I jumped the gun here?
Php Solutions
Solution 1 - Php
Have you tried using the concat() function?
ON tableTwo.query = concat('category_id=',tableOne.category_id)
Solution 2 - Php
SELECT ..., CONCAT( 'category_id=', tableOne.category_id) as query2 FROM tableOne
LEFT JOIN tableTwo
ON tableTwo.query = query2
Solution 3 - Php
Here is a great answer to that:
SET sql_mode='PIPES_AS_CONCAT';
Find more here: https://stackoverflow.com/a/24777235/4120319
Here's the full SQL:
SET sql_mode='PIPES_AS_CONCAT';
SELECT * FROM tableOne
LEFT JOIN tableTwo
ON tableTwo.query = 'category_id=' || tableOne.category_id;
Solution 4 - Php
MySQL uses CONCAT() to concatenate strings
SELECT * FROM tableOne
LEFT JOIN tableTwo
ON tableTwo.query = CONCAT('category_id=', tableOne.category_id)