Run Microsoft .SQL file in PHP

Question

How can I run MSSQL .sql files with php?

I have a collection of MS SQL files like the following which were used with ToadSQL to retrieve data in the past:

-- RUN AS SCRIPT SO THAT MONDAY IS FIRST DAY OF THE WEEK
set datefirst 1;

select top 10 artist_name, album_name, sum(units) total_units, sum(sales) total_sales from (

-- Sales by album
select c.[name] album_name, d.[name] artist_name, sum(b.price) sales, count(b.Id) units from [order] a, orderalbum b , album c, artist d
where b.albumid=c.id 
and c.ArtistId = d.Id
and a.successful=1 and a.id=b.orderid
and datepart(ww, a.OrderDate) = datepart(ww, getDate())-1
and datepart(yyyy, a.OrderDate) = datepart(yyyy, getDate())
group by c.[name], d.[name]

union

-- Sales by album variant
select d.[name] album_name, e.[name] artist_name, sum(b.price) sales, count(b.Id) units from [order] a, orderalbum b , albumvariant c, album d, artist e
where d.id=c.albumid and  b.albumvariantid=c.id   
and d.ArtistId = e.Id
and a.successful=1 and a.id=b.orderid
and datepart(ww, a.OrderDate) = datepart(ww, getDate())-1
and datepart(yyyy, a.OrderDate) = datepart(yyyy, getDate())
group by d.[Name], e.[Name]

) x
group by x.album_name, x.artist_name
having sum(x.sales) > 0
order by total_units desc;

I’m now building an application in to automate the process but I don’t want to reinvent the wheel with the queries. How can I run MSSQL .sql files with php

It’s made particularly complex by the fact that some query two tables and use ‘union’ is this possible?

Hope there are some SQL heads out there who can help.

Ben

Important detail missing, is this PHP for Windows or Unix? Microsoft provides an MSSQL connectivity "driver", but only for Windows, thus the answer will be different for each. — Jeff Parker
– Jeff Parker, Commented Mar 23, 2011 at 10:56

Ben · Accepted Answer · 2011-03-23 12:16:34Z

2

In CodeIgniter I've come up with the following:

function runsql($file = '')
{

    $this->db_download = $this->load->database('download', TRUE);

    $sql = file_get_contents($file);

    $query = $this->db_download->query($sql);
    $result = $query->result();

    return $result;

}

It's as simple as getting the file contents and running it as a query.

answered Mar 23, 2011 at 12:16

Ben

4,4016 gold badges40 silver badges62 bronze badges

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

Comments

Dytut · Accepted Answer · 2011-03-23 11:57:13Z

0

So you goal is to make a php file that runs a query based on an input file and then outputs the data as html?

Wouldn't this be just a matter of reading the file into a string in php and then just executing it through ODBC or something similar?

answered Mar 23, 2011 at 11:57

Dytut

3171 gold badge4 silver badges9 bronze badges

Collectives™ on Stack Overflow

Run Microsoft .SQL file in PHP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related