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
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.
<?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).
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.
<?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.
This means that if a new row is inserted into the Stores table without specifying a value for ShipCorp, the default value 'Speedy Gonzales' will be used instead.
Our MySQL code inserts a new row into the "Stores" table with the following values for each column:
"s_id": NULL (since it is an auto-increment column, the database will generate a unique value for this column automatically)
"city": 'xxx'
"store_name": 'yyy'
"product": 'ppp'
"available": 5421
"price": 500
The statement is successful and affects 1 row, as confirmed by the message "Query OK, 1 row affected".
mysql> SELECT * FROM Stores WHERE s_id=10;
+------+------+------------+---------+-----------+-------+-----------------+
| s_id | city | store_name | product | available | price | ShipCorp |
+------+------+------------+---------+-----------+-------+-----------------+
| 10 | xxx | yyy | ppp | 5421 | 500 | Speedy Gonzales |
+------+------+------------+---------+-----------+-------+-----------------+
1 row in set (0.00 sec)
mysql>
Our select instruction code gets all columns from the "Stores" table where the value of the "s_id" column is equal to 10.
The result is a table with one row that matches the condition, showing the values of all columns in that row.
mysql> ALTER TABLE Stores ALTER ShipCorp SET DEFAULT 'Daffy Duck';
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
We can change the default value of the "ShipCorp" column in the "Stores" table to 'Daffy Duck'.
This means that if a new row is inserted into the "Stores" table without specifying a value for the "ShipCorp" column, it will be automatically set to 'Daffy Duck'.
Note that this change does not affect existing rows in the table - it only affects future inserts.
If you want to update the "ShipCorp" value for existing rows that do not have a value specified, you can use the UPDATE statement with a WHERE clause that checks for NULL values in the "ShipCorp" column.
mysql> DESCRIBE Stores;
+------------+-------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+------------+----------------+
| s_id | int | NO | PRI | NULL | auto_increment |
| city | varchar(30) | YES | | NULL | |
| store_name | varchar(30) | YES | | NULL | |
| product | varchar(50) | YES | | NULL | |
| available | int | YES | | NULL | |
| price | int | YES | | NULL | |
| ShipCorp | varchar(50) | YES | | Daffy Duck | |
+------------+-------------+------+-----+------------+----------------+
7 rows in set (0.00 sec)
mysql>
The ALTER TABLE statement is used to modify the structure of an existing table, such as adding or dropping columns, changing column data types or constraints, renaming the table, or changing the table engine.
Our ALTER statement is adding a new column named "Manager" to the "Stores" table in the MySQL database, with a data type of VARCHAR (variable character) and a maximum length of 50 characters.
The MySQL HAVING clause is used to filter the results of an SQL query that includes a GROUP BY clause.
It allows you to specify a condition that must be met by the groups that are created by the GROUP BY clause.
The HAVING clause is similar to the WHERE clause, but while the WHERE clause is used to filter individual rows, the HAVING clause filters groups of rows.
The HAVING clause is always used after the GROUP BY clause in a SELECT statement.
The GROUP BY clause in MySQL is used to group together rows in a table based on one or more columns - it is typically used in combination with aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX(), which are used to perform calculations on the grouped data.
mysql> SELECT * FROM Stores
-> WHERE price > 50
-> HAVING available > 50;
+------+--------+------------+----------+-----------+-------+
| s_id | city | store_name | product | available | price |
+------+--------+------------+----------+-----------+-------+
| 1 | London | London_1 | Gold PSU | 153 | 100 |
| 2 | London | London_2 | Gold PSU | 75 | 100 |
+------+--------+------------+----------+-----------+-------+
2 rows in set (0.02 sec)
mysql>
Our query selects all columns from the "Stores" table where the "price" column is greater than 50.
Then, the HAVING clause is used to further filter the results to only include records where the "available" column is greater than 50.
So, WHERE clause is used to filter individual rows based on a specific condition, and the HAVING clause is used to filter groups of rows based on a specific condition.
mysql> SELECT * FROM Stores
-> WHERE store_name LIKE "%on%"
-> HAVING price > 25;
+------+--------+------------+----------+-----------+-------+
| s_id | city | store_name | product | available | price |
+------+--------+------------+----------+-----------+-------+
| 1 | London | London_1 | Gold PSU | 153 | 100 |
| 2 | London | London_2 | Gold PSU | 75 | 100 |
+------+--------+------------+----------+-----------+-------+
2 rows in set (0.00 sec)
mysql>
The query selects all columns from the "Stores" table where the "store_name" column contains the string "on".
Then, the HAVING clause is used to further filter the results to only include records where the "price" column is greater than 25.
MySQL views are virtual tables that are created based on the result of a SELECT query.
In other words, a view is a stored query that you can treat as a table. When you create a view, you define the SELECT statement that will be used to populate the view.
The main purpose of views is to simplify the complexity of a database by allowing users to access only the information they need, without giving them direct access to the underlying tables.
Views can also be used to combine data from multiple tables or to restrict access to sensitive data.
Once a view is created, you can query it just like any other table, using SELECT statements
MySQL views can be created using the CREATE VIEW statement, and can be modified or dropped using the ALTER VIEW and DROP VIEW statements, respectively.
mysql> CREATE VIEW Prices_Higher_Than_100 AS
-> SELECT store_name, product, available, price
-> FROM Stores
-> WHERE Price > 100;
Query OK, 0 rows affected (0.42 sec)
mysql>
The query creates a view named "Prices_Higher_Than_100" that selects data from a table named "Stores".
The view includes four columns: "store_name", "product", "available", and "price".
The view only includes records where the "price" column is greater than 100.
Once this view is created, it can be queried like any other table in the database.
Tables in our database, with our view:
mysql> SHOW TABLES FROM Corp;
+------------------------+
| Tables_in_corp |
+------------------------+
| documents |
| internalcontrol |
| prices_higher_than_100 |
| stores |
+------------------------+
4 rows in set (0.02 sec)
mysql>
In this case, there are four tables: "documents", "internalcontrol", "prices_higher_than_100", and "stores".
This command is useful to quickly get an overview of the tables in a database, and to ensure that a particular table or view exists before executing queries or commands that reference it.
When we run our view:
mysql> SELECT * FROM prices_higher_than_100;
+------------+-----------+-----------+-------+
| store_name | product | available | price |
+------------+-----------+-----------+-------+
| Berlin_1 | Green PSU | 50 | 120 |
+------------+-----------+-----------+-------+
1 row in set (0.00 sec)
mysql>
The query retrieves all records from the view, which only includes records where the "price" column is greater than 100.
The result set returned by the query includes one record that matches the criteria specified in the view's SELECT statement. The values in these columns correspond to the data in the underlying "Stores" table, but only for records where the "price" column is greater than 100.
Our query demonstrates how views can be used to simplify database access and retrieve only the data that meets specific criteria.
mysql> SELECT MID(product, 1, 3) AS First3Report
-> FROm Stores;
+--------------+
| First3Report |
+--------------+
| Gol |
| Gol |
| Gre |
| XYZ |
| Ext |
| LPT |
| COM |
| NIC |
+--------------+
8 rows in set (0.00 sec)
mysql>
Our code select the first three characters of the "product" column from the "Stores" table and gives it an alias "First3Report".
Not really useful, but why not:
mysql> SELECT MID(city, 2, 4) AS WeirdReport FROm Stores;
+-------------+
| WeirdReport |
+-------------+
| ondo |
| ondo |
| erli |
| erli |
| osco |
| osco |
| iami |
| aris |
+-------------+
8 rows in set (0.00 sec)
mysql>
In this case, the starting position is 2, so the function will skip the first character of each city name.
The length of the substring is 4, so the function will return the next four characters of each city name. The resulting column is given the alias "WeirdReport".
The output of this code shows the resulting substring of each city name for each row in the table.
mysql> SELECT UCASE(product) AS Products FROM Stores;
+-----------------+
| Products |
+-----------------+
| GOLD PSU |
| GOLD PSU |
| GREEN PSU |
| XYZ MOTHERBOARD |
| EXTENSION CABLE |
| LPT CABLES |
| COM CABLES |
| NIC |
+-----------------+
8 rows in set (0.02 sec)
mysql>
Our code executes a SELECT statement on the "Stores" table in MySQL. The query uses the UCASE() function to convert the values in the "product" column to uppercase, and renames the resulting column as "Products" using the AS keyword.
The resulting "Products" column contains the same values as the "product" column, but with all letters converted to uppercase.
LCASE usage:
mysql> SELECT LCASE(product) AS Products FROM Stores;
+-----------------+
| Products |
+-----------------+
| gold psu |
| gold psu |
| green psu |
| xyz motherboard |
| extension cable |
| lpt cables |
| com cables |
| nic |
+-----------------+
8 rows in set (0.00 sec)
mysql>
The LCASE() function is a MySQL string function that converts a string to lowercase.
Here, it is applied to the "product" column of the "Stores" table, which contains strings representing product names in uppercase, and returns their lowercase equivalents in the "Products" column of the query result.
The MySQL SUM() function is used to calculate the sum of values in a given column of a table.
It takes a single argument, which is the name of the column or expression that you want to sum.
Here's an example query that uses the SUM() function:
SELECT SUM(price) AS TotalPrice FROM Stores;
The resulting sum is assigned an alias "TotalPrice" using the AS keyword. The query returns a single row with a single column "TotalPrice" which contains the sum of all values in the "price" column of the "Stores" table.
mysql> SELECT SUM(available) AS InALLStores FROm Stores;
+-------------+
| InALLStores |
+-------------+
| 2633 |
+-------------+
1 row in set (0.00 sec)
mysql>
This MySQL query uses the SUM() function to calculate the total sum of the "available" column in the "Stores" table.
The result of the SUM() function is assigned an alias "InALLStores" using the AS keyword.
The value returned in this case is 2633, which means that the total sum of all values in the "available" column of the "Stores" table is 2633.
We can combine SUM and WHERE functions:
mysql> SELECT SUM(available) AS AllGoldPSUs FROM Stores WHERE product='GOLD PSU';
+-------------+
| AllGoldPSUs |
+-------------+
| 228 |
+-------------+
1 row in set (0.00 sec)
mysql>
Our query returns a single row with a single column "AllGoldPSUs" which contains the total sum of all values in the "available" column of the "Stores" table where the "product" column equals 'GOLD PSU'.
Combination of SUM, WHERE and LIKE functions:
mysql> SELECT SUM(available) AS AllPSUs FROM Stores WHERE product LIKE "%PSU%";
+---------+
| AllPSUs |
+---------+
| 278 |
+---------+
1 row in set (0.00 sec)
mysql>
Here we use the SUM() function with a WHERE clause to calculate the total sum of the "available" column in the "Stores" table where the "product" column contains the string 'PSU'.
The % symbol is a wildcard character used to match any sequence of characters, so the LIKE "%PSU%" condition matches any value in the "product" column that contains the string "PSU".
The result of the SUM() function is assigned an alias "AllPSUs" using the AS keyword.
In MySQL, the MAX and MIN functions are used to return the maximum and minimum values, respectively, from a column or expression in a table. Here's how these functions work:
MAX function: The MAX function returns the maximum value from a column or expression.
SELECT MAX(price) AS max_price FROM Stores;
Query will return a single row with a single column "max_price" that contains the highest value found in the "price" column of the "Stores" table.
MIN function: The MIN function returns the minimum value from a column or expression.
SELECT MIN(price) AS min_price FROM Stores;
This query will return a single row with a single column "min_price" that contains the lowest value found in the "price" column of the "Stores" table.
mysql> SELECT MAX(price) AS MostExpensive FROM Stores;
+---------------+
| MostExpensive |
+---------------+
| 120 |
+---------------+
1 row in set (0.00 sec)
mysql>
Our query selects the maximum value of the "price" column in the "Stores" table and assigns it an alias "MostExpensive". The MAX function is used to calculate the maximum value of the "price" column.
The result is a single row with a single column "MostExpensive" which contains the highest value found in the "price" column of the "Stores" table. The value returned in this case is 120, which means that the highest value found in the "price" column of the "Stores" table is 120.
MIN function example:
mysql> SELECT MIN(price) AS CheapestOne FROM Stores;
+-------------+
| CheapestOne |
+-------------+
| 5 |
+-------------+
1 row in set (0.00 sec)
mysql>
Here we selects the minimum value of the "price" column in the "Stores" table and assigns it an alias "CheapestOne". The MIN function is used to calculate the minimum value of the "price" column.
MAX and WHERE combination:
mysql> SELECT MAX(price) AS ExpensivePSU FROM Stores WHERE product='Gold PSU';
+--------------+
| ExpensivePSU |
+--------------+
| 100 |
+--------------+
1 row in set (0.00 sec)
mysql>
The result is a single row with a single column "ExpensivePSU" which contains the highest value found in the "price" column of the "Stores" table where the "product" column is "Gold PSU".
The value returned in this case is 100, which means that the highest value found in the "price" column of the "Stores" table where the "product" column is "Gold PSU" is 100.
Also, we can search for a substring:
mysql> SELECT MAX(price) AS MaxPriceCables FROM Stores WHERE product LIKE "%Cable%";
+----------------+
| MaxPriceCables |
+----------------+
| 25 |
+----------------+
1 row in set (0.00 sec)
mysql>
This MySQL query selects the maximum value of the "price" column in the "Stores" table where the "product" column contains the word "Cable".
