I'm having a problem regarding the return information limit per query,
For example:
I have a table called "Cars". This table has 2 fields "Model" and "Color".
How can I make a query that returns me 3 cars from the Gol model and 2 cars from the Celta model?
I'm not sure, but I think it's something related to "LIMIT", but I'm not able to do with 2 conditions, which in this case is limiting the query to 3 cars of the Gol model and 2 cars of the Celta model. >
Note: I'm using Mysql.
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 you can adopt two approaches.
In PHP just create two different queries and execute. 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 use UNION to get 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 it plastered to reuse in another situation in the future. Thus, you can use the two options by creating a function that receives the array with the parameters.
For the first option a 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 loop is created to concatenate more SQL queries.
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 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.