Author: alien

Khóa học miễn phí MySQLi – Select Query nhận dự án làm có lương

MySQLi – Select Query

The SQL SELECT command is used to fetch data from the MySQL database. You can use this command at mysql> prompt as well as in any script like PHP.

Syntax

Here is generic SQL syntax of SELECT command to fetch data from the MySQL table −

SELECT field1, field2,...fieldN
FROM table_name1, table_name2...
[WHERE Clause]
[OFFSET M ][LIMIT N]

You can use one or more tables separated by comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command.
You can fetch one or more fields in a single SELECT command.
You can specify star (*) in place of fields. In this case, SELECT will return all the fields.
You can specify any condition using the WHERE clause.
You can specify an offset using OFFSET from where SELECT will start returning records. By default, the offset starts at zero.
You can limit the number of returns using the LIMIT attribute.

Fetching Data from a Command Prompt

This will use SQL SELECT command to fetch data from the MySQL table tutorials_tbl.

Example

The following example will return all the records from the tutorials_tbl table −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           1 | Learn PHP      | John Poul       | 2007-05-21      |
|           2 | Learn MySQL    | Abdul S         | 2007-05-21      |
|           3 | JAVA Tutorial  | Sanjay          | 2007-05-21      |
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.01 sec)

mysql>

Fetching Data Using a PHP Script

PHP uses mysqli query() or mysql_query() function to select records from a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli→query($sql,$resultmode)

Sr.No.	Parameter & Description
1	$sql Required – SQL query to select records from a MySQL table.
2	$resultmode Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Sr.No.

Parameter & Description

$sql

Required – SQL query to select records from a MySQL table.

$resultmode

Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example to select a record from a table −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Creating MySQL Table</title>
   </head>
   <body>
      <?php
         $dbhost = ''localhost
         $dbuser = ''root
         $dbpass = ''root@123
         $dbname = ''TUTORIALS
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf(''Connected successfully.<br />'');

         $sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl";

         $result = $mysqli->query($sql);

         if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d <br />",
                  $row["tutorial_id"],
                  $row["tutorial_title"],
                  $row["tutorial_author"],
                  $row["submission_date"]);
            }
         } else {
            printf(''No record found.<br />'');
         }
         mysqli_free_result($result);
         $mysqli→close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output. Here we”ve entered multiple records in the table before running the select script.

Connected successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021
Id: 4, Title: Java Tutorial, Author: Mahesh, Date: 2021
Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQLi – Like Clause nhận dự án làm có lương

MySQLi – Like Clause

We have seen the SQL SELECT command to fetch data from the MySQL table. We can also use a conditional clause called as the WHERE clause to select the required records.

A WHERE clause with the ‘equal to’ sign (=) works fine where we want to do an exact match. Like if “tutorial_author = ”Sanjay””. But there may be a requirement where we want to filter out all the results where tutorial_author name should contain “jay”. This can be handled using SQL LIKE Clause along with the WHERE clause.

If the SQL LIKE clause is used along with the % character, then it will work like a meta character (*) as in UNIX, while listing out all the files or directories at the command prompt. Without a % character, the LIKE clause is very same as the equal to sign along with the WHERE clause.

Syntax

The following code block has a generic SQL syntax of the SELECT command along with the LIKE clause to fetch data from a MySQL table.

SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = ''somevalue''

You can specify any condition using the WHERE clause.
You can use the LIKE clause along with the WHERE clause.
You can use the LIKE clause in place of the equals to sign.
When LIKE is used along with % sign then it will work like a meta character search.
You can specify more than one condition using AND or OR operators.
A WHERE…LIKE clause can be used along with DELETE or UPDATE SQL command also to specify a condition.

Using the LIKE clause at the Command Prompt

This will use the SQL SELECT command with the WHERE…LIKE clause to fetch the selected data from the MySQL table – tutorials_tbl.

Example

