CODEWITHSUNDEEP
phpmysql
Fred W.
Fred W.

Reputation: 5

Manipulating MySQL database using php

Let's say that I want to perform 2 MySQL operations:

Operation 1: 

SELECT * From Comments ORDER BY ID DESC

Operation 2: 

SELECT * From Comments WHERE Date >'2012-05-01', ORDER BY ID ASC

Clearly, the second table is contained within the first; the specifics are just hypothetical. My question: Rather than perform 2 separate MySQL queries as done above, is it possible to obtain just the first, use it, then LATER manipulate it using php to obtain the second? In the example above, the php function would need to alter the order of the table and remove inapplicable rows, but again, it's just an example.

Why do I care?

Well, I'm trying to limit the number of MySQL connections to my server. Rather than have 5 separate queries that reference the same one master table, I'd prefer to have just 1, and use php to manipulate it 4 times so as to get the same final product.

Thanks!

Upvotes: 0

Views: 422

Answers (2)

walkeros
walkeros

Reputation: 4942

It seems that can be done like this:

  1. Launch first query

  2. iterate backwords through results (you will programatically have ASC order instead of DESC)

    2a. for each record check if "Date" meets your criteria

Upvotes: 0

deceze
deceze

Reputation: 522251

No, you cannot "alter the table". When you query the database from PHP, you're not getting "the table" back as a result, you're just getting a result set back. What you can perfectly do is store this result in a PHP variable and use PHP code to further filter or sort that data:

$result  = $pdo->query('SELECT * From Comments ORDER BY ID DESC')->fetchAll();
$result2 = array_filter($result, function (array $row) {
    return strtotime($row['Date']) > strtotime('2012-05-01');
});
usort($result2, function (array $a, array $b) {
    return $a['ID'] - $b['ID'];
});

Upvotes: 3

Related Questions