So how do yo find the most popular cake in the Shire Bakery? Here’s an idea.
-- Show the best cookie -- See: Ben Forta Chap. 16 SELECT COUNT( cakes.cake ) AS 'popular', cakes.cake, costumers.who FROM cakes, costumers, likes -- sort out the cardinalities (relations) WHERE cakes.cakes_id = likes.cakes_id AND costumers.costumers_id = likes.costumers_id -- group by the cake group by cakes.cake -- and sort (most popular and downwards) order by popular desc
Here’s a short tutorial on how to solve some basic SQL cardinalities. First have a look at the ER-diagram for the database and the resulting tables in PhpMyAdmin.
Many to many cardinalities may be solved like this:
SELECT cake, who
FROM cakes, costumers, likes
WHERE costumers.costumers_id = likes.costumers_id
AND cakes.cakes_id = likes.cakes_id
Note that there are two where clauses combined via AND.
This cardinality is more easy:
SELECT inventory .* , costumers.who
FROM inventory, costumers
WHERE inventory.costumers_id = costumers.costumers_id
In the “many” table you simply connect via the “one” table’s id.
You can download and try out the samples from my Github Repository oneToMany_ManyToMany.
These samples are inspired by Ben Forta’s “MySQL Crash Course” Ch. 15 – 16.
Det blev lidt kringlet, da grafen:
I PHP MyAdmin fandt jeg ud af, at SQL-sætningen kunne se omtrent således ud:
$sql = $wpdb->get_results( "SELECT * FROM `tablename` WHERE name='$username' ORDER BY adate DESC" );
$sql er sorteret sådan, at grafen ville vende modsat læseretningen, og det er forvirrende. Derfor skulle den kringles sådan:
$sql = array_reverse($sql, true);
Og så vender grafen rigtigt.
Programmeringsmæssigt bliver en af udfordringerne at håndtere “pagination”, dvs. sideopdeling af astmadagbogen. Efterhånden som der kommer flere og flere poster i astmabloggens tabel, så bliver det en nødvendighed. Pt. har jeg ikke fundet et oplagt eksempel.
Måske skal man bare tænke det lidt enkelt. En tæller “i” = længden af arrayet. Så trækker man “limit” fra ind til i
Mange kodeeksempler er umådeligt komplicerede, men det her må kunne lade sig gøre. $wpdb skal bruge en SQL sætning med en limit på fx 30 stk. pr. søgning (det ville svare til ca en måneds input, hvis folk skriver peakflow en gang pr. dag).
We use cookies - more information
Multimusen.dk will set a few cookies from Doubleclick, Google and the Social Media plugins they ay set some cookies. Some of my pages use APIs - such as YouTube, LinkedIn, Google Fonts, Google Maps, Mapbox, Spotify, Jetpack, Twitter, Facebook &c.. Such plugins may set the odd cookie.
