Concatenate string with field value in MySQL

php mysql sql concatenation
Ben picture Ben · Oct 9, 2013 · Viewed 133.7k times · Source

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?

Answer

Will Hannah picture Will Hannah · Oct 9, 2013

Have you tried using the concat() function?

ON tableTwo.query = concat('category_id=',tableOne.category_id)

Related questions

How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
MySQL query String contains

I've been trying to figure out how I can make a query with MySQL that checks if the value (string $haystack ) in a certain column contains certain data (string $needle), like this: mysql_query(" SELECT * FROM `table` WHERE `column`.contains(…

Read More
How to 'insert if not exists' in MySQL?

I started by googling, and found this article which talks about mutex tables. I have a table with ~14 million records. If I want to add more data in the same format, is there a way to ensure the record I …

Read More