Free PHP URL shortener script that kicks ass

Reading time: About 2 minutes

After seeing some of the other coding approaches to personal short URL services, I decided to write my own PHP URL shortener script and share it with the world.

Thanks to the help of others, such as Nicolas, Derek Gathright, and Adrien Gibrat I’ve been able to take this script much farther.

Benefits

  • Can shorten over 42 billion unique URLs in 6 or less characters (it can do more than 12,000,000 in only 4!)
  • Super duper fast—uses very little server resources
  • Includes an API so you can create your own short URLs on the fly
  • Option to turn clickthru tracking on and off
  • Option to limit usage to 1 IP address for personal use and to prevent spamming from others
  • Only uses alphanumeric characters so all browsers can interpret the URL
  • Secure—several data filters in place to prevent SQL injection hacks
  • Option to check if the URL is real (doesn’t respond with a 404) before shortening
  • Uses 301 redirects for SEO and analytics yumminess
  • Option to store a local cache to prevent database queries on every redirect
  • Option to change the characters allowed in a shortened url

Installation

  1. Make sure your server meets the requirements:
    • Optionally you can run this from your current domain or find a short domain
    • Apache
    • PHP
    • MySQL
    • Access to run SQL queries for installation
  2. Download a .zip file of the PHP URL shortener script files
  3. Upload the contents of the .zip file to your web server
  4. Update the database info in config.php
  5. Run the SQL included in shortenedurls.sql. Many people use phpMyAdmin for this, if you can’t do it yourself contact your host.
  6. Rename rename.htaccess to .htaccess
  7. If you want to use the caching option, create a directory named cache with permissions 777

Using your personal URL shortener service

To manually shorten URLs open in your web browser the location where you uploaded the files. You should see a form that looks and acts like this.

To programmatically shorten URLs with PHP use the following code:

$shortenedurl = file_get_contents('http://yourdomain.com/shorten.php?longurl=' . urlencode('http://' . $_SERVER['HTTP_HOST']  . '/' . $_SERVER['REQUEST_URI']));

A look at the PHP code for curious programmers

Shorten.php – Script shortens URLs

<?php
/*
 * First authored by Brian Cray
 * License: http://creativecommons.org/licenses/by/3.0/
 * Contact the author at http://briancray.com/
 */
 
ini_set('display_errors', 0);
 
$url_to_shorten = get_magic_quotes_gpc() ? stripslashes(trim($_REQUEST['longurl'])) : trim($_REQUEST['longurl']);
 
if(!empty($url_to_shorten) && preg_match('|^https?://|', $url_to_shorten))
{
	require('config.php');
 
	// check if the client IP is allowed to shorten
	if($_SERVER['REMOTE_ADDR'] != LIMIT_TO_IP)
	{
		die('You are not allowed to shorten URLs with this service.');
	}
 
	// check if the URL is valid
	if(CHECK_URL)
	{
		$ch = curl_init();
		curl_setopt($ch, CURLOPT_URL, $url_to_shorten);
		curl_setopt($ch,  CURLOPT_RETURNTRANSFER, TRUE);
		$response = curl_exec($ch);
		curl_close($handle);
		if(curl_getinfo($ch, CURLINFO_HTTP_CODE) == '404')
		{
			die('Not a valid URL');
		}
	}
 
	// check if the URL has already been shortened
	$already_shortened = mysql_result(mysql_query('SELECT id FROM ' . DB_TABLE. ' WHERE long_url="' . mysql_real_escape_string($url_to_shorten) . '"'), 0, 0);
	if(!empty($already_shortened))
	{
		// URL has already been shortened
		$shortened_url = getShortenedURLFromID($already_shortened);
	}
	else
	{
		// URL not in database, insert
		mysql_query('LOCK TABLES ' . DB_TABLE . ' WRITE;');
		mysql_query('INSERT INTO ' . DB_TABLE . ' (long_url, created, creator) VALUES ("' . mysql_real_escape_string($url_to_shorten) . '", "' . time() . '", "' . mysql_real_escape_string($_SERVER['REMOTE_ADDR']) . '")');
		$shortened_url = getShortenedURLFromID(mysql_insert_id());
		mysql_query('UNLOCK TABLES');
	}
	echo BASE_HREF . $shortened_url;
}
 
function getShortenedURLFromID ($integer, $base = ALLOWED_CHARS)
{
	$length = strlen($base);
	while($integer > $length - 1)
	{
		$out = $base[fmod($integer, $length)] . $out;
		$integer = floor( $integer / $length );
	}
	return $base[$integer] . $out;
}

Redirect.php – Script handles short url redirects

<?php
/*
 * First authored by Brian Cray
 * License: http://creativecommons.org/licenses/by/3.0/
 * Contact the author at http://briancray.com/
 */
 
ini_set('display_errors', 0);
 
if(!preg_match('|^[0-9a-zA-Z]{1,6}$|', $_GET['url']))
{
	die('That is not a valid short url');
}
 
require('config.php');
 
$shortened_id = getIDFromShortenedURL($_GET['url']);
 
