2

I spent most of yesterday coding a "TOP 10" page for my website to pull from my database.

It was a pretty simple SQL statement:

SELECT * FROM `cards` GROUP By `name` ORDER BY `viewsperweek` DESC LIMIT 10;

It was all working perfectly for me last night and the info was correct but when I woke up this morning it no longer works.

After some testing it seems only LIMITS of 1-5 will work. If I put it past 5 now the SQL result now longer shows on the website (it DOES continue to work on the database itself).

So basically now I can seemingly only do this:

SELECT * FROM `cards` GROUP By `name` ORDER BY `viewsperweek` DESC LIMIT 5;

Can't for the life of me figure out why it would work last night and not this morning. The code itself is being called from a js file which in turn calls the php file for the SQL code.

  • You have tagged your question as [php] but there isn't php code – McNets Oct 4 '17 at 8:04
  • Sorry, I tagged it as php as the SQL code is running from a PHP file as stated at the end, even though it only seems to be the SQL that is causing issues. – GenesisBits Oct 4 '17 at 8:06
  • SELECT * ... GROUP BY name ... violates a principle of GROUP BY. The code is free to give you arbitrary values for anything that is not directly related to name in the *. Regardless of whether we (or you) fix the "variations" question, your output will continue to be broken. – Rick James Oct 7 '17 at 3:56
1

Fixed it finally.

Was an issue with the I was using GROUP BY, ORDER BY and LIMIT, at least I think.

I changed it to the following and it's working smoothly now:

SELECT DISTINCT `name`, `id`, `viewsperweek`, `totalviews` FROM `cards` GROUP BY `name` ORDER BY `viewsperweek` DESC LIMIT 10

I ran into this issue before with my Database and forgot about it. There are some cards that are duplicates so I needed to remove them through the SQL code. In my original code it was hitting some duplicates after the 5th and breaking. Honestly not sure why (as the code worked in my back-end db), but it's working now.

  • What exactly is the query supposed to return? Because both the original query in the question and the one in this answer are junk. Sorry for the language but the queries are not even valid SQL. It's a pity that MySQL allows them but at least their documentation strictly suggests to not use such queries (that have column in the SELECT list that are not in the GROUP BY list.) because any such query may give incorrect, junk results. – ypercubeᵀᴹ Oct 4 '17 at 9:35
  • The DISTINCT in this query will do nothing anyway. The GROUP BY name already gives only 1 row per different name. The DISTINCT name, ... will do nothing more. – ypercubeᵀᴹ Oct 4 '17 at 9:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.