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.

Are "userName" and "pickUpLocationID" column names? If they are, remove the quotes. — Funk Forty Niner
– Funk Forty Niner, Commented Dec 4, 2014 at 1:59
Show us some error messages please.By the way,you can't quote column names in SQL, you should use ` to wrap column names, like "SELECT COUNT( `userName` ) FROM %s as total WHERE `pickUpLocationID`='1'". — Seeinyou
– Seeinyou, Commented Dec 4, 2014 at 2:24

Skatox · Accepted Answer · 2014-12-04 02:41:47Z

2

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);

answered Dec 4, 2014 at 2:41

Skatox

4,27412 gold badges47 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

codingNovice Over a year ago

Thank you for that! I did not realize there was a difference between ` and '. However, I'm now getting the error " Fatal error: Cannot use object of type mysqli_result as array" -- how would you display this if my $custCount = print_r($result['total']) is incorrect? @Skatox

Skatox Over a year ago

Oh, that's because mysqli_query returns a mysqli_result object, so you can't use it as an array. You can read more about it at: php.net/manual/en/class.mysqli-result.php

Collectives™ on Stack Overflow

PHP MySQL Count Query Issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related