CODEWITHSUNDEEP
phpmysql
rawrPowershell
rawrPowershell

Reputation: 124

mySQL query not returning concatenated results in PHP

I currently have a mySQL query that works in mySQL workbench 5.2, but unfortunately when I translate it over to my php file the CONCAT function is only able to grab FirstName, but not LastName. I am able to retrieve the full name if I don't use the single quotes to build a space between the first and last name, but when I add the code for the spacing, it only retrieves the first name.

For example if I don't add the spacing between the names and call EmployeeName with this modified portion of the code it works:

    SELECT * , concat(FirstName,LastName) as 'EmployeeName'

but the output would be BruceWayne

The code below is where CONCAT fails to retrieve LastName when I call EmployeeName

      $sql = " 

      SELECT * , concat(FirstName," . "' '" . ",LastName) as 'EmployeeName'
      FROM tblEquipment e
      INNER JOIN tblEmployee em on em.employee_id = a.employee_id
      INNER JOIN tblStatus s on s.Status_id = e.equipment_id
      WHERE e.name = " . "'" . $q . "'" . ";
    ";

         while($row = mysql_fetch_array($result))
    {
          echo "
            <td>
                Equipment ID:
            </td>
            <td>
                <input type='text' size='25' disabled value = " . $row['Equipment_ID'] . ">
            </td>

            <td style='padding-left:10px;'>
                Currently Assigned To:
            </td>
            <td>
                <input type='text' size='25' disabled value = " . $row['EmployeeName'] . ">
            </td>
           ";
           }

Output is only the first name : Bruce. Does anyone have any suggestions?

Upvotes: 3

Views: 104

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270637

Your value= attributes in the HTML markup are not quoted, and therefore will only accept one word up the the first whitespace. You should both escape and quote the value attributes. Whenever you print variables into HTML markup, you need to escape them via htmlspecialchars().

When printing variables into HTML tag attributes, I recommend using htmlentities() with the ENT_QUOTES option to escape both single and double quotes inside the attribute.

echo "
        <td>
            Equipment ID:
        </td>
        <td>
            <input type='text' size='25' disabled value='" . htmlentities($row['Equipment_ID'], ENT_QUOTES) . "'>
        </td>

        <td style='padding-left:10px;'>
            Currently Assigned To:
        </td>
        <td>
            <input type='text' size='25' disabled value='" . htmlentities($row['EmployeeName'], ENT_QUOTES) . "'>
        </td>
       ";
       }

Note that there is no need to break the string and concatenate in your CONCAT():

" SELECT * , concat(FirstName, ' ',LastName) as 'EmployeeName

Likewise in the WHERE, the $q can be directly interpolated in the double-quoted string. We assume it has been properly escaped via mysql_real_escape_string(). In the long run, consider switching to an API supporting prepared statements. The mysql_*() API is facing deprecation in the upcoming PHP 5.5.

"WHERE e.name = '$q'";

Upvotes: 3

Related Questions