CODEWITHSUNDEEP
phpmysqlsqlpdo
argie cruz
argie cruz

Reputation: 223

How can i query two different tables in mysql

how can i validate two queries, if the first query is NULL proceed to second query?

1st query:

 SELECT max(logs) as logs,id FROM table1 WHERE 
 message NOT LIKE "[ SYSTEM%"

2nd query:

 SELECT max(logs) as logs,id FROM table2 WHERE 
 message LIKE "[ SYSTEM%"

Upvotes: 7

Views: 138

Answers (3)

parveen
parveen

Reputation: 577

This query looks ugly, but I think you can do it

(SELECT max(logs) as logs,id
 FROM table1 
 WHERE message NOT LIKE "[ SYSTEM%")
UNION ALL
(SELECT max(logs) as logs,id
 FROM  table2
 WHERE message LIKE "[ SYSTEM%")

You can imagine how faster we can get the records if used only one query instead of two.

you can also add flag in result (just to check if record from table 1 or table 2 )

Upvotes: 1

cn0047
cn0047

Reputation: 17061

I think you should look to your explain. Because less count queries to db - better. In this case you should use union:

SELECT max(logs) as logs, id, 'table1' type
FROM table1 WHERE message NOT LIKE "[ SYSTEM%"
UNION
SELECT max(logs) as logs, id, 'table2' type
FROM table2 WHERE message LIKE "[ SYSTEM%"
;

by field type you can understand from which table you receive data.
But if your explain will be bad, you should use separate queries:

<?php

$dbh = new PDO('mysql:dbname=test;host=127.0.0.1', 'root');

$sth = $dbh->prepare('
    SELECT max(logs) as logs, id
    FROM table1 WHERE message NOT LIKE :like
');
$sth->bindParam(':like', $like, PDO::PARAM_STR);
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_ASSOC);

if (empty($result)) {
    $sth = $dbh->prepare('
        SELECT max(logs) as logs, id
        FROM table2 WHERE message LIKE :like
    ');
    $sth->bindParam(':like', $like, PDO::PARAM_STR);
    $sth->execute();
    $result = $sth->fetchAll(PDO::FETCH_ASSOC);
}

var_export($result);

Upvotes: 1

Vinod B Dhupad
Vinod B Dhupad

Reputation: 32

   $query1 = "SELECT max(logs) as logs,id FROM table1 WHERE 
 message NOT LIKE '[ SYSTEM%'";
$query2 = SELECT max(logs) as logs,id FROM table2 WHERE 
 message LIKE '[ SYSTEM%'";
if(mysql_query($query1)) {
    $result = mysql_query($query1);
} else {
   $result = mysql_query($query2);
}

Upvotes: 0

Related Questions