The following example will return all the records from the tutorials_tbl table for which the author name ends with jay −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl
   → WHERE tutorial_author LIKE ''%jay
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|      3      |  JAVA Tutorial |     Sanjay      |    2007-05-21   |
+-------------+----------------+-----------------+-----------------+
1 rows in set (0.01 sec)

mysql>

Using LIKE clause inside PHP Script

PHP uses mysqli query() or mysql_query() function to select records in a MySQL table using Like clause. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli→query($sql,$resultmode)

Sr.No.	Parameter & Description
1	$sql Required – SQL query to select records in a MySQL table using Like Clause.
2	$resultmode Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Sr.No.

Parameter & Description

$sql

Required – SQL query to select records in a MySQL table using Like Clause.

$resultmode

Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example to select a record using like clause in a table −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Using Like Clause</title>
   </head>
   <body>
      <?php
         $dbhost = ''localhost
         $dbuser = ''root
         $dbpass = ''root@123
         $dbname = ''TUTORIALS
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf(''Connected successfully.<br />'');

         $sql = ''SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl where tutorial_author like "Mah%"

         $result = $mysqli→query($sql);

         if ($result→num_rows > 0) {
            while($row = $result→fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d <br />",
                  $row["tutorial_id"],
                  $row["tutorial_title"],
                  $row["tutorial_author"],
                  $row["submission_date"]);
            }
         } else {
            printf(''No record found.<br />'');
         }
         mysqli_free_result($result);
         $mysqli→close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output. Here we”ve entered multiple records in the table before running the select script.

Connected successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQLi – Where Clause nhận dự án làm có lương

MySQLi – WHERE Clause

We have seen the SQL SELECT command to fetch data from a MySQL table. We can use a conditional clause called the WHERE Clause to filter out the results. Using this WHERE clause, we can specify a selection criteria to select the required records from a table.

Syntax

The following code block has a generic SQL syntax of the SELECT command with the WHERE clause to fetch data from the MySQL table −

SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

You can use one or more tables separated by a comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command.
You can specify any condition using the WHERE clause.
You can specify more than one condition using the AND or the OR operators.
A WHERE clause can be used along with DELETE or UPDATE SQL command also to specify a condition.

The WHERE clause works like an if condition in any programming language. This clause is used to compare the given value with the field value available in a MySQL table. If the given value from outside is equal to the available field value in the MySQL table, then it returns that row.

Here is the list of operators, which can be used with the WHERE clause.

Assume field A holds 10 and field B holds 20, then −

Operator	Description	Example
=	Checks if the values of the two operands are equal or not, if yes, then the condition becomes true.	(A = B) is not true.
!=	Checks if the values of the two operands are equal or not, if the values are not equal then the condition becomes true.	(A != B) is true.
>	Checks if the value of the left operand is greater than the value of the right operand, if yes, then the condition becomes true.	(A > B) is not true.
<	Checks if the value of the left operand is less than the value of the right operand, if yes then the condition becomes true.	(A < B) is true.
>=	Checks if the value of the left operand is greater than or equal to the value of the right operand, if yes, then the condition becomes true.	(A >= B) is not true.
<=	Checks if the value of the left operand is less than or equal to the value of the right operand, if yes, then the condition becomes true.	(A <= B) is true.

The WHERE clause is very useful when you want to fetch the selected rows from a table, especially when you use the MySQL Join. Joins are discussed in another chapter.

It is a common practice to search for records using the Primary Key to make the search faster.

If the given condition does not match any record in the table, then the query would not return any row.

Fetching Data from the Command Prompt

This will use the SQL SELECT command with the WHERE clause to fetch the selected data from the MySQL table – tutorials_tbl.

Example

The following example will return all the records from the tutorials_tbl table for which the author name is Sanjay.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl WHERE tutorial_author = ''Sanjay
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|      3      | JAVA Tutorial  |      Sanjay     |    2007-05-21   |
+-------------+----------------+-----------------+-----------------+
1 rows in set (0.01 sec)

