Milan Smudja: MySQLi

Showing posts with label MySQLi. Show all posts

Wednesday, April 23, 2025

MySQLi Last Insert ID

last_insert_id.php


<?php
//Config

$server = "localhost";
$user = "root";
$password = "";
$database = "address_book";

//Establishing a Connection to MySQL Server
$connection = mysqli_connect($server, $user, $password, $database);

//Check Connection
if (!$connection) {
	die("<h2>Total Fail</h2> " . mysqli_connect_error());
} else {
	echo "Connection Successfull <br>";
}

//SQL Command
$sql_command = "INSERT INTO people (id, name, lastname, telephon, email, address)
VALUES (NULL, 'John', 'Smith', 555777, 'mailx@server.net', 'Main Road 12a')";

//Check SQL Command
if (mysqli_query($connection, $sql_command)) {
	$last_entry = mysqli_insert_id($connection);
	echo "SQL Command OK, Last ID: " . $last_entry . "<hr>" ;
} else {
	echo "SQL ERROR" . mysqli_error($connection);
}

?>

After we run ti multiple times:


Connection Successfull
SQL Command OK, Last ID: 11

MySQLi Finishing Pure PHP CMS

update_all.html


<form action="update_all_script.php" method="post">
<label>ID</label><br>
<input type="text" name="id">
<br><br>

<label>Name</label><br>
<input type="text" name="name">
<br><br>

<label>Last Name</label><br>
<input type="text" name="lastname">
<br><br>

<label>Telephon</label><br>
<input type="text" name="telephon">
<br><br>

<label>email</label><br>
<input type="text" name="email">
<br><br>

<label>Address</label><br>
<input type="text" name="address">
<br><br>

<input type="submit" value="Submit">
</form>

This is an HTML form that allows a user to input data for updating a record in a MySQL database table. The form has fields for ID, name, last name, telephone, email, and address. When the form is submitted, the data is sent to a PHP script for processing.

The PHP script will receive the data submitted by the form via the $_POST superglobal variable. It will then establish a connection to the MySQL server using the given credentials and database name. The script will then construct an SQL command to update the record in the table using the ID specified in the form. The SQL command will update all columns in the table for the specified ID.

If the SQL command executes successfully, the script will display a message indicating success. If an error occurs during the execution of the SQL command, the script will display an error message containing the error description returned by MySQL.

update_all_script.php


<?php

$id = $_POST["id"];
$name = $_POST["name"];
$lastname = $_POST["lastname"];
$telephon = $_POST["telephon"];
$email = $_POST["email"];
$address = $_POST["address"];

//Config

$server = "localhost";
$user = "root";
$password = "";
$database = "address_book";

//Establishing a Connection to MySQL Server
$connection = mysqli_connect($server, $user, $password, $database);

//Check Connection
if (!$connection) {
	die("<h2>Total Fail</h2> " . mysqli_connect_error());
} else {
	echo "Connection Successfull <br>";
}

//SQL Command
$sql_command = "UPDATE people SET 
name = '$name',
lastname = '$lastname',
telephon = '$telephon',
email = '$email',
address = '$address' WHERE id = '$id'";

if (mysqli_query($connection, $sql_command)) {
	echo "SQL Command OK";	
} else {
	echo "SQL Error " . mysqli_error($connection) ;
}

?>

This PHP script updates the information of a person in an address book stored in a MySQL database. It receives the new information through a form submitted with the POST method, and uses the values provided to update the fields of the corresponding person in the database.

The script connects to the MySQL server and checks the connection. If the connection is successful, it builds an SQL command to update the values of the person with the specified ID. If the SQL command is executed successfully, the script outputs "SQL Command OK". Otherwise, it outputs an error message with the reason for the failure.

When you run it with new data:


Connection Successfull
SQL Command OK

Always check entries with html_table_report.php

Navigation - general structure for php include or html hardcoding


<a href="html_table_report.php">Home - Report</a> |
<a href="html_form_to_table.html">New Post</a> |
<a href="delete_by_id.html">Delete by ID</a> |
<a href="update_all.html">Update by ID</a><br><br>

This is a navigation menu in HTML that includes links to different pages or actions related to an address book application. The links are:

