MySQL SELECT COUNT(*) FROM issue with LIMIT

-
Recently working on my core database class I found an issue with my num_of_rows() method.

I was parsing my query to do various things, which basically boiled down to running a query like:
[code]SELECT COUNT(*) FROM `table_name`[/code]
To return the number of rows generated by this query.

Now, when I started introducing pagination in my front end, I wanted to utilise LIMIT/OFFSET in my queries, like so;
[code]SELECT COUNT(*) FROM `table_name` LIMIT 30, 10[/code]
Which I would hope returned 10 (or less if I had less than 30 rows in my table.

Anyway, this was throwing a wobbler in my PDO class, I wasn't getting any error or feedback that there was an issue, and in phpmyadmin I was simply getting "MySQL returned an empty result set (i.e. zero rows)".

Baffled, I took to google and found this work around:

[code]SELECT COUNT(*) FROM (SELECT * FROM `projects` LIMIT 30,10) AS subquery[/code]

Happy coding

Comments

Post a Comment

Popular posts from this blog

Navigating the Jungle of Web Traffic: A Technical Team Lead's Guide to "I'm a Celebrity, Get Me Out of Here"

-
"I'm a Celebrity, Get Me Out of Here" has become a cultural phenomenon, captivating audiences worldwide with its thrilling challenges and celebrity antics. As a Technical Team Lead, preparing for the surge in traffic during the show's broadcast is akin to navigating a dense jungle of user interactions. In this article, we'll explore the strategies and considerations that technical teams can employ to ensure a seamless online experience while aligning with marketing promotions. 1. Assessing the Infrastructure: Before the chaos ensues, evaluate your web infrastructure. Ensure that servers are optimized, databases are finely tuned, and CDN (Content Delivery Network) configurations are robust. Conduct load testing to simulate peak traffic and identify potential bottlenecks. This step is crucial for preventing server crashes and maintaining site performance under increased load. 2. Scalability and Elasticity: Consider implementing auto-
Read more

TCP Handshake over IPv6

-
The internet we know today heavily relies on TCP/IP to send our information around the world at lightspeed.  With more devices than ever connected to the internet (insert link to back this up, it sounded cool), using IPv4 we're soon going to run out of addresses.  And in fact if it weren't for technologies such as NAT we'd be in a stickier situation than we already are.   In preperation of running out of IP addresses, IPv6 was mustered up around 1998 by the IETF, a 128-bit addressing convention vs the old IPv4 32-bit addressing. So that means IPv6 has been around quite a while now.  Almost 20 years, yet still IPv4 is all the rage.  Perhaps us techies have trouble letting go of what we know.   I mean why set up an AAAA record, when you only need to set up an A record right?  Why configure DNS to use   2001:4860:4860:0000:0000:0000:0000:8888 or 2001:4860:4860::8888 when you can just use 8.8.8.8 right?  Well yeah, let's save all that for another article, once I've h
Read more

The Vital Importance of Secure Wi-Fi Networks

-
In today's digitally interconnected world, Wi-Fi has become an indispensable part of our daily lives. From homes to businesses, coffee shops to airports, we rely heavily on wireless networks to stay connected. However, amidst the convenience and ease that Wi-Fi brings, security should never be taken lightly. In this blog post, we'll delve into the critical importance of using secure Wi-Fi networks and the potential risks associated with neglecting this aspect of our online lives. The Threat Landscape First and foremost, let's address the ever-looming threats that exist in the digital realm. Unsecured Wi-Fi networks are a goldmine for cybercriminals, as they provide an easy entry point to intercept sensitive data. When you connect to an open, unencrypted Wi-Fi network, your data becomes vulnerable to eavesdropping and interception. Malicious actors can capture login credentials, personal information, and even financial details, putting you at risk of identity theft, financi
Read more