We're growing into something new. Read More
JeffreyWay

Jeffrey Way


Discussion

Cross-Browser CSS3 in Seconds

I created a new web service that automatically updates your stylesheet's CSS3 properties to use all of the necessary browser prefixes. Have a look!

Discussion

JavaScript Validation Thingy

This is a JavaScript validation script that I built this afternoon. Feedback/improvements/criticism welcomed! :)

<?php

class MysqlDB {

   protected $_mysql;
   protected $_where = array();
   protected $_query;
   protected $_paramTypeList;

   public function __construct($host, $username, $password, $db) {
      $this->_mysql = new mysqli($host, $username, $password, $db) or die('There was a problem connecting to the database');
   }

   /**
    *
    * @param string $query Contains a user-provided select query.
    * @param int $numRows The number of rows total to return.
    * @return array Contains the returned rows from the query.
    */
   public function query($query) 
   {
      $this->_query = filter_var($query, FILTER_SANITIZE_STRING);

      $stmt = $this->_prepareQuery();
      $stmt->execute();
      $results = $this->_dynamicBindResults($stmt);
      return $results;
   }

   /**
    * A convenient SELECT * function.
    *
    * @param string $tableName The name of the database table to work with.
    * @param int $numRows The number of rows total to return.
    * @return array Contains the returned rows from the select query.
    */
   public function get($tableName, $numRows = NULL) 
   {

      $this->_query = "SELECT * FROM $tableName";
      $stmt = $this->_buildQuery($numRows);
      $stmt->execute();

      $results = $this->_dynamicBindResults($stmt);
      return $results;
   }

   /**
    *
    * @param <string $tableName The name of the table.
    * @param array $insertData Data containing information for inserting into the DB.
    * @return boolean Boolean indicating whether the insert query was completed succesfully.
    */
   public function insert($tableName, $insertData) 
   {
      $this->_query = "INSERT into $tableName";
      $stmt = $this->_buildQuery(NULL, $insertData);
      $stmt->execute();

      if ($stmt->affected_rows)
         return true;
   }

   /**
    * Update query. Be sure to first call the "where" method.
    *
    * @param string $tableName The name of the database table to work with.
    * @param array $tableData Array of data to update the desired row.
    * @return boolean
    */
   public function update($tableName, $tableData) 
   {
      $this->_query = "UPDATE $tableName SET ";

      $stmt = $this->_buildQuery(NULL, $tableData);
      $stmt->execute();

      if ($stmt->affected_rows)
         return true;
   }

   /**
    * Delete query. Call the "where" method first.
    *
    * @param string $tableName The name of the database table to work with.
    * @return boolean Indicates success. 0 or 1.
    */
   public function delete($tableName) {
      $this->_query = "DELETE FROM $tableName";

      $stmt = $this->_buildQuery();
      $stmt->execute();

      if ($stmt->affected_rows)
         return true;
   }

   /**
    * This method allows you to specify a WHERE statement for SQL queries.
    *
    * @param string $whereProp A string for the name of the database field to update
    * @param mixed $whereValue The value for the field.
    */
   public function where($whereProp, $whereValue) 
   {
      $this->_where[$whereProp] = $whereValue;
   }

   /**
    * This method is needed for prepared statements. They require
    * the data type of the field to be bound with "i" s", etc.
    * This function takes the input, determines what type it is,
    * and then updates the param_type.
    *
    * @param mixed $item Input to determine the type.
    * @return string The joined parameter types.
    */
   protected function _determineType($item) 
   {
      switch (gettype($item)) {
         case 'string':
            return 's';
            break;

         case 'integer':
            return 'i';
            break;

         case 'blob':
            return 'b';
            break;

         case 'double':
            return 'd';
            break;
      }
   }

