[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

[pre]

Contacts        Contacts_Urls          Urls 

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

ContactID -----< ContactID        +----  UrlID

UserID          UrlID      >-----+      UserID

Name                                    Url

email

[/pre]

 

is this what you mean?

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

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

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

(
    [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

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

-- 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', '[email protected]'),
(2, 1, 'Kerry Kirsch', '[email protected]');

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

-- 
-- 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

<?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

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...