Flag of Ukraine
SymfonyCasts stands united with the people of Ukraine

Limiting the Number of Results

Keep on Learning!

If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.

Start your All-Access Pass
Buy just this tutorial for $12.00

Limiting the Number of Results

Our app would be a little more fun to play with if we had some more interesting pets in our pet table. So I made a file with some SQL queries that insert some new furry friends. Open up the resources/episode3/pets.sql file:

TRUNCATE TABLE `pet`;

INSERT INTO `pet`
    (`id`, `name`, `breed`, `age`, `weight`, `bio`, `image`)
    VALUES
    (1, 'Chew Barka', 'Bichon', '2 years', 8, 'The park, The pool ...', 'pet1.png');

Cool. TRUNCATE is the mysql keyword you use when you want to empty a table. It’s handy when you’re developing - but try not to use this on the production database, you’d probably end up in the dog house. After that, we just have a bunch of INSERT queries. Copy the contents. Now open up PHPMyAdmin, click the SQL tab and paste it in there. And voila! We’ve got 4 pets.

Tip

You can also execute all the queries in this file from the command line:

mysql -u root -p air_pup < resources/episode3/pets.sql

When we refresh the homepage, we see our 4 pets. In fact, if we had 1 million pets in our table, they would all load on this page. That would be way more dog food than I can afford!

Instead, no matter how big this table gets, let’s only show 3 pets on the homepage. To do this, we can change our query to tell MySQL to only return 3 rows. This is done by adding a LIMIT at the end:

// lib/functions.php

function get_pets()
{
    // ...

    $result = $pdo->query('SELECT * FROM pet LIMIT 3');
    $pets = $result->fetchAll();

    return $pets;
}

When we refresh now, only 3 pets! Hey, that’s one more little MySQL trick. But get_pets() is kind of broken now - it always returns only 3 results. So our contact page says that we only have 3 pets. That’s not true!

Instead of hardcoding 3 in the query, let’s add a $limit argument to the function:

// lib/functions.php
function get_pets($limit)
{
    // ...
}

Now we can use this to dynamically create the query string. Technically, this is simple. But wait! What I’m about to show you is a huge security hole, I mean huge! Take the $limit variable and add it to the end of the string. This is called concatenation:

// lib/functions.php
function get_pets($limit)
{
    // ...

    // THIS IS A HUGE SECURITY FLAW - TODO - WE WILL FIX THIS!
    $result = $pdo->query('SELECT * FROM pet LIMIT '.$limit);

    // ...
}

Now, open up index.php and pass 3 as an argument to get_pets():

// index.php
require 'lib/functions.php';

$pets = get_pets(3);

Now refresh. We still have 3 pets!

SQL Injection Attacks

But don’t celebrate - we have a big security bug! Whenever you create a database query that has some variable part to it, you open yourself up to an SQL injection attack. This is probably the most common attack on the web and one mess-up will give an attacker full access to do anything to your databse.

I’ll explain this attack more in a few minutes, and we’ll close this security hole.

Leave a comment!

4
Login or Register to join the conversation
Jay-P Avatar

Where can I download the complete resources files?

Reply
Victor Avatar Victor | SFCASTS | Jay-P | posted 2 months ago | edited

hey Jay,

You can download all the resources related to this course on any course video page - see the "Download" button in the top right corner of this page above the video player. If the links are not active for you - that means the course is paid and to download the resources you either need to buy the specific course or buy a SymfonyCasts subscription first.

If you have trouble accessing resources of courses you paid for - feel free to contact us directly via our Contact form describing your problem: https://symfonycasts.com/contact

Cheers!

Reply
Jay-P Avatar

Thank you Victor, I just finished the course and it was great! Thank you.

Reply

Hey Jay,

Awesome! Thanks for confirming it works for you now :)

If you ever have any questions - don't hesitate to ask ;)

Cheers!

Reply
Cat in space

"Houston: no signs of life"
Start the conversation!

userVoice