News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

SQL Help

Started by mickjav, February 18, 2023, 07:50:24 AM

Previous topic - Next topic

mickjav

I have the following SQL

$dbquery = $smcFunc['db_query']('', '
SELECT MIN(c.wkend) AS charted, c.chart_id, c.art_id, c.rec_id, a.artist_name,
a.artist_the, a.artist_name_extra, a.combi_artist, r.rec_title, r.site_link,
COUNT(IF(c.pos = 1,1,NULL)) AS at_one
FROM {db_prefix}charts_one AS c
INNER JOIN {db_prefix}artists AS a ON (c.art_id = a.art_id)
INNER JOIN {db_prefix}records AS r ON (c.rec_id = r.rec_id)
WHERE c.chart_id = {int:chart_id}
GROUP BY c.art_id, c.rec_id, a.artist_name, a.artist_the, a.artist_name_extra, r.rec_title, r.site_link
ORDER BY COUNT(IF(c.pos = 1,1,NULL)) DESC
LIMIT '. $st . ', 40',
array(
'chart_id' => (int) $chr,
));

What I've been trying to do is only return rows that COUNT(IF(c.pos = 1,1,NULL)) > 1 I've tried Adding to the Where Clause

AND COUNT(IF(c.pos = 1,1,NULL)) > 1 which gave error
Then I tried

AND at_one > 1 which gave error

Hopefully somebody will be able to put me right

All the best mick

Edit: The results page is here: https://www.databasedreams.co.uk/charts/index.php?action=music;area=charts;sa=oneall


Arantor

Try:

$dbquery = $smcFunc['db_query']('', '
SELECT MIN(c.wkend) AS charted, c.chart_id, c.art_id, c.rec_id, a.artist_name,
a.artist_the, a.artist_name_extra, a.combi_artist, r.rec_title, r.site_link,
COUNT(IF(c.pos = 1,1,NULL)) AS at_one
FROM {db_prefix}charts_one AS c
INNER JOIN {db_prefix}artists AS a ON (c.art_id = a.art_id)
INNER JOIN {db_prefix}records AS r ON (c.rec_id = r.rec_id)
WHERE c.chart_id = {int:chart_id}
GROUP BY c.art_id, c.rec_id, a.artist_name, a.artist_the, a.artist_name_extra, r.rec_title, r.site_link
HAVING COUNT(IF(c.pos = 1,1,NULL)) > 1
ORDER BY COUNT(IF(c.pos = 1,1,NULL)) DESC
LIMIT '. $st . ', 40',
array(
'chart_id' => (int) $chr,
));

I'm not 100% sure this is correct because I'm not 100% sure I follow the exact logic you're trying to do here. But... you can't apply a WHERE clause to somethnig you're grouping by because WHERE clauses are done before the grouping - so you can't WHERE on a thing that doesn't exist yet, since WHERE is used to find the rows you do/don't want to make up the groups that you're then going to GROUP BY and perform a COUNT on.

But, you can do the calculation after the GROUP BY with a HAVING clause, so WHERE finds you all the rows that are theoretically viable, does the GROUP BY, then HAVING will let you disregard rows in the result groups if that's what you want to do.

mickjav

Thanks worked a treat.

mickjav

I have another one  :(

This is the results of below
https://www.databasedreams.co.uk/testing/index.php

What I am trying to do is select the 4 records that are no one for the given week.
The query below selects the dates for current weeks charts that have been tagged as one_level this query seems to work.

NOTE I did try to get what I needed from one query but couldn't get that working so decided to split it into two to get it working than maybe have another go at the one query.

$adbquery = $smcFunc['db_query']('', '
    SELECT MAX(w.wkend) AS weekend, w.chart_id
FROM {db_prefix}chart_weeks AS w
INNER JOIN {db_prefix}chart_names AS n ON (w.chart_id = n.chart_id)
WHERE n.one_level > 0
GROUP BY w.chart_id
LIMIT 4');

while($row = $smcFunc['db_fetch_assoc']($adbquery)){
$wks[] =  $row['weekend'];
echo $row['weekend'] . '  <br>';
}

I used this just as a visual aid echo $row['weekend'] . '  <br>';

I think I'm having problems with the In() as the dates being retuned don't match the $wks Array but Google didn't help  :(

//Now get the latest charts to get no ones from
$dbquery = $smcFunc['db_query']('', '
    SELECT c.wkend, n.chart_id, n.one_level, c.art_id, c.rec_id,
a.artist_name, a.artist_the, a.artist_name_extra, a.combi_artist, r.rec_title, r.site_link
    FROM {db_prefix}chart_names AS n
INNER JOIN {db_prefix}charts_one AS c ON (n.chart_id = c.chart_id)
INNER JOIN {db_prefix}artists AS a ON (c.art_id = a.art_id)
INNER JOIN {db_prefix}records AS r ON (c.rec_id = r.rec_id)
WHERE n.one_level > 0 AND c.wkend IN(' . implode(',', array_unique($wks)) . ')
ORDER BY n.one_level ASC, n.chart_id DESC
LIMIT 4');

while($row = $smcFunc['db_fetch_assoc']($dbquery))
{
echo $row['wkend'] . ' ' . $row['artist_name'] . ' ' . $row['rec_title'] . '<br>';
}

$smcFunc['db_free_result']($dbquery);

This is what I'm trying to replace Look at the no one's centre block which is HTML I'm trying to replace it with a php script that I won't have to edit every week.

https://www.databasedreams.co.uk/charts/index.php

mickjav

I'm going to try another way, I just thought of thanks if you looked.

All the best mick

Advertisement: