Saturday 27 June 2015

PHP: error_log

(PHP 4, PHP 5)
error_log — Send an error message to the defined error handling routines

Description

bool error_log ( string $message [, int $message_type = 0 [, string $destination [, string $extra_headers ]]] )
Sends an error message to the web server's error log or to a file.

Parameters

message
The error message that should be logged.
message_type
Says where the error should go. The possible message types are as follows:
error_log() log types
0 message is sent to PHP's system logger, using the Operating System's system logging mechanism or a file, depending on what the error_log configuration directive is set to. This is the default option.
1 message is sent by email to the address in the destination parameter. This is the only message type where the fourth parameter, extra_headers is used.
2 No longer an option.
3 message is appended to the file destination. A newline is not automatically added to the end of the message string.
4 message is sent directly to the SAPI logging handler.
destination
The destination. Its meaning depends on the message_type parameter as described above.
extra_headers
The extra headers. It's used when the message_type parameter is set to 1. This message type uses the same internal function as mail() does.

Return Values

Returns TRUE on success or FALSE on failure.

Notes

Warning
error_log() is not binary safe. message will be truncated by null character.
Tip
message should not contain null character. Note that message may be sent to file, mail, syslog, etc. Use appropriate conversion/escape function, base64_encode(), rawurlencode() or addslashes() before calling error_log().

Email PHP errors instead of displaying it

By default, most servers are set to display an error message when an error occured in one of your script. For security reasons, you may want to get an email with the error, instead of displaying it to the public.

<?php

// Our custom error handler
function nettuts_error_handler($number, $message, $file, $line, $vars){
 $email = "
  <p>An error ($number) occurred on line
  <strong>$line</strong> and in the <strong>file: $file.</strong>
  <p> $message </p>";

 $email .= "<pre>" . print_r($vars, 1) . "</pre>";

 $headers = 'Content-type: text/html; charset=iso-8859-1' . "\r\n";

 // Email the error to someone...
 error_log($email, 1, 'you@youremail.com', $headers);

 // Make sure that you decide how to respond to errors (on the user's side)
 // Either echo an error message, or kill the entire project. Up to you...
 // The code below ensures that we only "die" if the error was more than
 // just a NOTICE.
 if ( ($number !== E_NOTICE) && ($number < 2048) ) {
  die("There was an error. Please try again later.");
 }
}

// We should use our custom function to handle errors.
set_error_handler('nettuts_error_handler');

// Trigger an error... (var doesn't exist)
echo $somevarthatdoesnotexist;

PHP: Generate CSV from mysql and email out 


<?php

//mysql user variables
$mysqli = new mysqli("localhost", "user", "password", "database");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

//grab all rows from the table where state is "new" and put into array 
$query = "SELECT Field1,Field2";

//loop the fetch and concactenate into string
if ($result = $mysqli->query($query)) {        

    $data[] = array('Field1','Field2');
    while ($row = $result->fetch_assoc()) {
        $data[] = array($row['Field1'],$row['Field2']);
 }
    $result->free();
}

function create_csv_string($data2) {
 // Open temp file pointer
 if (!$fp = fopen('php://temp', 'w+')) return FALSE;
 
 // Loop data and write to file pointer
 foreach ($data2 as $line) fputcsv($fp, $line);
 
 // Place stream pointer at beginning
 rewind($fp);
 
 // Return the data
 return stream_get_contents($fp);
}

function send_csv_mail ($csvData, $body, $to = 'to email address',  $from = 'senders email',$subject = 'Test email with attachment' ) {
 // This will provide plenty adequate entropy
 $multipartSep = '-----'.md5(time()).'-----';
 
 // Arrays are much more readable
 $headers = array("From: $from","Reply-To: $from","Content-Type: multipart/mixed; boundary=\"$multipartSep\"");
 
 // Make the attachment
 $attachment = chunk_split(base64_encode(create_csv_string($csvData))); 
 
 // Make the body of the message
 $body = "--$multipartSep\r\n"
        . "Content-Type: text/plain; charset=ISO-8859-1; format=flowed\r\n"
        . "Content-Transfer-Encoding: 7bit\r\n"
        . "\r\n"
        . "$body\r\n"
        . "--$multipartSep\r\n"
        . "Content-Type: text/csv\r\n"
        . "Content-Transfer-Encoding: base64\r\n"
        . "Content-Disposition: attachment; filename=\"AFILE.csv\"\r\n"
        . "\r\n"
        . "$attachment\r\n"
        . "--$multipartSep--";
 // Send the email, return the result
 return @mail($to, $subject, $body, implode("\r\n", $headers)); 
}

