Exporting data from SQL Server Express 2005 to CSV using PHP

By David Pratt / Tags: , / 5 Comments / Published: 10-06-11

Annoyingly, one of the limitations of SQL Server Express 2005 is that it doesn’t allow you to export data as CSV, or any other format (bar SQL) for that matter. This is fine is you have a small dataset as you can just copy the data out of the results pane, and then paste into a spreadsheet program to save in the format of your choosing. If like me on the other hand whereby you have a massive result set of +100k records then that doesn’t quite work.

Googleing around for a solution I came across a couple of different options (using DTS Wizard, another using a 3rd party app etc.), but nothing looked like it could do the job I was after – basically I wanted a no-fuss phpmyadmin style export to csv option. Dissatisfied with the options out there, I ended up deciding to write a php script that would connect to the db using the mssql php extension, get the required data, and automatically spit out a csv file. Writing the script was the easy part, the difficult bit was getting PHP to talk to the database due to an incompatibility between the SQL Server Express 2005 db driver and the php extension. To get PHP to play nicely with MS SQL Server 2005 Express you need to resolve this driver problem, here’s how you do it:

  1. Search your computer for ntwdblib.dll
  2. If you right click on each occurance of the file (I had one in the php, apache and system32 directories)
  3. Hit the details tab, and if any of them say a number that ISN’T 2000.80.194.0 then you need to download that version of the file.
  4. Once you have downloaded the file, you need to replace all of the existing ntwdblib.dll files with the one you have downloaded, or figure out which one your php version is using. Obviously, make back-ups first.

A more in depth tutorial on how to do this can be found
here.

Here’s the code for connecting to the db, generating a CSV file and outputting to file in the simplest way I could come up with:

//MSSQL connection string details.
$dbhost = "LOCALHOST\SQLEXPRESS";
$dbuser = 'dave';
$dbpass = 'password';
$dbname = 'target_db_name';
$db = mssql_connect($dbhost, $dbuser, $dbpass);
if (!$db) {
	die('Could not connect to mssql - check your connection details & make sure that ntwdblib.dll is the right version!');
}
$db_selected = mssql_select_db($dbname, $db);
if (!$db_selected) {
	die('Could not select mssql db');
}
$sql = "SELECT * FROM target_database_table_name";
$results = mssql_query($sql, $db);
//Generate CSV file - Set as MSSQL_ASSOC as you don't need the numeric values.
while ($l = mssql_fetch_array($results, MSSQL_ASSOC)) {
	foreach($l AS $key => $value){
		//If the character " exists, then escape it, otherwise the csv file will be invalid.
		$pos = strpos($value, '"');
		if ($pos !== false) {
			$value = str_replace('"', '\"', $value);
		}
		$out .= '"'.$value.'",';
	}
	$out .= "\n";
}
mssql_free_result($results);
mssql_close($db);
// Output to browser with the CSV mime type
header("Content-type: text/x-csv");
header("Content-Disposition: attachment; filename=table_dump.csv");
echo $out;

For reference, I’m using PHP 5.2.3 on wamp – the driver fix mentioned above didn’t seem to work with 5.3.*

Wget examples for a Linux newbie

By David Pratt / Tags: , , / 1 Comment / Published: 06-05-11

Having ditched Windows entirety at the start of the year and made the switch to Linux (Ubuntu flavour), I’ve discovered a range of tools that would have come in very handy in my days as a Windows user. One of those tools is called wget. Wget is basically a bit of software that allows you to download files from webservers via the command line. That in itself doesn’t sound very exciting, but when you start wielding some of its options, you can do some interesting things with it. To showcase some of the things that wget can do, here is a collection of one-liners that you might find interesting or useful – I haven’t come up with them all myself, mostly collected them from around the net from forums and places such as command line fu:

