PHP And MSSQL

This article will show you how to connect to Microsoft’s MSSQL database via PHP. Typically, most PHP configurations involves the use of MySQL database, but every once in a while, there might be a need to connect PHP with MSSQL. So, here’s a sample script to get you started.

<?php
$sql_server = "localhost";
$sql_user   = "username";
$sql_pass   = "password";
$sql_db     = "database"; 
 
// Connect to the database
$db = mssql_connect($sql_server, $sql_user, $sql_pass)
or die("Can't connect to the MSSQL Server."); 
 
// Select a database
$select = mssql_select_db($sql_db, $db)
or die("Can't open the database " .$sql_db); 
 
// SQL statement
$query = "SELECT * FROM tablename WHERE id='3'";
 
// Execute the SQL query
$result = mssql_query($query);
 
// Display rows returned
$num_rows = mssql_num_rows($result); 
echo $num_rows." rows"; 
 
// Display results 
while($row = mssql_fetch_array($result)) {
  echo $row["id"];
  echo $row["first"];
}
 
// Close DB connection
mssql_close($db);
?>

Black-Letterhead 1.7

After a year and a half hiatus, I’m releasing a new version of the Black-Letterhead theme. As some of you already know, the Black-Letterhead is a WordPress theme that I released under the GPL license several years ago. The latest version is now available for download to anyone for free. So, what are the new features?

View Demo | Download Black-Letterhead 1.7

New Features

  • Compatible with the latest WordPress 3.2.1
  • Automatic Feed Links
  • Post Thumbnal support
  • Custom Menus support
  • Custom Headers
  • Rotating Header Images
  • Custom Backgrounds
  • Theme Toolkit
  • Choose right or left sidebar
  • Two widths: 760px or 960px
  • Ability to remove Title or Tagline
  • Turn on/off Single Pages with Sidebar
  • Display Page border/No border
  • Ability to Customize Fonts
  • Ability to change Text Colors

View Demo | Download Black-Letterhead 1.7

Leaving Out the PHP Closing Tags

Typically, you will open and close PHP files like this:

<?php
// php code here
?>

Recently, I was introduced to another way. You leave them out!

<?php
// php code here
/* end of php file */

It seems a little weird at first leaving out the PHP closing tag. Feeling a little exposed and naked? It will take a little getting used to this way of closing out PHP. Somehow, it just doesn’t seem normal. This was done mainly to avoid the extra characters at the end of the file. You normally get a warning that headers were already sent or you cannot modify header information, if you have extra characters at the end of the PHP file.

I realized that you can only do this with certain files that end with PHP. For files that end with HTML or Javascript code, you will still need to close PHP further up the chain, otherwise the sky will fall on your head, and you don’t want that to happen. So, what do you think of think of leaving out the PHP closing tags?

Fuel PHP

I recently started looking into Fuel PHP. As much as I love CodeIgniter, it’s far from perfect. I’m more than curious if Fuel PHP can offer anything new to a glut of PHP frameworks that is already out there. There is Zend, CakePHP, CodeIgniter – which is my current favorite at the moment, and now Fuel. I was introduced to Fuel because some of the main supporters of CodeIgniter are now working with Fuel.

Here are some details from Fuel’s website.

Fuel is a simple, flexible, community driven PHP 5.3 web framework based on the best ideas of other frameworks with a fresh start.

The framework was started in late 2010 by Dan Horrigan then shortly after the team grew to include Phil Sturgeon, Jelmer Schreuder and Harro Verton. The team has decades of PHP experience between them and have all been involved with Open-Source projects such as CodeIgniter, PyroCMS, ExciteCMS and DataMapper ORM to name but a few.

April 1st, 2011 was the date of the first feature frozen Release Candidate, marking Fuel as ready to be used for development of new projects. v1.0 final is not yet ready but the only changes will be bug-fixes.

 

Adding FULLTEXT Search

