Forum Moderators: coopster
which has rows:
relateds
relateds1
relateds2
The parent id (manufacturers_id) is the same for all 3 of these rows.
query (it does what I want to do, but is it necessary to have 3 different queries? Is there a more efficient way to do this?):
#1
$the_manufacturers_desc_query= tep_db_query("select n.relateds, p.manufacturers_name from " . TABLE_MANUFACTURERS_INFO . " n left join " . TABLE_MANUFACTURERS . " p on (n.relateds=p.manufacturers_id) where n.relateds=p.manufacturers_id and n.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'");
$the_manufacturers_desc = tep_db_fetch_array($the_manufacturers_desc_query);
#2
$the_manufacturers_desc1_query= tep_db_query("select n.relateds1, p.manufacturers_name from " . TABLE_MANUFACTURERS_INFO . " n left join " . TABLE_MANUFACTURERS . " p on (n.relateds1=p.manufacturers_id) where n.relateds1=p.manufacturers_id and n.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'");
$the_manufacturers_desc1 = tep_db_fetch_array($the_manufacturers_desc1_query);
#3
$the_manufacturers_desc2_query= tep_db_query("select n.relateds2, p.manufacturers_name from " . TABLE_MANUFACTURERS_INFO . " n left join " . TABLE_MANUFACTURERS . " p on (n.relateds2=p.manufacturers_id) where n.relateds2=p.manufacturers_id and n.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'");
$the_manufacturers_desc2 = tep_db_fetch_array($the_manufacturers_desc2_query);
If you give me some more information about the tables, what they are used for etc + a structure dump, i'd be happy to provide you with some feedback
The rest are rows, within table "manufacturers_info"
row 1: relateds
row 2: relateds1
row 3: relateds2
relateds, relateds1 & relateds2 all have the same id as the id in "manufacturers"
However. As well as sharing the ID, I am also trying to pull the manufacturer_name row (which resides in table "manufacturers") This is different from everything BUT it's ID is the same as relateds1, relateds2, relateds3.
I don't know whether or not I can somehow join within the same query.. I am just not very good with the more technical side of php/sql. :)
Prior to me trying out 3 different queries. I WAS able to successfully pull the 3 ID's and link them properly. What I could NOT achieve was pulling the different manufacturer_name(s) for the 3 selections.
what do you mean with 'group within the same table'?
and thousands of rows is peanuts for a database, so i wouldn't worry about that. When it becomes millions is when you should start to worry a little.
> This table is "manufacturers"
>
> The rest are rows, within table "manufacturers_info"
> row 1: relateds
> row 2: relateds1
> row 3: relateds2
>
> relateds, relateds1 & relateds2 all have the same id as the id
> in "manufacturers"
But relateds, relateds1 & relateds2 are all columns, right?
> I don't know whether or not I can somehow join within the same query..
> I am just not very good with the more technical side of php/sql. :)
It is possible, you could create 2 more outer joins but that is _not_ the way to go. You really have to consider changing the datamodel, because whatever you're going to build on the current one will give you problems, slow queries and a lot of headaches.
> Prior to me trying out 3 different queries. I WAS able to
> successfully pull the 3 ID's and link them properly. What I could
> NOT achieve was pulling the different manufacturer_name(s) for the 3
> selections.
That may be, but like i said before, you're going down the wrong path.
If you post here the structure dump of the two tables and maybe some examples of how it is filled and what these tables represent i'll have a look and show you how it can be done differently and also how you can then form another select query.
CREATE TABLE IF NOT EXISTS `manufacturers` (
`manufacturers_id` int(20) NOT NULL auto_increment,
`manufacturers_name` varchar(33) NOT NULL default '',
`date_added` datetime default NULL,
`last_modified` datetime default NULL,
PRIMARY KEY (`manufacturers_id`),
KEY `IDX_MANUFACTURERS_NAME` (`manufacturers_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10007 ;INSERT INTO `manufacturers` (`manufacturers_id`, `manufacturers_name`, `date_added`, `last_modified`) VALUES
(10, 'Black Widget', '2005-09-13 18:50:09', '2007-09-29 23:59:57'),
(11, 'Blue Widget', '2005-09-13 19:50:16', '2007-07-28 22:10:02'),
dump of "manufacturers_info":
CREATE TABLE IF NOT EXISTS `manufacturers_info` (
`manufacturers_id` int(11) NOT NULL default '0',
`relateds` varchar(200) NOT NULL default '0',
`relateds1` varchar(200) default NULL,
`relateds2` varchar(200) NOT NULL,
PRIMARY KEY (`manufacturers_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;INSERT INTO `manufacturers_info` (`manufacturers_id`, `relateds`, `relateds1`, `relateds2`) VALUES
(10, 1, '50', '0'),
(11, 1, '76', '0'),
Tried to cut a lot of useless stuff out, hopefully this is to the point.
Here is the php I am using to output the data:
<?php
if (isset($HTTP_GET_VARS['manufacturers_id']) && ((int)$HTTP_GET_VARS['page'] =="")){
if ($the_manufacturers_desc['relateds'] && $the_manufacturers_desc1['relateds1'] && $the_manufacturers_desc2['relateds2']>0){
$the_manufacturers['manufacturers_name'] . ' Also:';
echo '<a href="' . tep_href_link(FILENAME_DEFAULT, '&manufacturers_id=' . $the_manufacturers_desc['relateds']) . '" target="_parent">' . $the_manufacturers_desc['manufacturers_name'] . "</a>\n";
echo ' • <a href="' . tep_href_link(FILENAME_DEFAULT, '&manufacturers_id=' . $the_manufacturers_desc1['relateds1']) . '" target="_parent">' . $the_manufacturers_desc1['manufacturers_name'] . "</a>\n";
echo ' • <a href="' . tep_href_link(FILENAME_DEFAULT, '&manufacturers_id=' . $the_manufacturers_desc2['relateds2']) . '" target="_parent">' . $the_manufacturers_desc2['manufacturers_name']) . "</a><p>\n";
}
CREATE TABLE `manufacturers_related` (
`parent_id` int(10) unsigned NOT NULL,
`child_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`parent_id`,`child_id`)
) ENGINE=MyIsam DEFAULT CHARSET=latin1
In this table you can store all the relationships between 1 manufacturer and all the others that are related to it.
The big advantage of this approach over your existing one is that you can have as many relationships as you like or even none + the queries are much easier to make and much faster executing! In this way it would also be very easy to create an overview of which manufacturer has the most relationships or to select all the manufacturers without relations.
So, here's how you form the query:
SELECT
m.*,
m2.*
FROM manufacturers AS m, manufacturers_related AS mr, manufacturers AS m2
WHERE mr.parent_id = m.manufacturers_id
AND mr.child_id = m2.manufacturers_id
Or, if your mysql-version allows for it:
SELECT
m.*,
GROUP_CONCAT(m2.manufacturers_name) AS related_names
FROM manufacturers AS m, manufacturers_related AS mr, manufacturers AS m2
WHERE mr.parent_id = m.manufacturers_id
AND mr.child_id = m2.manufacturers_id
GROUP BY m.manufacturers_id
If you require some more explanation, let me know
In manufacturers, I add:
parent_id
child_id
In the addition script, I do basically the same thing as I presently have - except move it to manufacturers edit page (rather than manufacturers_info edit page).
Am I following correctly?
But what is parent_id?
Tried both version but it is still giving me the same name for all 3.. the link & rest of the data is working ideally, though.
Perhaps I am not calling it correctly through PHP & the sql is all correct.
How can I properly echo the name? The name should be different for all 3.
> In manufacturers, I add:
> parent_id
> child_id
That would be wrong.
These two columns should into a new table that has only those two columns
Also, what is the exact query you're using now?
