CODEWITHSUNDEEP
phpmysqlmysqli
Daniel Retzl
Daniel Retzl

Reputation: 1

SQL SELECT COUNT rawQuery with PHP-MySQLi-Database-Class

I use the mysqli class from here: https://github.com/joshcam/PHP-MySQLi-Database-Class

...and got problems with this rawquery: 

    $q = mysql_query("SELECT id, value, (
                              SELECT COUNT( * ) 
                              FROM ".$dbprefix."pages
                              WHERE roles = ".$dbprefix."roles.id
                             )count
                             FROM '".$dbprefix."roles' ORDER by id ASC");
    $result = $db->rawQuery ($q);

It does not work, I get a fatal error preparing the query on line 776 in MysqliDb.php - the mysql query itself works, but when I use it with the mysqli class it does not work. I believe it's got to do with the COUNT (*) or striped tags or... something. I've tried several things but I stuck. Maybe I am not the only person with that problem- maybe someone can help out? This would be great! Thanks in advice!

Upvotes: 0

Views: 1373

Answers (1)

Peter Bowers
Peter Bowers

Reputation: 3093

Since you are using the PHP-MySQLi-Database-Class, no need to mess with table prefixes manually - just set it with $db->setPrefix(...).

Then don't do mysql_query(...) (which is deprecated anyway!) when your chosen library does this directly. rawQuery() is looking for a string with an SQL query, not the statement that mysql_query() returns.

Finally, avoid sub-queries and replace with various types of JOIN whenever possible - far better performance and far better readability. In general people way overuse sub-queries - get in the habit of trying to figure out a JOIN solution whenever you are tempted by sub-queries. Sometimes you have to use the sub-query, but most of the time not...

$db->setPrefix ($dbprefix);

$sql = "SELECT id, value, COUNT(pages.roles) AS c
        FROM roles
        LEFT JOIN pages ON pages.roles = roles.id
        ORDER by id ASC"
$result = $db->rawQuery($sql);

Upvotes: 1

Related Questions