Abby and Win’s Blog

Archive for the ‘PHP’ Category

Here’s a couple of quick tips to connecting to a MSSQL 2005 server using PHP on a Windows webserver. You will need to enable the php_mssql.dll in your php.ini file.

Here’s the code to connect to the server.

<?php

$conn = mssql_connect(‘my_server’, ‘db_username’, ‘db_password’);
if (!$conn) { die(‘ERROR: Unable to connect to the database.’); }
mssql_select_db(‘database_name’);

?>

Here is a helpful function I found that will help escape strings. I found this at http://stackoverflow.com/questions/574805/how-to-escape-strings-in-mssql-using-php.

<?php

function ms_escape_string($data) {
//if ( !isset($data) or empty($data) ) return ”;
if ( !isset($data) or empty($data) ) return “””;         // modified to handle empty $data
if ( is_numeric($data) ) return $data;

$non_displayables = array(
‘/%0[0-8bcef]/’, // url encoded 00-08, 11, 12, 14, 15
‘/%1[0-9a-f]/’, // url encoded 16-31
‘/[\x00-\x08]/’, // 00-08
‘/\x0b/’, // 11
‘/\x0c/’, // 12
‘/[\x0e-\x1f]/’ // 14-31
);
foreach ( $non_displayables as $regex )
$data = preg_replace( $regex, ”, $data );
$data = str_replace(“‘”, “””, $data );
//return $data;
return “‘”.$data.”‘”;        // modified to handle empty $data
}

?>

Here is a sample query to the database.

<?php

$id = ms_escape_string($_GET['id']);    // escape variable
$sql = ‘SELECT id FROM table_name WHERE id=’.$id.”;
$rs = mssql_query($sql);

if (!$rs)
{
echo ‘ERROR: Unable to get records. ‘.mssql_get_last_message();    // equivalent to mysql_error()
}else{
while ($row = mssql_fetch_assoc($rs))
{
echo ‘The id = ‘.$row['id'].’<br />’;
}
}

?>

Another thing I learned from the article is the equivalent of “mysql_insert_id()”. It is “SELECT @@IDENTITY”. To handle dates and make them friendly to PHP use CONVERT(VARCHAR, [date], 20). This will display it as if you are using date(‘Y-m-d H:i:s) – 24 hour format. So an example would be…

SELECT CONVERT(VARCHAR, getdate(), 20) AS now

I tried to use PDO but was having a hard time getting it to work. I kept getting errors that had something to do with functions. I searched for solutions and tried them but none worked for me. Also, the rowCount() will always return less than or equal to 0.

I had trouble on both sides PHP and Microsoft. This seems to be the best solution that works for me. Hope it helps others.

I’m using the jQuery plugin from http://bassistance.de/jquery-plugins/jquery-plugin-validation. The plugin is greate. I did run into a problem with arrays. Say your form has a group of checkboxes or radio buttons and their names are checkbox or radio. To have them be in the same group, their names have to be the same and to process it using PHP as post variables you have to throw them in arrays. So in the form their names will be checkbox[] or radio[].

In your validation you probably have something like

rules: {
checkbox: { required: true },
radio: { required: true }
}

The element name no longer matches because in your form they are checkbox[] and radio[] while in your javascript they are checkbox and radio. To solve this, use quotes and add the []. So here’s a quick example.

<script type=”text/javascript”>
$(document).ready(function()
{
$(‘#form1′).validate({
rules: {
“checkbox[]“: { required: true },
“radio[]: { required: true }
}
});
});
</script>

<form id=”form1″>
<input name=”checkbox[]” type=”checkbox” value=”checkbox1″ /> checkbox 1
<input name=”checkbox[]” type=”checkbox” value=”checkbox2″ /> checkbox 2

<input name=”radio[]” type=”radio” value=”radio1″ checked=”checked” />radio  1
<input name=”radio[]” type=”radio” value=”radio2″ />radio  2
</form>

The radio button is an overkill since you normally have 1 checked by default unless you forget. The plugin is great so if you haven’t checked it out, I highly recommend it.

I’ve been searching for a way to create a wrapper page within WordPress without having to install a plugin. I browsed the WordPress files and found wp-blog-header.php in the root install directory. It’s really simple.

