select from one table, count from another where id's linked

heres my code:

$sql = mysql_query("select c.name, c.address, c.postcode, c.dob, c.mobile, c.email, 
                    count(select * from bookings where b开发者_如何学Python.id_customer = c.id) as purchased, count(select * from bookings where b.the_date > $now) as remaining, 
                    from customers as c, bookings as b 
                    where b.id_customer = c.id
                    order by c.name asc");

you can see what i am trying to do, but im not sure how to write this query properly.

heres the error i get:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

heres my mysql_fetch_assoc:

<?php

while ($row = mysql_fetch_assoc($sql))
{
    ?>

    <tr>
    <td><?php echo $row['name']; ?></td>
    <td><?php echo $row['mobile']; ?></td>
    <td><?php echo $row['email']; ?></td>
    <td><?php echo $row['purchased']; ?></td>
    <td><?php echo $row['remaining']; ?></td>
    </tr>

    <?php   
}

?>

---

Try changing the likes of...

count(select * from bookings where b.id_customer = c.id)

...to...

(select count(*) from bookings where b.id_customer = c.id)

---

Your query incorrectly uses COUNT, which has been covered by @Will A's answer.

I would also like to suggest a possibly better constructed alternative, which, I think, reflects the same logic:

SELECT
  c.name,
  c.address,
  c.postcode,
  c.dob,
  c.mobile,
  c.email,
  COUNT(*) AS purchased,
  COUNT(b.the_date > $now OR NULL) AS remaining
FROM customers AS c
  INNER JOIN bookings AS b ON b.id_customer = c.id
GROUP BY c.id
ORDER BY c.name ASC

Note: Normally you are expected to include all the non-aggregated SELECT expressions into GROUP BY. However MySQL supports shortened GROUP BY lists, so it's enough to specify the key expressions that uniquely identify all the non-aggregated data you are pulling. Please avoid using the feature arbitrarily. If a column not included in GROUP BY has more than one value per group, you have no control over which value will actually be returned when pulling that column without aggregation.