oomdimas
Joined: 21 May 2004
Posts: 6
Location: singapore
Posted: 2004-05-21 08:24
Back to top
Reply with quote
|
i'm having a problem figuring out how to do this task....currently i have 2 tables i.e. parts and vendors....in the parts table, i have a column for vendor....
wot i want to do is this:
the vendors and parts table both have the part_no column....i want to look up to the entry in the vendors table, which has a vendor_id column and is_preferred column, and put the particular vendor_id which has the is_preferred column set to TRUE into the vendor column of a particular part_no in the the parts table....
anybody can help me how i shd go abt doing it????
|
oomdimas
Joined: 21 May 2004
Posts: 6
Location: singapore
Posted: 2004-05-24 02:46
Back to top
Reply with quote
|
i've tried e code tt u gave me, but i dun think it works...there is still no output under the vendor column in the parts table...is it true because the filters do not work??? is there any other way to solve it???
btw, i'm still open to changes made in my vendor database as it is still relatively small...perhaps a suggestion on its structure wd be welcomed...thx b4hand...
|
oomdimas
Joined: 21 May 2004
Posts: 6
Location: singapore
Posted: 2004-05-24 03:25
Back to top
Reply with quote
|
| Code: |
CREATE TABLE `vendors` (
`ID` int(11) NOT NULL auto_increment,
`part_no` int(11) NOT NULL default '0',
`vendor_name` varchar(50) NOT NULL default '',
`vendor_id` varchar(50) NOT NULL default '',
`is_preferred` varchar(10) NOT NULL default 'TRUE',
`order_desc` varchar(50) default NULL,
`price` decimal(13,2) NOT NULL default '0.00',
PRIMARY KEY (`ID`),
KEY `part_no` (`part_no`),
KEY `ID` (`ID`)
) TYPE=MyISAM AUTO_INCREMENT=4 ; |
| Code: |
CREATE TABLE `parts` (
`part_no` int(11) NOT NULL auto_increment,
`desc_short` varchar(50) default NULL,
`descr_long` varchar(50) default NULL,
`cat_1` varchar(50) default NULL,
`cat_2` varchar(50) default 'N/A',
`dwg_no` varchar(80) default NULL,
`picture` varchar(50) default NULL,
`capacity` decimal(13,2) default '0.00',
`datasheet` varchar(50) default 'N/A',
`datasheet_ID` varchar(50) default NULL,
`price` decimal(13,2) default '0.00',
`price_currency` varchar(50) default 'SGD',
`cost` decimal(13,2) default NULL,
`cost_currency` varchar(50) default 'SGD',
`is_stockitem` varchar(10) NOT NULL default 'TRUE',
`is_assembly` varchar(10) default NULL,
`vendor` varchar(50) default NULL,
PRIMARY KEY (`part_no`),
KEY `part_no` (`part_no`),
KEY `cat_1` (`cat_1`),
KEY `desc_short` (`desc_short`),
KEY `part_no_2` (`part_no`)
) |
|
hbernard
Joined: 23 Mar 2003
Posts: 159
Location: FRANCE
Posted: 2004-05-24 19:50
Back to top
Reply with quote
|
Please note that 'filters' is used for filtering items in dropdown during ADD/EDIT mode (with a sql WHERE) while 'join' is useful for having a correct LEFT JOIN against the main table in LIST/VIEW mode
$opts['fdd']['vendor']['values']['join'] =
'PMEjoin0.part_no = PMEjoin1.part_no
AND
is_preferred = "true" ';
Maybe will do the correct join you want.
_________________
hbernard - phpMyEdit updater.
|
oomdimas
Joined: 21 May 2004
Posts: 6
Location: singapore
Posted: 2004-05-25 02:13
Back to top
Reply with quote
|
hbernard, i tried to use your code but it still doesn't work...it's giving me a mysql_error 1109: unknown table 'PMEjoin0' in on clause....
can u explain to me a bit about the join task dat PME does???? how do u name the table and columns etc???
then mebbe i can try n alter my code...
thx for the help!!!
SELECT PMEtable0.part_no AS qf0,PMEtable0.desc_short AS qf1,PMEtable0.descr_long AS qf2,CONCAT(PMEjoin3.category) AS qf3,PMEtable0.cat_1 AS qf3_idx,PMEtable0.is_stockitem AS qf4,PMEtable0.is_stockitem AS qf4_idx,PMEtable0.is_assembly AS qf5,PMEtable0.is_assembly AS qf5_idx,PMEjoin6.vendor_name AS qf6,PMEtable0.vendor AS qf6_idx,PMEtable0.dwg_no AS qf7,PMEtable0.picture AS qf8,CONCAT(PMEjoin9.datatable_name) AS qf9,PMEtable0.datasheet AS qf9_idx,concat("<a href=\"javascript:openwindow3('",datasheet,".php?myvalue=",datasheet_ID,"&operation=View&rec=",datasheet_ID,"')\">",datasheet_ID,"</a>") AS qf10 FROM parts AS PMEtable0 LEFT OUTER JOIN engineering.categories AS PMEjoin3 ON PMEjoin3.category = PMEtable0.cat_1 LEFT OUTER JOIN engineering.vendors AS PMEjoin6 ON PMEjoin0.part_no = PMEjoin1.part_no AND is_preferred = "TRUE" LEFT OUTER JOIN engineering.datasheets AS PMEjoin9 ON PMEjoin9.datatable_name = PMEtable0.datasheet LEFT OUTER JOIN engineering.currencies AS PMEjoin12 ON PMEjoin12.currency = PMEtable0.price_currency LEFT OUTER JOIN engineering.currencies AS PMEjoin14 ON PMEjoin14.currency = PMEtable0.cost_currency ORDER BY PMEtable0.part_no LIMIT 0,15264.517 miliseconds
|
oomdimas
Joined: 21 May 2004
Posts: 6
Location: singapore
Posted: 2004-05-25 03:42
Back to top
Reply with quote
|
yup, i'm using that latest version of phpmyedit class....i reckoned the problem dat i posted was with the ['values']['description']['columns']['0'] line...
if i were to ignore dat particular line, there is no mysql error coming up....therefore i haf scrapped e vendor_name stuff altogether....
however it still fails to give e output dat i want...the vendor column is still empty...
|
oomdimas
Joined: 21 May 2004
Posts: 6
Location: singapore
Posted: 2004-05-26 06:12
Back to top
Reply with quote
|
i've sorted it out....e problem was with e name of the columns....
| Code: |
$opts['fdd']['vendor']['values']['join'] = 'PMEjoin6.part_no = PMEtable0.part_no AND is_preferred = "TRUE" ';
|
it does e trick for me...thx guys!!!
|
|
Post new topic
Reply to topic
|
|
Login
Registration
Slovak
