CODEWITHSUNDEEP
phpmysqlsql
Jeanclaude
Jeanclaude

Reputation: 189

SQL query not returning count value

SQl query running to return the total count from the query.

this code works when running SQL with PHPmyAdmin

But on the page it is not displaying echo of the count ?

Not sure if I could have overlooked something here.

Many Thanks!

$sql2=mysql_query("SELECT count(*) 
FROM main_table LEFT JOIN houses ON main_table.housenumber = houses.housenumber AND main_table.streetname = houses.streetname
WHERE main_table.city='1'
group by main_table.city ORDER BY average DESC, houseID DESC, reviewID DESC;");

while($row=mysql_fetch_array($sql2))
{
    $count=$row['count'];
    echo $count;;
}

Upvotes: 0

Views: 79

Answers (4)

Zeeshan
Zeeshan

Reputation: 1675

Try this ....

$sql2=mysql_query("SELECT 
      COUNT(*) AS count
    FROM
      main_table 
      LEFT JOIN houses 
        ON main_table.housenumber = houses.housenumber 
        AND main_table.streetname = houses.streetname 
    WHERE main_table.city = '1' 
    GROUP BY main_table.city 
    ORDER BY average DESC,
      houseID DESC,
      reviewID DESC") ;

    while($row=mysql_fetch_array($sql2))
    {
        $count=$row['count'];
        echo $count;
    }

You have mistake in your query, you are not adding count in select as aliases, and below in while you are using aliases . Try this.

Upvotes: 1

Tata
Tata

Reputation: 809

try starting your query with :

SELECT count(*) as count ...

Upvotes: 0

lionheart98
lionheart98

Reputation: 978

add SELECT count(*) as 'count' from ...

Upvotes: 0

Pascalz
Pascalz

Reputation: 2378

name your column :

...mysql_query("SELECT count(*) as count....

Upvotes: 0

Related Questions