Tag: php

ON DUPLICATE KEY UPDATE

Tim 4 Comments

When you are working with high traffic sites it’s important to optimize every query you can. It’s also important to realize with so much traffic multiple people could be doing the same thing so data can messy.

Say you create a new table to track how many users login to your site every day. You create a table with the fields Date and Table. I used to do it this way:

{code type=php}$query = “SELECT * FROM `logins` WHERE `Date`=’09-17-2012′”;
$result = mysql_query($query);
if (mysql_num_rows($result) == 0) {
$query = “INSERT INTO `logins` SET `Date`=’09-17-2012′, `Logins`=1”;
$result = mysql_query($query);
} else {
$query = “UPDATE `logins` SET `Logins`=`Logins`+1 WHERE `Date`=’09-17-2012′”;
$result = mysql_query($query);
}{/code}

The problem is two fold: There are two queries for one task, and if you have a thousand people hitting that page at the same time there can be multiple inserts into the logins table.

Yes, you can make sure MySQL doesn’t allow multiple inserts, but it’ll result in an error and those logins won’t be counted.

What I found was a new way to do the same thing.. In one query, like so:

{code type=php}$query = “INSERT INTO `logins` SET `Date`=’09-17-2012′, `Logins`=1 ON DUPLICATE KEY UPDATE `Logins`=`Logins`+1”;
$result = mysql_query($query);{/code}

That’s it.. ON DUPLICATE KEY UPDATE tells MySQL to update the table if the key is a duplicate. Here the table structure would have the Key being “Date” and set to unique.

PHP Multi-Page MySQL Results

Tim

If you use any search engine, they will all limit the number of results per page. Some searches can return millions of pages! Now what about your site? Do you have any pages that return more than 30 items? You can easily break that down into 10 items per page.

In this example, I will show you how to return a results page with PHP, using a MySQL database. This script will return the entire table, with 10 items per page, and it will generate the appropriate previous and next links.

The MySQL Table
This code assumes you have a table named directory with “ID”, “URL”, and “Title” fields.

The Code
[code type=php]<?
if ($page < 1) {
$page = 0;
} else {
$links[] = "<a href=’directory.php?page=".($page-1)."’>Previous</a>";
}

$global_dbh = mysql_connect("localhost","username","password");
mysql_select_db("mydatabasename", $global_dbh);

$query = "SELECT * FROM `directory` ORDER BY `ID` DESC LIMIT ".($page*10).", 11";
$result = mysql_query($query, $global_dbh);
$cnt = mysql_num_rows($result);

if ($cnt > 10) {
$cnt = 10;
$links[] = "<a href=’directory.php?page=".($page+1)."’>Next</a>";
}

echo @implode(" | ",$links);

for ($i=0; $i<$cnt; $i++) {
$row = mysql_fetch_array($result);
echo "<a href='{$row["URL"]}’>{$row["Title"]}</a><P>";
}

echo @implode(" | ",$links);
?>[/code]

The Explaination
In this code example it is assumed you have already connected to your database previously in the script, with $global_dbh

Lines 2-6: If $page is less than 1, make sure it is set to 0 to prevent negative input. Otherwise create a previous link to go back.

Lines 8-10: Here we select the database information. We start at the current page * 10, skipping over previous pages. Notice we are going to select 11 entries – if it returns 11 entries, we know to add a next page link.

Lines 12-15: Check if the returned results is greater than 10, if it is – set the counter to 10 (so we only get 10 results) and create the next page link.

Lines 17,24: Here we output the links to the browser. It looks nice having “Previous | Next”, but ugly if it was just “Previous |” or “| Next”. Using the implode function (with an @ in case there is only one link) we get the “|” only if there are two links.

Lines 19-22: Simply outputing the table rows.

Unzip files with PHP

Tim 2 Comments

I’ve been looking everywhere on how to do this, and I’ve been noticing lots of searches to the site looking for this. So I finally figured out how to do it, and am puting my findings here for you.

The first thing you’ll need to do is make sure you have the ZZIPlib library installed. If you have WHM, goto the Apache Build page, and just build apache with the “Zip” checkbox checked. That simple.

So what I’ve done is created a script that unzips the file “zip.zip” to the folder “zip”. You should know that if the zip file contains folders, the code below will not create those folders, but this should be enough to get you going on your own.

Unzip File PHP Code
[code type=php]<?php
$zip = zip_open(“zip.zip”);
if ($zip) {
while ($zip_entry = zip_read($zip)) {
$fp = fopen(“zip/”.zip_entry_name($zip_entry), “w”);
if (zip_entry_open($zip, $zip_entry, “r”)) {
$buf = zip_entry_read($zip_entry, zip_entry_filesize($zip_entry));
fwrite($fp,”$buf”);
zip_entry_close($zip_entry);
fclose($fp);
}
}
zip_close($zip);
}
?>[/code]
Zip files usually have more than one file inside them, so when you open a zip file you need to go through each file. That is what the zip_read and zip_entry_read functions are doing.

zip_read is getting all the info for each file, and zip_entry_read is getting the file contents from the info returned by zip_read.

zip_entry_name returns the path of the file within the zip. If the file is in a folder inside the zip, you’ll need to create the directory before trying to write the file.

So there you have it: How to unzip a zip archive with PHP!