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);
		}

}
?>


Javascript : AJAX : Update html tag

This AJAX example changes the text enclosed with the DIV tag with the id “demo”.

http://www.anthonychurch.net/web/ajax/Test_AJAX_001.html

HTML code:


<!DOCTYPE html>
<html>
<body>

<h2>AJAX</h2>

<div id="demo"><h2>Let AJAX change this text</h2></div>

<button type="button" onclick="loadDoc('demo')">Change Content</button>

<script>
function loadDoc(id) {
 var xhttp = new XMLHttpRequest();
 xhttp.onreadystatechange=function() {
 if (xhttp.readyState == 4 && xhttp.status == 200) {
 document.getElementById(id).innerHTML = xhttp.responseText;
 }
 }
 xhttp.open("GET", "Test_demo_get.php", true);
 xhttp.send();
}
</script>

</body>
</html>

Code for Test_demo_get.php:

function returnText()
 {
 return 'Change this text';
 }
echo returnText();</pre>
<pre>

PHP : Find URL in text

This function converts any string of characters whose structure reflects that of an html hyper link to a html link.
NOTE: This function can not cater for a string that is encapsulated with HTML tag:-
e.g. http://google.com


$text = 'http://www.google.com is a great website. Visit http://google.com, and so is ftp://ftp.theweb.com, plus www.smart.com, /go.org and go.org.nz';

$reg_exUrl = "/(http|https|ftp|ftps|)+(\:|)+([\/\/]|)+[a-zA-Z0-9\-\.]+\.[a-zA-Z]{2,3}(\/\S*)?/";

$html_ahref_attrs = 'target="_blank"';
/**
This function converts any string of characters whose structure reflects that of an html hyper link to a html link.
NOTE: This function can not cater for a string that is encapsulated with HTML tag:-
e.g. <a href="http://www.google.com" >http://google.com</a>
**/
function convert_string_to_HTML_link($text, $regX, $html_attributes)
{
/**
Reg expression
**/
if (preg_match_all($regX, $text, $matches))
{
foreach($matches[0] as $key => $match)
{
$text = str_replace($match,'<a href='.$match.' '.$html_attributes.'">'.$match.'</a>',$text);
}
}
return $text;The
}
echo convert_string_to_HTML_link($text, $reg_exUrl, $html_ahref_attrs);

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)

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-&gt;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."
";
}
}

PHP : Reg Exp : Convert String to HTML link

The convert_string_to_HTML_link function converts a string of characters to a HTML hyper link.

It employs Regular Expressions to identify a pattern in a string of characters that is the same structure of as a HTML hyper link, calling the PHP preg_match_all function.

It loops through the first element of preg_match_all function matches array only, using a foreach construct, using a key value mapping.

The $text parameter is updated using PHP’s str_replace function where the string of characters that is the same structure of as a HTML hyper link is replaced by the same string, but encapsulated with a HTML a href tag, attributes added from the $html_attributes parameter.

The convert_string_to_HTML_link function returns $text parameter, with all the updates to the HTML hyper links. It uses the following Regular Expression to catch all possible string combinations that match a HTML hyper link:

/(http|https|ftp|ftps|)+(\:|)+([\/\/]|)+[a-zA-Z0-9\-\.]+\.[a-zA-Z]{2,3}(\/\S*)?/g

<a href="https://regex101.com/" target="_blank">https://regex101.com/</a>
https://regex101.com/ was used to construct and test the Reg Ex

(http|https|ftp|ftps|) = match one of these string structure including no characters, using the pipe “|” character as a OR operator

(\:|)  = match the colon symbol or no characters, using the pipe “|” character as a OR operator

([\/\/]|) = match one or two forward slash characters or no characters, using the pipe “|” character as a OR operator

[a-zA-Z0-9\-\.] = match any combination of alpha numeric characters and “-” plus period “.” symbols

\.[a-zA-Z]{2,3}(\/\S*) = match a period “.” symbol, followed by any combination of alpha numeric characters and subsequent period “.” symbol and  any combination of alpha numeric characters, which will also have either a forward slash symbol “/” or non-white-space character “S” .  The “*” character means Matches the previous element zero or more times

Note: the “g” flag at the end of the Regular Expression is not used in the PHP code as it is not required. It indicates that the regular expression should be tested against all possible matches in a string. The preg_match_all function fulfills this purpose.


$text = 'http://www.google.com is a great website. Visit http://google.com, and so is ftp://ftp.theweb.com, plus www.smart.com, /go.org and go.org.nz';
$reg_exUrl = "/(http|https|ftp|ftps|)+(\:|)+([\/\/]|)+[a-zA-Z0-9\-\.]+\.[a-zA-Z]{2,3}(\/\S*)?/";
$html_ahref_attrs = 'target="_blank"';

/**
This function converts any string of characters whose structure reflects that of an html hyper link to a html link.
NOTE: This function can not cater for a string that is encapsulated with HTML tag:-
e.g. <a href="http://www.google.com" >http://google.com</a>
**/
function convert_string_to_HTML_link($text, $regX, $html_attributes)
	{
		/**
		Reg expression
		**/
		if (preg_match_all($regX, $text, $matches))
			{
				foreach($matches[0] as $key => $match)
					{
						$text = str_replace($match,'<a href='.$match.' '.$html_attributes.'">'.$match.'</a>',$text);
					}
			}
		return $text;The
	}

echo convert_string_to_HTML_link($text, $reg_exUrl, $html_ahref_attrs);