Query in SQL, query a limited number of record

Asked

Viewed 246 times

0

I am having a problem regarding the limit of return of information per query,

For example:

I have a table called "Cars". This table has 2 fields "Model" and "Color".

How can I make an appointment that returns me 3 cars of the Gol model and 2 cars of the Celta model?.

I’m not sure, but I believe it is something related to the "LIMIT", but I’m not able to do with 2 conditions, in this case is to limit the consultation to 3 cars of the Gol model and 2 cars of the Celta model.

Note: I am using Mysql.

php database
  • 1

    For example, in Mysql you can merge several darlings using UNION to get a result only: (SELECT modelo, cor FROM carros WHERE modelo="Gol" LIMIT 3) UNION (SELECT modelo, cor FROM carros WHERE modelo="Celta" LIMIT 2). It would be interesting to click on [Edit] and add the type of BD you are using.

1 answer

0


In most cases SQL does not have functions for this. if I have been using Mysql it is certain that it is not possible. But to solve your problem it is possible to adopt two approaches.

  • Use two different queries in PHP
  • Use Mysql UNION function

In PHP just create two different queries and run. Example:

$return = array();

$stmt = $conn->prepare('SELECT `modelo`, `cor` WHERE `modelo` = :modelo LIMIT :limit');
$stmt->execute( array( ':modelo' => 'Gol', ':limit' => 3 ) );
$list = $res->fetchAll();
foreach( $list as $record ) {
    $return[] = $record;
}

$stmt->execute( array( ':modelo' => 'Celta', ':limit' => 2 ) );
$list = $res->fetchAll();
foreach( $list as $record ) {
    $return[] = $record;
}

return $return;

In Mysql you can turn to UNION so your PHP;

$stmt = $conn->prepare('
    ( SELECT `modelo`, `cor` WHERE `modelo` = :modelo1 LIMIT :limit1 )
    UNION
    ( SELECT `modelo`, `cor` WHERE `modelo` = :modelo2 LIMIT :limit2 )
');

$stmt->execute( array(
    ':modelo1' => 'Gol',
    ':limit1' => 3,
    ':modelo2' => 'Celta',
    ':limit2' => 2
) );
return $res->fetchAll();

Both cases solve the problem, but leave the cast to reuse in another situation in the future. Thus, you can resort to both options by creating a function that receives the array with the parameters.

For the first option a repetition loop is created to execute a query by position of the array passed to the function, thus:

function modelos ($parameters) {
    $return = array();

    foreach( $parameters as $parameter ) {
        $stmt = $conn->prepare('SELECT `modelo`, `cor` WHERE `modelo` = :modelo LIMIT :limit');
        $stmt->execute( $parameter );
        $list = $res->fetchAll();

        foreach( $list as $record ) {
            $return[] = $record;
        }
    }
    return $return;
}

For the second option a repeat loop is created to concatenate more queries to sql.

function modelos ($parameters) {
    $sql = array();
    $newParameters = array();

    foreach( $parameters as $index => $parameter ) {
        $sql[] = '( SELECT `modelo`, `cor` WHERE `modelo` = :modelo' . $index . ' LIMIT :limit' . $index . ' )';
        $newParameters[':modelo' . $index] = $newParameters[':modelo'];
        $newParameters[':limit' . $index] = $newParameters[':limit'];
    }
    $sql = join(' UNION ', $sql);
    $stmt = $conn->prepare($sql);
    $stmt->execute($newParameters);
    return $res->fetchAll();
}

To call the methods:

$parameters = array (
    array( ':modelo' => 'Gol', ':limit' => 3 ),
    array( ':modelo' => 'Celta', ':limit' => 2 )
);
$modelos = modelos($parameters);

The two functions can affect the performance of the system depending on the amount of query, but for the problem presented are some viable solutions, and I believe that the function of different queries in PHP is something more elegant.

Browser other questions tagged php database

You are not signed in. Login or sign up in order to post.