StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here's how I'd do it in PHP with PDO:</p> <pre><code>$sql = " SELECT COUNT(*) AS mediastats_members, AVG(status_rating) AS mediastats_avscore, SUM(status_status = 'done') AS mediastats_done, SUM(status_status = 'doing') AS mediastats_doing, SUM(status_status = 'redoing') AS mediastats_redoing, SUM(status_status = 'dropped') AS mediastats_dropped, SUM(status_status = 'wantto') AS mediastats_wantto, SUM(status_status = 'wont') AS mediastats_wont, SUM(status_status = 'stalled') AS mediastats_stalled, SUM(status_rating = 1) AS mediastats_rating_1, SUM(status_rating = 2) AS mediastats_rating_2, SUM(status_rating = 3) AS mediastats_rating_3, SUM(status_rating = 4) AS mediastats_rating_4, SUM(status_rating = 5) AS mediastats_rating_5, SUM(status_rating = 6) AS mediastats_rating_6, SUM(status_rating = 7) AS mediastats_rating_7, SUM(status_rating = 8) AS mediastats_rating_8, SUM(status_rating = 9) AS mediastats_rating_9, SUM(status_rating = 10) AS mediastats_rating_10 FROM status WHERE status_media_id = :id"; $stmt = $pdo->prepare($sql); $stmt->execute(array("id"=>$id)); $params = $stmt->fetch(PDO::FETCH_ASSOC); </code></pre> <p>This way you calculate all the aggregates in one pass of the table, instead of using a separate subquery for each count.</p> <p>I'm using a trick of MySQL -- the SUM() of a boolean expression is equal to the COUNT() where the expression is true. This is because MySQL boolean expressions always return 0 or 1, and the SUM of 0's and 1's is equal to the COUNT of the 1's.</p> <p>Then you can use the result from the above query as the parameters array to an UPDATE statement:</p> <pre><code>$sql = " UPDATE mediastats SET mediastats_members = :mediastats_members, mediastats_avscore = :mediastats_avscore, mediastats_done = :mediastats_done, mediastats_doing = :mediastats_doing, mediastats_redoing = :mediastats_redoing, mediastats_dropped = :mediastats_dropped, mediastats_wantto = :mediastats_wantto, mediastats_wont = :mediastats_wont, mediastats_stalled = :mediastats_stalled, mediastats_rating_1 = :mediastats_rating_1, mediastats_rating_2 = :mediastats_rating_2, mediastats_rating_3 = :mediastats_rating_3, mediastats_rating_4 = :mediastats_rating_4, mediastats_rating_5 = :mediastats_rating_5, mediastats_rating_6 = :mediastats_rating_6, mediastats_rating_7 = :mediastats_rating_7, mediastats_rating_8 = :mediastats_rating_8, mediastats_rating_9 = :mediastats_rating_9, mediastats_rating_10 = :mediastats_rating_10 WHERE mediastats_media_id = :id"; $stmt = $pdo->prepare($sql); $params["id"] = $id; $stmt->execute($params); </code></pre> <p>Since PHP 5.3.4, PDO accepts parameter array keys without the leading <code>:</code>. You need the colon when you declare the parameter placeholder in your query, but you don't need it in the array of values you supply to execute(). </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload