2. PHPRunner General questions
  3. fields on print and view page
This topic is locked

fields on print and view page

 		7/7/2006 9:50:08 AM
PHPRunner General questions
adamdidthis author

HI.
I have two tables in my DB orderform and suppliers. There is a field in orderform called supname and this uses a lookup wizard to find the suppliers name from the sam field in the suppliers table.
Is it possible to show the rest of the contact details from the suppliers table e.g. phone number and address in the view and print pages?
J
 		Jane 7/7/2006

Hi,
you can use join clause for your purpose.

Modify SQL query on the Edit SQL query tab in the following way:

select table1.field1,

table1.field2,

table2.field3

from table1 inner join table2

on table1.field1 = table2.field3



Read more info here:

Using Join Clause
adamdidthis author 7/7/2006

Tis is the querey I am using, does it look correct?

select *,

supplier.suppname,

supplier.suppadd1,

supplier.supppost,

supplier.supphone,

supplier.suppcunt,

supplier.suppemail,

supplier.suppfax,

supplier.suppcont,

supplier.suppcount,

(orderform.quant1 * orderform.unit1) as mycost1,

(orderform.quant2 * orderform.unit2) as mycost2,

(orderform.quant3 * orderform.unit3) as mycost3,

(orderform.quant4 * orderform.unit4) as mycost4,

(orderform.quant5 * orderform.unit5) as mycost5,

(orderform.quant6 * orderform.unit6) as mycost6,

(orderform.quant7 * orderform.unit7) as mycost7,

(orderform.quant8 * orderform.unit8) as mycost8,

(orderform.quant9 * orderform.unit9) as mycost9,

(orderform.quant10 * orderform.unit10) as mycost10,

(case when orderform.vatfree=1 then 0 else ((orderform.quant1 * orderform.unit1) + (orderform.quant2 * orderform.unit2) + (orderform.quant3 * orderform.unit3) + (orderform.quant4 * unit4) + (orderform.quant5 * orderform.unit5) + (orderform.quant6 * orderform.unit6) + (orderform.quant7 * orderform.unit7) + (orderform.quant8 * orderform.unit8) + (orderform.quant9 * orderform.unit9) + (orderform.quant10 * orderform.unit10) + orderform.delivery) * 0.175 end) as mvyat,

((orderform.quant1 * orderform.unit1) + (orderform.quant2 * orderform.unit2) + (orderform.quant3 * unit3) + (orderform.quant4 * orderform.unit4) + (orderform.quant5 * orderform.unit5) + (orderform.quant6 * orderform.unit6) + (orderform.quant7 * orderform.unit7) + (orderform.quant8 * orderform.unit8) + (orderform.quant9 * orderform.unit9) + (orderform.quant10 * orderform.unit10) + orderform.delivery )*(case when vatfree=1 then 1 else 1.175 end) as mytotal

From `orderform` inner join `supplier` on orderform.supname = supplier.suppname
adamdidthis author 7/10/2006

OK am using the query above which does seem to be working. But when I do a search on all fields I get the following error:

Column 'supphone' in where clause is ambiguous


The table structure is as follows:

CREATE TABLE `supplier` (

`id` int(10) unsigned NOT NULL auto_increment,

`suppname` varchar(45) default '',

`suppadd1` varchar(45) default '',

`suppadd2` varchar(45) default '',

`supppost` varchar(45) default '',

`supphone` varchar(45) default '',

`suppcunt` varchar(45) default '',

`suppemail` varchar(45) default '',

`suppfax` varchar(45) default '',

`suppcont` varchar(45) default '',

`suppcount` varchar(45) default '',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `orderform` (

`ordnum` int(10) unsigned NOT NULL auto_increment,

`orddate` date NOT NULL default '0000-00-00',

`refnum` varchar(45) default NULL,

`supname` varchar(45) NOT NULL default '',

`supadd1` varchar(45) default NULL,

`supadd2` varchar(45) default NULL,

`supcounty` varchar(45) default NULL,

`supcountry` varchar(45) default NULL,

`suppost` varchar(45) default NULL,

`supphone` varchar(45) NOT NULL default '',

`supfax` varchar(45) default NULL,

`supemail` varchar(45) default NULL,

`deliverto` varchar(100) NOT NULL,

`chartgeto` varchar(45) NOT NULL default '',

`quant1` decimal(10,0) NOT NULL default '0',

`descript1` varchar(200) NOT NULL,

`quant2` decimal(10,0) NOT NULL default '0',

`descript2` varchar(200) default NULL,

`quant3` decimal(10,0) NOT NULL default '0',

`descript3` varchar(200) default NULL,

`quant4` decimal(10,0) NOT NULL default '0',

`descript4` varchar(200) default NULL,

`quant5` decimal(10,0) NOT NULL default '0',

`descript5` varchar(200) default NULL,

`quant6` decimal(10,0) NOT NULL default '0',

`descript6` varchar(200) default NULL,

`quant7` decimal(10,0) NOT NULL default '0',

`descript7` varchar(200) default NULL,

`quant8` decimal(10,0) NOT NULL default '0',

`descript8` varchar(200) default NULL,

`quant9` decimal(10,0) NOT NULL default '0',

`descript9` varchar(200) default NULL,

`quant10` decimal(10,0) NOT NULL default '0',

`descript10` varchar(200) default NULL,

`delivery` decimal(10,2) NOT NULL default '0.00',

`auth` varchar(45) default NULL,

`delivdate` date NOT NULL default '0000-00-00',

`authdate` date NOT NULL default '0000-00-00',

`unit1` decimal(10,2) NOT NULL default '0.00',

`unit2` decimal(10,2) unsigned NOT NULL default '0.00',

`unit3` decimal(10,2) NOT NULL default '0.00',

`unit4` decimal(10,2) NOT NULL default '0.00',

`unit5` decimal(10,2) NOT NULL default '0.00',

`unit6` decimal(10,2) NOT NULL default '0.00',

`unit7` decimal(10,2) NOT NULL default '0.00',

`unit8` decimal(10,2) NOT NULL default '0.00',

`unit9` decimal(10,2) NOT NULL default '0.00',

`unit10` decimal(10,2) NOT NULL default '0.00',

`glcode` varchar(45) default NULL,

`costcen` varchar(45) default NULL,

`findept` varchar(45) default NULL,

`jobcode` varchar(45) default NULL,

`jobanal` varchar(45) default NULL,

`notes` varchar(250) default NULL,

`manager` varchar(45) NOT NULL default '',

`supcont` varchar(45) NOT NULL default '',

`vatfree` int(10) unsigned default NULL,

`secondauthdate` date NOT NULL default '0000-00-00',

`secondauth` varchar(45) NOT NULL default '',

`glcode2` varchar(45) default NULL,

`glcode3` varchar(45) default NULL,

`glcode4` varchar(45) default NULL,

`glcode5` varchar(45) default NULL,

`glcode6` varchar(45) default NULL,

`glcode7` varchar(45) default NULL,

`glcode8` varchar(45) default NULL,

`glcode9` varchar(45) default NULL,

`glcode10` varchar(45) default NULL,

`costcen2` varchar(45) default NULL,

`costcen3` varchar(45) default NULL,

`costcen4` varchar(45) default NULL,

`costcen5` varchar(45) default NULL,

`costcen6` varchar(45) default NULL,

`costcen7` varchar(45) default NULL,

`costcen8` varchar(45) default NULL,

`costcen9` varchar(45) default NULL,

`costcen10` varchar(45) default NULL,

`findept2` varchar(45) default NULL,

`findept3` varchar(45) default NULL,

`findept4` varchar(45) default NULL,

`findept5` varchar(45) default NULL,

`findept6` varchar(45) default NULL,

`findept7` varchar(45) default NULL,

`findept8` varchar(45) default NULL,

`findept9` varchar(45) default NULL,

`findept10` varchar(45) default NULL,

`jobcode2` varchar(45) default NULL,

`jobcode3` varchar(45) default NULL,

`jobcode4` varchar(45) default NULL,

`jobcode5` varchar(45) default NULL,

`jobcode6` varchar(45) default NULL,

`jobcode7` varchar(45) default NULL,

`jobcode8` varchar(45) default NULL,

`jobcode9` varchar(45) default NULL,

`jobcode10` varchar(45) default NULL,

`jobanal2` varchar(45) default NULL,

`jobanal3` varchar(45) default NULL,

`jobanal4` varchar(45) default NULL,

`jobanal5` varchar(45) default NULL,

`jobanal6` varchar(45) default NULL,

`jobanal7` varchar(45) default NULL,

`jobanal8` varchar(45) default NULL,

`jobanal9` varchar(45) default NULL,

`jobanal10` varchar(45) default NULL,

`suppcode` varchar(45) default NULL,

`vc1` char(1) default NULL,

`vc2` char(1) default NULL,

`vc3` char(1) default NULL,

`vc4` char(1) default NULL,

`vc5` char(1) default NULL,

`vc6` char(1) default NULL,

`vc7` char(1) default NULL,

`vc8` char(1) default NULL,

`vc9` char(1) default NULL,

`vc10` char(1) default NULL,

`secauthto` varchar(45) default NULL,

`jobname` varchar(45) default NULL,

`txdate` datetime default NULL,

`costcomp` varchar(45) default 'No',

`creword` varchar(45) default NULL,

PRIMARY KEY (`ordnum`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;
adamdidthis author 7/10/2006

Have sorted out the above problem but the information from the following fields do not appear in view pages:
suppadd1

suppadd2

suppcunt

suppcount

supppost

suppfax

suppemail
this happens even if i know that the above information is in the supplier table.
any ideas?
adamdidthis author 7/10/2006

It's OK have managed to fix that now, thanks anyway
adamdidthis author 7/10/2006

OK onle last problem there seems to be duplicate records appearing in the list. so the php display order 100422 twice in the list but it only appears the database once?
Alexey admin 7/10/2006

Adam,
I suppose your orders appear twice because you have two suppliers with the same suppname.

It's unsafe to bind your tables using non-unique field.

Consider adding supplier_id field to orders table and use it in JOIN clause.
adamdidthis author 7/10/2006

made the nelow change:

From `orderform` inner join `supplier` on orderform.supname = supplier.id


but it now only displays one order in the lise whane there is well over 400
Alexey admin 7/10/2006

Adam,
you JOIN clause is incorrect.
I recommended you to do the following.

  1. Create supplier_id field of int type in orderform table.
  2. Fill it with the corresponding values from suppliers.id field.
  3. Use the following JOIN clause:
    inner join `supplier` on orderform.supplier_id = supplier.id


There is also an easier solution for this.
Just remove duplicate supname values from suppliers table.
adamdidthis author 7/10/2006

Just remove duplicate supname values from suppliers table.


That did it, thanks for that