Export as csv

Export as csv

am 13.12.2009 20:17:03 von bharani kumar

--00504502b0ab8560da047aa10010
Content-Type: text/plain; charset=ISO-8859-1

hi friend

Am working in php and mysql, i want to write the join query from the 4
table,

Am very confused how to do ,

Here is the atachment ,

Please tell me , How to to ....

Master table is

CREATE TABLE `med_patient` (
`id` bigint(20) NOT NULL auto_increment,
`cInitial` varchar(5) default NULL,
`cFname` varchar(50) default NULL,
`cLname` varchar(50) default NULL,
`cAddress` text,
`cCity` varchar(50) default NULL,
`cState` varchar(50) default NULL,
`cZipcode` varchar(50) default NULL,
`cTelephone` varchar(50) default NULL,
`cNextkin` varchar(50) NOT NULL default '',
`cAlleged` varchar(50) default NULL,
`cMmrcashworker` varchar(50) default NULL,
`cDOC` varchar(15) default NULL,
`cScreenout` varchar(50) default NULL,
`cScreenoutR` varchar(50) default NULL,
`cSsn` varchar(50) default NULL,
`cDOB` varchar(15) default NULL,
`cDOD` varchar(15) default NULL,
`cAaddress` text,
`cAcity` varchar(50) default NULL,
`cAstate` varchar(50) default NULL,
`cAzipcode` varchar(50) default NULL,
`cAtelephone` varchar(50) default NULL,
`cHospital` varchar(50) default NULL,
`cHoscashworker` varchar(50) default NULL,
`cImpaiments` varchar(50) default NULL,
`cHosamountbill` varchar(50) default NULL,
`cCurstatus` varchar(50) default NULL,
`cDOT` varchar(15) default NULL,
`cScreenoutR2` varchar(50) default NULL,
`cDODi` varchar(15) default NULL,
`entryDate` varchar(90) default NULL,
`entryUser` varchar(50) default NULL,
`updateDate` datetime default NULL,
`updateUser` varchar(50) default NULL,
`ip` double default NULL,
`sessionid` varchar(200) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;



And other table are

CLAIM table


CREATE TABLE `med_claim` (
`id` bigint(20) NOT NULL auto_increment,
`paitentid` bigint(20) default NULL,
`cClaimtype` varchar(30) default NULL,
`cDatereferal` varchar(15) default NULL,
`cDateclaimobtained` varchar(15) default NULL,
`cDatemedicalevidance` varchar(15) default NULL,
`cDateSSA` varchar(15) default NULL,
`cSSAclaim` varchar(35) default NULL,
`cDateinitialdecision` varchar(15) default NULL,
`cAllowance` varchar(30) default NULL,
`cDateinitialPERC` varchar(15) default NULL,
`cDatemedicaidcertification` varchar(15) default NULL,
`cSSAmedicaidcertification` varchar(35) default NULL,
`cMMRmedicaidcertification` varchar(15) default NULL,
`cHospitalmedicaidcertification` varchar(15) default NULL,
`entryUser` varchar(30) default NULL,
`entryDate` datetime default NULL,
`updateUser` varchar(30) default NULL,
`updateDate` datetime default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `paitentid` (`paitentid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;



Invoice table

CREATE TABLE `med_invoice` (
`invoice_id` int(30) NOT NULL auto_increment,
`patientid` int(30) default NULL,
`d_hos_invoiced` varchar(90) default NULL,
`d_hos_paid_invoiced` varchar(90) default NULL,
`d_payment_rec` varchar(90) default NULL,
`d_payment_sent_mmr` varchar(90) default NULL,
`userid` varchar(30) default NULL,
`date` varchar(90) default NULL,
`updateUser` varchar(30) default NULL,
`updateDate` varchar(30) default NULL,
`status` int(5) default '1',
PRIMARY KEY (`invoice_id`),
UNIQUE KEY `patient_id` (`patientid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;


Remark table
CREATE TABLE `med_remarks` (
`remark_id` int(30) NOT NULL auto_increment,
`patientid` int(30) NOT NULL,
`remark` text NOT NULL,
`userid` varchar(30) NOT NULL,
`date` varchar(90) NOT NULL,
`updateUser` varchar(10) default NULL,
`updateDate` varchar(90) default NULL,
`status` int(5) NOT NULL,
PRIMARY KEY (`remark_id`),
UNIQUE KEY `patientid` (`patientid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;




I want to render record using join function ,

this record need export as csv

Thanks

--00504502b0ab8560da047aa10010--

Re: Export as csv

am 13.12.2009 22:23:56 von mos

At 01:17 PM 12/13/2009, bharani kumar wrote:
>hi friend
>
>Am working in php and mysql, i want to write the join query from the 4
>table,
>
>Am very confused how to do ,

I'm not sure why you would want to join all of the tables together and then
export them. It will mean you will have rows repeated because the you
obviously have 1:many relationship from the med_patient to the other
tables. It might make sense if there was a 1:1 relationship for all the
tables. You can of course export each table individually and let the
program that will import this data link the data together.

If you really want to join the tables together then use a Left Join on the
tables, as in:

select * from med_patient t1 left join med_claim t2 on t1.id=t2.id left
join med_invoice t3 on t3.id=t1.id left join med_remarks t4 on t4.id=t1.id;



To export the data to a CSV file use "Select ... INTO OUTFILE ...". Here is
an example from the manual:


Here is an example that produces a file in the comma-separated values (CSV)
format used by many programs:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

See http://dev.mysql.com/doc/refman/5.1/en/select.html

Mike


>Here is the atachment ,
>
>Please tell me , How to to ....
>
>Master table is
>
>CREATE TABLE `med_patient` (
> `id` bigint(20) NOT NULL auto_increment,
> `cInitial` varchar(5) default NULL,
> `cFname` varchar(50) default NULL,
> `cLname` varchar(50) default NULL,
> `cAddress` text,
> `cCity` varchar(50) default NULL,
> `cState` varchar(50) default NULL,
> `cZipcode` varchar(50) default NULL,
> `cTelephone` varchar(50) default NULL,
> `cNextkin` varchar(50) NOT NULL default '',
> `cAlleged` varchar(50) default NULL,
> `cMmrcashworker` varchar(50) default NULL,
> `cDOC` varchar(15) default NULL,
> `cScreenout` varchar(50) default NULL,
> `cScreenoutR` varchar(50) default NULL,
> `cSsn` varchar(50) default NULL,
> `cDOB` varchar(15) default NULL,
> `cDOD` varchar(15) default NULL,
> `cAaddress` text,
> `cAcity` varchar(50) default NULL,
> `cAstate` varchar(50) default NULL,
> `cAzipcode` varchar(50) default NULL,
> `cAtelephone` varchar(50) default NULL,
> `cHospital` varchar(50) default NULL,
> `cHoscashworker` varchar(50) default NULL,
> `cImpaiments` varchar(50) default NULL,
> `cHosamountbill` varchar(50) default NULL,
> `cCurstatus` varchar(50) default NULL,
> `cDOT` varchar(15) default NULL,
> `cScreenoutR2` varchar(50) default NULL,
> `cDODi` varchar(15) default NULL,
> `entryDate` varchar(90) default NULL,
> `entryUser` varchar(50) default NULL,
> `updateDate` datetime default NULL,
> `updateUser` varchar(50) default NULL,
> `ip` double default NULL,
> `sessionid` varchar(200) default NULL,
> PRIMARY KEY (`id`),
> UNIQUE KEY `id` (`id`)
>) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;
>
>
>
>And other table are
>
>CLAIM table
>
>
>CREATE TABLE `med_claim` (
> `id` bigint(20) NOT NULL auto_increment,
> `paitentid` bigint(20) default NULL,
> `cClaimtype` varchar(30) default NULL,
> `cDatereferal` varchar(15) default NULL,
> `cDateclaimobtained` varchar(15) default NULL,
> `cDatemedicalevidance` varchar(15) default NULL,
> `cDateSSA` varchar(15) default NULL,
> `cSSAclaim` varchar(35) default NULL,
> `cDateinitialdecision` varchar(15) default NULL,
> `cAllowance` varchar(30) default NULL,
> `cDateinitialPERC` varchar(15) default NULL,
> `cDatemedicaidcertification` varchar(15) default NULL,
> `cSSAmedicaidcertification` varchar(35) default NULL,
> `cMMRmedicaidcertification` varchar(15) default NULL,
> `cHospitalmedicaidcertification` varchar(15) default NULL,
> `entryUser` varchar(30) default NULL,
> `entryDate` datetime default NULL,
> `updateUser` varchar(30) default NULL,
> `updateDate` datetime default NULL,
> PRIMARY KEY (`id`),
> UNIQUE KEY `paitentid` (`paitentid`)
>) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;
>
>
>
>Invoice table
>
>CREATE TABLE `med_invoice` (
> `invoice_id` int(30) NOT NULL auto_increment,
> `patientid` int(30) default NULL,
> `d_hos_invoiced` varchar(90) default NULL,
> `d_hos_paid_invoiced` varchar(90) default NULL,
> `d_payment_rec` varchar(90) default NULL,
> `d_payment_sent_mmr` varchar(90) default NULL,
> `userid` varchar(30) default NULL,
> `date` varchar(90) default NULL,
> `updateUser` varchar(30) default NULL,
> `updateDate` varchar(30) default NULL,
> `status` int(5) default '1',
> PRIMARY KEY (`invoice_id`),
> UNIQUE KEY `patient_id` (`patientid`)
>) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;
>
>
>Remark table
>CREATE TABLE `med_remarks` (
> `remark_id` int(30) NOT NULL auto_increment,
> `patientid` int(30) NOT NULL,
> `remark` text NOT NULL,
> `userid` varchar(30) NOT NULL,
> `date` varchar(90) NOT NULL,
> `updateUser` varchar(10) default NULL,
> `updateDate` varchar(90) default NULL,
> `status` int(5) NOT NULL,
> PRIMARY KEY (`remark_id`),
> UNIQUE KEY `patientid` (`patientid`)
>) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;
>
>
>
>
>I want to render record using join function ,
>
>this record need export as csv
>
>Thanks


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org