PHP MySQL Count Query Issue

Question

*Disclaimer: I apologize in advance if there is a simple solution for this - I have looked at a lot of other examples similar to mine but cannot figure out how to solve this.

I am trying to count the number of customers in a sample database that use the same pickup location for their goods.

After connecting to the database, I have the following code:

$Table="Customer";

$custPerLocationCount = sprintf("SELECT COUNT('userName') FROM %s as total WHERE 'pickUpLocationID'='1'", $Table);

$result = mysqli_query($db, $custPerLocationCount);

$body .= "<h1>Customers Per Location</h1><table align='center'>
            <tr>
                <th>Location Name</th>
                <th>Number of Customers</th>
            </tr>";

    while ($recordArray = mysqli_fetch_object($result)) 
        {


             $locationName = "LOCATION1";
             $custCount = print_r($result['total']);

            $body .= "<tr>
                <td>".$locationName."</td>
                <td>".$custCount."</td>
            </tr>";
        };
        $body .="</table></br></br></br>";

Please ignore the table formatting. I keep getting different errors as I change the code, but have not yet found how to solve this.

Answer 1

In mysql, you use ` to make the DB interprent the content as the column name, so you have to change your query to remove quotes from columns and you have to move the alias total to the end of the SELECT statement and before the FROM:

custPerLocationCount = sprintf("SELECT COUNT(`userName`) as total FROM %s WHERE `pickUpLocationID`='1'", $Table);