PHP: PDO Query data from different tables

Question

I would like to ask assistance to help me solve my coding problem, I would like to obtain information from two tables in the same database.

DB structure:

Table Name: _net

id
name
ip
comment

I can get all relevant details from this table with the following code:

$netsql = 'SELECT * FROM _net ORDER BY id';
$qnet = $conn->query($netsql);
$qnet->setFetchMode(PDO::FETCH_ASSOC);

Table: _net_port (other information I require to do a query)

id
netid
port
comment

I would like to obtain the information from table _net_port where the relevant id = to netid (id in Table _net will be the same as netid in Table _net_port. Especially to obtain the list of ports.

you need to learn about INNER JOINs in SQL. This is nothing to do with PHP or PDO specifically — ADyson
– ADyson, Commented Sep 19, 2018 at 11:57

Mureinik · Accepted Answer · 2018-09-19 11:57:36Z

1

You could join the _net table on an aggregate query of _net_port with group_concat:

SELECT n.*, p.ports
FROM   _net n
JOIN   (SELECT   netid, GROUP_CONCAT(port)
        FROM     _net_ports
        GROUP BY netid) p ON n.id = p.netid

answered Sep 19, 2018 at 11:57

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

Comments

MorganFreeFarm · Accepted Answer · 2018-09-19 11:58:54Z

1

You need JOIN

SELECT * FROM _net ORDER BY id
INNER JOIN _net_port ON _net_port.id = _net.id

Your question is about MySQL, not about PHP script.

answered Sep 19, 2018 at 11:58

MorganFreeFarm

3,7318 gold badges26 silver badges50 bronze badges

Collectives™ on Stack Overflow

PHP: PDO Query data from different tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related