Home - Report: this link goes to the "html_table_report.php" page, which likely displays a table with data from the address book.
New Post: this link goes to the "html_form_to_table.html" page, which likely displays a form to add a new entry to the address book.
Delete by ID: this link goes to the "delete_by_id.html" page, which likely displays a form to delete a specific entry from the address book by ID.
Update by ID: this link goes to the "update_all.html" page, which likely displays a form to update a specific entry from the address book by ID.

Report/Listing on every php or html page (remove part for connection if connection is already established) - it's same code as in html_table_report.php


<?php
//Config

$server = "localhost";
$user = "root";
$password = "";
$database = "address_book";

//Establishing a Connection to MySQL Server
$connection = mysqli_connect($server, $user, $password, $database);

//Check Connection
if (!$connection) {
	die("<h2>Total Fail</h2> " . mysqli_connect_error());
} else {
	echo "Connection Successfull <br>";
}

//SQL Command
$sql_command = "SELECT * FROM people";
$action = mysqli_query($connection, $sql_command);

echo "<h1>Results: </h1>";
echo "<table width='80%' cellspan='2' border='2'><tr bgcolor='yellow'>";
echo "<td>ID</td>";
echo "<td>Name</td>";
echo "<td>Lastname</td>";
echo "<td>Telephon</td>";
echo "<td>email</td>";
echo "<td>Address</td>";
echo "<td>Data/Time</td></tr>";

while ($line = mysqli_fetch_assoc($action)) {
	echo "<tr><td>" . $line["id"] . "</td>
	<td>" . $line["name"] . "</td>
	<td>" . $line["lastname"] . "</td>
	<td>" . $line["telephon"] . "</td>
	<td>" . $line["email"] . "</td>
	<td>" . $line["address"] . "</td>
	<td>" . $line["meta"] . "</td>";
	
}
echo "</table>";

?>

This PHP script is used to display the results of a MySQL query on a webpage in the form of an HTML table.

It first sets up the connection to a MySQL server using the given configuration parameters. It then constructs a SELECT query that fetches all rows from the "people" table in the specified database.

After executing the query, it prints an HTML table with headers for each column in the table. It then loops through each row returned by the query and prints out the values for each column in that row. Each row is represented as an HTML table row, and each column is represented as an HTML table data cell.

Finally, it closes the HTML table and outputs it to the webpage.

MySQLi Delete by ID

delete_by_id.html


<form action="delete_id.php" method="post">
<label>ID To Delete</label><br>
<input type="text" name="id_to_delete">
<br><br>

<input type="submit" value="Submit">
</form>

This form is used to delete a record from the database. It asks for the ID of the record to be deleted and has a submit button. When the form is submitted, it sends the data to a PHP script located at "delete_id.php" using the HTTP POST method.

delete_id.php


<?php
//Config

$server = "localhost";
$user = "root";
$password = "";
$database = "address_book";

$get_id_to_delete = $_POST["id_to_delete"];

//Establishing a Connection to MySQL Server
$connection = mysqli_connect($server, $user, $password, $database);

//Check Connection
if (!$connection) {
	die("<h2>Total Fail</h2> " . mysqli_connect_error());
} else {
	echo "Connection Successfull <br>";
}

//SQL Command
$sql_command = "DELETE FROM people WHERE id=$get_id_to_delete";

if (mysqli_query($connection, $sql_command)) {
	echo "SQL Command OK";	
} else {
	echo "SQL Error " . mysqli_error($connection) ;
}

?>

This PHP script connects to a MySQL server and receives a POST variable called "id_to_delete" from a form.

It then constructs a SQL command to delete a row from the "people" table where the ID matches the value of the POST variable. If the SQL command is successful, it displays the message "SQL Command OK".

If there is an error, it displays the error message using the mysqli_error() function. The script assumes that the database configuration values are correct and the connection to the database server is successful.

If you submit existing ID to delete:


Connection Successfull
SQL Command OK

Check html_table_report.php to see what's in database now.

MySQLi Admin Panel

You are strongly advised to check this YouTube tutorial to fully understand our cms and custom admin panel:

MySQLi Html Form to Table

html_form_to_table.html


<form action="data_insert.php" method="post">
<label>Name</label><br>
<input type="text" name="name">
<br><br>

<label>Last Name</label><br>
<input type="text" name="lastname">
<br><br>

<label>Telephon</label><br>
<input type="text" name="telephon">
<br><br>

<label>email</label><br>
<input type="text" name="email">
<br><br>

<label>Address</label><br>
<input type="text" name="address">
<br><br>

<input type="submit" value="Submit">
</form>

This is an HTML form that allows a user to input data to be inserted into a database table. The form uses the POST method to send the data to a script called "data_insert.php" for processing.

The form contains the following input fields:

Name: a text field for the user's first name
Last Name: a text field for the user's last name
Telephon: a text field for the user's telephone number
email: a text field for the user's email address
Address: a text field for the user's address

There is also a "Submit" button that the user can click to submit the form data.

data_insert.php


<?php
$name = $_POST["name"];
$lastname = $_POST["lastname"];
$telephon = $_POST["telephon"];
$email = $_POST["email"];
$address = $_POST["address"];
//Config

$server = "localhost";
$user = "root";
$password = "";
$database = "address_book";

//Establishing a Connection to MySQL Server
$connection = mysqli_connect($server, $user, $password, $database);

//Check Connection
if (!$connection) {
	die("<h2>Total Fail</h2> " . mysqli_connect_error());
} else {
	echo "Connection Successfull <br>";
}

//SQL Command
$sql_command = "INSERT INTO people (id, name, lastname, telephon, email, address)
VALUES (NULL, '$name', '$lastname', '$telephon', '$email', '$address')";

if (mysqli_query($connection, $sql_command)) {
	echo "SQL Command OK";	
} else {
	echo "SQL Error " . mysqli_error($connection) ;
}

?>

This PHP code establishes a connection to a MySQL server and inserts the values from the HTML form into the people table using an INSERT INTO SQL command.

Just make sure that the database connection details (server, user, password, database) are correct and that the people table exists in the database.

After we submit some data:


Connection Successfull
SQL Command OK

And when we check html_table_report.php:


Connection Successfull
Results:
ID	Name	Lastname	Telephon	email	Address	Data/Time
1	Slavoj	Zizek	222555	slavojzizek@someserver.com	Slavoj Zizek 123	2020-07-25 15:34:45
2	Jordan	Peterson	888454	jordan@server.com	Jordan P. 234	2020-07-25 15:35:43

MySQLi Truncate Table

delete_all.php


<?php
//Config

$server = "localhost";
$user = "root";
$password = "";
$database = "address_book";

//Establishing a Connection to MySQL Server
$connection = mysqli_connect($server, $user, $password, $database);

//Check Connection
if (!$connection) {
	die("<h2>Total Fail</h2> " . mysqli_connect_error());
} else {
	echo "Connection Successfull <br>";
}

//SQL Command
$sql_command = "TRUNCATE TABLE people";

if (mysqli_query($connection, $sql_command)) {
	echo "SQL Command OK";	
} else {
	echo "SQL Error " . mysqli_error($connection) ;
}

?>

This SQL command will delete all the data from the people table in the connected database.

The TRUNCATE TABLE command is used to delete all rows from a table while keeping its structure intact. So, in this case, the table structure (columns and data types) will still be there, but all the data will be deleted.

If the command is successful, it will output "SQL Command OK", otherwise it will output "SQL Error" along with the error message generated by MySQL.

When we run it, all rows are gone.


Connection Successfull
SQL Command OK

MySQLi Update Statement

update_statement.php


<?php
//Config

$server = "localhost";
$user = "root";
$password = "";
$database = "address_book";

//Establishing a Connection to MySQL Server
$connection = mysqli_connect($server, $user, $password, $database);

//Check Connection
if (!$connection) {
	die("<h2>Total Fail</h2> " . mysqli_connect_error());
} else {
	echo "Connection Successfull <br>";
}

//SQL Command
$sql_command = "UPDATE people SET telephon=888999 WHERE name='John'";

if (mysqli_query($connection, $sql_command)) {
	echo "SQL Command OK";	
} else {
	echo "SQL Error " . mysqli_error($connection) ;
}

?>

This SQL command updates the "telephon" field for the row(s) where the "name" field is equal to "John". Specifically, it sets the "telephon" field to 888999 for all rows where the "name" field is "John". If the query is successful, the message "SQL Command OK" is displayed. Otherwise, the error message returned by mysqli_error() is displayed.

When we run it:


Connection Successfull
SQL Command OK

MySQLi Delete From Table

delete_from_table.php


<?php
//Config

$server = "localhost";
$user = "root";
$password = "";
$database = "address_book";

//Establishing a Connection to MySQL Server
$connection = mysqli_connect($server, $user, $password, $database);