mysql>

Unless performing a LIKE comparison on a string, the comparison is not case sensitive. You can make your search case sensitive by using the BINARY keyword as follows −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl
   WHERE BINARY tutorial_author = ''sanjay
Empty set (0.02 sec)

mysql>

Fetching Data Using a PHP Script

PHP uses mysqli query() or mysql_query() function to select records in a MySQL table using where clause. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli→query($sql,$resultmode)

Sr.No.	Parameter & Description
1	$sql Required – SQL query to select records in a MySQL table using Where Clause.
2	$resultmode Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Sr.No.

Parameter & Description

$sql

Required – SQL query to select records in a MySQL table using Where Clause.

$resultmode

Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example to select a record using where clause in a table −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Using Where Clause</title>
   </head>
   <body>
      <?php
         $dbhost = ''localhost
         $dbuser = ''root
         $dbpass = ''root@123
         $dbname = ''TUTORIALS
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf(''Connected successfully.<br />'');

         $sql = ''SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl where tutorial_author = "Mahesh"

         $result = $mysqli→query($sql);

         if ($result→num_rows > 0) {
            while($row = $result→fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d <br />",
                  $row["tutorial_id"],
                  $row["tutorial_title"],
                  $row["tutorial_author"],
                  $row["submission_date"]);
            }
         } else {
            printf(''No record found.<br />'');
         }
         mysqli_free_result($result);
         $mysqli→close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output. Here we”ve entered multiple records in the table before running the select script.

Connected successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQLi – Delete Query nhận dự án làm có lương

MySQLi – Delete Query

If you want to delete a record from any MySQL table, then you can use the SQL command DELETE FROM. You can use this command at the mysql> prompt as well as in any script like PHP.

Syntax

The following code block has a generic SQL syntax of the DELETE command to delete data from a MySQL table.

DELETE FROM table_name [WHERE Clause]

If the WHERE clause is not specified, then all the records will be deleted from the given MySQL table.
You can specify any condition using the WHERE clause.
You can delete records in a single table at a time.

The WHERE clause is very useful when you want to delete selected rows in a table.

Deleting Data from the Command Prompt

This will use the SQL DELETE command with the WHERE clause to delete selected data into the MySQL table – tutorials_tbl.

Example

The following example will delete a record from the tutorial_tbl whose tutorial_id is 3.

root@host# mysql -u root -p password;
Enter password:*******

mysql> use TUTORIALS;
Database changed

mysql> DELETE FROM tutorials_tbl WHERE tutorial_id=3;
Query OK, 1 row affected (0.23 sec)

mysql>

Deleting Data Using a PHP Script

PHP uses mysqli query() or mysql_query() function to delete records in a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli→query($sql,$resultmode)

Sr.No.	Parameter & Description
1	$sql Required – SQL query to delete records in a MySQL table.
2	$resultmode Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Sr.No.

Parameter & Description

$sql

Required – SQL query to delete records in a MySQL table.

$resultmode

Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example to delete a record in a table −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Deleting MySQL Table record</title>
   </head>
   <body>
      <?php
         $dbhost = ''localhost
         $dbuser = ''root
         $dbpass = ''root@123
         $dbname = ''TUTORIALS
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf(''Connected successfully.<br />'');

         if ($mysqli→query(''DELETE FROM tutorials_tbl where tutorial_id = 4'')) {
            printf("Table tutorials_tbl record deleted successfully.<br />");
         }
         if ($mysqli→errno) {
            printf("Could not delete record from table: %s<br />", $mysqli→error);
         }

         $sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl";

         $result = $mysqli→query($sql);

         if ($result→num_rows > 0) {
            while($row = $result→fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d <br />",
                  $row["tutorial_id"],
                  $row["tutorial_title"],
                  $row["tutorial_author"],
                  $row["submission_date"]);
            }
         } else {
            printf(''No record found.<br />'');
         }
         mysqli_free_result($result);
         $mysqli→close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output. Here we”ve entered multiple records in the table before running the select script.

Connected successfully.
Table tutorials_tbl record deleted successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021
Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQLi – Using Joins nhận dự án làm có lương

MySQLi – Using Joins

In the previous chapters, we were getting data from one table at a time. This is good enough for simple takes, but in most of the real world MySQL usages, you will often need to get data from multiple tables in a single query.

You can use multiple tables in your single SQL query. The act of joining in MySQL refers to smashing two or more tables into a single table.

You can use JOINS in the SELECT, UPDATE and DELETE statements to join the MySQL tables. We will see an example of the LEFT JOIN also which is different from the simple MySQL JOIN.

Using Joins at the Command Prompt

Assume we have two tables tcount_tbl and tutorials_tbl, in TUTORIALS. Now take a look at the examples given below −

Example

The following examples −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * FROM tcount_tbl;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
|      mahran     |       20       |
|      mahnaz     |      NULL      |
|       Jen       |      NULL      |
|      Gill       |       20       |
|    John Poul    |        1       |
|     Sanjay      |        1       |
+-----------------+----------------+
6 rows in set (0.01 sec)
mysql> SELECT * from tutorials_tbl;
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|      1      |  Learn PHP     |     John Poul   |    2007-05-24   |
|      2      |  Learn MySQL   |      Abdul S    |    2007-05-24   |
|      3      | JAVA Tutorial  |      Sanjay     |    2007-05-06   |
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.00 sec)
mysql>

Now we can write an SQL query to join these two tables. This query will select all the authors from table tutorials_tbl and will pick up the corresponding number of tutorials from the tcount_tbl.

mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
   → FROM tutorials_tbl a, tcount_tbl b
   → WHERE a.tutorial_author = b.tutorial_author;
+-------------+-----------------+----------------+
| tutorial_id | tutorial_author | tutorial_count |
+-------------+-----------------+----------------+
|      1      |    John Poul    |        1       |
|      3      |     Sanjay      |        1       |
+-------------+-----------------+----------------+
2 rows in set (0.01 sec)
mysql>

Using Joins in a PHP Script

PHP uses mysqli query() or mysql_query() function to get records from a MySQL tables using Joins. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli→query($sql,$resultmode)

Sr.No.	Parameter & Description
1	$sql Required – SQL query to get records from multiple tables using Join.
2	$resultmode Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Sr.No.

Parameter & Description

$sql

Required – SQL query to get records from multiple tables using Join.

$resultmode

Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

First create a table in MySQL using following script and insert two records.

create table tcount_tbl(
   tutorial_author VARCHAR(40) NOT NULL,
   tutorial_count int
);
insert into tcount_tbl values(''Mahesh'', 3);
insert into tcount_tbl values(''Suresh'', 1);

Example

Try the following example to get records from a two tables using Join. −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Using joins on MySQL Tables</title>
   </head>
   <body>
      <?php
         $dbhost = ''localhost
         $dbuser = ''root
         $dbpass = ''root@123
         $dbname = ''TUTORIALS
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf(''Connected successfully.<br />'');

         $sql = ''SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
				FROM tutorials_tbl a, tcount_tbl b
				WHERE a.tutorial_author = b.tutorial_author

         $result = $mysqli→query($sql);

         if ($result→num_rows > 0) {
            while($row = $result→fetch_assoc()) {
               printf("Id: %s, Author: %s, Count: %d <br />",
                  $row["tutorial_id"],
                  $row["tutorial_author"],
                  $row["tutorial_count"]);
            }
         } else {
            printf(''No record found.<br />'');
         }
         mysqli_free_result($result);
         $mysqli→close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.
Id: 1, Author: Mahesh, Count: 3
Id: 2, Author: Mahesh, Count: 3
Id: 3, Author: Mahesh, Count: 3
Id: 5, Author: Suresh, Count: 1