A few days ago, I wrote an article on how to add a search feature in CodeIgniter projects. The method I used was “Pattern Matching” using SQL’s LIKE statement. Although the LIKE statement works great, it is slow. The search will crawl in situations where there are millions of rows in a table.

An alternative method is using a FULLTEXT index search. It uses SQL’s MATCH AGAINST. To start using a FULLTEXT search, a database table will need to be indexed first. You can accomplish this by altering a table and adding FULLTEXT indexing to certain fields:

Alter Table

ALTER TABLE books ADD FULLTEXT(bookname,author,characters,synopsis);

Once a table has been indexed, you can start using MATCH AGAINST.

Match Against

I tried using CodeIgniter’s active record and MATCH AGAINST, but I could not get them to work. So, I ended up writing out the SQL statement using $this->db->query.

$match = $this-&gt;input-&gt;post('search');
$searchcount = $this-&gt;db-&gt;query("SELECT * FROM books WHERE MATCH 
  (bookname,author,characters,synopsis) AGAINST ('.$match.')");

So, if you want to increase search performance, consider using a FULLTEXT search.

Adding Search to CodeIgniter Projects

This is a little tutorial that will add a Search function to your CodeIgniter projects. As you may already know, CodeIgniter is a PHP Framework that uses the MVC model. MVC stands for Model, Views and Controller. This article assumes you’ve work with CodeIgniter before and that you are looking for a search feature that you can add to your application. To add the search feature, we will get started with the Controller.

Controller

This is assuming you already loaded your libraries, helpers and database model. Lets add the Search function.

function search()
{
$data['query'] = $this->Books_model->get_search();
$this->load->view(‘books’, $data);
}

Model

In this database query, I am searching for any matches in any of the 4 fields: bookname, author, characters and synopsis. If there are matches, it will return the results.

function get_search() {
  $match = $this->input->post(‘search’);
  $this->db->like(‘bookname’,$match);
  $this->db->or_like(‘author’,$match);
  $this->db->or_like(‘characters’,$match);
  $this->db->or_like(‘synopsis’,$match);
  $query = $this->db->get(‘books’);
  return $query->result();
}

Views

Here’s the search form.

<?=form_open(‘books/search’);?>
<?php $search = array(‘name’=>’search’,'id’=>’search’,'value’=>,);?>
<?=form_input($search);?><input type=submit value=’Search’ /></p>
<?=form_close();?>

The result can be displayed using a HTML table.

<table>
<tr><th>ID</th><th>Book</th><th>Author</th><th>Published</th><th>Price</th></tr>
<?php foreach($query as $item):?>
<tr>
<td><?= $item->id ?></td>
<td><?= $item->bookname ?></td>
<td><?= $item->author ?></td>
<td><?= $item->datepublished ?></td>
<td><?= $item->price ?></td>
</tr>
<?php endforeach;?>
</table>

Let me know what you think.

ShiftEdit

ShiftEdit is an online IDE for developing PHP, Ruby, Python, Perl, Java, HTML, CSS and JavaScript through FTP, SFTP and Dropbox. I recently started using ShiftEdit after seeing it from the Chrome Web Store. I’m using it at the moment to manage several of my websites.

ShiftEdit allows you to create and edit PHP, Ruby, Java, HTML, CSS and JavaScript scripts. ShiftEdit has a built-in SFTP support to manage your websites. Just supply your FTP credentials within ShiftEdit to manage your sites. ShiftEdit also has syntax highlighing, block tabbing, undo/redo, line numbers, as well as jump to any line number.

If you’re looking to manage your website via the browser, without the need for a file editor or FTP client, you should look into ShiftEdit. All your work is done via the browser. No more downloading and uploading files. No more editing offline. Changes are immediate.

ShiftEdit keeps tracks of your file changes as well with Revisions History. You can highlight several files and look at file differences. You can also bookmark files for easy access later. There are more features. Visit ShiftEdit if you’re interested.

Simple Invoices Quotation

I have professed my love for Simple Invoices previously on this blog. Simple Invoices is a free, open-source, web-based invoicing system that can be installed on your desktop or web server. In fact, I use Simple Invoices on one of my businesses.

Today, I started using the Quotation feature. Quotations are not much different than Invoices. The only difference that you save the form as a Quote instead of Invoice. The default selection is Invoice.

A quote can be easily converted later to an invoice later by simply changing the category. Simple enough. If you need an web-based invoicing system, be sure to check Simple Invoices.

Simple Invoices

I’ve been looking around for a simple invoice program. I even thought of creating one. Then, I found an open source program called Simple Invoices. I downloaded it and gave it a try. The installation was very straightforward. The process starts with downloading the program, editing the config.ini file, uploading to a server, and running the installation.

The installation gives the user the option to populate it with sample data or with no data. I chose no data. I entered myself as a biller and created several customers. I also created several product types like Labor, Hardware, Software and Miscellaneous. I would love to see more details in this area, perhaps a description field for each product type.

Once an invoice is created, it can be viewed, printed, exported in many formats like PDF, DOC or XLS. The invoice can also be sent via Email with a PDF attachment. Simple Invoices uses your host’s mail servers. The email feature doesn’t work on a localhost installation with no mail support. It worked on mine hosted at Hostgator. I imagine it will work on most host servers with mail support.

I wanted to modify the invoice number so that it will start at a certain number. This is for the continuity with my existing invoice numbering sequence. The default invoice number starts at 1. Unfortunately, there is no facility to change the invoice number except to manually edit it the database via PHPMyAdmin. The documentation about this sketchy, but I figured it out after a couple of tries. You will need to edit the cs_invoices and si_index tables and change the ids and the index_id.

Another great feature is the integration with Paypal. I’m using PayPal as a payment method and it works pretty good. The invoice can be sent via email to each customer. Simple Invoices places a Paypal button with a link to each invoice. The link redirects customers to Paypal to initiate payment. The customer can then use their Paypal account or use a credit card to send payment.

I have been unable to see any of the reporting features because I dont have the XSLT processor installed on my localhost. I’m using MAMP on the MacBook Air. I figured most hosting companies have this feature as standard, but after scouring the internet for a few minutes, I’m not so sure if Hostgator supports it. Will I ever see the reporting feature on this program? I’m not sure. I won’t know until I install it online. Update: this feature works at Hostgator!

Overall, the Simple Invoices program is great. It’s simple enough to be picked up by anyone. It has some excellent features such as exports to PDF, Word or Excel. The Paypal payment method is great. Simple Invoices needs more work in some areas, but it’s not bad start for an open-source program.

Finally, all software need some good documentation, and this one especially needs it. I’m sure the author can use a few volunteers here and there.

CodeIgniter: Two Ways of Writing Arrays

There are two ways of writing arrays in PHP. We will use CodeIgniter in this example. Since CodeIgniter is a MVC framework, we will look at code in models. Assuming that a form is being submitted, and data is saved to the database. In this example, we will use a function called ‘add_entry.’

The add entry function is empty at the moment.

function add_entry() {
}

We will now add post data to our function. In this example, we will use the ‘url’ and ‘anchor’ fields. To sanitize, we set both values to TRUE. We assign it to a variable called $data in an array.

function add_entry() {
$data->url = $this->input->post(‘url’,true);
$data->anchor = $this->input->post(‘anchor’,true);
}

We will now insert data to a database table called ‘bookmarks.’

function add_entry() {
$data->url = $this->input->post(‘url’,true);
$data->anchor = $this->input->post(‘anchor’,true);
$this->db->insert(‘bookmarks’, $data);
}

The other way of writing an array is this:

function add_entry() {
$data = array(
‘url’ => $this->input->post(‘url’,true),
‘anchor’ => $this->input->post(‘anchor’,true));
$this->db->insert(‘bookmarks’, $data);
}

Essentially, both are the same. Somehow, I prefer the second. It seems cleaner somehow. It seems like, I only have to deal with a single variable called $data. What’s your preferred method?