How do i work with the COUNT()
$sql = $connect->prepare("SELECT COUNT() FROM discos_events e INNER JOIN discos_events_guests eg ON (e.ID = eg.eID) INNER JOIN users u ON (eg.uID = u.id) WHERE e.dID =:id");
$sql->bindValue(":id", $cID);
$sql->execute();
...then what? echo $sql["count"]; ? to output the count?
You need an alias name for your COUNT() column:
$sql = $connect->prepare("SELECT COUNT() AS num_events FROM discos_events e INNER JOIN discos_events_guests eg ON (e.ID = eg.eID) INNER JOIN users u ON (eg.uID = u.id) WHERE e.dID =:id");
$sql->bindValue(":id", $cID);
// Fetch the results and then access the alias
$sql->execute();
$result = $sql->fetch();
echo $result['num_events'];
fetch(). But if you're selecting multiple columns with a COUNT() aggregate you need to fetch()SELECT COUNT() than to select all the individual columns and then determine the rowCount() even though you don't have to fetch to get the rowCount(). It is the COUNT() query that runs faster, not that it is faster/easier to code. You still have to fetch the COUNT() but the query itself will run much more quickly in the databaseyou need to execute() the query, so:
$result = $sql->execute(array('id' => $cId)); // just to illustrate that you can use this instead of bindParam
if ($result) {
$row = $sql->fetch();
}
after execute, you have to store_result() and fetch()
As @Michael suggests, you may alias the count(), to get it in more reatable form.
Your query needs to assign the count value to a name, like so:
SELECT COUNT() n FROM discos_events ...
Then you can reference the name n in your PHP array:
echo $sql["n"];
You can, of course, call it 'count' or any other name you prefer, but be careful of using names which are reserved words in SQL (such as 'count'). If you want to give it a reserved name, you need to enclose it in backtick characters so that SQL recognises that it's a name you want to use rather than its own reserved word.