MySQL LEFT JOIN

A MySQL left join is different from a simple join. A MySQL LEFT JOIN gives some extra consideration to the table that is on the left.

If I do a LEFT JOIN, I get all the records that match in the same way and IN ADDITION I get an extra record for each unmatched record in the left table of the join: thus ensuring (in my example) that every AUTHOR gets a mention.

Example

Try the following example to understand the LEFT JOIN.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
   → FROM tutorials_tbl a LEFT JOIN tcount_tbl b
   → ON a.tutorial_author = b.tutorial_author;
+-------------+-----------------+----------------+
| tutorial_id | tutorial_author | tutorial_count |
+-------------+-----------------+----------------+
|      1      |    John Poul    |       1        |
|      2      |     Abdul S     |      NULL      |
|      3      |     Sanjay      |       1        |
+-------------+-----------------+----------------+
3 rows in set (0.02 sec)

You would need to do more practice to become familiar with JOINS. This is slightly a bit complex concept in MySQL/SQL and will become more clear while doing real examples.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQLi – Insert Query nhận dự án làm có lương

MySQLi – Insert Query

To insert data into a MySQL table, you would need to use the SQL INSERT INTO command. You can insert data into the MySQL table by using the mysql> prompt or by using any script like PHP.

Syntax

Here is a generic SQL syntax of INSERT INTO command to insert data into the MySQL table −

INSERT INTO table_name ( field1, field2,...fieldN )
   VALUES
   ( value1, value2,...valueN );

To insert string data types, it is required to keep all the values into double or single quotes. For example “value”.

Inserting Data from the Command Prompt

To insert data from the command prompt, we will use SQL INSERT INTO command to insert data into MySQL table tutorials_tbl.

Example

The following example will create 3 records into tutorials_tbl table −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed

mysql> INSERT INTO tutorials_tbl
   →(tutorial_title, tutorial_author, submission_date)
   →VALUES
   →("Learn PHP", "John Poul", NOW());
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tutorials_tbl
   →(tutorial_title, tutorial_author, submission_date)
   →VALUES
   →("Learn MySQL", "Abdul S", NOW());
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tutorials_tbl
   →(tutorial_title, tutorial_author, submission_date)
   →VALUES
   →("JAVA Tutorial", "Sanjay", ''2007-05-06'');
Query OK, 1 row affected (0.01 sec)
mysql>

NOTE − Please note that all the arrow signs (→) are not a part of the SQL command. They are indicating a new line and they are created automatically by the MySQL prompt while pressing the enter key without giving a semicolon at the end of each line of the command.

In the above example, we have not provided a tutorial_id because at the time of table creation, we had given AUTO_INCREMENT option for this field. So MySQL takes care of inserting these IDs automatically. Here, NOW() is a MySQL function, which returns the current date and time.

Inserting Data Using a PHP Script

PHP uses mysqli query() or mysql_query() function to insert a record into a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli→query($sql,$resultmode)

Sr.No.	Parameter & Description
1	$sql Required – SQL query to insert record into a table.
2	$resultmode Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Sr.No.

Parameter & Description

$sql

Required – SQL query to insert record into a table.

$resultmode

Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

