Combine two tables with same DB SELECT statements-CodePudding

Most people instruct that you shouldn't make a bunch of DB connections in one request. So i tried to combine the following tables (car & bike) and failed. Any idea how to combine both and avoid duplicate entries if i use the following php while functions?

PHP:

$sql = "SELECT * FROM car WHERE price NOT LIKE '' order by id asc";
$qry = mysqli_query($conn,$sql);
while( $val = mysqli_fetch_array($qry) ) {

    if ( ( ($val['city'] == $to ) && ($val['airport'] == $from) ) || ( ($val['airport'] == $to ) && ($val['country'] == $from) ) ) {
    
    $distance = $val['distance'];
    
    } 
}


$sql = "SELECT * FROM bike WHERE price NOT LIKE '' order by id asc";
$qry = mysqli_query($conn,$sql);
while( $val = mysqli_fetch_array($qry) ) {

    if ( ( ($val['village'] == $to ) && ($val['airport'] == $from) ) || ( ($val['airport'] == $to ) && ($val['country'] == $from) ) ) {
    
        $distance = $val['distance'];
    
    } 
}

SQL:

SELECT * FROM car WHERE price NOT LIKE '' order by id asc

and

SELECT * FROM bike WHERE price NOT LIKE '' order by id asc

CodePudding user response：

If you want to remove duplicates, that being defined as two cars or bikes having the same name, you may use a union between the two tables:

SELECT name FROM car
UNION
SELECT name FROM bike;

If the car and bike tables happen to have different structure (i.e. different columns and/or types of columns), then you would need to specify which columns you want to combine to have in the final result set.

CodePudding user response：

You can use UNION, which will also remove duplicates. Also LIKE is not needed because you aren't using a pattern:

SELECT name 
FROM car 
WHERE price <> '' 
ORDER BY id asc

UNION 

SELECT name  
FROM bike 
WHERE price <> '' 
ORDER BY id asc