[To understand what the options after the wget command, you'll need to refer to the wget documentation]

Download a single file

Start with an easy one!

wget http://www.example.com/archive.zip

Download an entire website

If you don’t want to be courteous, then you can ignore the –random-wait switch if you don’t mind running the risk of getting banned. If you only want to download the site to a certain depth, then you can use the switch -l followed by a number to indicate the depth e.g. “-l 2″ to a depth level of 2. Downloading an entire website can also be achieved using the -mirror parameter.

wget --random-wait -r -p -e robots=off -U mozilla http://www.example.com

Download an entire ftp directory using wget

Handy if you don’t want to spark up an FTP client.

wget -r ftp://username:password@ftp.example.com
or
wget --ftp-user=username --ftp-password=password example.com

Check for broken links using wget as a spider

This is the command line equivalent of using a Windows based tool called Xenu Link Sleuth. It will spider an entire website, ignoring the robots.txt and generate a log file of all broken links. Handy.

wget --spider -o wget.log -e robots=off --wait 1 -r -p http://www.example.com

Get server information

wget -S -q -O - http://www.example.com | grep ^Server

Diff remote webpages using wget

diff <(wget -q -O - http://www.example.com) <(wget -q -O - http://www.example2.com)

Schedule a download

If you've got a big file to download, you could schedule getting it in this manner. Couple this command with a cron job and you could feasibly create a snapshot of a website at given time intervals.

echo 'wget http://www.example.com' | at 01:00

Using FB.api to make a full post to a users wall

By David Pratt / Tags: , / 20 Comments / Published: 26-09-10

I just thought I’d make another quick post about the Facebook Graph API seen as there seems to be so little documentation and examples for it. The below example shows you how to make a full wall post with message, name, description, link, picture and caption to the wall of a Facebook user using JavaScript to call the Facebook FB.api.

So, assuming that you have an authenticated session, here’s what you need to do:

var params = {};
params['message'] = 'Message';
params['name'] = 'Name';
params['description'] = 'Description';
params['link'] = 'http://apps.facebook.com/summer-mourning/';
params['picture'] = 'http://summer-mourning.zoocha.com/uploads/thumb.png';
params['caption'] = 'Caption';
 
FB.api('/me/feed', 'post', params, function(response) {
  if (!response || response.error) {
	alert('Error occured');
  } else {
	alert('Published to stream - you might want to delete it now!');
  }
});

If you do this right you should get something like this appearing:
Showing a full post to the Facebook wall

Spamtastic.

Create a Facebook page that shows a different version to those that “like” it

By David Pratt / Tags: / No Comments / Published: 17-09-10

Continuing on the Facebook theme from by previous post about how to create a Facebook photo album and insert photos into it using PHP, I thought I’d share another Facebook technique that I’ve used a few times recently. That is, one of showing one version of a Facebook fan/tab page to those people who “like” it, and another to those who are yet to “like” it. I won’t talk about why you would want to do this here, I’ll just show you how…

Assuming that you have installed the Facebook static FBML application on your fan page, and you are on the page where you enter your source code, drop in the following (but obviously replace yourdomain with your own server address):

<link rel="stylesheet" type="text/css" href="http://yourdomain.com/stylesheet.css?v=1.1" />
<div id="wrapper">
	<fb:visible-to-connection>
		<img src="http://yourdomain.com/thanks.jpg" alt="Thanks!" width="520" height="400" />
		<fb:else>
			<div id="non-like"><img src="http://yourdomain.com/please-like-this.jpg" alt="Like!" width="520" height="400" /></div>
		</fb:else>
	</fb:visible-to-connection>
</div>

Include the following CSS in your stylesheet:

#wrapper{position:relative;}
#non-like{position:absolute; top:0; left:0;}

You can flesh out the above example to make a richer example, but it should be enough to get you started. When you are developing the page, remember that if you make changes to your stylesheet while you are working on the page, increment the stylesheet version number otherwise your changes won’t be picked up in Facebook due to their aggressive caching policy! The other gotcha is if your mark-up isn’t valid – if that is the case then the mark-up in between the will probably not render. Oh, and if you are the admin of the page then it won’t work properly either, so be sure to test the page with a Facebook login that doesn’t have admin privileges for the fan page you are editing.

Here’s an example… don’t be shy in liking it! ;-)

Create photo albums and upload photos using the Facebook Graph API

By David Pratt / Tags: , / 70 Comments / Published: 11-09-10

UPDATE: Thanks to some of the comments below I have been able to revise the code – thanks Luke & Guilherme

I thought I’d share some recent learning’s with using the Facebook Graph API as there seems to be so few examples out there about how to utilise various aspects of its functionality. I will explain how to create a photo album and then insert a photo into it using PHP.

The code example assumes that you have already generated an authenticated session and have the correct permissions (read_stream, publish_stream, photo_upload, user_photos, user_photo_video_tags). If you haven’t already done so, then there is a good example here about how to do so and you can read more about the Facebook permissions on the Facebook dev site.

The following snippet creates a photo album and then uploads a photo into it:

//At the time of writing it is necessary to enable upload support in the Facebook SDK, you do this with the line:
$facebook->setFileUploadSupport(true);
 
//Create an album
$album_details = array(
		'message'=> 'Album desc',
		'name'=> 'Album name'
);
$create_album = $facebook->api('/me/albums', 'post', $album_details);
 
//Get album ID of the album you've just created
$album_uid = $create_album['id'];
 
//Upload a photo to album of ID...
$photo_details = array(
	'message'=> 'Photo message'
);
$file='app.jpg'; //Example image file
$photo_details['image'] = '@' . realpath($file);
 
$upload_photo = $facebook->api('/'.$album_uid.'/photos', 'post', $photo_details);

You can add more detail to the created album and photo than I have in the example by using additional parameters within the arguments array.