PHP / SQL - Multiple table search

Question

Hey guys i am looking for some way to search in multiple database table without using UNION. My code is pretty simple, its something like:

$con = new PDO( DB_HOST, DB_USER, DB_PASS ); 
    $con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $sql1 = "SELECT * FROM evidence_vin WHERE vin = :vin LIMIT 1";
    $sql2 = "SELECT * FROM national_register_sk WHERE vin = :vin LIMIT 1";
    $sql = $sql1 . ' UNION ' . $sql2;

    $stmt = $con->prepare( $sql ) union ( $sql2 );
    $stmt->bindValue( "vin", $this->vin, PDO::PARAM_STR );
    $stmt->execute();
        echo "<table>";
        echo "<th>Progress</th>";
        echo "<th>Claim number</th>";
        echo "<th>Make</th>";
        echo "<th>Status</th>";
        echo "<th>View</th>";
        echo "<th>Action</th>";
        while ($row = $stmt->fetch()){
            echo "<tr>";
            echo "<td>24</td>";
            echo "<td>".$row['claim_number']."</td>";
            echo "<td>".$row['license']."</td>";
            echo "<td>".$row['country']."</td>";
            echo "<td>".$row['vin']."</td>";
            echo "<td><a href=\"detail.php?id=".$row["id"]."&action=detail\">detail</td>";
            echo "</tr>";
             } 
        }catch(PDOExeption $e){
        echo $e->getMessage();

I have some problems with this because i will need some specific data from table one and some specific from table 2 and i am not sure which one i will need yet. So i need some way to get a line from each table where VIN is $this->vin. (vin is in both tables same)

p.s. Tables dont have same number of colums

As you select only one row from each table, why not use 2 queries? — Marek
– Marek, Commented Aug 14, 2014 at 10:02
right i want to avoid write same function twice and looks i dont know to write 2 queries in 1 function. Can you help me with that? — Martin Ferko
– Martin Ferko, Commented Aug 14, 2014 at 10:04

SudoGetBeer · Accepted Answer · 2014-08-14 10:01:45Z

1

Have a look at JOINS: http://www.sitepoint.com/understanding-sql-joins-mysql-database/

If i understand your problem correctly that should solve it.

answered Aug 14, 2014 at 10:01

SudoGetBeer

1281 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

lukaszwierz · Accepted Answer · 2014-08-14 11:39:08Z

0

If You want to get different columns from those tables, just make two queries. Seems like only way. Also if You want all ingo on one VIN in one line , than use JOINS , but than You need to be sure that format of those VIN columns is the same.

@Martin , as for multiple queries: http://php.net/manual/en/mysqli.quickstart.multiple-statement.php,

I know its mysqli but its only one I found - its very bad practice.

But in Your place I would do something like that:

SELECT * FROM evidence_vin ev LEFT OUTER JOIN national_register_sk nrs ON ev.vin = nrs.vin where ev.vin = :vin LIMIT 1

But remember that if VIN will not be present in first table and will be in second You won't get results.

edited Aug 14, 2014 at 11:39

answered Aug 14, 2014 at 10:06

lukaszwierz

212 bronze badges

1 Comment

Martin Ferko Over a year ago

I am not sure how to do multiple query in same funtion

Collectives™ on Stack Overflow

PHP / SQL - Multiple table search

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related