MYSQL & PHP Display data from multiple tables

Question

I have a database that has three tables with the same fields. I'm trying to get it to display the content, but I'm running into two issues. Here is what is on the PHP file. Can someone please help?

Issue 1: Only one table displays
Issue 2: If I add all tables and any have no data the out put is NULL

(I tried adding $query = "SELECT * FROM main_office,second_office,third_office"; but that didn't work.)

<?php
    class Location {
        public $address;
        public $city;
        public $us_state;
        public $zip;
        public $longitude;
        public $latitude;

    public function setAddress($address) {
        $this->address = $address;
    }
    public function setCity($city) {
        $this->city = $city;
    }
    public function setState($us_state) {
        $this->us_state = $us_state;
    }
    public function setZip($zip) {
        $this->zip = $zip;
    }
    public function setLongitude($longitude) {
        $this->longitude = $longitude;
    }
    public function setLatitude($latitude) {
        $this->latitude = $latitude;
    }
    }

header('content-type: application/json; charset=utf-8');

require 'config.php';

    $db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
mysql_select_db($db_database) or die("Unable to select database: " . mysql_error());

$query = "SELECT * FROM main_office";
mysql_query("SET NAMES utf8");
$result = mysql_query($query);
if (!$result) die ("Database access failed: " . mysql_error());

$rows = mysql_num_rows($result);

for ($j = 0 ; $j < $rows ; ++$j)
{
    $row = mysql_fetch_row($result);
    $location[$j] = new Location;
    $location[$j]->setAddress($row[5]);
    $location[$j]->setCity($row[6]);
    $location[$j]->setState($row[7]);
    $location[$j]->setZip($row[8]);
    $location[$j]->setLongitude($row[9]);
    $location[$j]->setLatitude($row[10]);
}

$json_string = json_encode($location); 
echo $json_string; 

?>

Do not, I repeat, DO NOT, use the mysql_* interface. Switch to mysqli or PDO. It is deprecated and gone in the latest PHP release. — Rick James
– Rick James, Commented Jun 5, 2017 at 1:48

Gordon Linoff · Accepted Answer · 2013-08-07 02:29:11Z

1

I think you want the union all operation:

SELECT *
FROM main_office
union all
select *
from second_office
union all
select *
from third_office;

This appends all the rows together. Your original version was doing a Cartesian product of the tables. That is, the output consisted of all combinations of all rows from all three tables. So, the total rows output would be the product of the rows in the three tables. And, no rows in one table means that no rows are output.

answered Aug 7, 2013 at 2:29

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

MYSQL & PHP Display data from multiple tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related