//Check Connection
if (!$connection) {
	die("<h2>Total Fail</h2> " . mysqli_connect_error());
} else {
	echo "Connection Successfull <br>";
}

//SQL Command
$sql_command = "DELETE FROM people WHERE id=3";

if (mysqli_query($connection, $sql_command)) {
	echo "SQL Command OK";	
} else {
	echo "SQL Error " . mysqli_error($connection) ;
}

?>

This SQL command will delete a row from the people table in the database where the id value is equal to 3. If the SQL command is executed successfully, the message "SQL Command OK" will be displayed. If there is an error, the message "SQL Error" along with the error message will be displayed.

After we run script once for that specific ID:


Connection Successfull
SQL Command OK

MySQLi Html Table Report

html_table_report.php


<?php
//Config

$server = "localhost";
$user = "root";
$password = "";
$database = "address_book";

//Establishing a Connection to MySQL Server
$connection = mysqli_connect($server, $user, $password, $database);

//Check Connection
if (!$connection) {
	die("<h2>Total Fail</h2> " . mysqli_connect_error());
} else {
	echo "Connection Successfull <br>";
}

//SQL Command
$sql_command = "SELECT * FROM people";
$action = mysqli_query($connection, $sql_command);

echo "<h1>Results: </h1>";
echo "<table width='80%' cellspan='2' border='2'><tr bgcolor='yellow'>";
echo "<td>ID</td>";
echo "<td>Name</td>";
echo "<td>Lastname</td>";
echo "<td>Telephon</td>";
echo "<td>email</td>";
echo "<td>Address</td>";
echo "<td>Data/Time</td></tr>";

while ($line = mysqli_fetch_assoc($action)) {
	echo "<tr><td>" . $line["id"] . "</td>
	<td>" . $line["name"] . "</td>
	<td>" . $line["lastname"] . "</td>
	<td>" . $line["telephon"] . "</td>
	<td>" . $line["email"] . "</td>
	<td>" . $line["address"] . "</td>
	<td>" . $line["meta"] . "</td>";
	
}
echo "</table>";

?>

This PHP code will retrieve all the data from the "people" table in the MySQL database and display it in an HTML table.

The code first runs a SQL SELECT query to retrieve all the data from the "people" table. Then, it uses a while loop and mysqli_fetch_assoc() function to retrieve each row of data one at a time. For each row, it echoes an HTML table row with the data from the columns, enclosed in table cells.

The table structure is defined at the beginning of the loop, and the table cells are populated with the values from the query results.

At the end of the loop, the PHP code closes the HTML table tag. The resulting output will be an HTML table showing all the data from the "people" table.

Nice Html Table Report:


Connection Successfull
Results:
ID	Name	Lastname	Telephon	email	Address	Data/Time
1	Samantha	Fox	555444	mail@server.com	Main Road 12a	2020-07-25 12:01:51
2	Samantha	Fox	555444	mail@server.com	Main Road 12a	2020-07-25 12:04:28
3	Samantha	Fox	555444	mail@server.com	Main Road 12a	2020-07-25 12:04:29

MySQLi Select All

select_all.php


<?php
//Config

$server = "localhost";
$user = "root";
$password = "";
$database = "address_book";

//Establishing a Connection to MySQL Server
$connection = mysqli_connect($server, $user, $password, $database);

//Check Connection
if (!$connection) {
	die("<h2>Total Fail</h2> " . mysqli_connect_error());
} else {
	echo "Connection Successfull <br>";
}

//SQL Command
$sql_command = "SELECT * FROM people";
$action = mysqli_query($connection, $sql_command);

while ($line = mysqli_fetch_assoc($action)) {
	echo "ID: " . $line["id"] . "<br>";
}

?>

The above PHP code executes an SQL SELECT statement to retrieve all data from the "people" table and prints the "id" field for each record retrieved using a while loop.

Here is an explanation of each line:

$sql_command = "SELECT * FROM people"; - This sets the SQL command to select all data from the "people" table.
$action = mysqli_query($connection, $sql_command); - This executes the SQL command on the database using the established connection and stores the result in the $action variable.
while ($line = mysqli_fetch_assoc($action)) { - This starts a while loop that runs as long as there are rows in the result set to be processed.
echo "ID: " . $line["id"] . "<br>"; - This prints the "id" field value of the current row in the result set to the screen, with some HTML formatting.
} - This ends the while loop.

