Databases
StackOverflow2013
Postsdbo
POTrouble with joining and the order of GROUP_CONCAT data
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTrouble with joining and the order of GROUP_CONCAT data
    text
    copied!<p>I'm having a few tables from which i want to merge the data. I select basic information from the <code>User</code> table and want to join data from the tables <code>User_Status</code>, <code>User_Sex_Info</code>, <code>User_Personal_Info</code> and <code>Media</code>. </p> <p>I'm encountering troubles with the data from the <code>Media</code> table, since this table can hold up many records of the same user, but i only want to display a maximum of six records (in order, In any case, one <code>Type</code> 1 image, followed by a maximum of five <code>Type</code> 2 images). The other tables only have one row of each user. I currently have the following table to select the data:</p> <pre><code> SELECT User.u_UserName, User.u_UserMail, User.u_UserRegistration, Status.us_PaymentStatus, Sex.us_Gender, Sex.us_Interest, Personal.up_Name, Personal.up_Dob, Personal.up_City, Personal.up_Province, UserMedia.m_Id, UserMedia.m_Type, SUBSTRING_INDEX( GROUP_CONCAT( CONCAT( UserMedia.m_Type, ':', UserMedia.m_File ) ORDER BY UserMedia.m_Type = 1, UserMedia.m_Date DESC SEPARATOR '-' ),'-',6 ) AS userFile FROM User AS User JOIN User_Status AS Status ON Status.User_u_UserId = User.u_UserId JOIN User_Sex_Info AS Sex ON Sex.User_u_UserId = User.u_UserId LEFT JOIN User_Personal_Info AS Personal ON Personal.User_u_UserId = User.u_UserId LEFT JOIN Media AS UserMedia ON UserMedia.User_u_UserId = User.u_UserId WHERE User.u_UserId = :uId GROUP BY UserMedia.m_Id </code></pre> <p>This one works fine on all the data, except for the media files. The output is <code>18-profile.jpg</code> while i was expecting <code>18-profile.jpg-18-2.jpg-18-2.jpg-18-4.jpg-18-5.jpg-18-6.jpg</code></p> <p>The <code>Media</code> table looks like this:</p> <pre><code>CREATE TABLE IF NOT EXISTS `Media` ( `m_Id` int(11) NOT NULL AUTO_INCREMENT, `m_Type` enum('1','2','3') NOT NULL DEFAULT '3' COMMENT '1 = Profile picture\n2 = User picture\n3 = Content image', `m_File` varchar(255) NOT NULL, `m_Date` datetime NOT NULL, `User_u_UserId` int(11) NOT NULL, PRIMARY KEY (`m_Id`,`User_u_UserId`), UNIQUE KEY `m_id_UNIQUE` (`m_Id`), UNIQUE KEY `m_name_UNIQUE` (`m_File`), KEY `fk_Media_User1_idx` (`User_u_UserId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ; INSERT INTO `Media` (`m_Id`, `m_Type`, `m_File`, `m_Date`, `User_u_UserId`) VALUES (2, '1', '18-profile.jpg', '2013-07-30 00:00:00', 18), (3, '2', '18-1.jpg', '2013-07-30 00:00:00', 18), (4, '2', '18-2.jpg', '2013-07-30 00:00:00', 18), (5, '2', '18-3.jpg', '2013-07-30 00:00:00', 18), (6, '2', '18-4.jpg', '2013-07-30 00:00:00', 18), (7, '2', '18-5.jpg', '2013-07-30 00:00:00', 18), (8, '2', '18-6.jpg', '2013-07-30 00:00:00', 18); </code></pre> <p>Do i overlook something? I don't see why it isn't working.</p> <p><strong>EDIT</strong> I created a <a href="http://sqlfiddle.com/#!2/86fd9/1" rel="nofollow">SQL Fiddle</a> with the query and that shows my data in seven rows. I don't understand why it's not displaying any more rows when i run it locally</p> <p><strong>EDIT 2</strong> Apparently the problem is in the <code>GROUP_CONCAT</code> since phpmyadmin shows the same result as the SQL Fiddle. I'm going to look at that</p> <p><strong>SOLVED IT!</strong> I grouped the data on the wrong parameter, i needed to group by <code>User.u_UserId</code>. Thanks for looking!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload