BrentNJ
Joined: 03 Jun 2004
Posts: 12
Posted: 2004-06-06 15:28
Back to top
Reply with quote
|
Hi,
Still trying to do a join.
I followed advice from http://opensource.platon.sk/projects/bug_view_advanced_page.php?f_bug_id=218
but get Unknown table 'PMEjoin0' in field list. I tried replacing 0 with 1-8, but it didn't work - don't really understand numbering convention.
Using a previously posted table structure for practice
mysql> describe parts
-> ;
+----------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+----------------+
| part_no | int(11) | | PRI | NULL | auto_increment |
| desc_short | varchar(50) | YES | MUL | NULL | |
| descr_long | varchar(50) | YES | | NULL | |
| cat_1 | varchar(50) | YES | MUL | NULL | |
| cat_2 | varchar(50) | YES | | N/A | |
| dwg_no | varchar(80) | YES | | NULL | |
| picture | varchar(50) | YES | | NULL | |
| capacity | decimal(13,2) | YES | | 0.00 | |
| datasheet | varchar(50) | YES | | N/A | |
| datasheet_ID | varchar(50) | YES | | NULL | |
| price | decimal(13,2) | YES | | 0.00 | |
| price_currency | varchar(50) | YES | | SGD | |
| cost | decimal(13,2) | YES | | NULL | |
| cost_currency | varchar(50) | YES | | SGD | |
| is_stockitem | varchar(10) | | | TRUE | |
| is_assembly | varchar(10) | YES | | NULL | |
| vendor | varchar(50) | YES | | NULL | |
+----------------+---------------+------+-----+---------+----------------+
17 rows in set (0.00 sec)
mysql> describe vendors
-> ;
+--------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+----------------+
| ID | int(11) | | PRI | NULL | auto_increment |
| part_no | int(11) | | MUL | 0 | |
| vendor_name | varchar(50) | | | | |
| vendor_id | varchar(50) | | | | |
| is_preferred | varchar(10) | | | TRUE | |
| order_desc | varchar(50) | YES | | NULL | |
| price | decimal(13,2) | | | 0.00 | |
+--------------+---------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
mysql>
I have this code in vendors.php
$opts['fdd']['part_no'] = array(
'name' => 'Part Number',
'select' => 'T',
'maxlen' => 11,
'default' => '0',
'required' => true,
'join' => array( 'table' => 'parts', 'column' => 'part_no' ),
'sort' => true
);
$opts['fdd']['Street'] = array(
'name' => 'Street',
'select' => 'T',
'input' => 'V',
'maxlen' => 40,
'sort' => true,
'sql' => 'PMEjoin0.desc_short'
);
Using version
/* $Platon: phpMyEdit/phpMyEdit.class.php,v 1.143 2004/04/22 16:15:14 nepto Exp
$ */
Any help would be greatly appreciated.
Thanks!!
|
ajh
Joined: 17 Dec 2003
Posts: 236
Posted: 2004-06-07 10:59
Back to top
Reply with quote
|
Firstly, I think you mean 'values' not 'join'. And secondly I think that you need to use the 'description' optn to actually get the join created.
Try this:
| Code: |
$opts['fdd']['part_no'] = array(
'name' => 'Part Number',
'select' => 'T',
'maxlen' => 11,
'default' => '0',
'required' => true,
'values' => array( 'table' => 'parts', 'column' => 'part_no', 'description' => 'desc_short' ),
'sort' => true
);
|
If you want the field to actually show the part number rather than description just set 'description' to 'part_no' instead.
|
BrentNJ
Joined: 03 Jun 2004
Posts: 12
Posted: 2004-06-07 17:13
Back to top
Reply with quote
|
Thanks for the info. But I still have some questions.
Lets take another example of vendors.php
I want to show
ID
desc_short
vendor_name
vendor_id
is_preferred
order_desc
price
where desc_short is based off of the parts table.
Here's the tricky part - when a user adds a new record, I need to have the numeric value for part_no inserted into the vendors table rather that the text of desc_short. Note that I will have the desc_short field a drop down listing all entries from parts table so that no new entries for desc_short can be added from vendors.php.
When I tested with values, the text was entered into the table.
I think I may need to do a join.
|
ajh
Joined: 17 Dec 2003
Posts: 236
Posted: 2004-06-08 09:39
Back to top
Reply with quote
|
That is what values should do. It displays the 'description' column but actually puts the 'column' column into the field. Did you check the value in PME or directly in SQL? In PME you will always see the text but the actual underlying table should have the part_no data in it.
The 'values' option creates a join automatically.
|
BrentNJ
Joined: 03 Jun 2004
Posts: 12
Posted: 2004-06-08 15:26
Back to top
Reply with quote
|
Thanks for your patience.
It works great!!
Here is my test details in case anyone else has the same question.
orders table
orderid
itemid
items table
itemid
name
CREATE TABLE `orders` (
`orderid` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`itemid` INT UNSIGNED NOT NULL ,
PRIMARY KEY ( `orderid` )
);
CREATE TABLE `items` (
`itemid` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 10 ) NOT NULL ,
PRIMARY KEY ( `itemid` )
);
INSERT INTO `items` ( `itemid` , `name` )
VALUES (
'1', 'test'
);
$opts['fdd']['orderid'] = array(
'name' => 'Orderid',
'select' => 'T',
'options' => 'AVCPDR', // auto increment
'maxlen' => 10,
'default' => '0',
'sort' => true
);
$opts['fdd']['itemid'] = array(
'name' => 'Part Number',
'select' => 'T',
'maxlen' => 11,
'default' => '0',
'required' => true,
'values' => array( 'table' => 'items', 'column' => 'itemid', 'description' => 'n
ame' ),
'sort' => true
);
mysql> select * from orders;
+---------+--------+
| orderid | itemid |
+---------+--------+
| 1 | 1 |
+---------+--------+
1 row in set (0.00 sec)
mysql>
|
BrentNJ
Joined: 03 Jun 2004
Posts: 12
Posted: 2004-06-08 15:26
Back to top
Reply with quote
|
Thanks for your patience.
It works great!!
Here is my test details in case anyone else has the same question.
orders table
orderid
itemid
items table
itemid
name
CREATE TABLE `orders` (
`orderid` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`itemid` INT UNSIGNED NOT NULL ,
PRIMARY KEY ( `orderid` )
);
CREATE TABLE `items` (
`itemid` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 10 ) NOT NULL ,
PRIMARY KEY ( `itemid` )
);
INSERT INTO `items` ( `itemid` , `name` )
VALUES (
'1', 'test'
);
$opts['fdd']['orderid'] = array(
'name' => 'Orderid',
'select' => 'T',
'options' => 'AVCPDR', // auto increment
'maxlen' => 10,
'default' => '0',
'sort' => true
);
$opts['fdd']['itemid'] = array(
'name' => 'Part Number',
'select' => 'T',
'maxlen' => 11,
'default' => '0',
'required' => true,
'values' => array( 'table' => 'items', 'column' => 'itemid', 'description' => 'n
ame' ),
'sort' => true
);
mysql> select * from orders;
+---------+--------+
| orderid | itemid |
+---------+--------+
| 1 | 1 |
+---------+--------+
1 row in set (0.00 sec)
mysql>
Thanks again
|
|
Post new topic
Reply to topic
|
|
Login
Registration
Slovak
