PHP Snippets and Short Cut Codes for MySqli

PHP Snippets and Short Cut Codes for MySqli

Here is a list of PHP snippets and short cut codes for MySqli. I have also included a php mailer script. First create a database with PHP you will need the server name, mostly this is called localhost. You will need your username and password. This is more than likely your cPanel login credentials.

Create a Database with PHP

// Create connection
$db= new mysqli($servername, $username, $password);
// Check connection
if ($db->connect_error) {
die("Connection failed: " . $db->connect_error);
}

// Create database
$sql = "CREATE DATABASE myDB";
if ($db->query($sql) === TRUE) {
echo "Database created successfully";
} else {
echo "Error creating database: " . $db->error;
}

$db->close();

Grant privileges to a Database

// create user
$sql = "CREATE USER " . $dbName . "'_user'@'localhost' IDENTIFIED BY " . $privilege_passwd;
$result = mysqli_query($link, $sql);
if (!$result)
{
$error = 'Error in creating new user.';
error_log($error);
exit();
}
// grant privileges
$sql = "GRANT SELECT , INSERT , UPDATE ,
DELETE ON * . * TO 'generic_user'@'localhost' IDENTIFIED BY '" . $privilege_passwd . "' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0";
$result = mysqli_query($link, $sql);
if (!$result)
{
$error = 'Error in granting privileges to new user.';
error_log($error);
exit();
}
// grant additional
$sql = "GRANT ALL PRIVILEGES ON '" . $dbName . "' . * TO '" . $dbName . "_user'@'localhost'";
$result = mysqli_query($link, $sql);
if (!$result)
{
$error = 'Error in granting privileges to new user.';
error_log($error);
exit();
}

Next make a config.php file and make sure that you include it on all your pages where you want to access a database. Depending on the file path you would use something like.

include('config.php');

Make a config.php File

$username = "dbusername"; 
$password = "dbpass"; 
$host = "localhost"; 
$dbname = "dbname";

See more on how to create a config.php file here.

Create a new Table in a MySqli Database

<?php

include('../config.php');
// Create connection
$db= new mysqli($host, $username, $password, $dbname);

// sql to create table
$sql = "CREATE TABLE about(
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(250) NOT NULL,
reg_date TIMESTAMP
)";

if ($db->query($sql) === TRUE) {
echo "Table about created successfully";
} else {
echo "Error creating table: " . $db->error;
}

$db->close();
?>

Drop a table from a database

include('../config.php');
// Create connection
$db= new mysqli($host, $username, $password, $dbname);

//test

$db = mysql_connect($host, $username, $password);
if(! $db )
{
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br />';
$sql = "DROP TABLE about";
mysql_select_db( 'YOUR-DATABSE' );
$retval = mysql_query( $sql, $db );
if(! $retval )
{
die('Could not delete table: ' . mysql_error());
}
echo "Table deleted successfully\n";
mysql_close($db);

Connect to a Database

Make sure to include config.php file.

Only cool people share!

$con= new mysqli("$host", "$username", "$password", "$dbname");
if($con->connect_errno > 0){
die('Unable to connect to database [' . $con->connect_error . ']');
}

Query results from a Database

$result = $con->query("SELECT * FROM XXX") ;

while ($row = $result->fetch_assoc()) {
echo $row['XXX'];
}

or

[code]$stmt = $dbConnection->prepare(‘SELECT * FROM employees WHERE name = ?’);
$stmt->bind_param(‘s’, $name);

$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
// do something with $row
}[/code]

Close a Database Connection

[code]mysqli_close($db);[/code]

Check to see if something is in the Database

[code] ///check to see if in database
if( $result && $result->num_rows ){
echo “Result”;
} else {
echo “something else”;
}[/code]

Getting Totals From Database Table

