Category Archives: Database Scripts

Twitter User Info Database Table SQL (Insert/Update)

To insert or update Twitter user info database table SQL, we first, grab the entire user data from Twitter REST API. If you are unsure on how to do this refer to the previous post on how to connect to Twitter API.

The Twitter User Data Object

Here is what the data returns as a full user Twitter data object.

SQL to Create a New User Table

SQL code to create a new table in your database to store the user data grabbed from Twitter.

--
-- Table structure for table `prof_twitter_data`
--

CREATE TABLE IF NOT EXISTS `prof_twitter_data` (
`profTwitterId` bigint(11) NOT NULL AUTO_INCREMENT,
`uid` bigint(11) NOT NULL,
`name` varchar(52) NOT NULL,
`screen_name` varchar(52) NOT NULL,
`description` varchar(255) NOT NULL,
`profile_image_url` varchar(255) NOT NULL,
`url` varchar(255) NOT NULL,
`location` varchar(52) NOT NULL,
`friends_count` int(5) NOT NULL,
`followers_count` int(5) NOT NULL,
`status_id_str` varchar(52) NOT NULL,
`status_text` varchar(255) NOT NULL,
`status_created_at` varchar(52) NOT NULL,
PRIMARY KEY (`profTwitterId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

SQL to Insert New Record into User Table

The variable $d stores the user info grabbed from Twitter API (see screenshot above). Also note the status is a stdClass data type and not an array.

// Insert User Info to database.
$query = mysql_query("INSERT INTO `".self::$settings['DB_TWITTER_DATA_TABLE']."` (`profTwitterId`, `uid`, `name`, `screen_name`, `description`, `profile_image_url`, `url`, `location`, `friends_count`, `followers_count`, `status_id_str`, `status_text`, `status_created_at`) VALUES ('', '".$uid."', '".$d['name']."', '".$d['screen_name']."', '".$d['description']."', '".$d['profile_image_url']."', '".$d['url']."', '".$d['location']."', '".$d['friends_count']."', '".$d['followers_count']."', '".$d['status']->id_str."', '".$d['status']->text."', '".$d['status']->created_at."');");

SQL to Update Record in User Table

//Update User Info in database.
$query = mysql_query("UPDATE `".self::$settings['DB_TWITTER_DATA_TABLE']."` SET `name` = '".$d['name']."', `screen_name` = '".$d['screen_name']."', `description` = '".$d['description']."', `profile_image_url` = '".$d['profile_image_url']."', `url` = '".$d['url']."', `location` = '".$d['location']."', `friends_count` = '".$d['friends_count']."', `followers_count` = '".$d['followers_count']."', `status_id_str` = '".$d['status']->id_str."', `status_text` = '".$d['status']->text."', `status_created_at` = '".$d['status']->created_at."' WHERE `uid` = ".$uid.";");

Here is the final store of the data in the user info table.

All PHP Scripts on this website are provided by phpscripts4u.com where you can find all the latest PHP code snippets, plugins and libraries.

Twitter oAuth Database Table SQL (Insert/Update)

1 Reply

Twitter oAuth Database Table SQL (Insert/Update)

First we grab the users open authentication token (key and secret) from Twitter REST API. If you are unsure on how to do this refer to the previous post on how to connect to Twitter API. You now only need to connect to twitter once and then your oAuth keys to generate an access token are stored in database so even when you clear cookies it will grab oAuth keys from database to generate a new token for the session.

SQL to Create a New Auth Table

SQL code to create a new table in your database to store the user authentication data grabbed from Twitter.

--
-- Table structure for table `user_auth`
--

CREATE TABLE IF NOT EXISTS `user_auth` (
`user_auth_id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
`uid` bigint(11) NOT NULL,
`oauth_provider` varchar(10) DEFAULT NULL,
`oauth_uid` text,
`oauth_token` text,
`oauth_secret` text,
`username` text,
PRIMARY KEY (`user_auth_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

SQL to Insert New Record into User Auth Table

The table name is stored in a variable called $settings. The access token is stored in the $_SESSION and copied to a variable called $access_token.

//Insert new record in database.
$query = mysql_query("INSERT INTO ".self::$settings['DB_AUTH_TABLE']." (uid, oauth_provider, oauth_uid, username, oauth_token, oauth_secret) VALUES ('{$uid}', 'twitter', {$oAuthUid}, '{$username}', '{$access_token['oauth_token']}', '{$access_token['oauth_token_secret']}')");

SQL to Update Record in User Auth Table

//Update User Authentication in database.
$query = mysql_query("UPDATE ".self::$settings['DB_AUTH_TABLE']." SET oauth_token = '{$access_token['oauth_token']}', oauth_secret = '{$access_token['oauth_token_secret']}', uid = '{$uid}' WHERE oauth_provider = '{$provider}' AND oauth_uid = {$oAuthUid}");

Here is the final store of the data in the authentication table.

All PHP Scripts on this website are provided by phpscripts4u.com where you can find all the latest PHP code snippets, plugins and libraries.

Database Table Structure for Store Twitter API User Data

Leave a reply

This post will show you Database Table Structure for Store Twitter API User Data. The tweets received from the Twitter streaming API are stored in the database in two steps as described in the code architecture.

First: The entire payload for each tweet is stored in the

json_cache

table by

get_tweets.php

. No parsing is done at this point, instead all the data for the tweet is inserted into the

raw_tweet field

Second:

parse_tweets.php

parses each new tweet and distributes the data across a series of tables:

tweets

tweet_mentions

tweet_tags

tweet_urls

, and

users

Note: Each

json_cache

row contains the

tweet_id

to make it possible to go back later and reparse the raw API results.

CREATE TABLE IF NOT EXISTS `json_cache` (
`tweet_id` bigint(20) unsigned NOT NULL,
`cache_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`cache_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`raw_tweet` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`parsed` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`cache_id`),
KEY `tweet_id` (`tweet_id`),
KEY `cache_date` (`cache_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `tweets` (
`tweet_id` bigint(20) unsigned NOT NULL,
`tweet_text` varchar(160) NOT NULL,
`entities` text NOT NULL,
`created_at` datetime NOT NULL,
`geo_lat` decimal(10,5) DEFAULT NULL,
`geo_long` decimal(10,5) DEFAULT NULL,
`user_id` int(10) unsigned NOT NULL,
`screen_name` char(20) NOT NULL,
`name` varchar(40) DEFAULT NULL,
`profile_image_url` varchar(200) DEFAULT NULL,
PRIMARY KEY (`tweet_id`),
KEY `created_at` (`created_at`),
KEY `user_id` (`user_id`),
KEY `screen_name` (`screen_name`),
KEY `name` (`name`),
FULLTEXT KEY `tweet_text` (`tweet_text`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `tweet_mentions` (
`tweet_id` bigint(20) NOT NULL,
`source_user_id` bigint(20) NOT NULL,
`target_user_id` bigint(20) NOT NULL,
KEY `tweet_id` (`tweet_id`),
KEY `source` (`source_user_id`),
KEY `target` (`target_user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `tweet_tags` (
`tweet_id` bigint(20) NOT NULL,
`tag` varchar(100) NOT NULL,
KEY `tweet_id` (`tweet_id`),
KEY `tag` (`tag`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `tweet_urls` (
`tweet_id` bigint(20) NOT NULL,
`url` varchar(140) NOT NULL,
KEY `tweet_id` (`tweet_id`),
KEY `url` (`url`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `users` (
`user_id` bigint(20) unsigned NOT NULL,
`screen_name` varchar(20) NOT NULL,
`name` varchar(40) DEFAULT NULL,
`profile_image_url` varchar(200) DEFAULT NULL,
`location` varchar(30) DEFAULT NULL,
`url` varchar(200) DEFAULT NULL,
`description` varchar(200) DEFAULT NULL,
`created_at` datetime NOT NULL,
`followers_count` int(10) unsigned DEFAULT NULL,
`friends_count` int(10) unsigned DEFAULT NULL,
`statuses_count` int(10) unsigned DEFAULT NULL,
`time_zone` varchar(40) DEFAULT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`),
KEY `user_name` (`name`),
KEY `last_update` (`last_update`),
KEY `screen_name` (`screen_name`),
FULLTEXT KEY `description` (`description`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

All PHP Scripts on this website are provided by phpscripts4u.com where you can find all the latest PHP code snippets, plugins and libraries.

PHP Scripts for Simple DailyPic / Picture of the Day

Leave a reply

Below is a quick and simple way to achieve a Picture of the Day / DailyPic script without database access for your website. You can just about put this anywhere in your web site.

1   <?php
2
3   $date = date("Y-m-d");
4
5   // URL to Images.
6   $image_url = "http://yourwebsite/DailyPic/photos/";
7
8   // Path to Images
9   $image_dir = "/path/to/your/site/htdocs/DailyPic/photos/";
10
11   // Default if no image is available for Today
12   $default = "http://yourwebsite/DailyPic/photos/default.jpg";
13
14   // Image Extension for Your Pics
15   $ext = ".jpg";
16
17   $photo = "$image_dir/$date" . $ext . "";
18
19   if (file_exists ($photo)){
20        echo '
21            <div>
22            <img src="'.$image_url.'/'.$date.$ext.'" border="0">
23            </div>
24        ';
25   } else {
26        echo '
27            <div>
28            <img src="'.$default.'" border="0">
29            </div>
30        ';
31   }
32   ?>

PHP Scripts for Free to Gallery Site

Leave a reply

The site is coded in php, comes with full source code. It’s 100% FREE and requires no database for the images. Simply upload your images and the script does the rest!

Completely FREE!,Written in php.All pages are created using Apaches mod-rewrite so it looks like flat html pages to the search engines.
Automatic creation of the thumbnail images.Ability to categorise into albums.
No database is needed. Simply upload your images into their own folders (albums) and the script will do the rest.Images are automatically titled with the original filename.JPG, PNG and static GIF images supported.Single config file allows for amending thumbnail size, how many per page, site title, colours and more. Automatic page titles/meta information based on the image,filename/directory.

<?php

function createhtmlname($name) {
$replace_values = array(" ", "'", "\"", "\\", "/", "?", "|", "@", "#", "~", "!", "£", "$", "%", "^", "&", "*", "(", ")", "[", "]", "{", "}", "+", "=", "-");
$name = str_replace($replace_values, "_", $name);
$name = str_replace(".", "", $name);
$name = str_replace(",", "", $name);
return strtolower($name);
}

function randomfilename() {
$salt = "0123456789abcdefghijklmnopqrstuvwxyz";
srand((double)microtime()*1000000);
$i = 0;
while ($i <= 25) {
$num = rand() % 33;
$tmp = substr($salt, $num, 1);
$nname = $nname . $tmp;
$i++;
}
return $nname;
}

function count_photos($folder) {
global $storage_location;
$dir_handle = @opendir($storage_location.$folder);
$total = 0;
while ($file = readdir($dir_handle)) {
if(($file != ".") && ($file != "..")) {
$exp   = explode(".", $file);
$where = COUNT($exp)-1;
$ext   = $exp[$where];
if(($ext == "jpg") || ($ext == "jpeg") || ($ext == "pjpg") || ($ext == "png") || ($ext == "gif")) $total++;
}
}
closedir($dir_handle);
return $total;
}

function load_photos($folder) {
global $storage_location;
$file_listing = array();
$dir_handle = @opendir($storage_location.$folder);
while ($file = readdir($dir_handle)) {
if(($file != ".") && ($file != "..")) {
$exp   = explode(".", $file);
$where = COUNT($exp)-1;
$ext   = $exp[$where];
$title = $exp[0];
if(($ext == "jpg") || ($ext == "jpeg") || ($ext == "pjpg") || ($ext == "png") || ($ext == "gif")) $file_listing[] = array('path'=>$storage_location.$folder."/".$file, 'filename'=>$file, 'title'=>$title, 'ext'=>$ext);
}
}
closedir($dir_handle);
return $file_listing;
}

function galleryImage ($path, $max_x, $justurl = FALSE) {

include_once("config.inc.php");

$sourceImagePath = $path;
$targetImagePath = "cache/".MD5($path.$max_x).".jpg";
$outputImageQuality = 80;

/* Check if file is already cached, if so just deliver existing image */
if(!file_exists($targetImagePath)) {

/* MAIN RESIZING SCRIPT */

/* Load Dimensions of Original Image */
$originalImageSize = getimagesize($sourceImagePath);
$original_x = $originalImageSize[0];
$original_y = $originalImageSize[1];

if($original_x > $original_y) {
$max_y = 0;
$max_x = $max_x;
}
else if($original_x < $original_y) {
$max_y = $max_x;
$max_x = 0;
}
else {
$max_y = $max_x;
$max_x = $max_x;
}

/* Work out ratios and which way to crop */
$state = 0;
if($square == 1) {
if($max_x == 0) $max_x = $max_y;
elseif($max_y == 0) $max_y = $max_x;
}
if($max_x == 0) $state = 1;
elseif($max_y == 0) $state = 2;
if($state == 0) {
$testratio = $max_x / $max_y;
$origratio = $original_x / $original_y;
if($origratio > $testratio) $state = 1;
elseif($origratio < $testratio) $state = 2;
else $state = 3;
}

/* With ratios sorted, plot co-ordinates */

if($state == 1) {
/* make new-y = max-y OR crop sides */

if($square == 0) {
if(($original_y > $max_y) || ($enlarge == 1)) $new_y = $max_y;
else $new_y = $original_y;
$new_x = round(($original_x / ($original_y / $new_y)), 0);
$srcx = 0;
$srcy = 0;
$srcw = $original_x;
$srch = $original_y;
}

else {
if(($original_y > $max_y) || ($enlarge == 1)) $new_y = $max_y;
else $new_y = $original_y;
$new_x = $new_y;
$tempratio = ($original_y / $new_y);
$sectionwidth = $new_y * $tempratio;
$srcy = 0;
$srch = $original_y;
$srcx = floor(($original_x - $sectionwidth) / 2);
$srcw = floor($sectionwidth);
}

}

elseif($state == 2) {
/* make new-x = max-x OR crop top & bottom */

if($square == 0) {
if(($original_x > $max_x) || ($enlarge == 1)) $new_x = $max_x;
else $new_x = $original_x;
$new_y = round(($original_y / ($original_x / $new_x)), 0);
$srcx = 0;
$srcy = 0;
$srcw = $original_x;
$srch = $original_y;
}

else {
if(($original_x > $max_x) || ($enlarge == 1)) $new_x = $max_x;
else $new_x = $original_x;
$new_y = $new_x;
$tempratio = ($original_x / $new_x);
$sectionheight = $new_x * $tempratio;
$srcx = 0;
$srcw = $original_x;
$srcy = floor(($original_y - $sectionheight) / 2);
$srch = floor($sectionheight);
}

}

elseif($state == 3) {
/* original image ratio = new image ratio - use all of image */

if($square == 0) {
if(($original_x > $max_x) || ($enlarge == 1)) $new_x = $max_x;
else $new_x = $original_x;
$new_y = round(($original_y / ($original_x / $new_x)), 0);
$srcx = 0;
$srcy = 0;
$srcw = $original_x;
$srch = $original_y;
}

else {
if(($original_x > $max_x) || ($enlarge == 1)) $new_x = $max_x;
else $new_x = $original_x;
$new_y = $new_x;
$srcx = 0;
$srcy = 0;
$srcw = $original_x;
$srch = $original_y;
}

}

/* Do Conversion */
if(strstr(strtolower($path), ".jpg")) $originalImage = ImageCreateFromJPEG($sourceImagePath);
elseif(strstr(strtolower($path), ".jpeg")) $originalImage = ImageCreateFromJPEG($sourceImagePath);
elseif(strstr(strtolower($path), ".pjpg")) $originalImage = ImageCreateFromJPEG($sourceImagePath);
elseif(strstr(strtolower($path), ".png")) $originalImage = ImageCreateFromPNG($sourceImagePath);
elseif(strstr(strtolower($path), ".gif")) $originalImage = ImageCreateFromGIF($sourceImagePath);
$newImage = ImageCreateTrueColor($new_x, $new_y);
ImageCopyResampled ($newImage, $originalImage, 0, 0, $srcx, $srcy, $new_x, $new_y, $srcw, $srch);
ImageJPEG ($newImage, $targetImagePath, $outputImageQuality);
ImageDestroy($newImage);
ImageDestroy($originalImage);

}

/* Output Image */
$imageSize = getimagesize($targetImagePath);
if($justurl == TRUE) return $targetImagePath;
else return "<img src=\"$targetImagePath\" width=\"$imageSize[0]\" height=\"$imageSize[1]\" border=\"0\" style=\"border:1px solid #000000;\">";

}

?>

&nbsp;

PHP converts data scripts

Leave a reply

Feed2Html is a free PHP script that converts data from RSS to HTML. If you already have an RSS feed, just install this script to your site. You will be amazed how convenient it will become to publish news. The Feed2Html script updates web-pages on-the-fly and makes sure that information from your RSS feeds is ready to be processed by search robots. To output information, site templates are used that help to embed the page generated by the script into any web site.Script that converts data from RSS to HTML. Feed2Html is a free PHP script that converts data from RSS to HTML. If you already have an RSS feed, just install this script to your site. You will be amazed how convenient it will become to publish news.

<?php
//
// RSS to HTML webpage script v.1.2
//
// Copyright 2002-2006 ExtraLabs Software
//
// Website: http://www.extralabs.net
// Support: support@extralabs.net
//
// License: Freeware
// This script may be used freely for business or personal use
//
include "./rss_export.php";

// Main Settings
//
// Your RSS feed:
$rss_feed="http://www.extralabs.net/my-mix.xml";

// Template for the feed:
$template="sample-template.rat";

// More info about date() function you can find here: http://www.php.net/manual/en/function.date.php.
$DateFormat="d M y, h:m:s";

if (isset($_REQUEST["RSSFILE"])) {
$rss_feed = $_REQUEST["RSSFILE"];
}

if (isset($_REQUEST["TEMPLATE"])) {
$template = $_REQUEST["TEMPLATE"];
}

$FeedMaxItems = 5000;
if (isset($_REQUEST["MAXITEMS"])) {
$FeedMaxItems = $_REQUEST["MAXITEMS"];
}

$RandomItems=0;
if (isset($_REQUEST["RANDOM"])) {
$RandomItems = $_REQUEST["RANDOM"];
}

error_reporting(E_ERROR);
$rss = new RSS_export;
$rss->cache_dir = './temp';
$rss->cache_time = 1200;
$from = 1;
$rss->date_format = $DateFormat;
if ($rs = $rss->get($rss_feed))
{
$theData = file($template);
$count = 0;
$from = -1;
foreach($theData as $line)
{
if ((strstr($line,"NOCRLF=")) || (strstr($line,"NAME=")) || (strstr($line,"FILEEXT=")) || (strstr($line,"DATEFORMAT=")) || (strstr($line,"TIMEFORMAT="))) {
$line="";
}
$line=str_replace("%Copyright%", "$rs[copyright]\n", $line);
$line=str_replace("%Copyright%", "", $line);
$line=str_replace("%Language%", "$rs[language]\n", $line);
$line=str_replace("%Language%", "", $line);
$line=str_replace("%Editor%", "$rs[managingEditor]\n", $line);
$line=str_replace("%Editor%", "", $line);
$line=str_replace("%Webmaster%", "$rs[webMaster]\n", $line);
$line=str_replace("%Webmaster%", "", $line);
$line=str_replace("%FeedPubTime%", "$rs[lastBuildDate]\n", $line);
$line=str_replace("%FeedPubTime%", "", $line);
$line=str_replace("%Rating%", "$rs[rating]\n", $line);
$line=str_replace("%Rating%", "", $line);
$line=str_replace("%Docs%", "$rs[docs]\n", $line);
$line=str_replace("%Docs%", "", $line);

$line=str_replace("%FeedTitle%", "$rs[title]\n", $line);
// $line=str_replace("%FeedLink%", "<a href=\"$rs[link]\">$rs[title]</a>\n", $line);
$line=str_replace("%FeedLink%", "$rs[link]\n", $line);
$line=str_replace("%FeedDescription%", $rs[description], $line);

$line=str_replace("&lt;", "<", $line);
$line=str_replace("&gt;", ">", $line);

$line=str_replace("&nbsp;", " ", $line);
$line=str_replace("&quot;", " ", $line);
$line=str_replace("&copy;", " ", $line);
$line=str_replace("&reg;", " ", $line);
$line=str_replace("&trade;", " ", $line);
$line=str_replace("&euro;", "?", $line);
$line=str_replace("&bdquo;", " ", $line);
$line=str_replace("&ldquo;", " ", $line);
$line=str_replace("&laquo;", " ", $line);
$line=str_replace("&raquo;", " ", $line);
$line=str_replace("&sect;", " ", $line);
$line=str_replace("&amp;", "&", $line);
$line=str_replace("", " ", $line);
$line=str_replace("&apos;", "'", $line);
if ($rs['image_url'] != '') {
$line=str_replace("%ImageItem%", "<a href=\"$rs[image_link]\"><img src=\"$rs[image_url]\" alt=\"$rs[image_title]\" vspace=\"1\" border=\"0\" /></a>\n", $line);
}
else {
$line=str_replace("%ImageItem%", "", $line);
}
$count = $count+1;
if (strstr($line,"%BeginItemsRecord%")){
$from = $count;
}
if ($from == -1){ echo $line;}
}

$linecount = 0;

foreach($rs['items'] as $item)
{

if ($RandomItems == 1) {

$seeder = hexdec(substr(md5(microtime()), -8)) & 0x7fffffff;
mt_srand($seeder);
$c=mt_rand(0,1);
if ($c == 0) {
$seeder = hexdec(substr(md5(microtime()), -8)) & 0x7fffffff;
mt_srand($seeder);
continue;
}
}

if ($linecount == $FeedMaxItems) {
break;
}
++$linecount;

$strcount=0;
foreach($theData as $line){
$strcount=$strcount+1;
if ($strcount>=$from){
$line=str_replace("%BeginItemsRecord%", "", $line);
$line=str_replace("%ItemTitle%", $item['title'], $line);
$line=str_replace("%ItemLink%", $item['link'], $line);
$line=str_replace("%ItemDescription%",$item['description'], $line);
$line=str_replace("%ItemPubTime%", $item['pubDate'], $line);
$line=str_replace("%ItemPubTime%", "", $line);

$line=str_replace("%EndItemsRecord%", "", $line);

$line=str_replace("&lt;", "<", $line);
$line=str_replace("&gt;", ">", $line);
$line=str_replace("&nbsp;", " ", $line);
$line=str_replace("&quot;", " ", $line);
$line=str_replace("&copy;", " ", $line);
$line=str_replace("&reg;", " ", $line);
$line=str_replace("&trade;", " ", $line);
$line=str_replace("&euro;", "?", $line);
$line=str_replace("&bdquo;", " ", $line);
$line=str_replace("&ldquo;", " ", $line);
$line=str_replace("&laquo;", " ", $line);
$line=str_replace("&raquo;", " ", $line);
$line=str_replace("&sect;", " ", $line);
$line=str_replace("&amp;", "&", $line);
$line=str_replace("", " ", $line);
$line=str_replace("&apos;", "'", $line);

echo $line; }
}
}
}
else
{
echo "Error: An error occured while parsing RSS file. Please contact with us at: support@extralabs.net\n";
}
?>

PHP Web Scripts

Leave a reply

PHPWeb ip2country PHP class for resolving IP address(es) to country. Use it for, providing location aware content, preventing fraud, displaying the country name from where the visitor is, web analytics, or blocking users from certain countries. There are many other uses, we will leave that to your imagination. Import script for the database is included in the zip file. First you need to download GeoLite country database and import it with the import script (the database is FREE and they do updates every month).Extract GeoIPCountryCSV.zip and ip2country.zip in same empty folder (example: ip2c). Do not rename any of the files extracted. In the folder should be a file import.php. Open it with your editor (ex. Wordpad), change the values marked to match your server settings and save the file.

<?php
/*********************************************************

* DO NOT REMOVE *

Project: PHPWeby ip2country software version 1.0.2
Url: http://phpweby.com/
Copyright: (C) 2008 Blagoj Janevski - bl@blagoj.com
Project Manager: Blagoj Janevski

More info, sample code and code implementation can be found here:
http://phpweby.com/software/ip2country

This software uses GeoLite data created by MaxMind, available from
http://maxmind.com

This file is part of i2pcountry module for PHP.

For help, comments, feedback, discussion ... please join our
Webmaster forums - http://forums.phpweby.com

**************************************************************************
* If you like this software please link to us!                          *
* Use this code:                                 *
* <a href="http://phpweby.com/software/ip2country">ip to country</a>    *
* More info can be found at http://phpweby.com/link                     *
**************************************************************************

License:
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License along
with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.

*********************************************************/

class ip2country {

var $mysql_host='localhost';
var $db_name='ip2c';
var $db_user='user';
var $db_pass='';
var $table_name='ip2c';

var $ip_num=0;
var $ip='';
var $country_code='';
var $country_name='';
var $con=false;

function ip2country()
{
$this->set_ip();
}

function get_ip_num()
{
return $this->ip_num;
}
function set_ip($newip='')
{
if($newip=='')
$newip=$this->get_client_ip();

$this->ip=$newip;
$this->calculate_ip_num();
$this->country_code='';
$this->country_name='';
}
function calculate_ip_num()
{
if($this->ip=='')
$this->ip=$this->get_client_ip();

$this->ip_num=sprintf("%u",ip2long($this->ip));
}
function get_country_code($ip_addr='')
{
if($ip_addr!='' && $ip_addr!=$this->ip)
$this->set_ip($ip_addr);

if($ip_addr=='')
{
if($this->ip!=$this->get_client_ip())
$this->set_ip();
}

if($this->country_code!='')
return $this->country_code;

if(!$this->con)
$this->mysql_con();

$sq="SELECT country_code,country_name FROM ".$this->table_name. " WHERE ". $this->ip_num." BETWEEN begin_ip_num AND end_ip_num";
$r=@mysql_query($sq,$this->con);

if(!$r)
return '';

$row=@mysql_fetch_assoc($r);
$this->close();
$this->country_name=$row['country_name'];
$this->country_code=$row['country_code'];
return $row['country_code'];
}

function get_country_name($ip_addr='')
{
$this->get_country_code($ip_addr);
return $this->country_name;
}

function get_client_ip()
{
$v='';
$v= (!empty($_SERVER['REMOTE_ADDR']))?$_SERVER['REMOTE_ADDR'] :((!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) ? $_SERVER['HTTP_X_FORWARDED_FOR']: @getenv('REMOTE_ADDR'));
if(isset($_SERVER['HTTP_CLIENT_IP']))
$v=$_SERVER['HTTP_CLIENT_IP'];
return htmlspecialchars($v,ENT_QUOTES);
}

function mysql_con()
{
$this->con=@mysql_connect($this->mysql_host,$this->db_user,$this->db_pass);

if(!$this->con)
return false;

if( !mysql_query('USE ' . $this->db_name))
{
$this->close();
return false;
}
return true;

}
function get_mysql_con()
{
return $this->con;
}
function create_mysql_table()
{
if(!$this->con)
return false;

mysql_query('DROP table ' . $this->table_name,$this->con);
return mysql_query("CREATE table " . $this->table_name ." (id int(10) unsigned auto_increment, begin_ip varchar(20),end_ip varchar(20),begin_ip_num int(11) unsigned,end_ip_num int(11) unsigned,country_code varchar(3),country_name varchar(150), PRIMARY KEY(id),INDEX(begin_ip_num,end_ip_num))ENGINE=MyISAM",$this->con);
}

function close()
{
@mysql_close($this->con);
$this->con=false;
}
}
?>

PHP link manager software Scripts

Leave a reply

Ink manager software will completely automate your link exchange! Here is how your reciprocal link exchange will work with LinkMan.Webmaster John is interested in exchanging links with your site,He ads a link to your website on his links page (or any other page on his website),He submits the “Add a link” form on your site,LinkMan verifies that a reciprocal link to your site has indeed been added,A few more automated checks to verify everything is OK and the new link automatically appears on your links page!It’s that simple! In the LinkMan admin manager panel you have a special tool that will crawl all URLs where your reciprocal link should be. If a reciprocal link isn’t found LinkMan will automatically remove the missing link from your links page.

<?php
// SETUP YOUR LINK MANAGER
// Detailed information found in the readme.htm file
// File last modified: 1.7 @ April 18, 2009

/* Password for admin area */
$settings['apass']='admin';

/* Your website URL */
$settings['site_url']='http://www.domain.com';

/* Your website title */
$settings['site_title']="My lovely website";

/* Your website description */
$settings['site_desc']="This is a brief description of my website.";

/* Show "add a link" form on the bottom of links page? 1 = YES, 0 = NO */
$settings['show_form']=1;

/* Send you an e-mail everytime someone adds a link? 1=YES, 0=NO */
$settings['notify']=0;

/* Admin e-mail */
$settings['admin_email']='you@yourdomain.com';

/* Maximum number of links */
$settings['max_links']=10000;

/* Allow generation of new pages; 1=YES, 0=NO */
$settings['allow_pages']=1;

/* Number of links per page */
$settings['max_per_page']=30;

/* Approve links manually? 1=YES, 0=NO */
$settings['man_approval']=0;

/* URL of the approve.php file on your server */
$settings['url_approval']='http://www.yourdomain.com/links/approve.php';

/* Prevent automated submissions (recommended YES)? 1 = YES, 0 = NO */
$settings['autosubmit']=1;

/* Checksum - just type some digits and chars. Used to help prevent SPAM */
$settings['filter_sum']='5fcbd40c97e1497d88ae4e8fc53e7a8d';

/* Enable SPAM filter? 1=YES, 0=NO */
$settings['spam_filter']=1;

/* Block superlatives from title and description? 1=YES, 0=NO */
$settings['superlatives']=1;

/* Use normal links? 0=NORMAL, 1=REDIRECT ALL, 2=REDIRECT RECIPROCAL ONLY */
$settings['clean']=0;

/* Add rel="nofollow" attribute to links? 0=NO, 1=YES, 2=FOR RECIPROCAL ONLY */
$settings['use_nofollow']=0;

/* Where to add new links? 0 = top of list, 1 = end of list */
$settings['add_to']=1;

/* Name of the file where link URLs and other info is stored */
$settings['linkfile']='linkinfo.txt';

/* Name of the file where banned websites are stored */
$settings['banfile']='banned_websites.txt';

/* Display website URL after Title? 1=YES, 0=NO */
$settings['show_url']=1;

/* Display Google PageRank? 0=NO, 1=YES, 2=IN ADMIN PANEL ONLY */
$settings['show_pr']=1;

/* Minimum Google PageRank to accept website? A value from 0 to 10 */
$settings['min_pr']=0;

/* Minimum Google PageRank of reciprocal links page? A value from 0 to 10 */
$settings['min_pr_rec']=0;

/* Block links with rel="nofollow"? 1=YES, 0=NO */
$settings['block_nofollow']=1;

/* Block link from pages with meta robots nonidex or nofollow? 1=YES, 0=NO */
$settings['block_meta_rob']=1;

/* Block duplicate entries (same website added more than once)? 1=YES, 0=NO */
$settings['block_duplicates']=1;

/* Display website thumbnails? 0=NO, 1=YES, 2=FEATURED LINKS ONLY */
$settings['show_thumbshots']=2;

/* URL of your thumbshots service */
$settings['thumb_url']='http://open.thumbshots.org/image.pxf?url=';

/* Turn debug mode on? 1=YES, 0=NO */
$settings['debug']=0;

/* Which sections to hide by default */
$settings['hide']=array();

/*******************
* DO NOT EDIT BELOW
*******************/
$settings['verzija']='1.7';
$settings['delimiter']="\t";

if (!defined('IN_SCRIPT')) {die('Invalid attempt!');}
if ($settings['debug'])
{
error_reporting(E_ALL ^ E_NOTICE);
}
else
{
ini_set('display_errors', 0);
ini_set('log_errors', 1);
}

function pj_input($in,$error=0) {
$in = trim($in);
if (strlen($in))
{
$in = htmlspecialchars($in);
}
elseif ($error)
{
problem($error);
}
return stripslashes($in);
}

function pj_isNumber($in,$error=0) {
$in = trim($in);
if (preg_match("/\D/",$in) || $in=='')
{
if ($error)
{
problem($error);
}
else
{
return '0';
}
}
return $in;
}
?>

PHP upload files script

Leave a reply

Using this script you can also upload files like JPEG, GIF, PNG, TIFF.This manipulation script sets the default file type as text.And set file size less than 4000.Easily customizable and configurable.

<?php
if ((($_FILES["file"]["type"] == "pdf")|| ($_FILES["file"]["type"] == "text/plain"))
&& ($_FILES["file"]["size"] < 4000))
{
if ($_FILES["file"]["error"] > 0)
{
echo "Error: " . $_FILES["file"]["error"] . "<br />";
}
else
{
echo "<table align=center style='border:1px solid green;'><tr><Td>Upload:</td><td> " . $_FILES["file"]["name"] . "</td></tr>";
echo "<tr><td>Type:</td><td> " . $_FILES["file"]["type"] . "</td></tr>";
echo "<tr><td>Size: </td><td>" . ($_FILES["file"]["size"] / 1024) . " Kb</td></tr>";
echo "<tr><td>Stored in:</td><td> " . $_FILES["file"]["tmp_name"]."</td></tr></table>";
}
}
else
{
echo "Invalid file";
}
?>

PHP database query tool scprit

Leave a reply

Easy to use mysql HDBQ 2.1 database query tool.UI to add, save and manage frequently user queries.Support for date based queries.Queries can be added with ‘XXDATE’ variable.On sleceting the date from a calendar, the date will automatically change.E.g Query: select * from xxxx where date like ‘XXDATE’Simple file web based password protection.Database to be queried can be modified in the config file.File based tool (No Database Required).

<?php
include "authheader.php";
if($block == false){
?>

<html>
<head>
<?php
$type=$_POST['type'];
$radioo=$_POST['rb1'];
$qtext = $_POST['qtext'];
$qtext = str_replace("\\","",$qtext);
$user = $_POST['user'];
$pass = $_POST['pass'];
$host = $_POST['host'];
$dbn = $_POST['dbname'];
include './config.php';
$dho = $hostname;
$dus = $username;
$dpa = $password;
$ddb = $dbname;

if($user != "" && $host != "" && $dbn != "")
{
$hostname = $host;
$username = $user;
$password = $pass;
$dbname = $dbn;
}
$db = @mysql_connect($hostname, $username,$password);
if(!$db)
$con = "conf";
else if(!@mysql_select_db($dbname,$db))
$con = "dbnf";

include "queries.php";

$total = count($query);
$half = round($total/2);
echo "<script language=javascript>";
echo "var quer = Array;\n";
for($xxy = 0; $xxy < $total; $xxy++)
{
$qqqu = html_entity_decode($query[$xxy], ENT_QUOTES);
$qqqu = str_replace('"','\"',$qqqu);
//$qqqu = str_replace('XXDATE','"+date+"',$qqqu);
echo "quer[$xxy] = \"".$qqqu."\";\n";
}
echo "</script>";
include "header.php";
?>

<script language=javascript>
function changae()
{
var ss = document.ssd.dd.value;
document.form15.qtext.value = ss;
}

function changab(ddc, ddate)
{
document.form15.rb1.value=ddc;
var ss = "";
var dat = new Date();

if(!isNaN(ddc)){
var sss = eval("document.xxx.rb1["+ddc+"]");

if(ddate == "today"){
var year = dat.getFullYear();
var month = parseInt(dat.getMonth())+1;
var dax = year+"-"+month+"-"+dat.getDate();
}else{
var dax = ddate;
}
var queer = quer[ddc].replace('XXDATE',dax);
document.form15.qtext.value = queer;
}else{
document.form15.qtext.value = " ";
}
}

function setdate()
{
var sdate = document.xxx.d1.value;
var sss = eval("document.xxx.rb1");
for(var y=0; y<sss.length; y++)
{
if(sss[y].checked == true){
changab(y,sdate);
}
}

}

function confirmDel()
{
document.form15.user.value = document.f14.un.value;
document.form15.pass.value = document.f14.pw.value;
document.form15.host.value = document.f14.hn.value;
document.form15.dbname.value = document.f14.dbn.value;
var sss = document.form15.qtext.value;
if(sss.indexOf("delete") != -1   || sss.indexOf("alter") != -1)
{
var dd = confirm("You are trying to do a delete or modify operation, press ok to continue.");
if(dd == true)
return true;
else
return false;
}
}

function changedef()
{
document.f14.hn.value = document.f14.hh.value;
document.f14.un.value = document.f14.uu.value;
document.f14.pw.value = document.f14.pp.value;
document.f14.dbn.value = document.f14.dd.value;
}
</script>

<tr bgcolor=#fefefe height=100%><td width=100% align=center valign=top>
<br>
<table cellpadding=0 cellspacing=1 align=center style="background-color: #33778a;
padding: 3px; font-family: arial, verdana, san-serif;" border=0>
<tr><td bgcolor=#dddddd align=center>
<form name=f14 onsubmit="return false">
<input type=hidden name=def value=def onclick="changedef()"></input>
<input size=10 type=hidden name=hn value="<?php echo($hostname); ?>"></input>
<input size=10 type=hidden name=dbn value="<?php echo($dbname); ?>"></input>
<input size=10 type=hidden name=un value="<?php echo($username); ?>"></input>
<input size=10 type=hidden name=pw value="<?php echo($password); ?>"></input>
<input type=hidden name=uu value="<?php echo($dus); ?>"></input>
<input type=hidden name=pp value="<?php echo($dpa); ?>"></input>
<input type=hidden name=dd value="<?php echo($ddb); ?>"></input>
<input type=hidden name=hh value="<?php echo($dho); ?>"></input></form>

<form name=ssd style="font-size: 14px; padding: 0px; margin: 0px;">
<select name=dd onchange="changae()" size=2 style="width:410px;">
<option value="select * from ">select * from </option>
<option value="select count(*) from ">select count(*) from </option>
<option value="show tables">show tables</option>
<option value="show databases">show databases</option>
<option value="insert in to ">insert in to </option>
<option value="desc">desc</option>
</select><br>
</form>

<form name=xxx action="#" method="get" style="font-size: 14px; padding: 0px; border: 0px; margin: 0px;">
<?php
echo "<table align=center cellpadding=0 cellspacing=0 style=\"font-size: 13px;\" ><tr align=left><td>";
for($dfd=0; $dfd < count($query); $dfd++){

if($radioo == $dfd && $radioo != null)
echo "<input name=rb1 type=radio checked=true onMouseUp=\"changab($dfd,'today')\"> $name[$dfd] <br>";
else
echo "<input name=rb1 type=radio onMouseUp=\"changab($dfd,'today')\"> $name[$dfd] <br>";
if($dfd >= 1 && $dfd == ($half-1)){
echo "</td><td valign=top>";
}
}
echo "</td></tr></table>";
?>

Date: <input name="d1" id="f_date_a" readonly="1" type="text" onchange="setdate()">
<img src="img.gif" id="f_trigger_a" style="cursor: pointer; border: 1px solid red;" title="Date selector"
onmouseover="this.style.background='red';" onmouseout="this.style.background=''" />
</form>

<script type="text/javascript">
Calendar.setup({
inputField     :    "f_date_a",     // id of the input field
ifFormat       :    "%Y-%m-%d", // format of the input field
button         :    "f_trigger_a", // trigger for the calendar (button ID)
align          :    "Tl",    // alignment (defaults to "Bl")
singleClick    :    true
});
</script>

<div align=right style="font-size: 12px;"><a href="update.php">Manage Queries</a></div>
</td></tr>
<tr><td>
<br>
<form name="form15" method="post" action="index.php" onsubmit="return confirmDel()">
<input type="hidden" name="rb1">
<input type="hidden" name="user" value="<?php echo($username);?>">
<input type="hidden" name="pass" value="<?php echo($password);?>" >
<input type="hidden" name="host" value="<?php echo($hostname);?>" >
<input type="hidden" name="dbname" value="<?php echo($dbname);?>" >
<input type="hidden" name="type" value="query" >
<textarea name="qtext" value="<?php echo($qtext);?>" rows=4 cols=55><?php echo($qtext);?></textarea>
<input type="Submit" name="submit" value="Go">
</form>
</td></tr></table>
<br><br>

<?php
include "footer.php";
}
?>

PHP SCRIPTS 4U

The Blog of Enthusiastic PHP Scripters

Category Archives: Database Scripts

Twitter User Info Database Table SQL (Insert/Update)

The Twitter User Data Object

SQL to Create a New User Table

SQL to Insert New Record into User Table

SQL to Update Record in User Table

Twitter oAuth Database Table SQL (Insert/Update)

SQL to Create a New Auth Table

SQL to Insert New Record into User Auth Table

SQL to Update Record in User Auth Table

Database Table Structure for Store Twitter API User Data

PHP Scripts for Simple DailyPic / Picture of the Day

PHP Scripts for Free to Gallery Site

PHP converts data scripts

PHP Web Scripts

PHP link manager software Scripts

PHP upload files script

PHP database query tool scprit