Update Values of Entire Table

This code assumes you are connected to a MySQL database which has a table with Names and Emails. The idea is that it will output a table of every single value from that table, as text inputs. You can then alter the values of these inputs and re-submit, updating all the values in the database.

//get data from db
$sql = mysql_query("SELECT * FROM table");
$count=mysql_num_rows($sql);

//start a table
echo '<form name="form1" method="post" action="">
<table width="292" border="0" cellspacing="1" cellpadding="0">';

//start header of table
echo '<tr>
<th>&nbsp;</th>
<th>Name</th>
<th>Email</th>
</tr>';

//loop through all results
while($r=mysql_fetch_object($sql)){

//print out table contents and add id into an array and email into an array
echo '<tr>
<td><input type="hidden" name="id[]" value='.$r->id.' readonly></td>
<td>'.$r->name.'</td>
<td><input name="email[]" type="text" id="price" value="'.$r->email.'"></td>
</tr>';
}

//submit button
echo'<tr>
<td colspan="3" align="center"><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</form>';

// if form has been submitted, process it
if($_POST["Submit"])
{
       // get data from form
       $name = $_POST['name'];
       // loop through all array items
   foreach($_POST['id'] as $value)
       {
       // minus value by 1 since arrays start at 0
               $item = $value-1;
               //update table
       $sql1 = mysql_query("UPDATE table SET email='$email[$item]' WHERE id='$value'") or die(mysql_error());
   }

// redirect user
$_SESSION['success'] = 'Updated';
header("location:index.php");
}

Submitted values are not cleaned in this example, as it is assumed only an admin would have access to this type of powerful entry system.

Backup Database

Class to back up entire databases and email them out, or individual tables.