This example will take three parameters from the user and will insert them into the MySQL table − −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Add New Record in MySQL Database</title>
   </head>
   <body>
      <?php
         if(isset($_POST[''add''])) {
            $dbhost = ''localhost
            $dbuser = ''root
            $dbpass = ''root@123
            $dbname = ''TUTORIALS
            $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

            if($mysqli→connect_errno ) {
               printf("Connect failed: %s<br />", $mysqli→connect_error);
               exit();
            }
            printf(''Connected successfully.<br />'');

            if(! get_magic_quotes_gpc() ) {
               $tutorial_title = addslashes ($_POST[''tutorial_title'']);
               $tutorial_author = addslashes ($_POST[''tutorial_author'']);
            } else {
               $tutorial_title = $_POST[''tutorial_title''];
               $tutorial_author = $_POST[''tutorial_author''];
            }
            $submission_date = $_POST[''submission_date''];
            $sql = "INSERT INTO tutorials_tbl ".
               "(tutorial_title,tutorial_author, submission_date) "."VALUES ".
               "(''$tutorial_title'',''$tutorial_author'',''$submission_date'')";

            if ($mysqli→query($sql)) {
               printf("Record inserted successfully.<br />");
            }
            if ($mysqli→errno) {
               printf("Could not insert record into table: %s<br />", $mysqli→error);
            }
            $mysqli→close();
         } else {
      ?>
      <form method = "post" action = "<?php $_PHP_SELF ?>">
         <table width = "600" border = "0" cellspacing = "1" cellpadding = "2">
            <tr>
               <td width = "250">Tutorial Title</td>
               <td><input name = "tutorial_title" type = "text" id = "tutorial_title"></td>
            </tr>
            <tr>
               <td width = "250">Tutorial Author</td>
               <td><input name = "tutorial_author" type = "text" id = "tutorial_author"></td>
            </tr>
            <tr>
               <td width = "250">Submission Date [   yyyy-mm-dd ]</td>
               <td><input name = "submission_date" type = "text" id = "submission_date"></td>
            </tr>
            <tr>
               <td width = "250"> </td>
               <td></td>
            </tr>
            <tr>
               <td width = "250"> </td>
               <td><input name = "add" type = "submit" id = "add"  value = "Add Tutorial"></td>
            </tr>
         </table>
      </form>
   <?php
      }
   ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server, enter details and verify the output on submitting the form.

Record inserted successfully.

While doing a data insert, it is best to use the function get_magic_quotes_gpc() to check if the current configuration for magic quote is set or not. If this function returns false, then use the function addslashes() to add slashes before the quotes.

You can put many validations around to check if the entered data is correct or not and can take the appropriate action.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQLi – Create Tables nhận dự án làm có lương

MySQLi – Create Table

To begin with, the table creation command requires the following details −

Name of the table
Name of the fields
Definitions for each field

Syntax

Here is a generic SQL syntax to create a MySQL table −

CREATE TABLE table_name (column_name column_type);

Now, we will create the following table in the TUTORIALS database.

create table tutorials_tbl(
   tutorial_id INT NOT NULL AUTO_INCREMENT,
   tutorial_title VARCHAR(100) NOT NULL,
   tutorial_author VARCHAR(40) NOT NULL,
   submission_date DATE,
   PRIMARY KEY ( tutorial_id )
);

Here, a few items need explanation −

Field Attribute NOT NULL is being used because we do not want this field to be NULL. So, if a user will try to create a record with a NULL value, then MySQL will raise an error.
Field Attribute AUTO_INCREMENT tells MySQL to go ahead and add the next available number to the id field.
Keyword PRIMARY KEY is used to define a column as a primary key. You can use multiple columns separated by a comma to define a primary key.

Creating Tables from Command Prompt

It is easy to create a MySQL table from the mysql> prompt. You will use the SQL command CREATE TABLE to create a table.

Example

Here is an example, which will create tutorials_tbl −

root@host# mysql -u root -p
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> CREATE TABLE tutorials_tbl(
   → tutorial_id INT NOT NULL AUTO_INCREMENT,
   → tutorial_title VARCHAR(100) NOT NULL,
   → tutorial_author VARCHAR(40) NOT NULL,
   → submission_date DATE,
   → PRIMARY KEY ( tutorial_id )
   → );
Query OK, 0 rows affected (0.16 sec)
mysql>

NOTE − MySQL does not terminate a command until you give a semicolon (;) at the end of SQL command.

Creating Tables Using PHP Script

PHP uses mysqli query() or mysql_query() function to create a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli→query($sql,$resultmode)

