How to select specific rows from 2 databases

Question

These requests work as expected. It gets everything from both the main_tb and the second_tb tables.

    if ($isSet != 'false') {
        $DB_con->query("INSERT INTO second_tb (art_id, dat_se, dat_seID, userID, createdOn) VALUES ('$id_art', '$dat_se', '$dat_seID', '".$_SESSION['userID']."', NOW())");
        $sql = $DB_con->query("SELECT second_tb.id, name, dat_se, DATE_FORMAT(second_tb.createdOn, '%Y-%m-%d') AS createdOn FROM second_tb INNER JOIN users ON second_tb.userID = users.id ORDER BY second_tb.id DESC LIMIT 1");
    } else {
        $DB_con->query("INSERT INTO main_tb (art_id, userID, dat_se, createdOn) VALUES ('$id_art', '".$_SESSION['userID']."','$dat_se',NOW())");
        $sql = $DB_con->query("SELECT main_tb.id, name, dat_se, DATE_FORMAT(main_tb.createdOn, '%Y-%m-%d') AS createdOn FROM main_tb INNER JOIN users ON main_tb.userID = users.id ORDER BY main_tb.id DESC LIMIT 1");
    }

    $data = $sql->fetch_assoc();

But I would only like to select rows from main_tb and second_tb where art_id = '$id_art' , and if it exists
$id_art is an intenger. So I tried this.

    if ($isSet != 'false') {
        $DB_con->query("INSERT INTO second_tb (art_id, dat_se, dat_seID, userID, createdOn) VALUES ('$id_art', '$dat_se', '$dat_seID', '".$_SESSION['userID']."', NOW())");
        $sql = $DB_con->query("SELECT  second_tb.id,second_tb.art_id, name, dat_se, DATE_FORMAT(second_tb.createdOn, '%Y-%m-%d') AS createdOn FROM second_tb WHERE second_tb.art_id = '$id_art' INNER JOIN users ON second_tb.userID = users.id ORDER BY second_tb.id DESC LIMIT 1");
    } else {
        $DB_con->query("INSERT INTO main_tb (art_id, userID, dat_se, createdOn) VALUES ('$id_art', '".$_SESSION['userID']."','$dat_se',NOW())");
        $sql = $DB_con->query("SELECT main_tb.id,main_tb.art_id, name, dat_se, DATE_FORMAT(main_tb.createdOn, '%Y-%m-%d') AS createdOn FROM main_tb WHERE main_tb.art_id = '$id_art' INNER JOIN users ON main_tb.userID = users.id ORDER BY main_tb.id DESC LIMIT 1");
    }

    $data = $sql->fetch_assoc();

But I get the following error.

Fatal error: Uncaught Error: Call to a member function fetch_assoc() on bool in C:\wamp64\www\blog\det_art.php on line 97

Could someone please help me with that ?

**WARNING**: Your queries are likely wide open to [SQL injection](https://stackoverflow.com/questions/601300/what-is-sql-injection); use [parameterized queries](https://stackoverflow.com/questions/4712037/what-is-parameterized-query) to mitigate this. — esqew, May 26 '20 at 18:49

score -1 · Answer 1 · answered May 26 '20 at 18:57

-1

The WHERE clause comes after JOIN, not before it.

$sql = $DB_con->query("
    SELECT  second_tb.id,second_tb.art_id, name, dat_se, DATE_FORMAT(second_tb.createdOn, '%Y-%m-%d') AS createdOn 
    FROM second_tb 
    INNER JOIN users ON second_tb.userID = users.id 
    WHERE second_tb.art_id = '$id_art' 
    ORDER BY second_tb.id DESC 
    LIMIT 1");

answered May 26 '20 at 18:57

Barmar

741,623
53
500
612

Thanks for your feedback @Barmar. I applied that fix but the issue is still reproducible – Kyv May 26 '20 at 23:01
Add error checking and tell me the error: https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-in-different-environments – Barmar May 26 '20 at 23:10

How to select specific rows from 2 databases

1 Answers1