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.

Tip: Click on images to make them larger. If you find anything wrong on this page or need further assistance please comment below or contact me. 🙂
include('config.php');

Make a config.php File *

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

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

 

Subscribe To My YouTube Channel:

Connect to a Database *

Make sure to include config.php file.

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

Query results from a Database *

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

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

or

$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
}

Close a Database Connection *

&nbsp;mysqli_close($db);

Check to see if something is in the Database *

 ///check to see if in database
if( $result && $result->num_rows ){
echo "Result";
} else {
echo "something else";
}

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($db,"UPDATE table&nbsp;SET row1='$var'
WHERE username='$var' ");

Delete From Database *

mysqli_query($db,"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 *

people.php?name=Joe

$_GET["name"]

<?php

// 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 *

<?php session_start(); ?>

Set Session Variables *

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

Please like, share and subscribe.

Advanced Programming Made Easy

I hope you enjoyed the article. If I was able to help you please consider a tip for the content.

One Time Tip

more tips

Reoccuring Tips

Tips

Cool People Share: