How to merge / simplify both same query

316
April 19, 2017, at 2:11 PM

I have two same query

Query 1 :

$request1 = $bdd->prepare('SELECT * from table1, table2 where table1.FK_license = table2.license and date = (select max(date) from table1 where user = :user)');
$request1-> execute(array(
    ':user' => $id
));

Query 2 :

$request2 = $bdd->prepare('SELECT * from table1, table2 where table1.FK_license = table2.license and date = (select max(date) from table1 where user = :user)');
$request2-> execute(array(
    ':user' => $id
));
$test = $request2->fetch();

But I need the first query to make a loop like this :

    <?php foreach ($request1 as $value) { ?>
        <tr>
            <td><?php echo $value['li']; ?></td>
            <td><?php echo $value['cu']; ?></td>
            <td><?php echo $value['qt']; ?></td>
            <td><?php echo $value['pk']; ?></td>
        </tr>
    <?php } ?>

And the second request only to retrieve data common to a certain line

$test['data_for_group_by']

How I can do for only use one query for do the same ? Because when I fetch the first request I can't do foreach after

Thank you

EDIT : Final code looks like

//This request allows me to create the loop to list all my articles
$request1 = $bdd->prepare('SELECT * from table1, table2 where table1.FK_license = table2.license and date = (select max(date) from table1 where user = :user)');
$request1-> execute(array(
    ':user' => $id
));

//With this query I retrieve a serial number (common to all articles) => $test['data_for_group_by']
$request2 = $bdd->prepare('SELECT * from table1, table2 where table1.FK_license = table2.license and date = (select max(date) from table1 where user = :user)');
$request2-> execute(array(
    ':user' => $id
));
$test = $request2->fetch();

//Here I count the number of article that I have
$license = $bdd->prepare('SELECT count(id) from table1 where number = :number');
$license ->execute(array(
    ":number" => $test['data_for_group_by']
    ));
$sum1 = $license->fetchColumn();

//And I recover the total weight of the articles (knowing that this details of weight this finds on another table)
$sum = $bdd->prepare('SELECT ROUND(SUM(pk), 2) from table1, table2 where table1.FK_license = table2.license and number = :number');
$sum ->execute(array(
    ":number" => $test['data_for_group_by']
    ));
$sum2 = $sum->fetchColumn();

?>
<p class="text-center">Quantity : <?php echo $sum1;?> - Weight : <?php echo $sum2;?> kg</p>
=== Foreach Loop ===
Rent Charter Buses Company
READ ALSO
AWS RDS + INTO OUTFILE

AWS RDS + INTO OUTFILE

I understand that we can't do a INTO OUTFILE when we are dealing with RDS

559
Display two similar queries in one table

Display two similar queries in one table

I'm trying to write a query which will return which garment generated the most revenue during the last three months compared to the same period last yearI want to display the results in one table

226
Mysql Update on Update trigger

Mysql Update on Update trigger

I have a Denormalized database where I have the total points of other table into the driver tableI want to update the total points of the table driver when I update the table where the points are

315
Unable to use LIKE with GROUP BY

Unable to use LIKE with GROUP BY

I want to get rid of duplicate entries from database, so I thought of using GROUP BY but it doesn't seem to work with LIKEBelow is my query

293