Note that this code could be extended to print out more fields or to process the data in some other way.

Initial report with just IDs is ok:


Connection Successfull
ID: 1
ID: 2
ID: 3
ID: 4
ID: 5
...
ID: 24
ID: 25
ID: 26

select_all_2.php


<?php
//Config

$server = "localhost";
$user = "root";
$password = "";
$database = "address_book";

//Establishing a Connection to MySQL Server
$connection = mysqli_connect($server, $user, $password, $database);

//Check Connection
if (!$connection) {
	die("<h2>Total Fail</h2> " . mysqli_connect_error());
} else {
	echo "Connection Successfull <br>";
}

//SQL Command
$sql_command = "SELECT * FROM people";
$action = mysqli_query($connection, $sql_command);

while ($line = mysqli_fetch_assoc($action)) {
	echo "ID: " . $line["id"] . "<br>";
	echo "Name: " . $line["name"] . "<br>";
	echo "Lastname: " . $line["lastname"] . "<br>";
	echo "Telephone: " . $line["telephon"] . "<br>";
	echo "email: " . $line["email"] . "<br>";
	echo "Address: " . $line["address"] . "<br>";
	echo "Date/Time: " . $line["meta"] . "<br>";
	echo "<hr>";
}

?>

This PHP code performs the following tasks:

Define a SQL command to select all columns from the table "people".
Execute the SQL command using the mysqli_query() function, which returns a result set.
Loop through each row in the result set using the mysqli_fetch_assoc() function.
For each row, print out the values of each column in the row, using associative array keys to access the values.
Separate each row with a horizontal rule ("<hr>").

Small part of report for first ID:


Connection Successfull
ID: 1
Name: Samantha
Lastname: Fox
Telephone: 555444
email: mail@server.com
Address: Main Road 12a
Date/Time: 2020-07-25 12:01:51
...

select_all_3.php


<?php
//Config

$server = "localhost";
$user = "root";
$password = "";
$database = "address_book";

//Establishing a Connection to MySQL Server
$connection = mysqli_connect($server, $user, $password, $database);

//Check Connection
if (!$connection) {
	die("<h2>Total Fail</h2> " . mysqli_connect_error());
} else {
	echo "Connection Successfull <br>";
}

//SQL Command
$sql_command = "SELECT * FROM people";
$action = mysqli_query($connection, $sql_command);

while ($line = mysqli_fetch_assoc($action)) {
	echo "ID: " . $line["id"] . 
	"Name: " . $line["name"] . 
	"Lastname: " . $line["lastname"] . 
	"Telephone: " . $line["telephon"] . 
	"email: " . $line["email"] . 
	"Address: " . $line["address"] . 
	"Date/Time: " . $line["meta"]
	. "<hr>";
}

?>

Part of report:


Connection Successfull
ID: 1 Name: Samantha Lastname: Fox Telephone: 555444 email: mail@server.com Address: Main Road 12a Date/Time: 2020-07-25 12:01:51
ID: 2 Name: Samantha Lastname: Fox Telephone: 555444 email: mail@server.com Address: Main Road 12a Date/Time: 2020-07-25 12:04:28
ID: 3 Name: Samantha Lastname: Fox Telephone: 555444 email: mail@server.com Address: Main Road 12a Date/Time: 2020-07-25 12:04:29

MySQLi Multiple Insert

multiple_inserts.php


<?php
//Config

$server = "localhost";
$user = "root";
$password = "";
$database = "address_book";

//Establishing a Connection to MySQL Server
$connection = mysqli_connect($server, $user, $password, $database);

//Check Connection
if (!$connection) {
	die("<h2>Total Fail</h2> " . mysqli_connect_error());
} else {
	echo "Connection Successfull <br>";
}

//SQL Command
$sql_command = "INSERT INTO people (id, name, lastname, telephon, email, address)
VALUES (NULL, 'John', 'Smith', 555777, 'mailx@server.net', 'Main Road 12a');";

$sql_command .= "INSERT INTO people (id, name, lastname, telephon, email, address)
VALUES (NULL, 'Samantha', 'Smith', 111222, 'mailz@server.net', 'Main Road 12a');";

$sql_command .= "INSERT INTO people (id, name, lastname, telephon, email, address)
VALUES (NULL, 'Anabela', 'Smith', 555777, 'mailx@server.net', 'Main Road 12a')";

