SQL

SQL: Select by order

$query = "SELECT ".$paging_id[0].",".$paging_current[0]." FROM ".$sqlTblePaging." WHERE ".$paging_got_posts[0]." = 0 ORDER BY ".$paging_id;
echo "query: ". $query . "
";
$allPages = $sql->getAllRows($query);//NOTE: every second item in array $allPages is the page address, first is paging_id
echo "cunt";
foreach($allPages as $ap)
{

foreach($ap as $a)
{
echo $a."
";
}
}

SQL : PHP : Getting the value of Multiple Rows from SQL

The function returns the value of a number of SQL rows specified through a SQL query.

The following code is a function extracted from a PHP class that I used to connect to a SQL Database (DB).

It is one function in a project that the I developed to capture Facebook post data from Facebook Graph API JSON feed.

function getAllRows($query)
   {
      $rows;
      $count = 0;
      $result = mysqli_query($this->conn,$query);
      while($row = $result->fetch_row()) 
         {
             if(sizeof($row) < 1)
                {
                    //Adds the first element of an array only
                    $rows[$count] = $row[0];
                 }
              else
                 {
                    $rows[$count] = $row;
                 }
 
              $count+=1;
          }
       return $rows;
   }

The function arguments requires a SQL query to be the value of the $Query variable. For example:

SELECT * FROM Customers;
SELECT * FROM Customers
WHERE Country='Mexico';
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

Note: Refer to the www3schools tutorials on SQL commands for further details regarding these examples.

Note: The ‘$this->conn’ refers to another function that is called to a SQL DB that returns the open connection.

The PHP’s mysqli_query function to return a value stored in ‘$result’ variable. The PHP fetch_row() function returns an array of strings that corresponds to the fetched row or NULL if there are no more rows in result set.

The array ‘$rows’ is returned.

SQL : PHP : Getting the Maximum Column Value

This function returns the row with the greatest value from a  SQL Database (DB).

The following code is a function extracted from a PHP class that I used to connect to a SQL DB.

It is one function in a project that the I developed to capture Facebook post data from Facebook Graph API JSON feed.

function getMaxColumnValue($column,$table)
   {
      $queryMAX = "SELECT MAX(" . $column . ") AS maxnum FROM " . $table;
      $result = mysqli_query($this->conn,$queryMAX);
      $row = mysqli_fetch_assoc($result);
      return $row['maxnum'];
   }

The function arguments require the SQL DB’s column and table to be specified and is injected into a string variable called ‘$queryMAX’ value that form the SQL query.

The value of ‘$queryMAX’ is then injected into PHP’s mysqli_query function to return a value stored in ‘$result’ variable. This variable is then injected into PHP’s  mysqli_fetch_assoc() to return the values associated with that row data as an array. In this case the rows value associated with the SQL DB column called ‘maxnum’ is returned.

Note: The ‘$this->conn’ refers to another function that is called to a SQL DB that returns the open connection.

SQL MAX()

The SQL actual syntax for getting the maximum from a column is:

SELECT MAX(column_name) FROM table_name;

Further link references are MAX (Transact-SQL)

PHP : SQL : Create MySQL Table

This section of code is a sample of how to create a MySQL table programmically

<?php include("class_sql_utilities.php");?>
<?php
// Main SQL Variables
// SQL Server host name and port number
$sqlHost = 'localhost';//'127.0.0.1';
$sqlPort = 3306;

// Common password for used across the SQL Databases
$sqlPasswd = 'password';
$sqlUser = 'database_user_name';
$sqlDatabase = 'database_name';

$sqlTbleArtist = 'table_name' ;

$sql = new sql($sqlHost,$sqlPort,$sqlUser,$sqlPasswd);
$connect = $sql->connect($sqlDatabase);
echo $connect[1];

$artist_id =  array('artist_id','INT NOT NULL AUTO_INCREMENT PRIMARY KEY');//0
$artist_category = array('category','VARCHAR(48)');//1 
$artist_email = array('email','VARCHAR(48)');//2 
$artist_web = array('web','VARCHAR(128)');//3
$artist_box = array('box','VARCHAR(128)');//4
$artist_hidden = array('hidden','VARCHAR(128)');//5 

// This variable array is used to create the MySQL Paging Table
$artist_array = array(
	array($artist_id[0],$artist_id[1]),
	array($artist_category[0],$artist_category[1]),
	array($artist_email[0],$artist_email[1]),
	array($artist_web[0],$artist_web[1]),
	array($artist_box[0],$artist_box[1]),
	array($artist_hidden[0],$artist_hidden[1])
	);
	
// This variable array only holds the key for the paging
$artist_KeyArray = array(
		$artist_id[0],$artist_category[0],$artist_email[0],
		$artist_web[0],$artist_box[0],$artist_hidden[0]
		);

// Create tables if not exist------------------------------------------------------------------------------------------------------------
$tc = $sql->createTable($artist_array,Null,$sqlDatabase.'.'.$sqlTbleArtist);
echo $tc[0] . " was created<br>";
echo $tc[1] . " <br>";
// Create tables if not exist-------------------------------------------------------------
	

?>

PHP : SQL : Class : MySQL Utilities

This class is a collection of functions that are used for connecting and manipulating MySQL Databases.

This class’s php file name is class_sql_utilities.php and is to be included at the head of any php file that is calling the class for example:

<?php include("class_sql_utilities.php");?>
<?php
/**
 * A collection of functions that are used for connecting and manipulating MySQL Databases.
 *
 * A collection of functions that are used for connecting and manipulating MySQL Databases.
 *
 * @category   MySQL
 * @package    PackageName
 * @author     Anthony Church <aw_church@yahoo.com.au>
 * @author     NA
 * @copyright  2015 Anthony Church
 * @license    http://www.php.net/license/3_0.txt  PHP License 3.0
 * @version    1.0.0
 * @link       NA
 * @see        NA
 * @since      File available since Release 1.0.12
 * @deprecated NA
 */


class sql
{
	public $host,$port,$user,$password;
	protected $conn;
	
	function __construct($hst,$prt,$usr,$pssword)
	{
		$this->host = $hst;
		$this->port = $prt;
		$this->user = $usr;
		$this->password = $pssword;
	}
	function connect($database)
	{
		// Create connection
		$this->conn = mysqli_connect($this->host,$this->user,$this->password,$database);
		
		// Check connection
		$success;
		if (mysqli_connect_errno())
			{
				$success = array(False,"class_sql_utilities :: "."Failed to connect to MySQL: " . mysqli_connect_error() . "<br>");
			}
		 else
			{
				$success = array(True,"class_sql_utilities :: "."Connected " . "<br>");
			}
		return $success;
	}

	function query($query)
	{
		$result = mysqli_query($this->conn,$query);
		return $result;
	}
	function echoArray($a)
	{
		for ($n = 0; $n < sizeof($a); $n++)
		{
			echo "class_sql_utilities :: "."echoArray[".$n."]: ".$a[$n]."<br>";
		}
	}
	//EXAMPLE : SELECT MAX(ID) FROM Customers;
	function getMaxColumnValue($column,$table)
		{
			//SELECT MAX(paging_id) AS maxnum FROM winmalee_facebook.paging;
			$queryMAX = "SELECT MAX(" . $column . ") AS maxnum FROM " . $table;
			$result = mysqli_query($this->conn,$queryMAX);
			$row = mysqli_fetch_assoc($result);
			return $row['maxnum'];
		}
	function get_next_free_number($id)
		{
			if($id == Null)
				{
					$id = 1;
				}
			else
				{
					$id += 1;//get first free row
				}
			return $id;
		}
		
	function un_null_number($id,$value)
		{
			if($id == Null)
				{
					$id = $value;
				}
			return $id;
		}
	//This is reduntant and needs to be phased out
	function getQuery($query)
		{
			$result = mysqli_query($this->conn,$query);
			return $result;
		}
	//Legacy for  gettRow($result) support Needs to be phased out. Replaced by getFirstRow($result)
	function getRow($result)
		{
			$row = mysqli_fetch_assoc($result);
			return $row;
		}
	function getFirstRow($result)
		{
			$row = mysqli_fetch_assoc($result);
			return $row;
		}
	function getAllRows($query)
		{
			$rows;
			$count = 0;
			$result = mysqli_query($this->conn,$query);
			while($row = $result->fetch_row()) 
				{
					if(sizeof($row) < 1)
						{
							$rows[$count] = $row[0];
						}
					else
						{
							$rows[$count] = $row;
						}
					$count+=1;
				}
			return $rows;
		}
	function insertValues($table,$columnArray,$valueArray)
		{
			$column;
			for ($i = 0; $i < sizeof($columnArray); $i++) 
				{
					$column .= $columnArray[$i] . ',';
				}
			$column	= rtrim($column, ",");
			$value;
			for ($i = 0; $i < sizeof($valueArray); $i++) 
				{
					echo "valueArray[".$i."]: ".$valueArray[$i]."<br>";
					$value .= "'".$valueArray[$i]."'" . ',';
				}
			$value	= rtrim($value, ",");
			$query = "INSERT INTO ". $table ."(".$column.")VALUES (".$value.")";
			$result = $this->getQuery($query);
			return $result;
		}
	function insertValues_kv($table,$array,$print)
		{
			$str_key;
			$str_value;
			foreach ($array as $key => $value)
				{
				   if (is_array($value))
						{
							echo "ERROR :: insertValues_kv() :: ".$key." Value Is an Array<br>";
							break;
						}
					else
						{
							if($value != Null)
								{
									$str_key .= '`'.$key.'`' . ',';
									if( is_int ( $value ) )// or ($value==Null) )
										{
											$str_value .= $value . ',';
										}
									else
										{
											$str_value .= "'".$value."'" . ',';
										}
								}
						}
				}
			$str_key = substr($str_key, 0, -1); 
			$str_value = substr($str_value, 0, -1);
			$query = "INSERT INTO ". $table ."(".$str_key.")VALUES (".$str_value.")";
			$result = Null;
			if($print)
				{
					$result = $this->getQuery($query);
				}
			else
				{
					file_put_contents("_DELETE_PHP__quey.txt", $query);
				}
			return $result;
		}
		
	function updateValues_kv($table,$array,$where,$print)
		{
			$str_key;
			$str_value;
			$str_set = '';
			$str_where = '';
			foreach ($array as $key => $value)
				{
				   if (is_array($value))
						{
							echo "Is an Array<br>";
							break;
						}
					else
						{
							$str_key = '`'.$key.'`';
							if( is_int ( $value ) )// or ($value==Null) )
								{
									$str_value = $value . ',';
								}
							else
								{
									$str_value = "'".$value."'";
								}
						}
				   
				   if ($key == $where)
						{
							$str_value = substr($str_value, 0, -1); 
							$str_where = " WHERE ".$str_key." = ".$str_value;
						}
					else
						{
							
							$str_set = $str_set.$str_key." = ".$str_value . ',';
							echo "updateValues_kv :: "."str_set = ".$str_set."<br>";
						}
				}
			$str_set = substr($str_set, 0, -1); 
			$query = "UPDATE ". $table ." SET ".$str_set.$str_where;
			$result = Null;
			if($print)
				{
					$result = $this->getQuery($query);
				}
			else
				{
					file_put_contents("_DELETE_PHP__quey.txt", $query);
				}
			return $result;
		}
	/**
	* This method uses an Array of multiple values that are mapped to a constant set of fields
	* NOTE: $primKeyID is the primary key index which can be ignored by having it = -1
	**/
	function insertValuesMultiple($table,$primKeyID,$columnArray,$array)
		{
			$result;
			if($primKeyID == Null)
				{
					$primKeyID = 1;
				}
			//$i needs to equal $primKeyID-1 becasue SQL column id start at 1 where php arrays start at 0
			for ($i = $primKeyID-1; $i < sizeof($array); $i++) 
				{
					$valueArray = array();
					if($primKeyID != -1)
						{
							array_push($valueArray,$primKeyID);
							$primKeyID += 1;
						}
					for ($j = 0; $j < sizeof($array[$i]); $j++)
						{
							array_push($valueArray,$array[$i][$j]);//Add ID value to SQL row
						}
					if(sizeof($columnArray) == sizeof($valueArray))
						{
							$result = $this->insertValues($table,$columnArray,$valueArray);
						}
					else
						{
							echo "class_sql_utilities :: "."ERROR :: Size of columnArray != valueArray<br>";
						}
					
				}
			return $result;
		}
	function createTable($colValArray,$fkArray,$table)
		{
		$query = "CREATE TABLE IF NOT EXISTS ".$table."(";
		//Get Value and data types
		for ($i = 0; $i < sizeof($colValArray); $i++) 
			{
				$query .= $colValArray[$i][0]." ".$colValArray[$i][1].", ";
			}
		//Get the Foreign Key Values if they exist	
		
		if($fkArray != Null)
			{
				//Add Foriegn Keys
				$query_ConName = "CONSTRAINT ";
				$query_ConFK = " FOREIGN KEY (";
				$query_ConRef = ") REFERENCES ";
				$query_ConEnd = ") ON DELETE SET NULL ON UPDATE CASCADE,";
				foreach($fkArray as $p)
					{
						$query_ConName .= $p[0];//Name
						$query_ConFK .= $p[1];
						$query_ConRef .= $p[3]."(".$p[2];
						$query .= $query_ConName.$query_ConFK.$query_ConRef.$query_ConEnd;

						//Reset vars
						$query_ConName = "CONSTRAINT ";
						$query_ConFK = " FOREIGN KEY (";
						$query_ConRef = ") REFERENCES ";
						$query_ConEnd = ") ON DELETE SET NULL ON UPDATE CASCADE,";/**/
					}
			}
		$query = rtrim($query,", ");
		$query .= ")";
		$result = array($this->query($query),$query);
		return $result;
	}	
	function disconnect($database)
		{
			mysqli_close($this->conn);
		}

}
?>