echo "emaling now <br>";
$resp=send_csv_mail($data, "Here is Today's Report:");
if( $resp ){
 echo "Mail sent <br>" ;
} else {
 echo "Mail not sent <br>";
}

echo "fin <br>";
?>

PHP: Converts enum fields from mysql into an array

enumExplode.php

<?php
function enumExplode($tableCol) {
    $expl = explode(".", $tableCol);
    $table = $expl[0];
    $column = $expl[1];
    $query = "show columns from {$table} where field = :column";
    $res = querydb($query, array("column" => $column), $error);
    $enum = $res[0]['Type'];
    $enum = explode(",", str_replace(array("enum(", ")", "'"), "", $enum));
    return $error ?: $enum;
}
/**
 *  queryDB
 *  -------
 *  @param string  sql query
 *  @param array   values for query
 *  @param array   errors returned
 *
 */
function queryDB($query, $values = array(), &$errors = null) {
    $pdo = new PDO(DBINFO, DBUSER, DBPASS);
    $stmt = $pdo->prepare($query);
    $stmt->execute($values);
    $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $errorInfo = $stmt->errorInfo();
    $errors = empty($errorInfo[2]) ? false : $stmt->errorInfo();
    $stmt->closeCursor();
    return $res;
}
?>

gistfile1.php
<?php
querydb("select * where id = :id and name = :name", array("id" => $id, "name" => $name), $error);
$query = "select * where id = :id and name = :name";
$values = array("id" => $id,"name" => $name);
$results = querydb($query, $values, $err);

/*
$results = array(
  0 => array(
    "name" => name,
    "id" => id
    ),
  1 => array(
    "name" => name,
    "id" => id
    )
  );
*/
?>

Mysql: Mass Find and Replace MySQL 


<?php
/*

* MySQL Mass Find and Replace

*/


// Connect to your MySQL database.
$hostname = "localhost";
$username = "root";
$password = "password";
$database = "db_name";
mysql_connect($hostname, $username, $password);

// The find and replace strings.
$find = "what_i_need_to_find";
$replace = "what_i_need_to_replace_with";

// Test mode
$test_mode = false;