Sr.No.	Parameter & Description
1	$sql Required – SQL query to create a MySQL table.
2	$resultmode Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Sr.No.

Parameter & Description

$sql

Required – SQL query to create a MySQL table.

$resultmode

Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example to create a table −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Creating MySQL Table</title>
   </head>
   <body>
      <?php
         $dbhost = ''localhost
         $dbuser = ''root
         $dbpass = ''root@123
         $dbname = ''TUTORIALS
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf(''Connected successfully.<br />'');

         $sql = "CREATE TABLE tutorials_tbl( ".
            "tutorial_id INT NOT NULL AUTO_INCREMENT, ".
            "tutorial_title VARCHAR(100) NOT NULL, ".
            "tutorial_author VARCHAR(40) NOT NULL, ".
            "submission_date DATE, ".
            "PRIMARY KEY ( tutorial_id )); ";
         if ($mysqli→query($sql)) {
            printf("Table tutorials_tbl created successfully.<br />");
         }
         if ($mysqli→errno) {
            printf("Could not create table: %s<br />", $mysqli→error);
         }
         $mysqli→close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.
Table tutorials_tbl created successfully.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQLi – Connection nhận dự án làm có lương

MySQLi – Connection

MySQL Connection Using MySQL Binary

You can establish the MySQL database using the mysql binary at the command prompt.

Example

Here is a simple example to connect to the MySQL server from the command prompt −

[root@host]# mysql -u root -p
Enter password:******

This will give you the mysqli command prompt where you will be able to execute any SQL command. Following is the result of above command −

The following code block shows the result of above code −

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2854760 to server version: 5.0.9

Type ''help;'' or ''h'' for help. Type ''c'' to clear the buffer.

In the above example, we have used root as a user but you can use any other user as well. Any user will be able to perform all the SQL operations, which are allowed to that user.

You can disconnect from the MySQL database any time using the exit command at mysql> prompt.

mysql> exit
Bye

MySQL Connection Using PHP Script

PHP provides mysqli contruct or mysqli_connect() function to open a database connection. This function takes six parameters and returns a MySQL link identifier on success or FALSE on failure.

Syntax

$mysqli = new mysqli($host, $username, $passwd, $dbName, $port, $socket);

Sr.No.	Parameter & Description
1	$host Optional − The host name running the database server. If not specified, then the default value will be localhost:3306.
2	$username Optional − The username accessing the database. If not specified, then the default will be the name of the user that owns the server process.
3	$passwd Optional − The password of the user accessing the database. If not specified, then the default will be an empty password.
4	$dbName Optional − database name on which query is to be performed.
5	$port Optional − the port number to attempt to connect to the MySQL server..
6	$socket Optional − socket or named pipe that should be used.

You can disconnect from the MySQL database anytime using another PHP function close().

Syntax

$mysqli→close();

Example

Try the following example to connect to a MySQL server −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Connecting MySQL Server</title>
   </head>
   <body>
      <?php
         $dbhost = ''localhost
         $dbuser = ''root
         $dbpass = ''root@123
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass);

         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf(''Connected successfully.<br />'');
         $mysqli→close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQLi – Select Database nhận dự án làm có lương

MySQLi – Select Database

Once you get connected with the MySQL server, it is required to select a database to work with. This is because there might be more than one database available with the MySQL Server.

Selecting MySQL Database from the Command Prompt

It is very simple to select a database from the mysql> prompt. You can use the SQL command use to select a database.

Example

Here is an example to select a database called TUTORIALS −

[root@host]# mysql -u root -p
Enter password:******
mysql> use TUTORIALS;
Database changed
mysql>

Now, you have selected the TUTORIALS database and all the subsequent operations will be performed on the TUTORIALS database.

NOTE − All the database names, table names, table fields name are case sensitive. So you would have to use the proper names while giving any SQL command.

Selecting a MySQL Database Using PHP Script

