Is it possible to make a selection from a selection using SQL tools? For example, the selection of all messages from the database:

$query = "SELECT * FROM table WHERE id='$id'"; $result= mysql_query($query);

Now in $ resoure - all messages. We display their number. Now it is necessary to select the records with the specified value of the 'is_read' field. Can I somehow use the previous result, or will have to do another sample (for the same data !!):

 $query = "SELECT * FROM table WHERE id='$id' AND is_read='not_read'"

    3 answers 3

    Why it is impossible, for example, to first choose the count by that query

     $query = "SELECT COUNT(*) as total FROM table WHERE id='$id'"; $result= mysql_query($query);

    output total , but then do the selection by the criteria you are interested in?

    • So I did. - Deus

    You can work with arrays, for example, you pulled everything from the database:

     $result= mysql_fetch_assoc(mysql_query($query)); // Далее если вам где нужно вытащить из этого массива то можно это так $a = array_filter($result,function ($v) {return $v['is_read'] == "not_read";}) print_r($a);

    This will give you all the arrays where is_read = 'not_read' and you do not need to make a request all the time. In general, something like this should be

    • Very good option for small amounts of data. But you can only pull the first entry out of the database with your code. Thus, arrays are out of the question. - KaZac
    • Oh yeah, you're right, it will only give the first element, but with the help of cycles you can get all the arrays we need, like this: // For a loop, you need a key such as $ result [$ i] ["not_read"]; for ($ i = 1; $ i <count ($ result) +1; $ i ++) {$ res [$ i] = array_values ​​(array_filter ($ result, function ($ v) {return $ v ['is_read'] == $ result [$ i] ["not_read"];})); } // if you make print_r ($ res), then you will see unnecessary keys np: array ([0] => [0] => [is_read], [1] => [1] => [is_read]) // for this delete the first foreach keys ($ res as $ k => $ v) $ res [$ k] = $ v [0]; - bemulima

    Alternatively, you can try using MySQL

     SELECT * FROM (SELECT COUNT(table.id) FROM table WHERE table.id='$id') As countId, (SELECT table.* FROM table WHERE table.id='$id') As tmpTabel WHERE tmpTabel.is_read='not_read'

    As a result, we get a table with the sample is_read='not_read' and an additional value in each row countId