<?php
   class Backup
       {
               /**
                * @var stores the options
                */
               var $config;

               /**
                * @var stores the final sql dump
                */
               var $dump;

               /**
                * @var stores the table structure + inserts for every table
                */
               var $struktur = array();

               /**
                * @var zip file name
                */
               var $datei;

               /**
                * this function is the constructor and phrase the options
                * and connect to the database
                * @return
                */
               public function Backup($options)
               {
                       // write options
                       foreach($options AS $name => $value)
                       {
                               $this->config[$name] = $value;
                       }

                       // check mysql connection
                       mysql_connect($this->config['mysql'][0], $this->config['mysql'][1], $this->config['mysql'][2]) or die(mysql_error());
                       mysql_select_db($this->config['mysql'][3]) or die(mysql_error());
               }

               /**
                * this function start the backup progress its the core function
                * @return
                */
               public function backupDB()
               {
                       // start backup
                       if(isset($_POST['backup']))
                       {
                               // check if tables are selected
                               if(empty($_POST['table']))
                               {
                                       die("Please select a table.");
                               }

                               /** start backup **/
                               $tables = array();
                               $insert = array();
                               $sql_statement = '';

                               // lock tables
                               foreach($_POST['table'] AS $table)
                               {
                                        mysql_query("LOCK TABLE $table WRITE");

                                       // Read table structure
                                       $res = mysql_query('SHOW CREATE TABLE '.$table.'');
                                       $createtable = mysql_result($res, 0, 1);
                                       $str = "\n\n".$createtable."\n\n";

                                       array_push($tables, $str);

                                       // Read table "inserts"
                           $sql = 'SELECT * FROM '.$table;
                           $query = mysql_query($sql) or die(mysql_error());
                           $feld_anzahl = mysql_num_fields($query);

                                       $sql_statement = '--
-- Data Table `$table`
--

                                               ';

                                       // start reading progress
                           while($ds = mysql_fetch_object($query)){
                               $sql_statement .= 'INSERT INTO `'.$table.'` (';

                               for ($i = 0;$i <$feld_anzahl;$i++){
                                   if ($i ==$feld_anzahl-1){
                                       $sql_statement .= mysql_field_name($query,$i);
                                   } else {
                                       $sql_statement .= mysql_field_name($query,$i).', ';
                                   }
                               }

                               $sql_statement .= ') VALUES (';

                               for ($i = 0;$i <$feld_anzahl;$i++){
                                   $name = mysql_field_name($query,$i);
                                   if (empty($ds->$name)){
                                       $ds->$name = 'NULL';
                                   }
                                   if ($i ==$feld_anzahl-1){
                                       $sql_statement .= '"'.$ds->$name.'"';
                                   } else {
                                       $sql_statement .= '"'.$ds->$name.'", ';
                                   }
                               }
                               $sql_statement .= ");\n";
                           }

                                       // insert "Inserts" into an array if not exists
                                       if(!in_array($sql_statement, $insert))
                                       {
                                               array_push($insert, $sql_statement);
                                               unset($sql_statement);
                                       }

                                       unset($sql_statement);

                               }

                               // put table structure and inserts together in one var
                               $this->struktur = array_combine($tables, $insert);

                               // create full dump
                               $this->createDUMP($this->struktur);

                               // create zip file
                               $this->createZIP();

                               /** end backup **/

                               // send an email with the sql dump
                               if(isset($this->config['email']) && !empty($this->config['email']))
                               {
                                       $this->sendEmail();
                               }

                               // output
                               echo '<h3 style="color:green;">Backup war erfolgreich</h3><a href="'.$this->datei.'">Download Backup</a>
                               <br />
                               <br />';
                       }
               }

               /**
                * this function generate an email with attachment
                * @return
                */
               protected function sendEmail()
               {
                               // start sending emails
                               foreach($this->config['email'] AS $email)
                               {
                                       $to = $email;

                                       $from = $this->config['email'][0];

                                       $message_body = "This email contains the database backup as a zip file.";

                                       $msep = strtoupper (md5 (uniqid (time ())));

                                       // set email header (only text)
                                       $header =
                                                 "From: $from\r\n" .
                                                 "MIME-Version: 1.0\r\n" .
                                                 "Content-Type: multipart/mixed; boundary="$msep"\r\n\r\n" .
                                                 "--$msep\r\n" .
                                                 "Content-Type: text/plain\r\n" .
                                                 "Content-Transfer-Encoding: 8bit\r\n\r\n" .
                                                 $message_body . "\r\n";

                                       // file name
                                       $dateiname = $this->datei;

                                       // get filesize of zip file
                                       $dateigroesse = filesize ($dateiname);

                                       // open file to read
                                       $f = fopen ($dateiname, "r");
                                       // save content
                                       $attached_file = fread ($f, $dateigroesse);
                                       // close file
                                       fclose ($f);

                                       // create attachment
                                       $attachment = chunk_split (base64_encode ($attached_file));

                                       // set attachment header
                                       $header .=
                                                  "--" . $msep . "\r\n" .
                                                  "Content-Type: application/zip; name='Backup'\r\n" .
                                                  "Content-Transfer-Encoding: base64\r\n" .
                                                  "Content-Disposition: attachment; filename='Backup.zip'\r\n" .
                                                  "Content-Description: Mysql Datenbank Backup im Anhang\r\n\r\n" .
                                                  $attachment . "\r\n";

                                       // mark end of attachment
                                       $header .= "--$msep--";

                                       // eMail Subject
                                       $subject = "Database Backup";

                                       // send email to emails^^
                                       if(mail($to, $subject, '', $header) == FALSE)
                                       {
                                               die("The email could not be sent. Please check the email address.");
                                       }

                                       echo "<p><small>Email was successfully sent.</small></p>";
                               }
               }

               /**
                * this function create the zip file with the database dump and save it on the ftp server
                * @return
                */
               protected function createZIP()
               {

                       // Set permissions to 777
                       chmod($this->config['folder'], 0777);

                       // create zip file
                       $zip = new ZipArchive();
                       // Create file name
                       $this->datei = $this->config['folder'].$this->config['mysql'][3]."_".date("j_F_Y_g:i_a").".zip";

                       // Checking if file could be created
                       if ($zip->open($this->datei, ZIPARCHIVE::CREATE)!==TRUE) {
                               exit("cannot open <".$this->datei.">\n");
                       }

                       // add mysql dump to zip file
                       $zip->addFromString("dump.sql", $this->dump);
                       // close file
                       $zip->close();

                       // Check whether file has been created
                       if(!file_exists($this->datei))
                       {
                               die("The ZIP file could not be created.");
                       }

                       echo "<p><small>The zip was created.</small></p>";
               }

               /**
                * this function create the full sql dump
                * @param object $dump
                * @return
                */
               protected function createDUMP($dump)
               {
                       $date = date("F j, Y, g:i a");

                       $header = <<<HEADER
-- SQL Dump
--
-- Host: {$_SERVER['HTTP_HOST']}
-- Erstellungszeit: {$date}

--
-- Datenbank: `{$this->config['mysql'][3]}`
--

-- --------------------------------------------------------

HEADER;
                       foreach($dump AS $name => $value)
                       {
                               $sql .= $name.$value;
                       }
                       $this->dump = $header.$sql;
               }

               /**
                * this function displays the output form to select tables
                * @return
                */
               public function outputForm()
               {
                       // select all tables from database
                       $result = mysql_list_tables($this->config['mysql'][3]);

                       $buffer = '
                       <fieldset>
                               <legend>Select some tables</legend>
                               <form method="post" action="">
                       <select name="table[]" multiple="multiple" size="30">';
                       while($row = mysql_fetch_row($result))
                       {
                               $buffer .= '<option value="'.$row[0].'">'.$row[0].'</option>';
                       }
                       $buffer .= '</select>
                       <br /><br />
                       <input type="submit" name="backup" value="Backup Tables" />
                       </form>
                       </fieldset>';

                       echo $buffer;
               }
       }
?>

Usage:

<?php

       //You can add as many email addresses as you like
       $options = array('email' => array('email1', 'email2'),
                                  'folder' => './backup/',
                                  'mysql' => array('localhost', 'root', '****', 'database'));

       $b = new Backup($options);

       // if submit form start backup
       if(isset($_POST['backup']))
       {
               // start backup
               $b->backupDB();
       }

       // display tables
       $b->outputForm();

?>

 

Sanitize Database Inputs

 

1) Function for stripping out malicious bits

<?php
function cleanInput($input) {

  $search = array(
    '@<script[^>]*?>.*?</script>@si',   // Strip out javascript
    '@<[\/\!]*?[^<>]*?>@si',            // Strip out HTML tags
    '@<style[^>]*?>.*?</style>@siU',    // Strip style tags properly
    '@<![\s\S]*?--[ \t\n\r]*>@'         // Strip multi-line comments
  );

    $output = preg_replace($search, '', $input);
    return $output;
  }
?>

2) Sanitization function

Uses the function above, as well as adds slashes as to not screw up database functions.

function sanitize($var){
    if(is_array($var)){
        return array_map('sanitize',$var);
    }
    else{
        if(get_magic_quotes_gpc()){
            $var = stripslashes($var);
        }
        $var = mysql_real_escape_string($var);
        $var  = cleanInput($var);
        return $var;
    }
}

Usage

<?php
  $bad_string = "Hi! <script src='http://www.evilsite.com/bad_script.js'></script> It's a good day!";
  $good_string = sanitize($bad_string);
  // $good_string returns "Hi! It\'s a good day!"

  // Also use for getting POST/GET variables
  $_POST = sanitize($_POST);
  $_GET  = sanitize($_GET);
?>

What Programming Language Should I Learn?