//Check SQL Command
if (mysqli_multi_query($connection, $sql_command)) {
	$last_entry = mysqli_insert_id($connection);
	echo "SQL Command OK, Last ID: " . $last_entry . "<hr>" ;
} else {
	echo "SQL ERROR" . mysqli_error($connection);
}

?>

This PHP code creates a SQL command that inserts multiple rows into the people table of the currently selected database.

The $sql_command variable is a string that concatenates multiple INSERT statements together.

Each INSERT statement specifies the columns and values to be inserted into the table. The id column is set to NULL, which will automatically generate a new value for this column using the AUTO_INCREMENT feature.

The mysqli_multi_query() function is used to execute the SQL command. If the command is successful, the mysqli_insert_id() function is used to retrieve the ID of the last inserted row.

The output will be either "SQL Command OK, Last ID: [ID]" if the command was successful or "SQL ERROR" if there was an error executing the command.

After we run it multiple times:


Connection Successfull
SQL Command OK, Last ID: 24

MySQLi Insert Into

insert_into_table.php


<?php
//Config

$server = "localhost";
$user = "root";
$password = "";
$database = "address_book";

//Establishing a Connection to MySQL Server
$connection = mysqli_connect($server, $user, $password, $database);

//Check Connection
if (!$connection) {
	die("<h2>Total Fail</h2> " . mysqli_connect_error());
} else {
	echo "Connection Successfull <br>";
}

//SQL Command
$sql_command = "INSERT INTO people (id, name, lastname, telephon, email, address)
VALUES (NULL, 'Samantha', 'Fox', 555444, 'mail@server.com', 'Main Road 12a')";

//Check SQL Command
if (mysqli_query($connection, $sql_command)) {
	echo "SQL Command OK";
} else {
	echo "SQL ERROR" . mysqli_error($connection);
}

?>

This PHP command will execute an SQL INSERT statement to insert a new row into the 'people' table of a MySQL database. The values for each column are provided in the VALUES clause:

id: Since it is set to NULL, the auto-increment feature of the 'id' column will automatically generate a new unique value for this row.
name: The value 'Samantha' will be inserted into the 'name' column.
lastname: The value 'Fox' will be inserted into the 'lastname' column.
telephon: The value 555444 will be inserted into the 'telephon' column.
email: The value 'mail@server.com' will be inserted into the 'email' column.
address: The value 'Main Road 12a' will be inserted into the 'address' column.

If the INSERT statement executes successfully, the code will output the message "SQL Command OK". Otherwise, if there is an error, the code will output "SQL ERROR" followed by the error message returned by MySQL.

After First Run, One Row is in Table. You can run it multiple times.


Connection Successfull
SQL Command OK

MySQLi Create Table

create_table.php


<?php
//Config

$server = "localhost";
$user = "root";
$password = "";
$database = "address_book";

//Establishing a Connection to MySQL Server
$connection = mysqli_connect($server, $user, $password, $database);

//Check Connection
if (!$connection) {
	die("<h2>Total Fail</h2> " . mysqli_connect_error());
} else {
	echo "Connection Successfull <br>";
}

//SQL Command
$sql_command = "CREATE TABLE people(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
telephon INT,
email VARCHAR(100),
address VARCHAR(100),
meta TIMESTAMP
)";

//Check SQL Command
if (mysqli_query($connection, $sql_command)) {
	echo "SQL Command OK";
} else {
	echo "SQL ERROR" . mysqli_error($connection);
}

?>

This PHP code creates a new table named "people" in the currently selected MySQL database using the "CREATE TABLE" SQL command. The "people" table has 7 columns: "id", "name", "lastname", "telephon", "email", "address", and "meta".

The "id" column is of type INT, NOT NULL, and serves as the primary key of the table. The "name" and "lastname" columns are of type VARCHAR(30) and are also NOT NULL. The "telephon" column is of type INT and can contain phone numbers. The "email" column is of type VARCHAR(100) and can contain email addresses. The "address" column is of type VARCHAR(100) and can contain physical addresses. The "meta" column is of type TIMESTAMP and will automatically update every time a row is added or modified.

The code first stores the SQL command in the variable $sql_command. It then uses the mysqli_query function to execute the command on the MySQL database. If the query is successful, the code outputs "SQL Command OK". If there is an error, the code outputs "SQL ERROR" followed by a message describing the error returned by mysqli_error($connection).

After First Run:


Connection Successfull
SQL Command OK

After Second Run:


