mySQL查询不返回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.

我目前有一个在mySQL workbench 5.2中工作的mySQL查询，但不幸的是，当我将它转换为php文件时，CONCAT函数只能获取FirstName，而不能获取LastName。如果我不使用单引号在姓和名之间建立空格，我可以检索全名，但是当我为空格添加代码时，它只检索名。

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

例如，如果我不将名称和调用EmployeeName之间的间隔添加到该代码的修改部分中，它可以工作:

    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

下面的代码是CONCAT在调用EmployeeName时无法检索LastName的地方。

      $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?

输出只是一个名字:Bruce。有人有什么建议吗?

1 个解决方案

#1

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().

不引用HTML标记中的值=属性，因此只接受第一个空格上的一个单词。您应该转义并引用值属性。无论何时将变量打印到HTML标记中，都需要通过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.

当将变量打印到HTML标记属性时，我建议使用htmlentities()使用ENT_QUOTES选项来在属性中避免单引号和双引号。

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():

注意，不需要在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中，$q可以直接插入到双引号字符串中。我们假设它已经通过sql_real_escape_string()正确地转义了。从长远来看，考虑切换到支持准备语句的API。在即将到来的PHP 5.5中，mysql_*() API正面临弃用。

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

#1