Perform 3 simultaneous searches in the same table

Asked

Viewed 156 times

1

I wonder if it is possible to perform 3 simultaneous searches in the same table. The situation is as follows: I have a table that contains let’s say the columns:

Car, Material, IP, date

Then I have to get: 

1 - Quantos carros foram cadastrados na ultima hora
2 - Quantos Materiais foram cadastrados na última 2 horas
3 - Quantos Ips acessaram nos últimos 30 minutos

it is possible for me to perform these 3 simultaneous searches, where I have to return the number of data found in each of them.

The way I do it is this:

$conn->prepare("QUERY 1");
$conn->prepare("QUERY 2");
$conn->prepare("QUERY 3");

I call the prepare 3 times, I would like to minimize this, call only once and return the number of data separated by the occurrence number:

Query 1= 10 
Query 2= 15
Query 3= 17

This is possible or I will have to search the way I currently do, all separately?

php mysql

2 answers

5

An output with mysqli is to use multi-query.

Follow an example from PHP documentation:

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query  = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";

/* execute multi query */
if ($mysqli->multi_query($query)) {
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->free();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------\n");
        }
    } while ($mysqli->next_result());
}

/* close connection */
$mysqli->close();
?>

Notes:

  • mysqli_store_result()Download a set of results from the active query
  • mysqli_next_result() Prepares the next multi_query result
  • mysqli_more_results() Checks if there are any other results of a multi query to be processed

3


Subquery no from seems to be an alternative:

SELECT a.carros, b.materiais, c.ips FROM 
    (SELECT carros ...) AS a, 
    (SELECT materiais ...) AS b, 
    (SELECT ips ...) AS c

I hope I helped, hug

  • Dude I really believe this is the way, but I’m getting it, how to separate what I want to tell you understands: SELECT COUNT(a.user_tentativa) FROM (SELECT * FROM tentativas_login WHERE email_tentativa = '[email protected]') AS a, this way it returns right, but the moment it adds another select, it ends up returning everything.

  • @abcd put the count(*) AS label in subselects and place in the main select fields only the Labels that will work.

  • Sorry, I tried to do it this way: "SELECT b, a FROM (SELECT COUNT(*) FROM tentativas_login WHERE email_tentativa = '[email protected]') AS a, (SELECT COUNT(*) FROM tentativas_login WHERE user_tentativa = 'jhon1') AS b"

  • I am very weak, in sql, usually I just make easy SELECTS... It does not return anything...

  • 1

    @abcd in the main select fields you need to put the label and field you want to bring, so: SELECT b.tentativa_b, a.tentativa_a FROM (SELECT COUNT(*) AS tentativa_a FROM tentativas_login WHERE email_tentativa = '[email protected]') AS a, (SELECT COUNT(*) AS tentativa_b FROM tentativas_login WHERE user_tentativa = 'jhon1') AS b

  • Thank you very much, it was right, now I will implement the way I wish. ATT

Show 1 more comment

Browser other questions tagged php mysql

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