Connection Successfull
SQL ERROR Table 'people' already exists

MySQLi Create Database

create_database.php


<?php
//Config

$server = "localhost";
$user = "root";
$password = "";

//Establishing a Connection to MySQL Server
$connection = mysqli_connect($server, $user, $password);

//Check Connection
if (!$connection) {
	die("<h2>Total Fail</h2> " . mysqli_connect_error());
} else {
	echo "Connection Successfull <br>";
}

//SQL Command
$sql_command = "CREATE DATABASE address_book";

//Check SQL Command
if (mysqli_query($connection, $sql_command)) {
	echo "DATABASE CREATED";
} else {
	echo "SQL ERROR" . mysqli_error($connection);
}

?>

This code is creating a new MySQL database named "address_book" using the mysqli_query() function. Here is what each line of the code does:

$sql_command = "CREATE DATABASE address_book"; - This line defines a SQL command that will create a new database named "address_book".
if (mysqli_query($connection, $sql_command)) { - This line checks if the SQL command was successfully executed using the mysqli_query() function. The $connection variable holds the connection object to the MySQL server that was established earlier.
echo "DATABASE CREATED"; - This line will be printed to the screen if the database creation is successful.
else { echo "SQL ERROR" . mysqli_error($connection); } - If the SQL command fails, this line will be executed and will print an error message that includes the error message returned by the mysqli_error() function.

After first run:


Connection Successfull
DATABASE CREATED

After second run:


Connection Successfull
SQL ERROR Can't create database 'address_book'; database exists

MySQLi Server Connection

server_connection.php


<?php
//Config

$server = "localhost";
$user = "root";
$password = "";

//Establishing a Connection to MySQL Server
$connection = mysqli_connect($server, $user, $password);

//Check Connection
if (!$connection) {
	echo "Check Config, or is Server Alive ?";
} else {
	echo "Connection Successfull";
}

?>

$server = "localhost";: This line defines a variable $server and sets its value to "localhost". This variable holds the hostname of the MySQL server.
$user = "root";: This line defines a variable $user and sets its value to "root". This variable holds the username to access the MySQL server.
$password = "";: This line defines a variable $password and sets its value to "". This variable holds the password to access the MySQL server.
$connection = mysqli_connect($server, $user, $password);: This line establishes a connection to the MySQL server using the mysqli_connect() function, which is provided by the MySQLi extension. It takes three parameters - $server, $user, and $password - which contain the hostname, username, and password to connect to the server. The function returns a connection object if the connection is successful, otherwise it returns false. The connection object is assigned to a variable $connection.
if (!$connection) {: This line starts an if statement that checks whether the connection was successful or not. The ! operator negates the value of $connection, so if $connection is false, the condition evaluates to true.
echo "Check Config, or is Server Alive ?";: This line prints an error message to the screen if the connection was not successful.
} else {: This line marks the beginning of the else block, which is executed if the connection was successful.
echo "Connection Successfull";: This line prints a success message to the screen if the connection was successful.
}: This line marks the end of the if-else block.

server_connection_with_dedicated_check.php


<?php
//Config

$server = "localhost";
$user = "root";
$password = "";

//Establishing a Connection to MySQL Server
$connection = mysqli_connect($server, $user, $password);

//Check Connection
if (!$connection) {
	die("<h2>Total Fail</h2> " . mysqli_connect_error());
} else {
	echo "Connection Successfull";
}

?>

if (!$connection) {: This line starts an if statement that checks if the connection was unsuccessful. The ! operator is used to check if the value of $connection is false.
die("<h2>Total Fail</h2> " . mysqli_connect_error());: This line prints an error message and stops the execution of the script if the connection was unsuccessful. The die() function is used to print the error message and exit the script. The mysqli_connect_error() function is used to retrieve the error message from the most recent MySQL connection error.
echo "Connection Successfull";: This line prints a success message if the connection was successful. The echo statement is used to output the message to the web page.

MySQLi Introduction

The MySQLi functions allows you to access MySQL database servers using PHP.

First, you must have MySQL server installed on your machine, or you can use remote MySQLserver, on some development/testing machine.

Also, you can install stand alone MySQL sever, or get "full package" with Easy PHP or similar solutions.

It's not hard, but you must be precise.

You are strongly advised to check corresponding Youtube video to understand installation, webserver dashboard and MySQL Server setup.

Thursday, May 1, 2025