Location in MySQL table

[Ayeisha_Bean]

Where is the device location in the SQL database? I am trying to export device name and locations but I don’t know which table it is located in.

[Jellyfrog]

in the locations table

[Ayeisha_Bean]

The locations table only shows all of the locations ever added in the database. It doesn’t have a device_id column where I can join the two tables. I need to pull the device name and it’s location.

For instance, I can join device and port information like
SELECT devices.sysName
FROM devices
JOIN ports
ON devices.device_id=ports.device_id

Is there a way to join the table so I can pull the location pertaining to the specific device?

[Jellyfrog]

$ php artisan tinker

foreach(Device::with('location')->get() as $d) {
  echo $d->hostname . " - " . $d->location->location . PHP_EOL;
}

[Craig]

If you want to do it by sql, here is a example…

select devices.device_id,devices.hostname,locations.location from devices, locations where devices.location_id = locations.id;

[Ayeisha_Bean]

Thanks this was really helpful!

[Ayeisha_Bean]

My final goal was to display device name, location and count active ports on particular Cisco devices.

SELECT devices.sysName, locations.location, COUNT(devices.device_id) FROM devices, locations, ports WHERE devices.device_id=ports.device_id AND devices.location_id=locations.id AND devices.hardware LIKE “ME-34%” AND ports.ifOperStatus LIKE “up” AND ports.ifAdminStatus LIKE “up” AND (ports.ifName LIKE “Gi%” OR ports.ifName LIKE “Fa%”) GROUP BY(devices.sysName);