Php Mya Amin My SQL PHP Hypertext Preprocessor

Php. Mya. Amin & My. SQL PHP: Hypertext Preprocessor

Php. My. Admin

What is Php. My. Admin? Php. My. Admin is one of the most popular applications for My. SQL databases management. It is a free tool written in PHP. Through this software you can create, alter, drop, delete, import and export My. SQL database tables. You can run My. SQL queries, optimize, repair and check tables, change collation and execute other database management commands.

Php. My. Admin Features The main Php. My. Admin features are as follows: § User-friendly web interface; § Support for most My. SQL functions like browse, drop, create, copy and alter databases, tables, views, fields and indexes, execute My. SQL queries, manage stored procedures and functions; § Import data from CSV and SQL files; § Export data to various formats: CSV, SQL, XML, PDF, ISO/IEC 26300 - Open. Document Text and Spreadsheet, Word, Excel, LATEX and others; § Searching globally in a database or a subset of it; § And much more. The full list of the Php. My. Admin features can be found in the official web site.

How to install Php. My. Admin ? by Xampp Application the Php. My. Admin installation. Or you can install Php. My. Admin from website and add it into your server as Apache server.

Php. My. Admin administration In the following pages of our Php. My. Admin Tutorial we will describe the functionality of the software, integrated in your server. Once you enter your Php. My. Admin application, you will see different areas.

Php. My. Admin administration

Php. My. Admin administration In the upper part you will find the server hostname. The databases which you will manage are stored on the same server as the software and the hostname is: localhost. Under it there is information regarding the My. SQL server, the My. SQL client and the Php. My. Admin version. Next, you will see the My. SQL charset and you will be able to define the My. SQL connection collation. In the right column you can change the default language, alter the style, customize theme color and the font size. Also there you will notice links to Php. My. Admin resources.

Php. My. Admin administration The main Php. My. Admin areas are as follows: 1) Databases 2) Status 3) Variables 4) Processes 5) Charsets 6) Engines 7) Export 8) Import

Databases area In the Databases tab you will find a list with all the databases which can be managed through the localhost server. Once you click on a chosen database, you can start its management.

Status area You can open the Status tab by clicking on the Show My. SQL runtime information link. There you will find detailed information regarding the My. SQL server since the last restart. You will see the traffic handled by the My. SQL server, the maximum number of simultaneous connections, the total number of connections, the failed and the aborted attempts, the total number of queries sent to the server and more related details.

Status area

Variables area The Variables tab will be opened after clicking on the Show My. SQL system variables link. You will see a list with the My. SQL server system variables and their values.

Processes area By clicking on the Processes link you will see all the processes running by your localhost server.

Charsets area The Character Sets and Collations link leads to the Charset area. There you will find all the charsets and collations supported by the My. SQL server.

Engines area The Storage Engines link opens a list with all the engines supported by the My. SQL server. The default one is My. ISAM. Another popular storage engine, used by many databases is Inno. DB. More about the My. SQL storage engines can be found in the official documentation.

Export area In the Export section you can export your database tables content in different formats (CSV, SQL, PDF, Microsoft Excel, Microsoft Word, XML, and many more). You can select all the database tables or just pick some of them.

Export area You can add custom comments in the header of the exported content. You can decide whether to export just the database structure, the data or both of them. You can export the database tables in a file and compress it or you can visualize the queries directly on the screen.

Import area In the Import section you can import your database tables from a file, saved on your local computer. You should browse for the file and pick its character set from the drop-down menu. If the file is too big, the My. SQL server timeout can be reached. In such a case you can interrupt the import action. Then you can continue with the data import defining the number of the queries to be skipped from the file beginning. In this way you will skip the imported queries and continue from the point of the interruption. Additionally you can pick the SQL server mode of the imported file. You can find more details in the Server SQL Modesdocumentation.

Import area

Manage My. SQL database tables with Php. My. Admin ? The main functionality of the Php. My. Admin tool is to manage your databases. Click on the Databases link. Pick the preferred database which you want to manage and click on its name.

Manage My. SQL database tables with Php. My. Admin ? In the new screen you will see a list with the database tables, the allowed actions with them, the number of the records, the storage engine, the collation, the tables' sizes and the overhead.

Manage My. SQL database tables with Php. My. Admin ? The possible actions which you can perform to a chosen table are: 1. Browse 2. Structure 3. Search 4. Insert 5. Empty 6. Drop

