Category Archives: PHP and MYSQL

PHP Compare 2 dates DATETIME mysql

Just a PHP code snippet to Compare two dates DATETIME mysql.

date_default_timezone_set('Australia/Brisbane');
$now = date('Y-m-d H:i:s');
$then = $result['date']; //timestamp
debug($now);
debug($then);
$newnow = date('Y-m-d H:i:s', strtotime('-1 hour')); //1 hour ago
debug($newnow);

Convert old mysql to new mysqli in PHP

Leave a reply

Convert old mysql to new mysqli database connections in PHP code. The use of mysql_connect is old and highly discouraged for instead you should migrate your code to use msqi_connect. self::$db in my code relates to store the db connection on a PHP OOP object.

//OPEN DB OLD WAY
$con = mysqli_connect(self::$settings['DB_HOST'],self::$settings['DB_USER'], self::$settings['DB_PASSWORD']);
self::$db = mysqli_select_db(self::$settings['DB_DATABASE'], $con);

//OPEN DB NEW WAY - now takes in the database in connection
$mysqli = new mysqli(self::$settings['DB_HOST'],self::$settings['DB_USER'], self::$settings['DB_PASSWORD'], self::$settings['DB_DATABASE']);

//add some error checking
if ($mysqli->connect_error) {
die('Database Connect Error (' . $mysqli->connect_errno . ') '. $mysqli->connect_error);
}
self::$db = $mysqli;

//CLOSE DB OLD WAY
mysql_close(self::$db); //close db connection

//CLOSE DB NEW WAY
mysqli_close(self::$db); //close db connection
or
if (self::$db) self::$db->close(); //close db connection

PHP MYSQL Giving Me a Blank Screen

Leave a reply

I just saw that PHP MYSQL was giving me a blank screen so I wanted to find out what caused that and how I could solve it.

Normally a blank screen means there is something wrong with the script like it can’t find an include or you have forgot a ; at the end of a line.

Other things you could try are:

PHP Error Reporting.
Test your code often.
Try a color-coded editor.
Comment it out.

Some errors I saw could happen:

ini_set("error_log", "/php_error_log");
ini_set("log_errors", 1);

So what I am saying is that change your site path absolute rather than a document path. So if you are trying to include a file called “file.txt” that is located in a directory called “files” the path would look like this.

/var/www/html/files/file.txt

PHP Simple MySQL Search Function

Leave a reply

Code shown below is just a very simple way to search a MySQL database.

Example:

mysqlsearch('items', 'title tags', isset($GET['q'])?$GET['q']:'', Array('columns'=>'*', 'method'=>'OR', 'extrasql'=>'AND active = "true" ORDER BY id DESC'));

if (!function_exists('mysql_search')) {

function mysql_search($table, $columns, $query = '', $options = Array()) {

if (empty($query)) { return Array(); }

$sql_query = Array();

$options['columns'] = isset($options['columns'])?$options['columns']:'*';
$options['method'] = isset($options['method'])?$options['method']:'OR';
$options['extra_sql'] = isset($options['extra_sql'])?$options['extra_sql']:'';

$query = ereg_replace('[[:<:]](and|or|the)[[:>:]]', '', $query);
$query = ereg_replace(' +', ' ', trim(stripslashes($query)));

$pattern = '/([[:alpha:]:]+)([[:alpha:] ]+)[[:alpha:]]?+[ ]?/i';

$regs = Array();

preg_match_all($pattern, $query, $regs);

$query = $regs[0];

while (list($key, $value) = @each($query)) {

$column = $columns;
$keywords = urldecode($value);

if (strpos($value, ':')) {

$column = substr($value, 0, strpos($value, ':'));
$keywords = trim(substr($keywords, strpos($keywords, ':') + 1));
$keywords = ereg_replace('\'', '', $keywords);

} else { $keywords = ereg_replace(' +', '|', $keywords); }

$column_list = explode(' ', $column);

$sql = Array();

for ($i = 0; $i < count($column_list); $i++) { $sql[] = '' . $column_list[$i] . ' REGEXP "' . $keywords . '"'; }

$query[$key] = Array('orignal'=>$value, 'sql'=>implode(' ' . $options['method'] . ' ', $sql));

$sql_query = array_merge($sql_query, $sql);
$sql_query = implode(' ' . $options['method'] . ' ', $sql_query);

}

$results = mysql_fetch_results(mysql_query('SELECT ' . $options['columns'] . ' FROM ' . $table . ' WHERE ' . $sql_query . ' ' . $options['extra_sql']));

return $results;

}

}

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 Sanitize XSS and MySQLi

