Ok, I admit - I need help.

[Eric]

Hey guys.

I've been racking my brain on how to do this. I don't know if it's just lack of sleep, if I'm over thinking it, or what.

But, I'm trying to figure out how to allow a domain (in my portfolio script) to be assigned to more than one category. Atm, here's how the SQL looks (just pulled it straight from install script, so includes some PHP code):

		// Category table
		$db->query("
			CREATE TABLE " . TABLE_PREFIX . "categories (
				catid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
				title VARCHAR(100) NOT NULL,
				description TEXT NOT NULL DEFAULT '',
				keywords TEXT NOT NULL DEFAULT '',
				PRIMARY KEY (catid)
			)
		") or $db->raise_error();

		// Domain table
		$db->query("
			CREATE TABLE " . TABLE_PREFIX . "domains (
				domainid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
				catid INT(10) UNSIGNED NOT NULL DEFAULT '0',
				domain VARCHAR(100) NOT NULL,
				description TEXT NOT NULL DEFAULT '',
				keywords TEXT NOT NULL DEFAULT '',
				registrar VARCHAR(100) NOT NULL,
				expiry INT(11) UNSIGNED NOT NULL DEFAULT '0',
				price DECIMAL(10,2) NOT NULL DEFAULT '0.00',
				status ENUM('For Sale', 'Not For Sale', 'Pending Sale', 'Make Offer', 'Sold') NOT NULL,
				added INT(11) UNSIGNED NOT NULL DEFAULT '0',
				issite TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
				hidden TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
				PRIMARY KEY (domainid),
				KEY catid (catid),
				KEY hidden (hidden)
			)
		") or $db->raise_error();

I've tried many things - and I'm just spent. Anyone have ideas? Please?

 

[Peter]

// Category table
        $db->query("
            CREATE TABLE " . TABLE_PREFIX . "categories (
                catid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                title VARCHAR(100) NOT NULL,
                description TEXT NOT NULL DEFAULT '',
                keywords TEXT NOT NULL DEFAULT '',
                PRIMARY KEY (catid)
            )
        ") or $db->raise_error();

        // Domain table
        $db->query("
            CREATE TABLE " . TABLE_PREFIX . "domains (
                domainid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                domain VARCHAR(100) NOT NULL,
                description TEXT NOT NULL DEFAULT '',
                keywords TEXT NOT NULL DEFAULT '',
                registrar VARCHAR(100) NOT NULL,
                expiry INT(11) UNSIGNED NOT NULL DEFAULT '0',
                price DECIMAL(10,2) NOT NULL DEFAULT '0.00',
                status ENUM('For Sale', 'Not For Sale', 'Pending Sale', 'Make Offer', 'Sold') NOT NULL,
                added INT(11) UNSIGNED NOT NULL DEFAULT '0',
                issite TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
                hidden TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
                PRIMARY KEY (domainid),
                KEY catid (catid),
                KEY hidden (hidden)
            )
        ") or $db->raise_error()
        
        // relationship table
        $db->query("
            CREATE TABLE " . TABLE_PREFIX . "dom_cat_relationship (
                id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                domainid INT(10) UNSIGNED NOT NULL,
                catid INT(10) UNSIGNED NOT NULL,
                PRIMARY KEY (id),
                KEY catid (catid),
                KEY domainid (domainid)
            )
        ") or $db->raise_error()

Once you have this then you can either do an elaborate select to get the categories while getting the domains. Alternatively you could do another select for each domain just to select the cats.

 

[Eric]

Interesting.

So, what would you do with that setup - and the query below:

	$getdomains = $db->query("
		SELECT domains.*, IF(categories.title IS NULL, 'None', categories.title) AS category
		FROM " . TABLE_PREFIX . "domains AS domains
		LEFT JOIN " . TABLE_PREFIX . "categories AS categories ON (categories.catid = domains.catid)
		WHERE domains.hidden != 1
			AND domains.status NOT IN('Sold', 'Pending Sale')
			" . (!is_null($catid) ? "AND domains.catid=$catid" : '') . "
		ORDER BY " . ($order_by == 'category' ? 'category' : "domains.$order_by") . " ASC
		LIMIT $pagination[limit], $config[maxperpage]
	") or $db->raise_error('Could not retrieve domain names');

 

[Peter]

hey SV

here is an example of a query I created for mine but demonstrates how you can do it without the previously supplied subquery

SELECT t1.id AS domain_id, t1.domain AS domain, date_format( t1.exp_date, '%D of %b %Y' ) AS expiry, t1.for_sale AS for_sale, t1.idna AS idna, t2.name AS registrar, t2.url AS registrar_url, t1.description AS description, t3.status AS dev_status, GROUP_CONCAT(t5.name) as cat
FROM domains AS t1
LEFT JOIN registrars AS t2 ON t1.registrar_id = t2.id
LEFT JOIN dev_status AS t3 ON t1.dev_status = t3.id
LEFT JOIN domain_2_cat AS t4 ON t1.id = t4.domain_id
LEFT JOIN categories AS t5 ON t4.cat_id = t5.id
WHERE t1.domain = 'phpmastermind.com'
GROUP BY t4.domain_id
LIMIT 1

 

[Kate]

I think you should add a junction table between domains and categories.

 

[Peter]

I think you should add a junction table between domains and categories.

if you read through the replies you will see that is exactly what I have suggested.