if(CACHE)
{
	$long_url = file_get_contents(CACHE_DIR . $shortened_id);
	if(empty($long_url) || !preg_match('|^https?://|', $long_url))
	{
		$long_url = mysql_result(mysql_query('SELECT long_url FROM ' . DB_TABLE . ' WHERE id="' . mysql_real_escape_string($shortened_id) . '"'), 0, 0);
		@mkdir(CACHE_DIR, 0777);
		$handle = fopen(CACHE_DIR . $shortened_id, 'w+');
		fwrite($handle, $long_url);
		fclose($handle);
	}
}
else
{
	$long_url = mysql_result(mysql_query('SELECT long_url FROM ' . DB_TABLE . ' WHERE id="' . mysql_real_escape_string($shortened_id) . '"'), 0, 0);
}
 
if(TRACK)
{
	mysql_query('UPDATE ' . DB_TABLE . ' SET referrals=referrals+1 WHERE id="' . mysql_real_escape_string($shortened_id) . '"');
}
 
header('HTTP/1.1 301 Moved Permanently');
header('Location: ' .  $long_url);
exit;
 
function getIDFromShortenedURL ($string, $base = ALLOWED_CHARS)
{
	$length = strlen($base);
	$size = strlen($string) - 1;
	$string = str_split($string);
	$out = strpos($base, array_pop($string));
	foreach($string as $i => $char)
	{
		$out += strpos($base, $char) * pow($length, $size - $i);
	}
	return $out;
}

config.php – Configures the URL shortener

<?php
/*
 * First authored by Brian Cray
 * License: http://creativecommons.org/licenses/by/3.0/
 * Contact the author at http://briancray.com/
 */
 
// db options
define('DB_NAME', 'your db name');
define('DB_USER', 'your db usernae');
define('DB_PASSWORD', 'your db password');
define('DB_HOST', 'localhost');
define('DB_TABLE', 'shortenedurls');
 
// connect to database
mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
mysql_select_db(DB_NAME);
 
// base location of script (include trailing slash)
define('BASE_HREF', 'http://' . $_SERVER['HTTP_HOST'] . '/');
 
// change to limit short url creation to a single IP
define('LIMIT_TO_IP', $_SERVER['REMOTE_ADDR']);
 
// change to TRUE to start tracking referrals
define('TRACK', FALSE);
 
// check if URL exists first
define('CHECK_URL', FALSE);
 
// change the shortened URL allowed characters
define('ALLOWED_CHARS', '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ');
 
// do you want to cache?
define('CACHE', TRUE);
 
// if so, where will the cache files be stored? (include trailing slash)
define('CACHE_DIR', dirname(__FILE__) . '/cache/');

.htaccess

DirectoryIndex index.php
 
# remove the next 3 lines if you see a 500 server error
php_flag register_globals off
php_flag magic_quotes_gpc off
php_value display_errors 0
 
FileETag none
ServerSignature Off
 
Options All -Indexes
 
<IfModule mod_rewrite.c>
RewriteEngine On
RewriteRule ^shorten/(.*)$ shorten.php?longurl=$1 [L]
RewriteRule ^([0-9a-zA-Z]{1,6})$ redirect.php?url=$1 [L]
</IfModule>

License and support

This script is provided as is, without warranty or guarantee, licensed under this creative commons license.

I hope you enjoy!

