Jump to content

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


cooldude832

Recommended Posts

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

 

Link to comment
Share on other sites

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

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

 

Link to comment
Share on other sites

  • 4 months later...
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.