Leave a reply

There have been many that didn’t include htmlentities or htmlspecialchars, and were still vulnerable to persistent/reflected XSS. So, this is just a simple function to sanitize your input of any XSS and MySQLi.

function clean($a)
{
if(get_magic_quotes_gpc())
{
$a = stripslashes($a);
}
$a = mysql_real_escape_string($a);
$a = htmlentities($a);
return $a;
}

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

Insert Query Generator with Strings in MySQL

Leave a reply

Inserting query generator with strings in MySQL. First off, this automatically surrounds strings with ‘single quotes,’ something that would take a lot of time if done by hand.

This code takes your table name and an array with keys that correspond to the column names and generates the syntax for you.

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/MySQL Twitter Like Timestamp

Leave a reply

Today, Web pages are not just data wrapped in HTML, instead they have become more real with “real-time” interaction between people. Hence, the timestamps also got changed to “6 mins ago”, “3 hours ago” like formats. Following is the PHP function that can be used to convert the timestamp into Twitter like “X min ago” format.

/* Works out the time since the entry post, takes a an argument in unix time (seconds)
*/
static public function Timesince($original) {
// array of time period chunks
$chunks = array(
array(60 * 60 * 24 * 365 , 'year'),
array(60 * 60 * 24 * 30 , 'month'),
array(60 * 60 * 24 * 7, 'week'),
array(60 * 60 * 24 , 'day'),
array(60 * 60 , 'hour'),
array(60 , 'min'),
array(1 , 'sec'),
);

$today = time(); /* Current unix time */
$since = $today - $original;

// $j saves performing the count function each time around the loop
for ($i = 0, $j = count($chunks); $i < $j; $i++) {

$seconds = $chunks[$i][0];
$name = $chunks[$i][1];

// finding the biggest chunk (if the chunk fits, break)
if (($count = floor($since / $seconds)) != 0) {
break;
}
}

$print = ($count == 1) ? '1 '.$name : "$count {$name}s";

if ($i + 1 < $j) {
// now getting the second item
$seconds2 = $chunks[$i + 1][0];
$name2 = $chunks[$i + 1][1];

// add second item if its greater than 0
if (($count2 = floor(($since - ($seconds * $count)) / $seconds2)) != 0) {
$print .= ($count2 == 1) ? ', 1 '.$name2 : " $count2 {$name2}s";
}
}
return $print;
}

Input to this function will be timestamp in Unix seconds. If your database table has timestamp field than you can convert it into Unix timestamp by using

UNIX_TIMESTAMP ()

method in MySQL.

SELECT id, username, UNIX_TIMESTAMP(joined_data) from UserTable;

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 Stuck in Callback Loop

Leave a reply

Twitter oAuth Stuck in Callback Loop

This problem can sometimes be caused by disabling or refusing to accept cookies. If not, the page cannot access the

$_GET parameters

in the URL then it will keep loooping if you have a check to look for them to see what stage your at in the oAuth process.

$dbc = mysqli_connect

must precede all the

mysqli_real_escape_string calls

to make it work. This is because you need an active mysqli connection to use that function.

Check your code for:

mysqli_real_escape_string

You need to change database connection to mysqli:

http://www.php.net/manual/en/mysqli.construct.php

This function was causing the loop:

// Escape/handler any bad characters passed through request variables.
private static function _escape($values)
{
debug('calling '.__FUNCTION__.'()...');
if(!is_array($values))
{
/* Quote if not integer */
if ( !is_numeric($values) || $values{0} == '0' )
{
$values = stripslashes($values);
// $values = mysqli_real_escape_string(self::$db, $values);

}
}
return $values;
}

Options for escaping:

$values = stripslashes($values);
// $values = mysqli_real_escape_string(self::$db, $values);
// $values = mysql_real_escape_string(self::$db, $values);

//alternative to mysql_real_escape_string
$search = array("\x00", "\n", "\r", "\\", "'", "\"", "\x1a");
$replace = array("\\x00", "\\n", "\\r", "\\\\" ,"\'", "\\\"", "\\\x1a");
$values = str_replace($search, $replace, $value);