PHP uses mysqli_select_db function to select the database on which queries are to be performed. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

mysqli_select_db ( mysqli $link , string $dbname ) : bool

Sr.No.	Parameter & Description
1	$link Required – A link identifier returned by mysqli_connect() or mysqli_init().
2	$dbname Required – Name of the database to be connected.

Sr.No.

Parameter & Description

$link

Required – A link identifier returned by mysqli_connect() or mysqli_init().

$dbname

Required – Name of the database to be connected.

Example

Try the following example to select a database −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Selecting MySQL Database</title>
   </head>
   <body>
   <?php
      $dbhost = ''localhost
      $dbuser = ''root
      $dbpass = ''root@123
      $conn = mysqli_connect($dbhost, $dbuser, $dbpass);

      if(! $conn ) {
         die(''Could not connect: '' . mysqli_error($conn));
      }
      echo ''Connected successfully<br />

      $retval = mysqli_select_db( $conn, ''TUTORIALS'' );

      if(! $retval ) {
         die(''Could not select database: '' . mysqli_error($conn));
      }
      echo "Database TUTORIALS selected successfullyn";
      mysqli_close($conn);
   ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Database TUTORIALS selected successfully

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQLi – Introduction nhận dự án làm có lương
MySQLi – Introduction
MySQLi is an extension to MySQL API available in PHP and is introduced from PHP 5.0 onwards. It is also known as MySQL improved extension. Motivation behind MySQLi was to take advantage of new features available in MySQL 4.1.3 onwards. It provides numerous benefits over MySQL extension.

MySQL provides an object oriented interface. It provides both object oriented and procedural approach to handle database operations.

Object Oriented Interface

<?php $mysqli = mysqli_connect("localhost", "user", "password", "database-name"); $result = mysqli_query($mysqli, "SELECT ''Welcome to MySQLi'' AS _msg FROM DUAL"); $row = mysqli_fetch_assoc($result); echo $row[''_msg'']; ?>

Procedural Approach

<?php $mysqli = new mysqli("localhost", "user", "password", "database-name"); $result = $mysqli→query("SELECT ''Welcome to MySQLi'' AS _msg FROM DUAL"); $row = $result→fetch_assoc(); echo $row[''_msg'']; ?>

MySQLi supports prepared statments.

MySQLi supports multiple statments.

MySQLi supports transactions.

MySQLi provides enhanced debugging capabilities.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Author: alien

MySQLi – Select Query

Syntax

Fetching Data from a Command Prompt

Example

Fetching Data Using a PHP Script

Syntax

Example

Output

MySQLi – Like Clause

Syntax

Using the LIKE clause at the Command Prompt

Example

Using LIKE clause inside PHP Script

Syntax

Example

Output

MySQLi – WHERE Clause

Syntax

Fetching Data from the Command Prompt

Example

Fetching Data Using a PHP Script

Syntax

Example

Output

MySQLi – Delete Query

Syntax

Deleting Data from the Command Prompt

Example

Deleting Data Using a PHP Script

Syntax

Example

Output

MySQLi – Using Joins

Using Joins at the Command Prompt

Example

Using Joins in a PHP Script

Syntax

Example

Output

MySQL LEFT JOIN

Example

MySQLi – Insert Query

Syntax

Inserting Data from the Command Prompt

Example

Inserting Data Using a PHP Script

Syntax

Example

Output

MySQLi – Create Table

Syntax

Creating Tables from Command Prompt

Example

Creating Tables Using PHP Script

Syntax

Example

Output

MySQLi – Connection

MySQL Connection Using MySQL Binary

Example

MySQL Connection Using PHP Script

Syntax

Syntax

Example

Output

MySQLi – Select Database

Selecting MySQL Database from the Command Prompt

Example

Selecting a MySQL Database Using PHP Script

Syntax

Example

Output

MySQLi – Introduction

Object Oriented Interface

Procedural Approach