Browse Database Tables Only the tables with existing records can be browsed. Once you click on the Browse icon a new window with the records list will be opened.

Browse Database Tables By clicking on the Pen icon you can edit the chosen record. You will see the record structure and you can alter the values of the records.

Structure Database Tables The next option is named Structure. In the Structure screen you will see the database's table structure.

Structure Database Tables You will see the fields' names, their types, collations, attributes, additional extra information, the default values and whether the fields' values can be NULL. You can browse for distinct values by clicking on the corresponding action icon. Also, you can edit a field's structure or delete a field. You can define different indexes: Primary, Unique, Index and Full text. More about the indexes can be found in the My. SQL Indexes documentation. In the Indexes area you will find the indexes assigned for the table and the fields for which they are set. You can edit and delete them. Additionally, in the same screen you can check the Space Usage and the Row Statistics.

Search Database Tables Through the Search action you can generate a search query for the chosen table.

Search Database Tables You can either write the WHERE clause or you can use the "query by example" functionality. You should click on the Gobutton to execute the query. For example, if you want to visualize all the records with a field value that starts with a you should select the fields which you want to show. Pick the LIKE operator from the drop-down menu and enter in the corresponding field value a% (% stands for a wildcard string). Click on the Go button to see the result.

Insert into Database Tables Using the Insert action you can insert records in your database table. Once you fill in the corresponding values click on the Go button and the new record will be inserted.

Empty Database Tables The Empty action allows you to empty your database table, removing the data and keeping the empty table.

Drop Database Tables Through the Drop action you can delete the whole table and all the records stored in it.

Create a My. SQL Database? Enter the database name in the text field and click on the Create button.

Add My. SQL Database Tables Navigate to your Php. My. Admin tool and open the newly create database. It is empty and there are no tables. Enter the table name and the number of fields. Click on the Go button to create the table.

Add My. SQL Database Tables On the next screen you should enter the fields' names and the corresponding properties. The properties are: Type Here you should pick the type of the data, which will be stored in the corresponding field. More details about the possible choices can be found in the official My. SQL Data Types documentation. Length/Values Here you should enter the length of the field. If the field type is "enum" or "set", enter the values using the following format: 'a', 'b', 'c'. . .

Add My. SQL Database Tables Collation Pick the data collation for each of the fields. Attributes The possible attributes' choices are: BINARY - the collation for the field will be binary, for example utf 8_bin; UNSIGNED - the field numeric values will be positive or 0; UNSIGNED ZEROFILL - the field numeric values will be positive or 0 and leading zeros will be added to a number; ON UPDATE CURRENT_TIMESTAMP - the value for a data type field has the current timestamp as its default value, and is automatically updated;

Add My. SQL Database Tables Null Here you define whether the field value can be NULL. More about the NULL value can be found in the corresponding My. SQL documentation. Default This property allows you to set the default value for the field. Extra In the Extra property you can define whether the field value is auto-increment. The radio buttons that come below define whethere is an Index defined for the particular field and specify the Indextype. Comments Here you can add comments, which will be included in the database sql code. At the end you can include Table comments and pick the My. SQL Storage Engine and the Collation. Once you are ready, click on the Save button.

Add My. SQL Database Tables

Add My. SQL Database Tables If you want to add more fields you should specify their number and click on the Go button instead of Save. The database table will be created and you will see the corresponding My. SQL query. Now we will proceed with the populating of the table with data.

Add Content in a Database Table In order to add records in a database table click on the Insert tab. Enter the data in the corresponding fields and click on the Go button to store it. At the bottom of the page you will see a drop-down menu labelled Restart insertion with x rows. There you can pick the number of the rows that you can populate with data and insert at once. By default the value is 2. The Ignore check box will allow you to ignore the data entered below it. It will not be added.

Add Content in a Database Table You can see the newly inserted record by clicking on the Browse tab. You can edit or delete the record by clicking on the corresponding icons. To insert more records, return to the Insert tab and repeat the procedure.

Backup a Database Once you are ready, you can create a backup of your database through the Export tab. Select the tables which you want to be exported. Leave the radio button selection to the SQL option. The Structure and the Data check boxes should remain checked. Select the Save as file check box and then click on the Go button. In this way you will save the dump SQL file with your database structure and content on your local computer.

Backup a Database