107 comments skip to comment form

  1. HB said— 27 minutes later

    Short and sweet (and secure). Very efficient, thanks for sharing this!

    #1
  2. Sean McArthur said— 28 minutes later

    FFFFFF is 16777215, says Windows Calculator.

    FFFFFFFF (8F’s) is the 4 billion.

    #2
  3. Brian Cray said— 33 minutes later

    Sean: OMG what an oversight! Fixed all references from 6 to 8

    #3
  4. Custom Themes said— 11 hours later

    GREAT! Oh this is absolutely perfect! I was just looking for a good script to use for a few short domains I have. Thank you so much!!

    Dan

    #4
  5. Nicolas said— 11 hours later

    `long_url` varchar(255) NOT NULL: Isn’t that a bit short?
    Also, your table is created using the MyISAM engine, which has size limitations on 32-bit systems. http://jeremy.zawodny.com/blog/archives/000796.html

    Finally, you should make 301 redirections instead of replying with 200: http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.3.2

    #5
  6. Brian Cray said— 12 hours later

    @Nicolas:

    1. There are not many URLs above 255 characters. I chose to avoid a TEXT field for the sake of a few rediculously long URLs. What is your suggestion?

    2. The MyISAM engine can handle the same amount of rows as this URL shortener can handle. So there’s no problem there.

    3. Great point! I thought that sending the Location header was a 301, I’m so happy you posted that link! I updated the code and tut. Thanks again!

    #6
  7. robo47 said— 12 hours later

    255 is really a bit short
    and at least some basic error-handling for querys would be nice if the query fails etc …

    #7
  8. Brian Cray said— 12 hours later

    @Robo47: 255 doesn’t seem short. Here’s an example of a URL that would be 255 characters long:

    http://briancray.com/2009/08/07/track-subscriber-inbound-traffic-google-analytics-wordpress/http://briancray.com/2009/08/07/track-subscriber-inbound-traffic-google-analytics-wordpress/http://briancray.com/2009/08/07/track-subscriber-inbound-traffic-google

    I would say a fraction of a percent of the URLs we use today are longer.

    #8
  9. reboltutorial said— 13 hours later

    Thanks that may be usefull for the RebTweeter Project:
    http://reboltutorial.com/blog/rebtweeter-firstproject-vision-draft/

    #9
  10. Nicolas said— 15 hours later

    @BrianCray: Thanks for your reply! Hopefully this comment will be properly formatted. :-/

    I think that the link I sent you about MyISAM talks about a limit of 4 billion bytes, not a limit of 4 billion rows. I’ve ran into this issue a few days ago using a table with less than 3 million rows, and quite similar to yours. I might be wrong, maybe you can try and insert more than 4 GB of data and see if it still holds.

    For the long URLs I have a good example (I think): The Google Chart API. Here is a link I posted on my company’s wiki last month: http://bit.ly/660Tw . The original URL is 290 characters long. So yes, some people use those links, and URL shorteners use them. You can take a look at this link for an overview of what is accepted by web browsers: http://www.boutell.com/newfaq/misc/urllength.html . If I had to pick a number I’d use IE’s limit.

    There is something else I thought about after I left the previous comment, and it has to do with concurrency.
    If two requests arrive at the same time with the same URL on your URL shortener, this can happen:
    (client 1) SELECT … where url=xxx
    (client 2) SELECT … where url=xxx
    (client 1) OK, no entry, I’ll add it then. INSERT … xxx
    (client 2) OK, no entry, I’ll add it then. INSERT … xxx
    (client 2) [Duplicate entry, failure]

    This actually happens relatively often on websites with heavy load, either because the database is overloaded, or because 2 clients will try to insert the same URL at the same time, or more often simply because a client has clicked twice on the submit button!

    The solution is to have a UNIQUE key on the `url` field, to INSERT without first checking if there already a row, and in case of duplicate entry error to SELECT and return the existing ID.
    You’ll even save a lot of SELECT queries!

    This or use an SQL transaction, which are not available using MyISAM.

    To finish on another subject, I think MySQL might not be the best tool here, and certainly not MyISAM. MyISAM locks the whole table during writes (insert or update), so when your clients post a link, they have to wait for all the other write queries to finish. Such locking time can reach more than 90%, and be very significant in the overall performance of your site.
    Hmm. I now realize this whole comment gets a bit too much into the details, which might not exactly be the purpose of your post. Sorry if I’m being picky.

    #10
  11. Jeremy said— 1 day later

    @Brian

    “1. There are not many URLs above 255 characters. I chose to avoid a TEXT field for the sake of a few rediculously long URLs. What is your suggestion?”

    Depending on your version of MySQL, there may not be a need to use a TEXT field, just use a larger limit on varchar. The 255 character limit was removed in MySQL version 5.0.3. For 5.0.3 or later, the effective maximum length of a varchar is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.
    See: http://dev.mysql.com/doc/refman/5.0/en/char.html

    That said, seems like a simple enough change for whomever is using the script. Just add a comment in the script letting them know they can change it if they want to. :)

    #11
  12. Derek Gathright said— 1 day later

    Here’s another method that can be used. Because it utilizes the whole alpha-numeric range, it can generate 1.5 billion in 6 characters or less.

    $url_id = $GLOBALS['db']->fetchOne(“SELECT url_id FROM urls WHERE url = ?”, array($long_url));

    /* Convert the $url_id to $key */
    $out = “”;
    $alphnum = “abcdefghjkmnpqrstuvwxyz0123456789″;

    while ($url_id > (strlen($alphnum)-1)) {
    $key = $url_id % strlen($alphnum);
    $url_id = floor($url_id / strlen($alphnum)) – 1;
    $out = $alphnum[$key].$out;
    }

    $key = $alphnum[$url_id].$out;

    #12
  13. Brian Cray said— 2 days later

    Derek – nice! I just reworked the script so that it uses the full alpha range AND uses capital letters. Now it does 42 billion in 6 characters or less, hehe. Nice work! I’ll be updating this tut later =)

    #13
  14. Adrien Gibrat said— 2 days later

    RewriteRule ^shorten/(.*)$ shorten.php?url=$1 [L]
    should be
    RewriteRule ^shorten/(.*)$ shorten.php?longurl=$1 [L]

    @Derek Gathright
    great idea, let’s do it well:
    * Add this funtion to the head of shorten.php
    function decbase ( $integer, $base = ’0123456789abcdefghijklmnopqrstuvwxyz’ ) {
    $length = strlen( $base );
    $out = ”;
    while ( $integer > ($length – 1) ) {
    $out .= $base[$integer % $length];
    $integer = floor( $integer / $length ) – 1;
    }
    return $out . $base[$integer];
    }
    * Replace ‘dechex’ call by ‘decbase’ in shorten.php
    * Replace ‘[0-9a-f]{1,8}’ by ‘[0-9a-z]{1,8}’ in redirect.php and .htaccess
    Voilà!

    #14
  15. Adrien Gibrat said— 2 days later

    Using capital… nice!
    so, let’s do it again:
    * Add this funtion to the head of shorten.php
    function decbase ( $integer, $base = ’0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ’ ) {
    $length = strlen( $base );
    $out = ”;
    while ( $integer > ($length – 1) ) {
    $out .= $base[$integer % $length];
    $integer = floor( $integer / $length ) – 1;
    }
    return $out . $base[$integer];
    }
    * Replace ‘dechex’ call by ‘decbase’ in shorten.php
    * Replace ‘|^[0-9a-f]{1,8}$|’ by ‘|^[0-9a-z]{1,6}$|i’ in redirect.php
    * Replace ‘RewriteRule ^([0-9a-f]{1,8})$ redirect.php?url=$1 [L]‘ by ‘RewriteRule ^([0-9a-z]{1,6})$ redirect.php?url=$1 [L,NC]‘ in .htaccess

    With ALL alphanumeric lower and upper case, (including i & l) it’s (26×2+10) ^ 6 = 56.8 billions… in 6 char
    Without i & l letters, for reading/spelling sake, it’s (24×2+10) ^ 6 = 38 billions… in 6 char, then regex should be [0-9a-hjkm-z]{1,6}

    #15
  16. Brian Cray said— 2 days later

    Adrien: I put the shorten function in config.php so that the function can be called from redirect.php and shorten.php =)

    #16
  17. Derek Gathright said— 2 days later

    I opted not to include the capital letters because the concept of user defined “aliases” is a neat feature many URL shorteners have, and those need to be case insensitive. But, if that isn’t a feature of the shortners you all are building, then caps is perfectly acceptable.

    Love the stats being put together here, never did the calculations myself, just figured “This is going to support a shit-ton of URLs”.

    Couple tips
    - If you do anticipate getting a few billion rows, be sure to make your ID field in the DB a “big int” as a regular int maxes at 2 billion’ish.
    - Do lots of filtering for the long_url to ensure it is a proper URL (which the above example does)
    - If you really wanted to go crazy, do a CURL request to ensure the HTTP response is not 404 (file not found). That can be done by executing the shell command “curl http://example.com/this_doesnt_exist -I | grep 404″ and looking for a response, or by using PHP’s curl library to do the same thing.

    #17
  18. Brian Cray said— 2 days later

    You’ll be glad to know that I just updated the code and the tutorial. Thanks everyone for your suggestions and expertise!

    #18
  19. Derek Gathright said— 2 days later

    Updated example looks great. I’ve tweaked some of the code in my personal shortner (wa.ly) to use some of the concepts here. Thanks everyone.

    #19
  20. Brian Cray said— 2 days later

    For what I contributed Derek, you’re welcome. It’s been great making something better together. That’s what this Internet thing is all about :P

    #20
  21. Derek Gathright said— 2 days later

    Updates looks good. I’ve tweaked my personal URL shortener (wa.ly) to use some of these concepts. Thanks everyone.

    #21
  22. Sean O said— 2 days later

    Script is looking great, Brian. I’m thinking about ways to integrate parts of this code (esp. shorten.php) with my tutorial on creating your own URL shortener:
    http://sean-o.com/short-URL

    #22
  23. Ochronus said— 3 days later

    Nice, but are you really sure mysql will perform well on a table filled with some 100k rows selecting by a varchar field? I think you should use some hashing algorithm on the long urls (md5 is quite fast and has a low collision rate) and store their md5 instead, that can be a char field which will be much more faster – or split the resulting 128 bit number into two 64 bit ones and use two BIGINT fields. Whatever. This was my 2 cents :)

    #23
  24. Ochronus said— 3 days later

    Oh, or even use memcache as a storage engine – or if you’re concerned about persistence, check out tokyo cabinet or even mongodb (though this latter is much more than a key-value storage).

    #24
  25. Adrien Gibrat said— 3 days later

    getIDFromShortenedURL is not working properly … just try with large numbers!
    This is the functions that works well:

    function decbase ( $integer, $base = ’0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ’ ) {
    $length = strlen( $base );
    $out = ”;
    while ( $integer > $length – 1 ) {
    $out = $base[fmod($integer, $length)] . $out;
    $integer = floor( $integer / $length );
    }
    return $base[$integer] . $out;
    }
    function basedec ( $string, $base = ’0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ’ ) {
    $length = strlen($base);
    $size = strlen($string) – 1;
    $string = str_split($string);
    $out = strpos($base, array_pop($string));
    foreach($string as $i => $char)
    $out += strpos($base, $char) * pow($length, $size – $i);
    return $out;
    }

    MyIsam will support about 5 billions lines (see http://dev.mysql.com/doc/refman/5.0/en/features.html middle of the page)… 42 billions, i’m not sure! InnoDB is not limited in size (file size), lock lines and not tables… but i don’t know about the maximum lines in a table.
    I’m making a class of that!

    #25
  26. Brian Cray said— 3 days later

    Ochronus: The varchar field is a UNIQUE indexed key, which certainly speeds up the queries. I really like your idea of the MD5 char field. I think I will use that idea (to your credit of course!)

    Adrien: I’m not sure what you mean by “large numbers” Could you provide an example?

    #26
  27. Ochronus said— 3 days later

    Brian: I’m glad you found the md5 idea useful, but it certainly isn’t mine :) It’s a common practice, you don’t need to give me credit for it :)

    #27
  28. Matt said— 3 days later

    Is there a concern about blocking requests during high load since you are locking the entire table before insert?

    #28
  29. Adrien Gibrat said— 3 days later

    any number greater than 58….

    #29
  30. Brian Cray said— 3 days later

    I don’t think so ’cause I’m only locking against write access, which queues the rest. Might cause a problem if you’re getting thousands of requests a second :P

    #30
  31. Brian Cray said— 3 days later

    Adrien: That’s odd because I tested it up to like 62. Did you create a bunch of fake URLs?

    #31
  32. Adrien Gibrat said— 3 days later

    trust me St Thomas:
    getIDFromShortenedURL(getShortenedURLFromID(59)) return 1
    getIDFromShortenedURL(getShortenedURLFromID(800)) return 104

    my functions works with any base… heavily tested!

    #32
  33. Jep said— 4 days later

    hoho, as a general rule, using such a basic fetch (an even more an update ! if tracking is enable) in an sql database *each time* someone goes through the redirect is an NO-NO for this kind of service. Imagine just one of you shortened URL poping up , for example, on the digg home page and then… your server is gone…

    #33
  34. Brian Cray said— 4 days later

    Jep: What’s your alernative?

    #34
  35. Jep said— 5 days later

    Hello Brian! First of all, please, ALWAYS use mysqli. Even more with PHP5.3 and its native driver.

    Now about the service itself, I’m really not convinced a mysql database make sense here. SQL is for “data mining” of some kind but here you basically just need a repository. Anyway you chose that, so just stick with it, but then it is not an option to forget about caching! whatever the solution you want (see memcached or even APC_add) but you must avoid accessing the database for each and every redirection, else you’re dead! Even only one user creating only one short url can make you sink depending on where it appears (something you cannot control).

    As a poor man caching solution, think that something as simple as : header(‘Location: ‘.file_get_contents($url_id)); is enough to redirect and that your websever was originally done to deal with file…

    #35
  36. Brian Cray said— 6 days later

    Adrien: Thanks so much for the updated conversion functions, they work great!

    Ochronus – since md5 doesn’t provide a decoder, I can’t use your md5 recommendation.

    Jep: Many people don’t have access to install memcached so I left it out of the tut. But if you have the knowledge to use it then yes, it’s highly recommended. For now, I took your recommendation to store a file cache of the URL to prevent database queries on every redirect. Thanks for your excellent input!

    #36
  37. Ochronus said— 6 days later

    Brian: Maybe I’m missing something but why do you need a “decoder” for md5 hashes? I would only use md5 as a ‘key’ to existing shortened url mappings, so for example the long url is
    ‘http://this.is.my/long/url’ , its md5 is 85a1c080acfce8d9721f2e521f4a2eae. Upon the first shortener request for this long url, you store somewhere (mysql, memcache, whatever) a ‘been there, done that’ type value, its md5 so that next time you a shortener request comes in for the same url, you know that you don’t have to run the ‘slow’ shortening method, just (very quickly) generate an md5 and check if the key exists in the db or in memcache.
    This is ONLY to replace your line:
    $already_shortened = mysql_result(mysql_query(‘SELECT id FROM ‘ . DB_TABLE. ‘ WHERE long_url=”‘ . mysql_real_escape_string($url_to_shorten) . ‘”‘), 0, 0);

    with a much faster query/memcache key check. An md5 value will be of the same length, so you can either use a CHAR(32) field or two BIGINT fields – I don’t know which is faster, but either of them is MUCH faster than a VARCHAR field. I would vote for the two bigints, escpecially on 64bit boxes – create a composite (unique) key.

    #37
  38. Brian Cray said— 6 days later

    Maybe I’m not understanding something, but that seems to add more data to increase the performance of shorten.php, which doesn’t get called near as often.

    #38
  39. Jim keller said— 1 week later

    Brian,
    you may want to consider using InnoDB tables. Using SELECT … FOR UPDATE when checking if a URL exists would take care of the race condition when two people are trying to add the same URL at the same time (I know this was mentioned ab0ve). Also, using row-level locking via transactions will be much more efficient than doing a full LOCK TABLES.

    #39
  40. Michael said— 2 weeks later

    Hi Brian,

    On every URL i shorten I get a MySQL error saying:

    Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 3

    I’m using the InnoDB storage engine, could that have to do with it? Thoughts?

    #40
  41. Brian Cray said— 2 weeks later

    Michael: I would try MyISAM and see what happens… let me know!

    #41
  42. Michael said— 2 weeks later

    Hi Brian,

    I created a MyISAM table and I get the exact same error there…

    thoughts?

    #42
  43. Brian Cray said— 2 weeks later

    Michael: That is just a warning–PHP warnings don’t affect the perfomance or functionality of the app. Turn off warnings with “php_value display_errors 0″ in your .htaccess or run ini_set(‘display_errors’, 0); at the beginning of the scripts

    #43
  44. Michael said— 2 weeks later

    Just to give you the full error:

    Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 3 in /path/to/shorten.php on line 35

    line 35 is this one:

    // check if the URL has already been shortened
    $already_shortened = mysql_result(mysql_query(‘SELECT id FROM ‘ . DB_TABLE. ‘ WHERE long_url=”‘ . mysql_real_escape_string($url_to_shorten) . ‘”‘), 0, 0);

    it looks like it basically throws an error for URLs that have not been already shortened but for some reason, it’s appended to the output of shorten.php, because it shows up in front of the shortened url in the text input box on your sample index.php

    how do I make sure this doesn’t happen?

    By the way, I already had to make another change in another place where error checking was not in place and I would get PHP errors complaining about files not existing.

    In redirect.php, I changed:

    $long_url = file_get_contents(CACHE_DIR . $shortened_id);

    to:

    if (file_exists(CACHE_DIR . $shortened_id)) {
    $long_url = file_get_contents(CACHE_DIR . $shortened_id);
    }

    because file_get_contents would throw and print errors prior to the redirect header being sent and thus breaking the application.

    Let me know…

    #44
  45. Michael said— 2 weeks later

    So I couldn’t get error_reporting to stop the mysql errors from showing up. I tried setting it via ini_set in the script as well in the php.ini file. It stopped all errors, except mysql ones.

    So instead of trying to hide the error, I just tried to prevent the error from happening in the first place. This code will do that:

    // check if the URL has already been shortened
    $result = mysql_query(‘SELECT id FROM ‘ . DB_TABLE. ‘ WHERE long_url=”‘ . mysql_real_escape_string($url_to_shorten) . ‘”‘);

    $num_rows = mysql_num_rows($result);

    if ($num_rows > 0) {
    $already_shortened = mysql_result($result, 0, 0);
    }

    #45
  46. Yuan said— 2 weeks later

    I try to install, and rename the .htaccess, but I got 500 error Internal Server Error

    #46
  47. Nick Yeoman said— 3 weeks later

    Great post!

    Thanks

    #47
  48. Adam Arnold said— 1 month later

    Your demo is not working Brian

    #48
  49. Amin said— 1 month later

    1. Why are “-” and “_” characters not present in the allowed Characters list?

    2. If this script is used on a high traffic website, after a small time, hundreds of thousands of files will be created and will remain in the cache folder forever. Some of them are not used very often. (zipping so many files will be a huge load for the daily backups.) I suggest you create a PHP script that checks the LAST ACCESS TIME of these cache files, and deletes the files which are not accessed during the past month. You can run this script in a cron job every week or every month.

    #49
  50. Scripte said— 1 month later

    Hey thanks for the trouble which you have reingesteckt in this excellent guide, this script is missing a lot.

    #50
  51. bootcat said— 1 month later

    Thank you brian .
    It really helped me in crafting a string to string mapping function for me.

    #51
  52. directory said— 1 month later

    i am getting 500 internal errors :(

    #52
  53. twe4ked said— 1 month later

    500 internal server errors from the .htaccess file also :(

    #53
  54. ThinkSoJoE said— 1 month later

    Just a thought. Apparently, if you use the format http://www.domain.tld, the www throws it off and causes an error. I’ll go through and tweak the code on my site, but just thought I’d let you know that it does that.

    #54
  55. ThinkSoJoE said— 1 month later

    Actually, scratch that. It’s just being picky on my server for some reason.

    #55
  56. ThinkSoJoE said— 1 month later

    Last comment, I promise! I figured out that it’s not the www that’s throwing things off – it’s just that when I put a URL in for the first time, it returns an error rather than the shortened URL. It puts it in the database, but doesn’t return it until the second time around.

    #56
  57. Pot said— 2 months later

    Hello. sorry im newbie.

    im looking for something that can change from

    mysite.com/profile.php?id=12

    to

    mysite.com/myname

    am i in the right place?

    please update to me. i really need to know. thanks. take care.

    #57
  58. David D Ochoa said— 2 months later

    Pot, I think you are looking for “Mod ReWrite” in your “.htaccess” file.

    #58
  59. David D Ochoa said— 2 months later

    Hmm, this doesn’t seem to work with my bluehost account settings. It may have something to do with register_globals or magic_quotes being OFF. Any suggestions?

    #59
  60. sp said— 3 months later

    This is my example using this script: http://icy.cc

    I made a few changes that someone posted above. Worked fine on my home WAMP environment but errors occurred on Dreamhost without those changes.

    #60
  61. Alexsander Akers said— 3 months later

    To All:

    I have a few questions:

    I realize that a lot of people are concerned with URLs that are longer than 255 characters. Is it possible just to modify the original query done on the database to make the `long_url` a varchar(1023)?

    I see this in the code:
              // change to limit short url creation to a single IP
              define(‘LIMIT_TO_IP’, $_SERVER['REMOTE_ADDR']);

    Is it possible to turn this off?

    And is it possible to allow multiple URI protocols? (e.g. mailto, feed, ftp)

    TIA, Alex.

    #61
  62. dennyhalim.com said— 3 months later

    possibly check links with surbl to prevent spam/phishing??

    #62
  63. Brian Cray said— 3 months later

    Denny: Neat idea! will look into it!

    #63
  64. Kit said— 3 months later

    To exclude shortened url character code such as ‘sex’, ‘fuck’, ‘porn’

    - You should use innodb so transaction can be used
    - Start transaction
    - Start loop
    - Insert the url and then with the returned last insert id, run the character code generation then check if the generated character code is within the excluded list (array)
    - If is within the exclude list, then delete the inserted row with the last insert id then loop again.
    - Exit loop
    - Commit transaction

    #64
  65. Janan said— 3 months later

    awesome! I just downloaded. Been looking to start my own free url shrinking service..

    Thanks

    #65
  66. Mickey said— 4 months later

    I get the same results on my site as your demo:
    http://briancray.com/tests/shorturl2/

    It goes to a 404…

    #66
  67. Brian Cray said— 4 months later

    @Mickey: Try again? I’m accessing the page just fine.

    #67
  68. Mickey said— 4 months later

    hmm… still happening

    I put in some random YouTube link at:
    http://briancray.com/tests/shorturl2/

    within the same text field my YouTube link I get:
    http://briancray.com/7I

    I copy and paste that into a new tab and I get a 404…

    #68
  69. Brian Cray said— 4 months later

    @Mickey: Ahhh… do it now.

    #69
  70. Mickey said— 4 months later

    That fixed it!

    I’m having the same problem when trying to get it to work on my domain.

    Was it something in the config file?

    #70
  71. Brian Cray said— 4 months later

    Yea:
    define(‘BASE_HREF’, ‘http://’ . $_SERVER['HTTP_HOST'] . ‘/’);
    change to…
    define(‘BASE_HREF’, ‘http://’ . $_SERVER['HTTP_HOST'] . ‘/tests/shorturl2/’);

    #71
  72. Icy said— 4 months later

    Great script. How would you redirect short URLS that don’t exist back to the root page? For example, if http://icy.com/asdf does not exist, how do I make it go to http://icy.com ?

    #72
  73. Dylan said— 4 months later

    @Icy

    Redirect your 404 error page to http://icy.com

    Do you have other purposes for your 404 error page? If not, this is a very efficient way to go.

    #73
  74. Sanjay said— 4 months later

    I.m getting an Internal Server Error message. Any idea what the problem might be?

    #74
  75. zizinya said— 5 months later

    Hey this script kicks *ss indeed. Lots of helpful info throughout the site.

    #75
  76. growco said— 5 months later

    great script. I came here for the distance calculator and I haven’t left yet!

    #76
  77. Greg Tsioros said— 5 months later

    Thanks for all the fantastic information. Very useful stuff.

    #77
  78. Alvin said— 5 months later

    I just launched a new url shortener named Bugr.Me!
    The script i used is from urli.
    You can personalize it by adding your name to it.

    http://bugr.me/“yourname”

    Have fun shortening!

    Alvin

    #78
  79. JD said— 5 months later

    I’m getting an error 500 on the .htaccess file as well. My host is Godaddy. What could be wrong?

    #79
  80. Less talk, More Example said— 6 months later

    Hi brian,

    I think the CURL process taken too much bandwidth since it request the full body of the web page. You can cut down the bandwith for curl procress under 90% by just requesting the HTTP header. Consider using CURLOPT_NOBODY option

    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url_to_shorten);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
    curl_setopt($ch, CURLOPT_NOBODY, TRUE); // only request HTTP header
    $response = curl_exec($ch);
    curl_close($handle);
    if(curl_getinfo($ch, CURLINFO_HTTP_CODE) == ’404′)
    {
    die(‘Not a valid URL’);
    }

    thanks.

    #80
  81. mark said— 6 months later

    Hey Brian, Great script, Thank you. I just installed as a private shortener and it works great.
    I am a newbie in the field and trying to learn the strings. Could anyone suggest a way that I add a query to see all of my shortened url’s for reference? I have the list but now have to turn the id into the shortened url – example http://**.**/10 = http://**.**/a

    #81
  82. vangel said— 6 months later

    @all,
    Yes you can use long_url higher than 255. However you can define a key on it longer than 255 because the max_key_length limit for mysql is 255. I am uncertain how it will impact the performance later if the unique_key definition is removed.

    I would use InnoDB UUID in combination with your URL generation to make an index on, this way i could have best of both worlds.. For some reason i only get the auto increment value in my shorturl. That is not what I was expecting or I am doing something wrong. The redirection works fine otherwise.

    @Brian you have written a good script here that saved me a lot of time from writing my own from scratch. I have done all the fixes and tweaks. There are a few minor bugs which are easily resolved.

    thank you.

    #82
  83. Ritz said— 6 months later

    Great Script and equally great contribution from all.

    Is it possible for a user to write its own short text, which then will be checked if it is available and then entered into database

    For eg. I want to save this google query
    http://www.google.co.uk/search?rlz=1C1GGLS_en-GB___GB366&sourceid=chrome&ie=UTF-8&q=shorturl

    to http://t1ny.us/shorturl where I provided the text ‘shorturl’

    Can someone help me to write a demo code?

    #83
  84. Ritz said— 6 months later

    o! just saw that Alvin at http://bugr.me/ has implemented what I was looking for.

    Alvin, would you like to share this with all the open source lovers…

    Thanks in advance.

    #84
  85. Ritz said— 6 months later

    I gnore above two posts, please as I found that it is based on http://urli.ca/ where you can download this script.

    Thanks a lot

    #85
  86. vangel said— 6 months later

    I agree with Ritz. this seems to be a lot better script and no bugs. Sorry Brian, your script had some logical errors that just cannot be.
    Nice try.

    #86
  87. Kaushik said— 6 months later

    is there any way i can see the number of shortened URLs and delete unwanted entries?

    #87
  88. Steve said— 6 months later

    Same with me… getting an Internal Server Error message also…any ideas?

    #88
  89. Brian Cray said— 6 months later

    If you’re getting 500 server errors, remove the following 3 lines from your .htaccess:
    php_flag register_globals off
    php_flag magic_quotes_gpc off
    php_value display_errors 0

    #89
  90. charles said— 6 months later

    I am getting following:
    Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 3 in /home/public_html/mydomain.com/shorten.php on line 35 http://mydomain.com/5

    #90
  91. Brian Cray said— 6 months later

    The new files (just uploaded) should solve that problem Charles.

    #91
  92. charles said— 6 months later

    Brian,
    Thanks for the latest upload. It worked perfect for me. Great code.

    #92
  93. DmitrySh said— 6 months later

    Great script. In addition, I think we can use md5-field as a primary key instead of `id`. It could be a checksum for `long_url`. More over, we can store md5 in binary format as binary(16).

    #93
  94. Perry said— 7 months later

    Great script and many thanks for this. I have incorporated this in my website to use more as a marketing tool. It now generates short URLs on the fly for my own domain which are used for the social media buttons. This, hopefully, helps by keeping the domain name for inbound links and should be useful and friendly to the search engines. It also gives a much more recognisable URL to anyone posting links. My Zen cart generated URLs are long and complicated, but have to stay!

    I am getting the ‘unable to jump to row 0’ error that others have mentioned. All works fine, but my error logs were getting a little too large. I have solved this by inserting ‘@’ before ‘mysql_result’ in both the redirect.php and shorten.php files – no more error messages.

    The code to programmatically shorten urls did need a small change. Towards the end ‘$_SERVER['HTTP_HOST'] . ‘/’ . $_SERVER['REQUEST_URI']’ needed to be changed to ‘$_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI']’ to take out the ‘/’ . This introduced an additional ‘/’ in the URL producing a new short url each time each page loaded – this lead to the database crashing (I think when it increased the long URL to over 255?).

    I would like to develop this further, but have very limited knowledge of PHP, and am surprised that I have managed to get this far!

    I would like the ability to define my own short (or not so short!) URLs for certain pages so that, say, http://starfishbooks.co.uk/index.php?main_page=product_info&cPath=1&products_id=149
    can be defined as
    http://starfishbooks.co.uk/Artists-sketch-book.
    This would help no end in producing relevant URLs for google adwords purposes. Would it be possible to create an input field so this can be done, possibly using a separate table in the database (so that the shorter ones can still be used for social media feeds and avoid the problem of duplicates)? If so how?

    Oh, and one other question – where are the short URLs stored? Stupid question I know, but I can’t see them in my database using phpmyadmin? Short on knowledge, but learning fast.

    #94
  95. Eli J said— 7 months later

    After removing the 3 lines from .htaccess I’m still getting the same 500 internal server error. Any suggestions? Many thanks for writing this script. You’re awesome!

    #95
  96. Rudolf said— 7 months later

    I would like to add a image upload with API how can we do this?

    #96
  97. Shane said— 7 months later

    Hoping for some help:

    I just installed this at http://equi.me and the script loads up fine, but the shortened URL doesn’t work. I’ve tried a few different URLs and none of them work. Any ideas? Thanks! :-)

    #97
  98. Peter Armenti said— 7 months later

    Is there anyway to choose a custom url?

    #98
  99. Dave said— 8 months later

    I like your call to LOCK TABLES, and consequently, UNLOCK TABLEs =) .. I used PHP to lock the table via usleep(n), where n is something like 3,000. Urlshorts.info… It still needs tracking implementation, and I’m thinking of open-sourcing it.

    #99
  100. Pete said— 8 months later

    Hello,

    Firstly, many thanks to the OP for this script, works great.

    One question however… It appears that the URLs stored in my database are then subsequently referred to in the ID field, which is defined as an INT(10) datatype. If this script does indeed generate shortened URLS that end alphanumeric, how can this data be stored in this field?

    Or perhaps I’m being really stupid here…

    Thanks!

    #100
  101. Steve said— 9 months later

    When i go to shorten a URL, it’s just writing the source of shorten.php into the textbox instead of returning the shortURL. Any ideas what I’ve done wrong?

    #101
  102. Paul said— 9 months later

    @Steve same problem here. :(
    Using PHP5.2 on CentOS

    #102
  103. Bryan said— 9 months later

    Am I missing something? I installed on my webserver (without any problems) however when I enter a URL in the form, nothing happens. The page reloads and the form/box clears.

    I tried on the sample form here on this site, and am getting the same results. I’ve tried with IE 7, IE 8, Firefox and Chrome.

    #103
  104. Gabriel said— 9 months later

    Steve, Paul: I ran into the same problem and finally found what’s causing it. Check the config.php, redirect.php and shorten.php files, they are missing some start and end PHP tags just make sure you have them everywhere and it will start working.

    #104
  105. Eddie said— 9 months later

    So where should the php start and end tags be?

    I get a 500 error when I put in on my domain off a sub directory, but couldnt seem to be able to load it.

    #105
  106. Mo said— 10 months later

    Excellent script! Question – how to integrate CAPTCHA? I pulled it down because of a lot of requests in a short period of time.

    #106
  107. Gaurav Dadhania said— 10 months later

    Thanks a lot Brian, the script works like a charm. Added a password field to prevent misuse :D

    @Gabriel Thanks for that pointer, totally ignored the tags!

    #107
  108. Respond to this post—

Return to navigation
1199