CODEWITHSUNDEEP
phpmysqlpdo
geass
geass

Reputation: 63

mysql query array inside array

sorry first for my lousy english ... but i got some question need some advice

how can i get data from database in one query?isit possible get array from db, inside the main array got another array?

table structure:

user_post - 
    id int(12) AI unique
    uid int(12)
    content text

user_image - 
    filename varchar255
    uid int(12)
    pid int(12) index
    pid->foreign_key user_post.id 
    ON DELETE CASCADE
    ON UPDATE CASCADE

example: for each user_post can contain more than 1 row user_image but maximum 10 row, how can i select and make it all together in 1 array so i don't need to run query 2 times like below?

query 2 times to done this example:

$db = new Db('main');
$db->query('SELECT `id`, `uid`, `content` FROM `user_post` WHERE `id` = :id LIMIT 1');
$db->bind(':id', '1'); //Example
$result = $db->single();

$db->query('SELECT `filename`, `uid`, `pid` FROM `user_image` WHERE `pid` = :pid LIMIT 10');
$db->bind(':pid', $result['id']);
$images = $db->resultset();

so the $images will contain all the images filename for the post, anyway to make it in 1 query?

Upvotes: 1

Views: 536

Answers (2)

nanofarad
nanofarad

Reputation: 41271

Simply, use an inner join. I haven't touched MySQL in a while, and it's been even longer with PHP, but I'll try to help:

SELECT `user_post`.*, `user_image`.* FROM `user_post` INNER JOIN ON `user_image`.`pid` = `user_post`.`id` WHERE `user_post`.`id` = :id

You'll want to set up a foreign key for this.

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

You can do this in only one query, by JOINing the two tables:

SELECT p.id, p.uid, p.content,
  i.filename, i.uid, i.pid
FROM user_post AS p
INNER JOIN user_image AS i ON i.pid = p.id
WHERE p.id = :id
LIMIT 10;

Upvotes: 1

Related Questions