I’m not sure when this was implemented. All of the articles I found online mentions plugins that had to be installed or creating a new page within the WordPress admin GUI then include a file, etc. I just need to create a custom PHP file that will display the header, sidebar, and footer.

Depending on the theme you are using will determine additional code between each section (header, sidebar, footer). So check out the page.php or index.php of the theme you are using to see how it is structured.

This post will be addressing 2 things.

1. How to use PEAR’s Pager class with PHP PDO
2. Addressing PDO bug #44639 (http://bugs.php.net/bug.php?id=44639)

Two queries will be used for this task. The first query, you will count the rows of the recordset. The second query is the same as the first but it will have the LIMIT – this is what will be used to display the data on each page.

Let’s say we have the following table.

CREATE TABLE IF NOT EXISTS`blog`
(
`blog_id` int(11) not null auto_increment,
`blog_text` varchar(4000) not null default ”,
PRIMARY KEY `pk_blog_id` (`blog_id`)
) ENGINE=INNODB;

Now we’ll query the database and get the row count using PDO.

<?php

require_once(‘file-with-pdo-db-connection.php’);

$sql = “SELECT * FROM `blog`”;
$stmt = $dbh->prepare($sql);

if ($stmt->execute())
{
// begin pager
require_once(‘Pager/Pager.php’);
$params = array
(
‘totalItems’ => $stmt->rowCount,
‘perPage’ => 10,
‘delta’ => 5,
‘mode’ => ‘Sliding’
);

$pager =& Pager::factory($params);

$links = $pager->getLinks();
echo $links['all'];

// offset setup
list($from, $to) = $pager->getOffsetByPageId();
$from = $from – 1;
$perPage = $params['perPage'];

// 2nd query based on 1st with LIMIT – this will be displaying data per page
$stmt2 = $db->prepare($sql.’ LIMIT :from, :perPage’);

// address bug 44639 – forces the variables to have the property of integer instead of string so no quotes will surround it
$stmt2->bindValue(‘:from’, $from, PDO::PARAM_INT);
$stmt2->bindValue(‘:perPage’, $perPage, PDO::PARAM_INT);
$stmt2->execute();

while ($row_blog = $stmt2->fetch())
{
echo ‘<div>’.$row_blog['blog_text'].’</div>’;
}

}

$dbh = null;

?>

If you don’t use the bindValue, you will get an error in your query. Your second query would look something like this… SELECT * FROM `blog` LIMIT ’0′, ’10′ and the error would occur right after LIMIT. You cannot have quotes surrounding the numbers. It took a few hours of Googling but found the solution.

If you want to read more about PEAR Pager, visit http://pear.php.net/package/Pager/docs.

Here’s a tutorial I found that helped me out a lot – http://www.codediesel.com/php/simple-pagination-in-php/.

Jeff called me up asking for help with his dedicated server on Godaddy. He needed to update his PHP 5.1.6 to the latest stable version. His server is running Centos 5.2 (Final). He tried to call Godaddy for assistance and of course they were no help. I’m no expert myself but have played around with different systems. I found out that you can use the yum command to manage the installed packages. The problem is that the current repositories yum looks at does not contain new versions of PHP. This is why you get an error that says something like – cannot find PHP. What you need to do is add a repository that does have a newer version of PHP. We’re going to add Jason Litka‘s repository.

• Log in to the Simple Control Panel
• Log in using SSH
• Type “su – ” to log in with root privileges
• Type “nano -w /etc/yum.repos.d/utterramblings.repo”. If you’re not comfortable with nano, use whatever editor you’re more comfortable with.
• In the utterramblings.repo file type the following
  

  [utterramblings]
  name=Jason’s Utter Ramblings Repo
  baseurl=http://www.jasonlitka.com/media/EL$releasever/$basearch/
  enabled=1
  gpgcheck=1
  gpgkey=http://www.jasonlitka.com/media/RPM-GPG-KEY-jlitka

• Save the file and close it
• Now you can use “yum update php” and it should update it to the latest that the repositories have
• Restart Apache

This tutorial is based on the one found at http://www.jasonlitka.com/yum-repository/. It took awhile for me to find the solution. I hope this helps out people who had the same problem as I did.