Topic: [SOLVED] Selecting multiple rows of 1 table into a single row in a query

[cooldude832]

3 tables

Contacts (ContactID = PK, UserID, Name, email)
Urls(UrlID, UserID, Url, etc.)
Contacts_Urls (ContactID, UrlID)

Contacts_Urls links a given contact to a url (you can have 0-infinity contacts on a single url)

What I want to do in a single query grouping by ContactID get all of the Users Urls (in a single row) with the UlrID, Url and then using an IF statement or something also get all the Urls linked to that  ContactID (whree there is a row with that contactID and urlID)

The difficulty I am having is that I'm trying to select anywhere from 0-1000 urls in each row (its realistically 0-10) and I don't know how to do that sort of array grab in a select.

Make any sense?

[mbeals]

can you show and example of what you want?

You can make just use joins to do this and it will return a 1:1 mapping (contact -> url), but each pair will be a single row.

and it's 1,2,3-trimethylbenzene and you owe me for the years of therapy that just ruined

[Barand]

Given your structure

Contacts         Contacts_Urls           Urls  
--------         -------------           ---------
ContactID -----< ContactID        +----  UrlID
UserID           UrlID      >-----+      UserID
Name                                     Url
email 

is this what you mean?

Code: [Select]

SELECT c.contactID, c.name, GROUP_CONCAT(u.url) as url_list
FROM Contacts c
    LEFT JOIN Contacts_Urls cu USING (contactID)
    LEFT JOIN Urls u USING (UrlID)
GROUP BY c.contactID

[cooldude832]

The url_list is showing up as NULL

Code: [Select]

SELECT c.contactID, c.name, GROUP_CONCAT( u.url ) AS url_list
FROM contacts c
LEFT JOIN contacts_urls cu
USING ( contactID )
LEFT JOIN urls u
USING ( UrlID )
GROUP BY c.contactID
LIMIT 0 , 30


[cooldude832]

Okay I changed it up a bit and got semi what I want but I have a question

Code: [Select]

SELECT c.contactID, c.name, GROUP_CONCAT(u.UrlID) as url_list,
GROUP_CONCAT(cu.UrlID) as Contact_Url_link
FROM contacts c
    LEFT JOIN contacts_urls cu USING (ContactID)
    LEFT JOIN urls u ON (u.UserId = c.UserID)
Where c.UserID = '1'
GROUP BY c.ContactID
A single rows return (from php print_r($row)) since its a blob in phpmyadmin

Code: [Select]

(
    [contactID] => 1
    [name] => John Camardese
    [url_list] => 29,13,18,13,18,10,28,10,28,14,40,14,40,12,38,12,38,11,29,11
    [Contact_Url_link] => 14,15,15,14,14,15,15,14,14,15,15,14,14,15,15,14,14,15,15,14
)

If u noticed the Contact_Url_link hs a ton of repeat entries but there is only 2 rows matching.


Okay I added DISTINCT to it and got what I wanted

Code: [Select]

SELECT c.contactID, c.name, GROUP_CONCAT(u.UrlID) as url_list,
GROUP_CONCAT(DISTINCT(cu.UrlID)) as Contact_Url_link
FROM contacts c
    LEFT JOIN contacts_urls cu ON (cu.ContactId = c.ContactID)
    LEFT JOIN urls u ON (u.UserId = c.UserID)
Where c.UserID = '1'
GROUP BY c.ContactID

Now is there a way to get GROUP_CONCAT return it as a 3d array for php and save me the exploding step?

[Barand]

can you attach a dump of the test data from the 3 tables so I can load it at my end?

[Barand]

Now is there a way to get GROUP_CONCAT return it as a 3d array for php and save me the exploding step?

I thought you wanted it in a single row

What I want to do in a single query grouping by ContactID get all of the Users Urls (in a single row)

[cooldude832]

Here is the dump and the php snippete I wrote it works like I feel free to comment

Code: [Select]

