mysql query array inside array

Question

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?

Mahmoud Gamal · Accepted Answer · 2013-09-01 07:40:13Z

1

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;

answered Sep 1, 2013 at 7:40

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

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

10 Comments

geass Over a year ago

thanks, will try now, quite confuse on inner join, so afterward the $result['user_image'] would be an array contain all images?

Mahmoud Gamal Over a year ago

@geass, no the result will contain all the fields coming from the two tables which you selected p.id, p.uid, p.content, i.filename, i.uid, i.pid, you can select only what you want to select, and use the WHERE clause in the end to put your conditions.

geass Over a year ago

erm...i tested and it totally get what i want. but got another question, isit possible to run if else in mysql query? the user_post got another field call type, if type 0 mean the post contain images, if type 1 mean the post don't have any images so no need to use INNER JOIN, isit possible to do if else inside query?

Mahmoud Gamal Over a year ago

@geass - Yes, you can using the CASE expression like this CASE WHEN type = 0 THEN ... ELSE ... END AS .. But I didn't understand what do you want to select depending on that field type?

geass Over a year ago

if field type = 0, mean the post contain images, have to run inner join, if field type = 1, mean the post didnt have any images with it, so no need to run inner join

|

nanofarad · Accepted Answer · 2013-09-01 07:40:53Z

0

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.

answered Sep 1, 2013 at 7:40

nanofarad

41.5k4 gold badges97 silver badges131 bronze badges

Collectives™ on Stack Overflow

mysql query array inside array

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related