// Loop through all tables and columns
$loop = mysql_query("

    SELECT
        concat('UPDATE ',table_schema,'.',table_name, ' SET ',column_name, '=replace(',column_name,', ''{$find}'', ''{$replace}'');') AS s
    FROM
        information_schema.columns
    WHERE
        table_schema = '{$database}'")
or die ('Cannot loop through database fields: ' . mysql_error());

while ($query = mysql_fetch_assoc($loop))
{
    if ($test_mode)
        echo "{$query['s']}<br/>";
    else
       mysql_query($query['s']);
} 
?>

Mysql: Back up mysql databases

<?php

// dump mysql file
$myUser = 'root';
$myPass = 'root';
$myHost = 'localhost';
$myDbs  = array('db1', 'db2');
$destDir = './backup/';
$date = date('Ymd');
$files = array();

foreach ($myDbs as $v) {
 $file = $destDir.$v.'-'.$date.'.sql.gz';
 $files[] = $file;
 exec("/usr/bin/mysqldump -u $myUser -h $myHost -p$myPass --opt $v | /bin/gzip -9 > $file");
}
?>

Mysql: Transaction with php+mysql 


<?php
mysql_connect("localhost", "test_user", "test_password");
mysql_select_db("work");

//transaction begin
mysql_query("BEGIN");
$sorgu1 = mysql_query("UPDATE test SET mevcut_para = mevcut_para – 10000 WHERE hesap_no = '625021'");
$sorgu2 = mysql_query("UPDATE test1 SET mevcut_para = mevcut_para + 10000 WHERE hesap_no = '124500'");

if (!$sorgu1 or !$sorgu2 ){
 mysql_query("ROLLBACK");
}else{
 mysql_query("COMMIT");
}
?>

PHP : Session handler via mySQL 


<?php









class MySQLSessionHandler {









 private $_dblink;




 private $_sessionName;




 private $_sessionTable;









 CONST SESS_EXPIRE = 3600;









 public function __construct($host, $user, $pswd, $db, $sessionName, $sessionTable) {




  $this->_dblink = new mysqli($host, $user, $pswd, $db);




  $this->_sessionName = $sessionName;




  $this->_sessionTable = $sessionTable;









  session_set_save_handler(




   array($this, "session_open"),




   array($this, "session_close"),




   array($this, "session_read"),




   array($this, "session_write"),




   array($this, "session_destroy"),




   array($this, "session_gc")




  );









  session_start();




 }









 function session_open($session_path, $session_name) {




  $this->sessionName = $session_name;




  return true;




 }









 function session_close() {




  return 1;




 }









 function session_write($SID, $value) {




  $stmt = $this->_dblink->prepare("




   INSERT INTO {$this->_sessionTable}




   (sid, value) VALUES (?, ?) ON DUPLICATE KEY




   UPDATE value = ?, expiration = NULL




   ");




  $stmt->bind_param('sss', $SID, $value, $value);




  $stmt->execute();




  session_write_close();




 }









 function session_read($SID) {




  $stmt = $this->_dblink->prepare("




   SELECT value FROM {$this->_sessionTable}




   WHERE sid = ? AND UNIX_TIMESTAMP(expiration) + " .




   self::SESS_EXPIRE . " > UNIX_TIMESTAMP(NOW())




   ");




  $stmt->bind_param('s', $SID);




  if ($stmt->execute()) {




   $stmt->bind_result($value);




   $stmt->fetch();




   if (!empty($value)) {




    return $value;




   }




  }




 }









 public function session_destroy($SID) {




  $stmt = $this->_dblink->prepare("




   DELETE FROM {$this->_sessionTable} WHERE SID = ?




   ");




  $stmt->bind_param('s', $SID);




  $stmt->execute();




 }









 public function session_gc() {




  $stmt = $this->_dblink->prepare("




   DELETE FROM {$this->_sessionTable} WHERE UNIX_TIMESTAMP(expiration) < " .




   UNIX_TIMESTAMP(NOW()) - self::SESS_EXPIRE);




  $stmt->execute();




 }









}









?>

PHP: Script to import json to mysql in php 


<?php 



$content = json_decode(file_get_contents("filename.json")), true);




mysql_connect('localhost', 'user', 'pass');



mysql_select_db('db'); 



foreach($content as $item) {




$columns = implode(", ",array_keys($item));




$escaped_values = array_map('mysql_real_escape_string', array_values($item));




$values  = "'".implode("', '", $escaped_values)."'";




$sql = "INSERT INTO `tbl_name`($columns) VALUES ($values)";




mysql_query($sql);




}




 




?>

PHP: Patch mysql injection 


<?php 



$patch_array = array($_GET['id'],$_GET['page']);




foreach ($patch_array as $key) 



{




 $patch_getter = $key;









 $patch_1 = ereg("-", $patch_getter);




 $patch_2 = ereg("order", $patch_getter);




 $patch_3 = ereg("_", $patch_getter);




 $patch_4 = ereg("concat", $patch_getter);




 $patch_5 = ereg("concat/*!()*/", $patch_getter);




 $patch_6 = ereg("/*!concat()*/", $patch_getter);




 $patch_7 = ereg("%20order%20", $patch_getter);




 $patch_8 = ereg("/*!00000uNiOn*/+/*!00000sElEcT*/", $patch_getter);




 $patch_9 = ereg("\*order\*", $patch_getter);




 $patch_10 = ereg("\+order\+", $patch_getter);




 $patch_11 = ereg("%20union%20", $patch_getter);




 $patch_12 = ereg("union", $patch_getter);




 $patch_13 = ereg("\*union\*", $patch_getter);




 $patch_14 = ereg("\+union\+", $patch_getter);




 $patch_15 = ereg("\*", $patch_getter);




 $patch_16 = ereg("'", $patch_getter);




 $patch_17 = ereg("/*!uNiOn*/+/*!sElEcT*/", $patch_getter);









 if($patch_1||$patch_2||$patch_3||$patch_4||$patch_5||$patch_6||$patch_7||$patch_8||$patch_9||$patch_10||$patch_11||$patch_12||$patch_13||$patch_14||$patch_15||$patch_16||$patch_17)




 {




  exit();




 }









}









?>

PHP: Script to show all tables in a MySQL schema 


<?php








//open database connection




$mysqli = new mysqli(<host>,<username>,<password>,<schema>);




 




//Display error message




if ($mysqli->connect_error) {




die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);




}




 




$sql="SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = <schema name>";




$result=$mysqli->query($sql);




while ( $tables = $result->fetch_assoc())




{




echo "<br>".$tables['TABLE_NAME'];




}




// Free memory by clearing result




$result->free();




 




// close connection 




$mysqli->close(); 







?>

Mysql: Backup Your MySQL Database Using PHP

<?php 





// Backup Your MySQL Database Using PHP




// ------------------------------------




// Use : backup_tables('localhost','username','password','blog');



// ( - 5th param would be the tables, empty or * will get all the complete db. )














/* Backup the db OR just a table */




function backup_tables($host,$user,$pass,$name,$tables = '*') {




 




 $link = mysql_connect($host,$user,$pass);




 mysql_select_db($name,$link);




 




 // Get all of the tables




 if($tables == '*') {




  $tables = array();




  $result = mysql_query('SHOW TABLES');




  while($row = mysql_fetch_row($result)) {




   $tables[] = $row[0];




  }




 } else {




  $tables = is_array($tables) ? $tables : explode(',',$tables);




 }




 




 // Cycle through




 foreach($tables as $table) {




  $result = mysql_query('SELECT * FROM '.$table);




  $num_fields = mysql_num_fields($result);




  




  $return.= 'DROP TABLE '.$table.';';




  $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));




  $return.= "\n\n".$row2[1].";\n\n";




  




  for ($i = 0; $i < $num_fields; $i++) {




   while($row = mysql_fetch_row($result)) {




    $return.= 'INSERT INTO '.$table.' VALUES(';




    for($j=0; $j<$num_fields; $j++) {




     $row[$j] = addslashes($row[$j]);




     $row[$j] = ereg_replace("\n","\\n",$row[$j]);




     if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }




     if ($j<($num_fields-1)) { $return.= ','; }




    }




    $return.= ");\n";




   }




  }




  $return.="\n\n\n";




 }




 




 // Save file




 $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');




 fwrite($handle,$return);




 fclose($handle);




}