SELECT type, SUM(price) FROM products GROUP BY type";
while($row = mysql_fetch_array($result)){
echo "Total ". $row['type']. " = $". $row['SUM(price)'];
echo "<br />";

Another Method for SUM using Prepare Statement

$db=mysqli_connect("$host","$username","$password","$dbname");

$stmt = $db->prepare("SELECT SUM(row) FROM table");
$stmt->execute();
$stmt->bind_result($var);
$stmt->fetch();

echo "$var";

Insert Into Database

mysqli_query($db,"INSERT INTO table(`row1`, `row2`, `row3`)
VALUES ('$var1','$var2','$var3')");

Update a Database

mysqli_query($con,"UPDATE table SET row1='$var'
WHERE username='$var' ");

Delete From Database

mysqli_query($con,"DELETE FROM table&nbsp;WHERE row='$var'");

PHP Mailer

$to = $email;
$subject = 'Service Schedule Confirmation';
$message = 'Hello';
$message .= ' message goes here ';
$headers = 'From: [email protected]' . "\r\n" .
'Reply-To: [email protected]' . "\r\n" .
'X-Mailer: PHP/' . phpversion();

mail($to, $subject, $message, $headers);

Query a string

test.php?string
$var=$_SERVER['QUERY_STRING'];

Another Query Method

//url string
people.php?name=Joe
<?php
$_GET["name"]
// The value of the variable name is found
echo "<h1>Hello " . $_GET["name"] . "</h1>";
?>

 Demo of Form and PHP processing

<html>
<head>
<title></title>

</head>
<body>
<?php

if(isset($_POST['submit'])){
$firstname = mysqli_real_escape_string($db, $_POST['firstname']);
$lastname = mysqli_real_escape_string($db, $_POST['lastname']);
$email = mysqli_real_escape_string($db, $_POST['email']);
$date = mysqli_real_escape_string($db, $_POST['date']);

echo $fistname;
echo $lastname;
echo $email;
echo $date;
include('config.php');
$db = new mysqli("$host", "$username", "$password", "$dbname");
if($db->connect_errno > 0){
die('Unable to connect to database [' . $db->connect_error . ']');
}
mysqli_query($db,"INSERT INTO myguests(`firstname`, `lastname`, `email`)
VALUES ('$fistname','$lastname','$email')");
}
?>
<form action="" method="post">
<input type="text" name="firstname" placeholder="first name"/><br/>
<input type="text" name="lastname" placeholder="last name"/><br/>
<input type="email" name="email" placeholder="email"/><br/>
<input type="date" name="date" /><br/>
<input type="submit" value="submit" name="submit" />
</form>
</body>
</html>

html special characters

<?php
$str = "This is some <b>bold</b> text.";
echo htmlspecialchars($str);
?>

PHP Upload Photo Script

<?php
if(isset($_POST['submit'])){
$name_array = $_FILES['photo']['name'];
$tmp_name_array = $_FILES['photo']['tmp_name'];
$type_array = $_FILES['photo']['type'];
$size_array = $_FILES['photo']['size'];
$error_array = $_FILES['photo']['error'];
$name_array = explode('.',$_FILES['photo']['name']);
$datee=date('M-d-Y');
$rand=rand();
$newfilename = $datee.$rand. '.' .end($name_array);
move_uploaded_file($tmp_name_array, 'uploads/'.$newfilename);
echo "<a href='http://djdoubleh.com/uploads/".$newfilename."' target='_blank'>Photo URL</a>";
echo "<form method='post' action='' data-ajax='false' enctype='multipart/form-data'>";
echo "<input type='file' name='photo' accept='image/*' capture='camera' /><br/><br/>";
echo "<input type='submit' name='submit' value='upload photo'/>";

}else{
echo "<form method='post' action='' data-ajax='false' enctype='multipart/form-data'>";
echo "<input type='file' name='photo' accept='image/*' capture='camera' /><br/><br/>";
echo "<input type='submit' name='submit' value='upload photo'/>";
};?>

get All Images in a folder

<?php

$directory = "uploads/";
$images = glob($directory . "*");

foreach($images as $image)
{
echo $image;
}

?>

Delete Images in A directory with PHP

<?php
if (array_key_exists('delete_file', $_POST)) {
$filename = $_POST['delete_file'];
if (file_exists($filename)) {
unlink($filename);
echo 'File '.$filename.' has been deleted';
} else {
echo 'Could not delete '.$filename.', file does not exist';
}
}

///test

$directory = "../uploads/";
$images = glob($directory . "*");
?>
<?php
foreach($images as $image)
{
echo "<li style='background: #aaa;'><img src='".$image."' width='100px''/></li>";
echo '<form method="post">';
echo '<input type="hidden" value="'.$image.'" name="delete_file" />';
echo '<input type="submit" value="Delete image" />';
echo '</form>';

};
?>

PHP Inner Join mysqli

<?php
// Connect to your database
$db_conx = mysqli_connect("localhost", "db_user", "db_password", "db_name");
if (!$db_conx) { die( mysqli_connect_error() ); }
// Query and build the display list
$list = "";
$sql = "SELECT people.username, people.country, options.youtube
FROM people INNER JOIN options
ON people.id = options.id
WHERE options.youtube != '' ORDER BY people.id ASC";
// the ON clause specifies that the "id" column from both tables must match
$query = mysqli_query($db_conx, $sql) or die( mysqli_error($db_conx) );
while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){
$list .= $row["username"]." from ".$row["country"].' has a Youtube Channel: ';
$list .= '<u>https://www.youtube.com/user/'.$row["youtube"].'</u> <hr>';
}
// Close the database connection
mysqli_close($db_conx);
echo $list;
?>

Update a Table in mysql or Search and Replace

UPDATE `table_name`
SET `field_name` = replace(same_field_name, 'unwanted_text', 'wanted_text')

Set Timezone in PHP

date_default_timezone_set('America/New_York');

PHP Session Start

Needs to go at the top of every page!

<?php session_start(); ?>

Set Session Variables

$_SESSION['favcolor'] = 'green';
$_SESSION['animal'] = 'cat';
$_SESSION['time'] = time();

PHP cURL Example of POST

Put this on that page to retrieve information.

<?php
$handle = curl_init();

$url = "https://biomonapp.com/test.php";

// Array with the fields names and values.
// The field names should match the field names in the form.

$postData = array(
  'firstName' => 'Lady',
  'lastName'  => 'Gaga',
  'submit'    => 'ok'
);

curl_setopt_array($handle,
  array(
     CURLOPT_URL => $url,
     // Enable the post response.
    CURLOPT_POST       => true,
    // The data to transfer with the response.
    CURLOPT_POSTFIELDS => $postData,
    CURLOPT_RETURNTRANSFER     => true,
  )
);

$data = curl_exec($handle);

curl_close($handle);

echo $data;
?>

Put this on the server to process the form.

<?php 
if(isset($_POST["submit"]))
{
  echo "Full name is " . $_POST["firstName"] .
     "  " . $_POST["lastName"];
  exit;
}
?>
 
<html>
<body>
 
<form method = "POST"  >
  <input  name="firstName"  type="text"> 
  <input  name="lastName"  type="text">
  <input  type="submit"  name="submit"  value="submit" >
</form>
</body>
</html>

Summary
PHP Snippets and Short Cut Codes for MySqli
Article Name
PHP Snippets and Short Cut Codes for MySqli
Description
Here is a list of PHP code snippets and short cut codes for MySqli. I have also included a php mailer script. First make a config.php file and make sure that you include it on all your pages where you want to access a database. Depending on the file path you would use something like.
Author