php - Is it possible to add a string to the result using mysql?

409

Here is the code:

// connect and select the database
$smt = $db->query(" SELECT 'one' which, col1, col2 FROM table1 WHERE col1 = '10'
                       UNION ALL
                    SELECT 'two' which, col1, col2 FROM table2 WHERE col1 = '10'
                    ");

while($result = $smt->fetch())  {
    if($result['which'] == 'one'){
        $result['col1'] = 'String: '.$result['col1'];
    }
}

As you see in the above code, there is anIF() statement for combining a string with the result ofcol1 (just fortable1). Now I want to know, is it possible to I do that using MySQL? (At the moment I do that using PHP)

In other word, can I do this in MySQL?

if($result['which'] == 'one'){
    $result['col1'] = 'String: '.$result['col1'];
}
332

Answer

Solution:

If you know what string you want to put in front of the first column of the first select statement, and it's not dynamic, then you can just concatenate the string you want in front of the first column:

$smt = $db->query("SELECT CONCAT('String:',col1), col2 FROM table1 WHERE col1 = '10'
                   UNION ALL
                SELECT CONCAT('String2: ',col1), col2 FROM table2 WHERE col1 = '10'
                ");
497

Answer

Solution:

You can add a fixed string to your query results using the CONCAT fuction: https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat

In your example I think what you're after isCONCAT('String: ', col1)

655

Answer

Solution:

You may need to use CASE and CONCAT. Try something like below

 $smt = $db->query(" SELECT 'one'which, CASE( WHEN (which = 'one') THEN CONCAT('String:',which) ELSE col1 END) as col1, col2 FROM table1 WHERE col1 = '10'
                   UNION ALL
                SELECT 'two' which, CASE( WHEN (which = 'two') THEN CONCAT('String:',which) ELSE col1 END) as col1, col2 FROM table2 WHERE col1 = '10'
                ");

People are also looking for solutions to the problem: php - Unwanted combination of arrays inside a for cycle

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.