   /**
    * Abstraction method that will compile the WHERE statement,
    * any passed update data, and the desired rows.
    * It then builds the SQL query.
    *
    * @param int $numRows The number of rows total to return.
    * @param array $tableData Should contain an array of data for updating the database.
    * @return object Returns the $stmt object.
    */
   protected function _buildQuery($numRows = NULL, $tableData = false) 
   {
      $hasTableData = null;
      if (gettype($tableData) === 'array') {
         $hasTableData = true;
      }

      // Did the user call the "where" method?
      if (!empty($this->_where)) {
         $keys = array_keys($this->_where);
         $where_prop = $keys[0];
         $where_value = $this->_where[$where_prop];

         // if update data was passed, filter through
         // and create the SQL query, accordingly.
         if ($hasTableData) {
            $i = 1;
				$pos = strpos($this->_query, 'UPDATE');
				if ( $pos !== false) {
					foreach ($tableData as $prop => $value) {
						// determines what data type the item is, for binding purposes.
						$this->_paramTypeList .= $this->_determineType($value);

						// prepares the reset of the SQL query.
						if ($i === count($tableData)) {
							$this->_query .= $prop . " = ? WHERE " . $where_prop . "= " . $where_value;
						} else {
							$this->_query .= $prop . ' = ?, ';
						}

						$i++;
					}
				}
         } else {
            // no table data was passed. Might be SELECT statement.
            $this->_paramTypeList = $this->_determineType($where_value);
            $this->_query .= " WHERE " . $where_prop . "= ?";
         }
      }

      // Determine if is INSERT query
      if ($hasTableData) {
         $pos = strpos($this->_query, 'INSERT');

         if ($pos !== false) {
            //is insert statement
            $keys = array_keys($tableData);
            $values = array_values($tableData);
            $num = count($keys);

            // wrap values in quotes
            foreach ($values as $key => $val) {
               $values[$key] = "'{$val}'";
               $this->_paramTypeList .= $this->_determineType($val);
            }

            $this->_query .= '(' . implode($keys, ', ') . ')';
            $this->_query .= ' VALUES(';
            while ($num !== 0) {
               ($num !== 1) ? $this->_query .= '?, ' : $this->_query .= '?)';
               $num--;
            }
         }
      }

      // Did the user set a limit
      if (isset($numRows)) {
         $this->_query .= " LIMIT " . (int) $numRows;
      }

      // Prepare query
      $stmt = $this->_prepareQuery();

      // Bind parameters
      if ($hasTableData) {
         $args = array();
         $args[] = $this->_paramTypeList;
         foreach ($tableData as $prop => $val) {
            $args[] = &$tableData[$prop];
         }
         call_user_func_array(array($stmt, 'bind_param'), $args);
      } else {
         if ($this->_where)
            $stmt->bind_param($this->_paramTypeList, $where_value);
      }

      return $stmt;
   }

   /**
    * This helper method takes care of prepared statements' "bind_result method
    * , when the number of variables to pass is unknown.
    *
    * @param object $stmt Equal to the prepared statement object.
    * @return array The results of the SQL fetch.
    */
   protected function _dynamicBindResults($stmt) 
   {
      $parameters = array();
      $results = array();

      $meta = $stmt->result_metadata();

      while ($field = $meta->fetch_field()) {
         $parameters[] = &$row[$field->name];
      }

      call_user_func_array(array($stmt, 'bind_result'), $parameters);

      while ($stmt->fetch()) {
         $x = array();
         foreach ($row as $key => $val) {
            $x[$key] = $val;
         }
         $results[] = $x;
      }
      return $results;
   }


   /**
   * Method attempts to prepare the SQL query
   * and throws an error if there was a problem.
   */
   protected function _prepareQuery() 
   {
      if (!$stmt = $this->_mysql->prepare($this->_query)) {
         trigger_error("Problem preparing query", E_USER_ERROR);
      }
      return $stmt;
   }


   public function __destruct() 
   {
		$this->_mysql->close();
   }

}

Mysqli Database Class

This is a helpful database wrapper for working with MySQLi in your projects. To utilize this class, first create a new file, called MysqlDb.php, and paste the code above into it. Next, require the file in your project. require_once('MysqlDb.php'); Raw Code » After that, create a new instance of the class. $Db = new MysqlDb('host', 'username', 'password', 'databaseName'); Raw Code » Next, prepare your data, and call the necessary methods. Insert Query <?php $insertData = array( 'title' => 'Inserted title', 'body' => 'Inserted body' ); if ( $Db->insert('posts', $insertData) ) echo 'success!'; Raw Code » Select Query $results = $Db->get('tableName', 'numberOfRows-optional'); print_r($results); // contains array of returned rows Raw Code » Update Query $updateData = array( 'fieldOne' => 'fieldValue', 'fieldTwo' => 'fieldValue' ); $Db->where('id', int); $results = $Db->update('tableName', $updateData); Raw Code » Delete Query $Db->where('id', integer); if ( $Db->delete('posts') ) echo 'successfully deleted'; Raw Code » Generic Query Method $results = $Db->query('SELECT * from posts'); print_r($results); // contains array of returned rows Raw Code » Where Method This method allows you to specify the parameters of the query. For now, it only accepts one key => value pair. $Db->where('id', int); $results = $Db->get('tableName'); print_r($results); // contains array of returned rows Raw Code »