?>

Mysql: How convert mysql date in php, back and foward 




Dates in PHP and MySQL

I see a lot of people on forums and on my training courses asking about the 



best way (or any way) to manage dates stored in a MySQL database and used in PHP. 



Three options follow, but first the problem.

 

PHP uses unix timestamps for all its date functionality. 



It has methods to convert these timestamps into pretty much any text format you 



could want but internally it uses the timestamp format. A timestamp is simply an integer. 



Specifically, it's the number of seconds that have elapsed since 



midnight on January 1st 1970 (greenwich mean time).




MySQL has three date types
 for use in columns. These are DATETIME, DATE, and TIMESTAMP. 

DATETIME columns store date and time in some internal format (I've not found what that is) 

for efficiency but always converts them to/from a string in the form YYYY-MM-DD HH:MM:SS 

(e.g. 2006-12-25 13:43:15) when accessing them. DATE columns use just the date part of 

this format - YYYY-MM-DD (e.g. 2006-12-25). TIMESTAMP columns, despite their name, are 

nothing like the unix timestamps used in PHP. A TIMESTAMP column is simply a DATETIME column

 that automatically updates to the current time every time the contents of that record are 

altered. (That's a simplification but broadly true and the details are not important here).

 In particular, since version 4.1 of MySQL the TIMESTAMP format is exactly the same as the 

DATETIME format.





So the problem is how to work with these two very different date formats - 



the PHP timestamp integer and the MySQL DATETIME string. There are three common 



solutions...




    

  1. One common solution is to store the dates in DATETIME 
fields and use PHPs date() and strtotime() functions to 
convert between PHP timestamps and MySQL DATETIMEs. 
The methods would be used as follows -
    


$mysqldate = date( 'Y-m-d H:i:s', $phpdate );
$phpdate = strtotime( $mysqldate );
    2. 

  2. Our second option is to let MySQL do the work. MySQL has functions we can use to convert the data at the point where we access 
the database. UNIX_TIMESTAMP will convert from DATETIME to PHP timestamp and FROM_UNIXTIME
 will convert from PHP timestamp to DATETIME. The methods are used 
within the SQL query. So we insert and update dates using queries like 
this -
    


    $query = "UPDATE table SET
    datetimefield = FROM_UNIXTIME($phpdate)
    WHERE...";
$query = "SELECT UNIX_TIMESTAMP(datetimefield)
    FROM table WHERE...";
    

    3. 

  3. Our last option is simply to use the PHP timestamp format 
everywhere. Since a PHP timestamp is a signed integer, use an integer 
field in MySQL to store the timestamp in. This way there's no conversion
 and we can just move PHP timestamps into and out of the database 
without any issues at all.
    

Be aware, however, that by using an integer field to store your dates
 you lose a lot of functionality within MySQL because MySQL doesn't know
 that your dates are dates. You can still sort records on your date 
fields since php timestamps increase regularly over time, but if you 
want to use any of MySQL's date and time functions on the data then 
you'll need to use FROM_UNIXTIME to get a MySQL DATETIME for the 
function to work on.
    

However, if you're just using the database to store the date 
information and any manipulation of it will take place in PHP then 
there's no problems.
    4. 




So finally we come to the choice of which to use. For me, if you 
don't need to manipulate the dates within MySQL then there's no contest 
and the last option is the best. It's simple to use and is the most 
efficient in terms of storage space in the data table and speed of 
execution when reading and writing the data.





However, some queries will be more complicated because your date is 
not in a date field (e.g. select all users who's birthday is today) and 
you may lose out in the long run. If this is the case it may be better 
to use either option 1 or 2. Which of these you use depends on whether 
you'd rather place the work on MySQL or PHP. I tend to use option 2 but 
there's no right or wrong answer - take your pick.

importing csv file into mysql using php 


<?php









define("DB_HOST", "localhost");




define("DB_NAME", "db_name");




define("DB_USER", "root");




define("DB_PASSWORD", "");




define("CSV_FILE", "file.csv");









$db = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . "," . DB_USER . "," . DB_PASSWORD);









if (($handle = fopen(CSV_FILE, "r")) !== FALSE) {




    




    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {   // fgetcsv(file, buffer, delimiter)




        




        // query




        $sql = "INSERT INTO mytable (name,email) VALUES (:name,:email)";




        $q = $db->prepare($sql);




        $q->execute(array(':name'=>$data[0],




                            ':email'=>$data[1]));




    }




    fclose($handle);




} else {




    die("Can't open file " . CSV_FILE);




}

Check MySQL db connection in php 


<?php









/**




 * Checking the MySQL connection




 */









 // Host name




 $host = "localhost";




 // MySQL username




 $username = "databaseusername";




 // MySQL password




 $password = "password";




 // MySQL database name




 $databasename = "dbname";









 echo "Checking connection with {$host}<br/>";









 // ------------------------------




 // Connect to mysql with the host, username, password




 // ------------------------------




 $con = mysql_connect($host,$username,$password);









 if (!$con)




 {




    die('Could not connect: ' . mysql_error());




 }









 // ------------------------------




 // Select database connection




 // ------------------------------




 $db_selected = mysql_select_db($databasename, $con);









 if (!$db_selected) die ("Can\'t use test_db : " . mysql_error());









 echo "Success!<br/>";









 // ------------------------------




 // Close connection




 // ------------------------------




 mysql_close($con);









?>

Convert mysql UTC timestamp to Local timestamp PHP 


function convert_time($timestamp,$timezone)




{




    date_default_timezone_set("UTC");




    $time_object = new DateTime($timestamp, new DateTimeZone('UTC'));




    $time_object->setTimezone(new DateTimeZone($timezone));




    return $time_object->format('Y-m-d g:i A');




}

mysql dump query to json on php 


<?php




mysql_connect('localhost','user','password');




mysql_select_db('database');




$query = "";




$sth = mysql_query($query);




$rows = array();




while($r = mysql_fetch_assoc($sth)) {




    $rows[] = $r;




}




print json_encode($rows);




?>

Export MySQL table in CSV format using PHP 


<?php




$link = mysql_connect($mysql_host,$mysql_user,$mysql_pass) or die('Could not connect: '.mysql_error());




mysql_select_db($mysql_db,$link) or die('Could not select database: '.$mysql_db);









$query = "SELECT * FROM $tablename ORDER BY id";




$result = mysql_query($query) or die("Error executing query: ".mysql_error());




$row = mysql_fetch_assoc($result);




$line = "";




$comma = "";




foreach($row as $name => $value)




{




  $line .= $comma . '"' . str_replace('"', '""', $name) . '"';




  $comma = ";";




}




$line .= "\n";




$out = $line;









mysql_data_seek($result, 0);









while($row = mysql_fetch_assoc($result))




{




  $line = "";




  $comma = "";




  foreach($row as $value)




  {




    $line .= $comma . '"' . str_replace('"', '""', $value) . '"';




    $comma = ";";




  }




  $line .= "\n";




  $out.=$line;




}




header("Content-type: text/csv");




header("Content-Disposition: attachment; filename=listino.csv");




echo $out;




exit;




?>

Export mySQL databases via PHP 


<?php









//In the event that PHP / mySQL is set up funkily.




error_reporting(E_ALL & ~(E_STRICT|E_NOTICE|E_DEPRECATED));




ini_set('display_errors', '1');









//Fill these in.




$host     = '';




$username = '';




$password = '';




$database = '';














//Taken from http://davidwalsh.name/backup-mysql-database-php




backup_tables($host,$username,$password,$database);









function backup_tables($host,$user,$pass,$name,$tables = '*') {









  $link = mysql_connect($host,$user,$pass);




  mysql_select_db($name,$link);









  //get all of the tables




  if($tables == '*')




  {




    $tables = array();




    $result = mysql_query('SHOW TABLES');




    while($row = mysql_fetch_row($result)) {




      $tables[] = $row[0];




    }




  }




  else




  {




    $tables = is_array($tables) ? $tables : explode(',',$tables);




  }




  $return = "";









  //cycle through




  foreach($tables as $table)




  {




    $result = mysql_query('SELECT * FROM '.$table);




    $num_fields = mysql_num_fields($result);









    $return.= 'DROP TABLE '.$table.';';




    $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));




    $return.= "\n\n".$row2[1].";\n\n";









    for ($i = 0; $i < $num_fields; $i++)




    {




      while($row = mysql_fetch_row($result))




      {




        $return.= 'INSERT INTO '.$table.' VALUES(';




        for($j=0; $j<$num_fields; $j++)




        {




          $row[$j] = addslashes($row[$j]);




          $row[$j] = preg_replace("\n","\\n",$row[$j]);




          if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }




          if ($j<($num_fields-1)) { $return.= ','; }




        }




        $return.= ");\n";




      }




    }




    $return.="\n\n\n";




  }









  header('Content-disposition:attachment; filename="db_output-'.time().'.sql"');




  echo $return;




}




?>

Subscribe to: Posts (Atom)