Query one table and if data is not there, query another table

Question

I have store the same data in two tables: tableA and tableB. I want to select first from tableA, and if there is no data, I want to used tableB. tableA and tableB hold exactly the same data. I used tableB for archiving. There maybe times when we delete data from tableA but it will still be there in tableB.

$q = $dbh->query("SELECT * FROM tableA WHERE id='1'");
if($q->fetchColumn==NULL){
    $q = $dbh->query("SELECT * FROM tableB WHERE id='1'");
}

foreach($q->fetchAll() as $data):
     echo $data['name'];
endforeach;

The problem with this code is that it returns from tableB whereas tableA data is there. How to solve this problem? Please help.

TableA

     id, name, age
     1   john   45

TableB

   bid, id,   name,    age
    1    1  old john    45

If there is no data in tableA, I want the query to select from tableB. But if there is data in TableA, I want to select from it(tableA). My code outputss from tableB.

The errors:

   fetchColumn()==NULL//does not output anything
   fetchColumn(0)==NULL//does not output anything
   fetchColumn==NULL//outputs from tableB
   fetchColumn==0//outputs from tableB
   fetchColumn()==0/does not output anything

Expected output: john

PHP Version 7.1.1

Use `fetchColumn()` and specify a column name in the query. Right now you're trying to assess *all* columns. — Jay Blanchard, Mar 20 '17 at 14:11
No, you want the first row, not the second. `fetchColumn(1)` gets the second row. *(Array counts start at 0)* — Jay Blanchard, Mar 20 '17 at 14:16
@JayBlanchard, I specify the column name but it does not work. No output. — Zone Security, Mar 20 '17 at 14:17
Do you have any errors? You're not checking for them in your code, so you'll have to look in the error logs. — Jay Blanchard, Mar 20 '17 at 14:18
isn't `if($q->fetchColumn==NULL){` suppose to be `if($q->fetchColumn()==NULL){` ? — Masivuye Cokile, Mar 20 '17 at 14:21
[That's what I said](http://stackoverflow.com/questions/42905797/query-one-table-and-if-data-is-not-there-query-another-table?noredirect=1#comment72911214_42905797) @MasivuyeCokile — Jay Blanchard, Mar 20 '17 at 14:21
I looked at php error logs and apache error logs. there is nothing there. — Zone Security, Mar 20 '17 at 14:21
I find it hard to believe there is nothing in either log, especially the access log. What web server are you using? — Jay Blanchard, Mar 20 '17 at 14:22
@JayBlanchard clearly the code should give undefined index `fetchColumn` — Masivuye Cokile, Mar 20 '17 at 14:22
add this at the top of your script : `ini_set('display_errors', 1); error_reporting(E_ALL);` — Masivuye Cokile, Mar 20 '17 at 14:23
@JayBlanchard, I am using xampp v3.2.2. I looked at all the logs. nothing like undefined is there. — Zone Security, Mar 20 '17 at 14:25
@MasivuyeCokile, I did your suggestion. If I use fetchColumn()==Null, it does not output anything, just blank screen. And then if I use fetchColumn==Null, it outputs from the tableB. But I want it to output from tableA because tableA holds the data. Only if there is no data in the tableA that tableB should output. — Zone Security, Mar 20 '17 at 14:29
the field in tableA are id, name, age, and the field in tableB holds bid, id, name,age. Each holds one row of data that is exactly the same. As you see tableB has bid as its primary key. — Zone Security, Mar 20 '17 at 14:33
@MasivuyeCokile, `$q->fetchColumn() == 0` takes data from tableB. — Zone Security, Mar 20 '17 at 14:35
How do u knw if this is returning data of table B? if the data are the same? — Masivuye Cokile, Mar 20 '17 at 14:35
Did you change the query to `"SELECT id FROM tableA WHERE id='1'"`? — Jay Blanchard, Mar 20 '17 at 14:47
No, I am using the code in my question except the fetchColumn part as you suggested. — Zone Security, Mar 20 '17 at 14:48
I also said to [select a specific column](http://stackoverflow.com/questions/42905797/query-one-table-and-if-data-is-not-there-query-another-table?noredirect=1#comment72911214_42905797). `fetchColumn()` needs a column to work with. — Jay Blanchard, Mar 20 '17 at 14:51
Then there must be something else wrong. There is no way the code I wrote would fail if you're telling us everything. — Jay Blanchard, Mar 20 '17 at 15:01
what does http://php.net/manual/en/pdo.error-handling.php to the queries show? also make sure that there is absolutely "no data" in the first table. If there are `NULL` default values, `NULL` is not the same as "empty" and vice-versa. Run a `var_dump()` also everywhere. — Funk Forty Niner, Mar 20 '17 at 15:16
your table isn't empty then @ZoneSecurity but that's a dump from where/what? — Funk Forty Niner, Mar 20 '17 at 15:32
thanks everyone for your help. I did solve my problem by adding another query to check whether the first query holds any data. — Zone Security, Mar 20 '17 at 15:57
*"If tableA is empty I want to output data from TableA"* - I don't quite understand this. How can you output something if "nothing" is in there to start with? Or did you make a mistake in that comment? — Funk Forty Niner, Mar 20 '17 at 16:26
@Fred-ii- he was trying to say, if the record does not exist on table A select on table B — Masivuye Cokile, Mar 20 '17 at 16:31
@MasivuyeCokile well we all know who the real "God" of bashing is too, huh? Really a touch of class I'll say. — Funk Forty Niner, Mar 20 '17 at 16:34
@JayBlanchard, why did you remove your answer? While you suggested using prepared query, you are also using unprepared in your answer. This is really confusing because you downvoted my answer. I did change my query to prepared one. — Zone Security, Mar 20 '17 at 17:33
I did not down vote your answer. I did not use a prepared statement because you were hard-coding a value in your original question. Hard-coded queries do no require prepared statements. — Jay Blanchard, Mar 20 '17 at 19:22

Masivuye Cokile · Accepted Answer · 2017-03-20T15:08:59.850

0

This will work, have tested with the sample data you given, as Jay have said when u use fetchColumn() you need to specify atleast one column.

<?php
    ini_set('display_errors', 1);
    error_reporting(E_ALL);

    $q    = $dbh->query("SELECT * FROM tablea WHERE id='1'");
    $data = $q->fetchColumn(1); //name
    if ($data) {

        echo $data;

    } else {

        $q = $dbh->query("SELECT * FROM tableb WHERE id='1'")->fetchall();
        foreach ($q as $data) {

            echo $data['name'];
        }

    }

    ?>

Results :

Results when id from table a is not 1

edited Mar 20 '17 at 15:08

answered Mar 20 '17 at 14:59

Masivuye Cokile

4,754
3
19
34

I did @JayBlanchard `fetchColumn(1)` this case the column is `name` – Masivuye Cokile Mar 20 '17 at 15:03
`SELECT * ` when fetchColumn()` counts at `(0)` its the id then at `(1)` its name – Masivuye Cokile Mar 20 '17 at 15:07
OK - I just tested and it works fine. Previous versions of PHP were a little more specific here. – Jay Blanchard Mar 20 '17 at 15:08
I'm at php 7.1 previously could not get results – Masivuye Cokile Mar 20 '17 at 15:09
Yeah - I was testing earlier PHP when I did this and it seems 7.1 is a little more lenient. – Jay Blanchard Mar 20 '17 at 15:10
in php 5.6 both your codes work with me as well. But I am using php 7.1.1 and it did not work. – Zone Security Mar 20 '17 at 15:15
I'm on php 7.1.1 and the code above works @ZoneSecurity as u can see the results – Masivuye Cokile Mar 20 '17 at 15:17
Yes I know. but the problem is that I have to write more codes. I want to put my code in one foreach loop. – Zone Security Mar 20 '17 at 15:29
Someone went through and Dv'd all of the answers, including the OP's – Jay Blanchard Mar 20 '17 at 16:15
@JayBlanchard the Op deserve the dv, `WHERE id='$id'` maybe he needed to prepare the statement – Masivuye Cokile Mar 20 '17 at 16:17
The OP deserves the DV because the methodology is wrong. He may not be aware of the SQL Injection problems, but we typically do not DV someone because they've put themselves at risk. – Jay Blanchard Mar 20 '17 at 16:19
I now know who DV'd all of the answers. See the new answer. – Jay Blanchard Mar 20 '17 at 16:21
thought as much also @JayBlanchard – Masivuye Cokile Mar 20 '17 at 16:22
I did give the answer as accepted because it solved the problem. But what I actually used is my answer below. – Zone Security Mar 20 '17 at 17:21

Your Common Sense · Answer 2 · 2017-03-20T16:28:09.670

0

Fantastic question, fantastic answers.

If there is no data in tableA, I want the query to select from tableB. But if there is data in TableA, I want to select from it(tableA).

$stmt = $dbh->prepare("SELECT * FROM tableA WHERE id=?");
$stmt->execute([$id]);
$data = $stmt->fetch();
if (!$data) {
    $stmt = $dbh->prepare("SELECT * FROM tableB WHERE id=?");
    $stmt->execute([$id]);
    $data = $stmt->fetch();
}
echo $data['name'];

incredible complex logic with a lot of queries

edited Mar 20 '17 at 16:28

answered Mar 20 '17 at 16:11

Your Common Sense

156,878
40
214
345

2

So, answers which work get downvoted? I don't understand, please enlighten. It is one thing to disagree with method, quite another to DV just because you don't care for the complexity. – Jay Blanchard Mar 20 '17 at 16:23

Zone Security · Answer 3 · 2017-03-20T17:11:44.090

-2

Million thanks to everyone who helps me here. I solved my problem by adding another query to check the first query. This solution gives me what I want.

$id = 1;
$qr = $dbh->prepare("SELECT id, name, age FROM tableA WHERE id=:id");
$qr->execute(array(':id' => $id));
$res = $dbh->prepare("SELECT COUNT(*) FROM tableA WHERE id=:id");   
$res->execute(array(':id' => $id));
$row = $res->fetchColumn();
if( $row <= 0){ 
   unset($qr);
   $qr = $dbh->prepare("SELECT * FROM tableB WHERE id=:id");
   $qr->execute(array(':id' => $id));
}
$results = $qr->fetchAll();
foreach($results as $data):
     echo $data['name'];//output is john
endforeach;

edited Mar 20 '17 at 17:11

answered Mar 20 '17 at 16:06

Zone Security

3
3

this is not how u use pdo – Masivuye Cokile Mar 20 '17 at 16:13
Yeah -- you're covering up for some other issue somewhere. There is *no reason* you should have to use another query. – Jay Blanchard Mar 20 '17 at 16:17
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! – Jay Blanchard Mar 20 '17 at 16:18
Yes I know. I use prepare query in my real code. This here is for my solution in an easy to understand code. – Zone Security Mar 20 '17 at 16:47
@JayBlanchard, I fixed my code with prepared query. This answer is which works for me. Without using another query as you suggested, It did not work for me. – Zone Security Mar 20 '17 at 17:16

Query one table and if data is not there, query another table

3 Answers3