Restore a Database Backup You can restore your database backup from the Import tab. lick on the Browse button to select your database backup file from your local computer. Pick the charset of the file from the corresponding drop-down menu. If the file is too big, the My. SQL server timeout can be reached. In such a case you can interrupt the import action. Then you can continue with the data import defining the number of the queries to be skipped from the file beginning. In this way you will skip the imported queries and continue from the point of the interruption.

Restore a Database Backup

Run My. SQL Query The one for advanced users is through the SQL tab. There you should enter the entire SQL query code and click on the Gobutton to execute it.

Run My. SQL Query The other option is to construct a My. SQL query in the Query tab.

Run My. SQL Query There you can define different search conditions, sort the results and query multiple tables. You should select the tables used in the query from the Use Tables list. The fields which will be included in the SELECT My. SQL statement should be picked from the Field drop-down menus. The. Show check box should be selected. In the Criteria text field you should enter the criteria according to which the search will be completed. Through the Sort drop-down menu you can visualize the result sorted in an ascending or a descending order. The text window located below allows you do add extra search conditions.

Run My. SQL Query Additionally, you can use the Ins and the Del check boxes to add or delete text rows for search conditions. The same can be performed through the Add/Delete Criteria Row drop-down menu. To add or delete columns use the corresponding Insand Del check boxes or the Add/Delete Field Columns drop-down menu. In the Modify section you can define the relations between the fields (whether they are connected through the AND or the. OR logical operators). You need to click on the Update Query button to complete the modifications. To run the query click on the Submit Query button. The query which we have included in our example is: SELECT `AT_admins`. * FROM AT_admins WHERE (`AT_admins`. `login` LIKE "a%"); It shows all the records from the AT_admins table for which the login field starts with "a".

My. SQL

My. SQL With PHP, you can connect to and manipulate databases. My. SQL is the most popular database system used with PHP combined with My. SQL are cross-platform (you can develop in Windows and serve on a Unix platform)

What is My. SQL? My. SQL is a database system used on the web My. SQL is a database system that runs on a server My. SQL is ideal for both small and large applications My. SQL is very fast, reliable, and easy to use My. SQL supports standard SQL My. SQL compiles on a number of platforms My. SQL is free to download and use My. SQL is developed, distributed, and supported by Oracle Corporation My. SQL is named after co-founder Monty Widenius's daughter: My

What is My. SQL? The data in My. SQL is stored in tables. A table is a collection of related data, and it consists of columns and rows. Databases are useful when storing information categorically. A company may have a database with the following tables: • Employees • Products • Customers • Orders

Queries A query is a question or a request. We can query a database for specific information and have a recordset returned. Look at the following query (using standard SQL): SELECT Last. Name FROM Employees The query above selects all the data in the "Last. Name" column from the "Employees" table.

PHP Connect to the My. SQL Server Three steps to connect to the My. Sql server : • Open a Connection to the My. SQL Server • manipulation in My. Sql server • Close a Connection

Open a Connection to the My. SQL Server mysqli_connect(host, username, password, dbname); Before we can access data in a database, we must open a connection to the My. SQL server. Parameter Description host Optional. Either a host name or an IP address username Optional. The My. SQL user name password Optional. The password to log in with dbname Optional. The default database to be used when performing queries

Open a Connection to the My. SQL Server <? php // Create connection $con=mysqli_connect(“localhost", “root", "23", "my_db"); // Check connection if (mysqli_connect_errno($con)) { echo "Failed to connect to My. SQL: ". mysqli_connect_error(); } ? >

Close a Connection mysqli_close(connection_name) The connection will be closed automatically when the script ends. To close the connection before, use the mysqli_close() function:

Close a Connection <? php $con=mysqli_connect("example. com", "peter", "abc 123", "my_db"); // Check connection if (mysqli_connect_errno($con)) { echo "Failed to connect to My. SQL: ". mysqli_connect_error(); } mysqli_close($con); ? >

Create a Database The CREATE DATABASE statement is used to create a database table in My. SQL. We must add the CREATE DATABASE statement to the mysqli_query() function to execute the command. The following example creates a database named "my_db":

