Convert T-SQL to PHP code.Bulk insert

Question

I want to use this code to php .Is it possible to convert this code to PHP?

DATABASE:ODBC

-- Thanks in advance

USE [ECPNWEB]
GO

/****** Object:  Table [dbo].[tblMCWD]    Script Date: 11/16/2012 14:42:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblMCWD](
    [ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [ConsumerCode] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [ConsumerName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [BillingPeriod] [datetime] NULL,
    [AccountStatus] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [AccountNumber] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [DueDate] [datetime] NULL,
    [CurrentBill] [float] NULL,
    [PreviousBill] [float] NULL,
    [TotalDiscount] [float] NULL,
    [TotalGrossAmountDue] [float] NULL,
    CONSTRAINT [PK_tblMCWD] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
        WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) 
        ON [PRIMARY]
GO

BULK INSERT tblmcwd
FROM 'c:\tblmcwd.txt'
WITH (
    FIELDTERMINATOR = '|',
    ROWTERMINATOR = '\n'
)
GO

/* Check the content of the table. */
SELECT *
FROM tblmcwd
GO

/* Drop the table to clean up database. */
DROP TABLE tblmcwd
GO

Yes, it's possible. The question is: do you want PHP to parse the data file and generate INSERTs, or do you want it to simply create a BULK INSERT statement? — Steven Moseley
– Steven Moseley, Commented Dec 4, 2012 at 0:41
Thanks for the reply @TheSmose .Yes I want to PHP to parse the data file and generate insert..thanks..the above code is running in Microsoft Sql Server Management Studio..thanks.. — Kristian Hernan C. Manuel
– Kristian Hernan C. Manuel, Commented Dec 4, 2012 at 1:23

Steven Moseley · Accepted Answer · 2012-12-04 06:09:36Z

1

Here's a start... I can't guarantee that it'll work, though... you need to test it and debug it:

<?php
try {
    $dbh = new PDO("mssql:host={$hostname};dbname={$dbname}", $username, $password);
} catch (PDOException $e) {
    exit;
}
if (($handle = fopen("c:\tblmcwd.txt", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 4096, "|")) !== FALSE) {
        if (count($data) == 10) {
            $sql = "INSERT INTO [dbo].[tblMCWD] (
                        [ID], 
                        [ConsumerCode], 
                        [ConsumerName], 
                        [AccountStatus], 
                        [AccountNumber], 
                        [DueDate], 
                        [CurrentBill], 
                        [PreviousBill], 
                        [TotalDiscount], 
                        [TotalGrossAmountDue]
                    ) VALUES (
                        ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
                    )";

            $stmt = $dbh->prepare($sql);
            $stmt->bindValue(1, $data[0]);
            $stmt->bindValue(2, $data[1]);
            $stmt->bindValue(3, $data[2]);
            $stmt->bindValue(4, $data[3]);
            $stmt->bindValue(5, $data[4]);
            $stmt->bindValue(6, $data[5]);
            $stmt->bindValue(7, $data[6]);
            $stmt->bindValue(8, $data[7]);
            $stmt->bindValue(9, $data[8]);
            $stmt->bindValue(10, $data[9]);
            $stmt->execute();
        }
    }
    fclose($handle);
}

answered Dec 4, 2012 at 6:09

Steven Moseley

16.4k4 gold badges43 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kristian Hernan C. Manuel Over a year ago

I debug the code that you build and there is no error but the page result in blank?What will I do?and I check the database and nothing happens..

Steven Moseley Over a year ago

I don't know, man... you're asking me to build a functioning app for you... I gave you plenty of direction so far.. I'm not going to kill myself for a checkmark from you. Learn PHP.

Steven Moseley Over a year ago

You're welcome. Sorry for being a jerk, but I really can't work magic here. I think if you have a specific error you can report, you'd do best to create a new question, posting the code and the error message (check the logs).

Kristian Hernan C. Manuel Over a year ago

Thanks again..I will a ask a new question., :)

Collectives™ on Stack Overflow

Convert T-SQL to PHP code.Bulk insert

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related