As I do my professional and personal work, I am always looking for the best tool for the job. In software development, there are several programming languages that can be used for a wide variety of reasons. I am often asked by people new to software development what is the best language to learn. They get confused when I ask them what they plan on doing. The reason is that people think there is going to be a best language for everything. However, everyone knows that there is no silver bullet. On the other hand, there are some languages which are better suited or more widely used in specific areas. So, given that idea, I came up with a list.
Enterprise Software Development – Java is typically used in this space as people are moving many administrative applications to an intranet.
Windows Development – C# should be used for any Windows development, this includes anything interface with the Microsoft Office Suite. Don’t tell me about POI for Java, I have used it, but the native libraries kick POI’s ass.
Rapid web prototyping and anything WordPress – PHP is really good for rapid prototyping what a web site should act like. It may even qualify as v1.0 for your site. It may not be a good long term solution and there are better options for large-scale development. It is also the main language for anything related to WordPress.
Web Prototype with a backbone – Python has quickly gained acceptance as the “next step” after PHP. Many current web applications use Python extensively. Adoption will continue as more services natively support Python like Google’s AppEngine.
General Web Development – (X)HTML, CSS and Javascript must be in your toolbox for any significant web development. If you try to remain standards compliant (which you should) then you need to look at the XHTML standards.
Data Integration – XML and JSON are the main data interchange formats on the web and in corporate development. With XML, there are various syndication formats (likely the subject of another post) and other business format standards to review.
Databases – SQL is critical to almost any application. If you learn standard SQL, then you can translate this to almost any database product on the market especially the popular engines like Microsoft SQLServer, Oracle, DB2, MySQL.
Toolbox – Every programmer should be able to do more than just program in one language. In addition, there are many scripting tools that can be part of your toolbox which can make you extra productive. Cygwin is a Unix shell that you can install on Windows, and I can not live without it. Unix scripting is very powerful when dealing with batch processing of files or even just interacting with the file system. Perl, the Pathetically Eclectic Rubbish Lister, is another language that can be used for web development, but it really shines when dealing with file and text processing.
I know I have ignored various tools and languages, but this is really just a starting point. In software development, it is always helpful to keep learning new things and new concepts. If you really want to stretch your mind, start working in Artificial Intelligence and programming in LISP, or do some logic programming in Prolog. If you feel really adventurous take a look at Standard ML. I am not sure what it is really useful for, but it is a completely different language than most.

Speeding up MySQL Queries for faster performance.

Speeding up MySQL Queries for faster performance.

MySQL Queries Can Really Kill Your Web Server. Yes its true that writing buggy SQL queries can result in a lot of server cpu usage. Many webmasters face this problem and they think that their service provider is not good and blah blah.

if you are in one of them or your server is getting overloaded because of MySQL then i will suggest you to look at below tweaks that can help in reducing the cpu usage of a script.

1) Avoid unnecessary UPDATE queries.

2) An example of fast and slow SELECT sql query:
Faster Query: mysql_query("SELECT id,name,title from table_name");
Slower Query: mysql_query("SELECT * from table_name");

3) Avoid using functions with SQL queries.
Like: mysql_query("SELECT id from table_name where time=time()");
(note: the above query is just for explanation.)
Similarly there are a lot of ways to improve and reduce the server load by optimizing the SQL queries.

Speeding up MySQL Queries for faster performance using MySQL Cache

MySQL Queries Can Really Kill Your Web Server. Yes its true that writing buggy SQL queries can result in a lot of server cpu usage. Many webmasters face this problem and they think that their service provider is not good and blah blah.
Best Example is of wapsites using sea 8.1 Script. :P

NOTE: Using it might eat up your ram (Don't use it on shared servers or any server with low RAM).

if you are in one of them or your server is getting overloaded because of MySQL then i will suggest you to look at below method of caching can help in reducing the cpu usage of a script.

ok so below is the way on how to use SQL cache.

Suppose your have a query
SELECT name from users where name='%coder%;

so query with cache will be like this:
SELECT SQL_CACHE name from users where name='%coder%;


This Will stores the result in cache.
Using This Can reduce Your Memory Requirement and load Your Site Faster :D


Similarly there are a lot of ways to improve and reduce the server load by optimizing the SQL queries.