Create a Database <? php $con=mysqli_connect("example. com", "peter", "abc 123"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to My. SQL: ". mysqli_connect_error(); } // Create database $sql="CREATE DATABASE my_db"; if (mysqli_query($con, $sql)) { echo "Database my_db created successfully"; } else { echo "Error creating database: ". mysqli_error(); } ? >

Create a Table The CREATE TABLE statement is used to create a table in My. SQL. We must add the CREATE TABLE statement to the mysqli_query() function to execute the command. The following example creates a table named "Persons", with three columns. The column names will be "Firstname", "Lastname" and "Age":

Create a Table <? php $con=mysqli_connect("example. com", "peter", "abc 123", "my_db"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to My. SQL: ". mysqli_connect_error(); } // Create table $sql="CREATE TABLE persons(Firstname CHAR(30), Lastname CHAR(30), Age INT)"; // Execute query if (mysqli_query($con, $sql)) { echo "Table persons created successfully"; } else { echo "Error creating table: ". mysqli_error(); } ? >

Primary Keys and Auto Increment Fields ach table in a database should have a primary key field. A primary key is used to uniquely identify the rows in a table. Each primary key value must be unique within the table. Furthermore, the primary key field cannot be null because the database engine requires a value to locate the record. The following example sets the PID field as the primary key field. The primary key field is often an ID number, and is often used with the AUTO_INCREMENT setting. AUTO_INCREMENT automatically increases the value of the field by 1 each time a new record is added. To ensure that the primary key field cannot be null, we must add the NOT NULL setting to the field:

Primary Keys and Auto Increment Fields $sql = "CREATE TABLE Persons ( PID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(PID), Firstname CHAR(15), Lastname CHAR(15), Age INT )";

Insert Data Into a Database Table The INSERT INTO statement is used to add new records to a database table. Syntax It is possible to write the INSERT INTO statement in two forms.

Insert Data Into a Database Table The first form doesn't specify the column names where the data will be inserted, only their values: INSERT INTO table_name VALUES (value 1, value 2, value 3, . . . ) The second form specifies both the column names and the values to be inserted: INSERT INTO table_name (column 1, column 2, column 3, . . . ) VALUES (value 1, value 2, value 3, . . . )

Insert Data Into a Database Table <? php $con=mysqli_connect("example. com", "peter", "abc 123", "my_db"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to My. SQL: ". mysqli_connect_error(); } mysqli_query($con, "INSERT INTO Persons (First. Name, Last. Name, Age) VALUES ('Peter', 'Griffin', 35)"); mysqli_query($con, "INSERT INTO Persons (First. Name, Last. Name, Age) VALUES ('Glenn', 'Quagmire', 33)"); mysqli_close($con); ? >

Insert Data From a Form Into a Database Now we will create an HTML form that can be used to add new records to the "Persons" table. html> <body> <form action="insert. php" method="post"> Firstname: <input type="text" name="firstname"> Lastname: <input type="text" name="lastname"> Age: <input type="text" name="age"> <input type="submit"> </form> </body> </html>

Insert Data From a Form Into a Database When a user clicks the submit button in the HTML form in the example above, the form data is sent to "insert. php". The "insert. php" file connects to a database, and retrieves the values from the form with the PHP $_POST variables. Then, the mysqli_query() function executes the INSERT INTO statement, and a new record will be added to the "Persons" table.

Insert Data From a Form Into a Database insert. php <? php $con=mysqli_connect("example. com", "peter", "abc 123", "my_db"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to My. SQL: ". mysqli_connect_error(); } $sql="INSERT INTO Persons (First. Name, Last. Name, Age) VALUES ('$_POST[firstname]', '$_POST[lastname]', '$_POST[age]')"; if (!mysqli_query($con, $sql)) { die('Error: '. mysqli_error()); } echo "1 record added"; mysqli_close($con); ? >

Select Data From a Database Table SELECT column_name(s) FROM table_name The SELECT statement is used to select data from a database. $select_st = “select * from table_name ”;

Execute the SQL statement recourse mysqli_query(connection_name, sql_statment) To get PHP to execute the statement above we must use the mysqli_query() function. This function is used to send a query or command to a My. SQL connection.

Insert Data From a Form Into a Database <? php $con=mysqli_connect("example. com", "peter", "abc 123", "my_db"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to My. SQL: ". mysqli_connect_error(); } $result = mysqli_query($con, "SELECT * FROM Persons"); while($row = mysqli_fetch_array($result)) { echo $row['First. Name']. " ". $row['Last. Name']; echo "<br />"; } mysqli_close($con); ? >

The WHERE clause SELECT column_name(s) FROM table_name WHERE column_name operator value The SELECT statement is used to select data from a database.

The WHERE clause <? php $con=mysqli_connect("example. com", "peter", "abc 123", "my_db"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to My. SQL: ". mysqli_connect_error(); } $result = mysqli_query($con, "SELECT * FROM Persons WHERE First. Name='Peter'"); while($row = mysqli_fetch_array($result)) { echo $row['First. Name']. " ". $row['Last. Name']; echo " "; } ? >

The ORDER BY Keyword SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC The ORDER BY keyword is used to sort the data in a recordset. The ORDER BY keyword sort the records in ascending order by default. If you want to sort the records in a descending order, you can use the DESC keyword.

The ORDER BY Keyword <? php $con=mysqli_connect("example. com", "peter", "abc 123", "my_db"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to My. SQL: ". mysqli_connect_error(); } $result = mysqli_query($con, "SELECT * FROM Persons ORDER BY age"); while($row = mysqli_fetch_array($result)) { echo $row['First. Name']; echo " ". $row['Last. Name']; echo " ". $row['Age']; echo " "; } mysqli_close($con); ? >

The ORDER BY Keyword It is also possible to order by more than one column. When ordering by more than one column, the second column is only used if the values in the first column are equal:

Update Data In a Database UPDATE table_name SET column 1=value, column 2=value 2, . . . WHERE some_column=some_value The UPDATE statement is used to update existing records in a table. Note: Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

Update Data In a Database <? php $con=mysqli_connect("example. com", "peter", "abc 123", "my_db"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to My. SQL: ". mysqli_connect_error(); } mysqli_query($con, "UPDATE Persons SET Age=36 WHERE First. Name='Peter' AND Last. Name='Griffin'"); mysqli_close($con); ? >

Delete Data In a Database DELETE FROM table_name WHERE some_column = some_value The DELETE FROM statement is used to delete records from a database table. Note: Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

Delete Data In a Database <? php $con=mysqli_connect("example. com", "peter", "abc 123", "my_db"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to My. SQL: ". mysqli_connect_error(); } mysqli_query($con, "DELETE FROM Persons WHERE Last. Name='Griffin'"); mysqli_close($con); ? >

SQL | Join (Inner, Left, Right and Full Joins) A SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. Different types of Joins are: INNER JOIN LEFT JOIN RIGHT JOIN FULL JOIN Consider the two tables below:

Student Course The simplest Join is INNER JOIN: The INNER JOIN keyword selects all rows from both the tables as long as the condition satisfies. This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i. e value of the common field will be same. Syntax: SELECT table 1. column 1, table 1. column 2, table 2. column 1, . . FROM table 1 INNER JOIN table 2 ON table 1. matching_column = table 2. matching_column; table 1: First table 2: Second table

SELECT Student. Course. COURSE_ID, Student. NAME, Student. AGE FROM Student INNER JOIN Student. Course ON Student. ROLL_NO = Student. Course. ROLL_NO; Output

LEFT JOIN This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join. The rows for which there is no matching row on right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER JOIN Syntax: SELECT table 1. column 1, table 1. column 2, table 2. column 1, . . FROM table 1 LEFT JOIN table 2 ON table 1. matching_column = table 2. matching_column; table 1: First table 2: Second table matching_column: Column common to both the tables. Note: We can also use LEFT OUTER JOIN instead of LEFT JOIN, both are same.

SELECT Student. NAME, Student. Course. COURSE_ID FROM Student LEFT JOIN Student. Course ON Student. Course. ROLL_NO = Student. ROLL_NO;

RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join. The rows for which there is no matching row on left side, the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN Syntax: SELECT table 1. column 1, table 1. column 2, table 2. column 1, . . FROM table 1 RIGHT JOIN table 2 ON table 1. matching_column = table 2. matching_column; table 1: First table 2: Second table matching_column: Column common to both the tables. Note: We can also use RIGHT OUTER JOIN instead of RIGHT JOIN, both are same.

SELECT Student. NAME, Student. Course. COURSE_ID FROM Student RIGHT JOIN Student. Course ON Student. Course. ROLL_NO = Student. ROLL_NO; Output:

FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for which there is no matching, the result-set will contain NULL values Syntax: SELECT table 1. column 1, table 1. column 2, table 2. column 1, . . FROM table 1 FULL JOIN table 2 ON table 1. matching_column = table 2. matching_column; table 1: First table 2: Second table matching_column: Column common to both the tables.

SELECT Student. NAME, Student. Course. COURSE_ID FROM Student FULL JOIN Student. Course ON Student. Course. ROLL_NO = Student. ROLL_NO; Output