-- phpMyAdmin SQL Dump
-- version 2.10.0.2
-- http://www.phpmyadmin.net
--
-- Host: lotus
-- Generation Time: Jul 22, 2008 at 03:35 PM
-- Server version: 4.1.22
-- PHP Version: 4.4.7

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `pira00_url`
--

-- --------------------------------------------------------

--
-- Table structure for table `contacts`
--

CREATE TABLE `contacts` (
  `ContactID` bigint(20) NOT NULL auto_increment,
  `UserID` bigint(20) NOT NULL default '0',
  `Name` varchar(128) collate utf8_unicode_ci NOT NULL default '',
  `Email` varchar(128) collate utf8_unicode_ci NOT NULL default '',
  PRIMARY KEY  (`ContactID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

--
-- Dumping data for table `contacts`
--

INSERT INTO `contacts` (`ContactID`, `UserID`, `Name`, `Email`) VALUES
(1, 1, 'John Camardese', 'JohnCamardese@gmail.com'),
(2, 1, 'Kerry Kirsch', 'Kerry_Kirsch@gmail.com');

-- --------------------------------------------------------

--
-- Table structure for table `contacts_urls`
--

CREATE TABLE `contacts_urls` (
  `ContactID` bigint(20) NOT NULL default '0',
  `UrlID` bigint(20) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `contacts_urls`
--

INSERT INTO `contacts_urls` (`ContactID`, `UrlID`) VALUES
(1, 15),
(1, 14);

-- --------------------------------------------------------

--
-- Table structure for table `urls`
--

CREATE TABLE `urls` (
  `UrlID` bigint(20) NOT NULL auto_increment,
  `UserID` bigint(20) NOT NULL default '0',
  `Url` text collate utf8_unicode_ci NOT NULL,
  `Active` tinyint(1) NOT NULL default '0',
  `Alert_Time` float NOT NULL default '10',
  PRIMARY KEY  (`UrlID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=54 ;

--
-- Dumping data for table `urls`
--

INSERT INTO `urls` (`UrlID`, `UserID`, `Url`, `Active`, `Alert_Time`) VALUES
(13, 1, 'http://www.ebay.com', 1, 10),
(12, 1, 'http://www.gwaclan.com', 1, 10),
(10, 1, 'http://www.upperstraitscleanlake.org', 1, 25),
(11, 1, 'http://www.google.com', 1, 1.25),
(14, 1, 'http://www.yahoo.com', 1, 10),
(21, 2, 'http://www.dev2net.com', 1, 10),
(18, 1, 'http://www.gwaclan.com/forums/', 1, 10),
(19, 2, 'http://www.sotrg.com', 1, 10),
(20, 2, 'http://www.storyoftherealms.com', 1, 10),
(38, 1, 'http://php.about.com/od/mysqlcommands/g/Limit_sql.htm', 1, 10),
(23, 2, 'http://www.infinitedark.com', 1, 10),
(24, 2, 'http://www.ekoed.com', 1, 10),
(25, 2, 'http://www.weblogmac.com', 1, 10),
(26, 2, 'http://ww.blacktrees.net', 1, 10),
(27, 2, 'http://www.enragedgamer.com', 1, 10),
(28, 1, 'http://php.net', 1, 10),
(29, 1, 'http://www.php.net', 1, 10),
(30, 2, 'http://blacktrees.net', 1, 10),
(31, 2, 'http://sotrg.com', 1, 10),
(32, 2, 'http://storyoftherealms.com', 1, 10),
(33, 2, 'http://dev2net.com', 1, 10),
(34, 2, 'http://enragedgamer.com', 1, 10),
(35, 2, 'http://infinitedark.com', 1, 10),
(36, 2, 'http://ekoed.com', 1, 10),
(37, 2, 'http://weblogmac.com', 1, 10),
(39, 3, 'http://break.com', 0, 10),
(40, 1, 'http://neopets.com', 0, 10),
(41, 3, 'http://google.com', 0, 10),
(42, 3, 'http://gmail.com', 0, 10),
(43, 3, 'http://thepiratebay.org', 0, 10),
(44, 3, 'http://neopets.com', 0, 10),
(45, 3, 'http://pinkpt.com', 0, 10),
(46, 3, 'http://addictinggames.com', 0, 10),
(47, 3, 'http://asactionvideo.com', 0, 10),
(48, 3, 'http://livonia.org', 0, 10),
(49, 3, 'http://livonia.org', 0, 10),
(50, 3, 'http://asactionvideo.com', 0, 10),
(51, 3, 'http://asactionvideo.com', 0, 10),
(52, 3, 'http://ltu.edu', 0, 10),
(53, 3, 'http://youtube.com', 0, 10);


PHP Snippet

Code: [Select]

<?php
#you need these constants too 
define("URLS_TABLE", "urls");  
define("CONTACTS_TABLE", "contacts");
define("CONTACTS_URLS_TABLE", "contacts_urls");

$fields = array(
CONTACTS_TABLE.".ContactID as ContactID",
CONTACTS_TABLE.".Email as Email",
CONTACTS_TABLE.".Name as Name",
"GROUP_CONCAT(DISTINCT(".URLS_TABLE.".UrlID)) as Urls_List",
"GROUP_CONCAT(DISTINCT(".URLS_TABLE.".Url)) as Urls_Text",
"GROUP_CONCAT(DISTINCT(".CONTACTS_URLS_TABLE.".UrlID)) as Contacts_Links"
);
$fields = implode(" , ",$fields);
$q = "Select ".$fields." from `".CONTACTS_TABLE."`
LEFT JOIN `".URLS_TABLE."` ON (".URLS_TABLE.".UserID = ".CONTACTS_TABLE.".UserID)
LEFT JOIN `".CONTACTS_URLS_TABLE."` ON (".CONTACTS_URLS_TABLE.".ContactID = ".CONTACTS_TABLE.".ContactID)
Where ".CONTACTS_TABLE.".UserID = '".$this->userid."'
GROUP BY  ".CONTACTS_TABLE.".ContactID";
$r = mysql_query($q) or die(mysql_error()."<br /><br />".$q);
#echo "<br /><br />".$q."<br /><br />";
if(mysql_num_rows($r) >0){
$i = 0;
while($row = mysql_fetch_assoc($r)){
$user_urls = explode(",",$row['Urls_List']);
$user_urls_text = explode(",",$row['Urls_Text']);
$contacts_urls = explode(",",$row['Contacts_Links']);
if($i == 0){
echo "<table border=\"1\">\n";
echo "<tr>\n";
echo "<td>Contact Name</td>\n";
echo "<td>Contact Email</td>\n";
foreach($user_urls_text as $value){
echo "<td>".$value."</td>\n";
}
echo "</tr>\n";
}
echo "<tr>\n";
echo "<td>".$row['Name']."</td>\n";
echo "<td>".$row['Email']."</td>\n";
foreach($user_urls as $value){
echo "<td>
<input type=\"checkbox\" name=\"user_urls[".$value."[".$row['ContactID']."]]\" ";
if(in_array($value, $contacts_urls)){
echo "checked=\"checked\" ";
}
echo " /></td>\n";
}
echo "</tr>\n";
$i++;
}
echo "</table>\n";
}
else{
$this->error_report("You have no contacts",1);
}

and the query outside of php

Code: [Select]

Select contacts.ContactID as ContactID , contacts.Email as Email , contacts.Name as Name ,
GROUP_CONCAT(DISTINCT(urls.UrlID)) as Urls_List ,
GROUP_CONCAT(DISTINCT(urls.Url)) as Urls_Text ,
GROUP_CONCAT(DISTINCT(contacts_urls.UrlID)) as Contacts_Links
from `contacts`
LEFT JOIN `urls` ON (urls.UserID = contacts.UserID)
LEFT JOIN `contacts_urls` ON (contacts_urls.ContactID = contacts.ContactID)
Where contacts.UserID = '1' GROUP BY contacts.ContactID


[janebush08]

I liked the code.. you saved my time... thanks for help...