I successfully fixed the problem by converting my db to the new mysqli way: Convert old mysql to new mysqli database connections in PHP code.

Full working escape function using mysqli:

// Escape/handler any bad characters passed through request variables.
private static function _escape($values)
{
debug('calling '.__FUNCTION__.'()...');
if(!is_array($values))
{
/* Quote if not integer */
if ( !is_numeric($values) || $values{0} == '0' )
{
if (!self::$db) self::_dbconnect();
$values = stripslashes($values);
self::$db->set_charset("utf8"); //set default character set
$values = mysqli_real_escape_string(self::$db, $values);
debug($values);
}
}
return $values;
}

This can be used with get vars to process

$_GET variables

safely to protect from mysql database injection attacks.

// Processes request vars into local vars dynamically.
private static function _getvars()
{
debug('calling '.__FUNCTION__.'()...');
$vars = array();
//load in GET variables
foreach($_GET as $n => $v)
{
$vars['GET'][$n] = self::_escape($v);
}

//load in POST variables
foreach($_POST as $n => $v)
{
$vars['POST'][$n] = self::_escape($v);
}
return $vars;
}

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

Convert Old MySQL to New mySQLi Database Connections in PHP code

1 Reply

Convert old mySQL to new mySQLi database connections in PHP code

The use of mysql_connect is old and highly discouraged, instead you should migrate your code to use mysqli_connect.

self::$db

in my code relates to store the db connection on a PHP OOP object.

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 Script Timeout

Leave a reply

PHP Script Timeout

If you’ve been having trouble with your script, and you cannot seem to figure out why your scripts just stop randomly at random times especially while reading large file… Try the following:

PHP: set_time_limit

void set_time_limit ( int $seconds )

Where it limits the maximum execution time.

When called,

set_time_limit()

restarts the timeout counter from zero. In other words, if the timeout is the default 30 seconds, and 25 seconds into script execution a call such as

set_time_limit(20)

is made, the script will run for a total of 45 seconds before timing out.

Note: This function has no effect when PHP is running in safe mode. There is no workaround other than turning off safe mode or changing the time limit in the

php.ini

. See here.

If you’re using

PHP_CLI SAPI

and getting error “Maximum execution time of N seconds exceeded” where N is an integer value, try to call

set_time_limit(0)

every M seconds or every iteration.

For example:

<?php

require_once('db.php');

$stmt = $db->query($sql);

while ($row = $stmt->fetchRow()) {
set_time_limit(0);
// your code here
}

?>

Keep in mind though that for

CLI SAPI max_execution_time

is hardcoded to 0. So it seems to be changed by

ini_set

set_time_limit

but it isn’t, actually.

The only references I’ve found to this strange decision are deep in bugtracker and in

php.ini

(comments for ‘max_execution_time’ directive).

Or if you got something like: msg:

set_time_limit() [function.set-time-limit]

: Cannot set time limit in safe mode.

Try this:

<?php
if( !ini_get('safe_mode') ){
set_time_limit(25);
}
?>

Unfortunately though, a script which gets into an infinite loop can produce an alarming amount of output in only a few seconds. Try attempting to debug a script, and add this to the beginning of the script:

<?php
set_time_limit(2);
?>

But still, even two seconds of run time produced enough output to overload the memory available to the browser.

To work it perfectly, add this to the beginning of the script:

<?php
set_time_limit(2);

ob_start(); // buffer output

function shutdown () {
// print only first 2000 characters of output
$out = ob_get_clean();
print substr($out, 0, 2000);
}

register_shutdown_function('shutdown');
?>

A short routine which would limit the execution time, and also limit the amount of output returned.

PHP SCRIPTS 4U

The Blog of Enthusiastic PHP Scripters

Category Archives: PHP and MYSQL

PHP Compare 2 dates DATETIME mysql

Convert old mysql to new mysqli in PHP

PHP MYSQL Giving Me a Blank Screen

PHP Simple MySQL Search Function

PHP Sanitize XSS and MySQLi

Insert Query Generator with Strings in MySQL

PHP/MySQL Twitter Like Timestamp

Twitter oAuth Stuck in Callback Loop

Convert Old MySQL to New mySQLi Database Connections in PHP code

PHP Script Timeout