Главная » Языки программирования

Using joins on MySQL Tables

На чтение 11 мин
Содержание
  1. MySQLi — Using Joins
  2. Using Joins at the Command Prompt
  3. Example
  4. Using Joins in a PHP Script
  5. Syntax
  6. Example
  7. Output
  8. MySQL LEFT JOIN
  9. Example
  10. MySQL — Using Joins
  11. Using Joins at the Command Prompt
  12. Example
  13. Using Joins in a PHP Script
  14. Syntax
  15. Example
  16. Output
  17. MySQL LEFT JOIN
  18. Example
  19. inner join in php – How to Use Joins in PHP?
  20. inner join in php – inner join program code in PHP, MySQL
  21. INNER JOIN
  22. RIGHT OUTER JOIN
  23. SELF JOIN
  24. PHP SQL Inner Join
  25. Related posts:

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

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

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

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

Читайте также:  Add shortcode in php file

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 −

    ", $mysqli→connect_error); exit(); > printf('Connected successfully.
'); $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 
", $row["tutorial_id"], $row["tutorial_author"], $row["tutorial_count"]); > > else < printf('No record found.
'); > mysqli_free_result($result); $mysqli→close(); ?>

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.

Источник

MySQL — 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 −

Читайте также:  Fetch array in javascript

Example

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

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

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 −

    connect_errno ) < printf("Connect failed: %s
", $mysqli->connect_error); exit(); > printf('Connected successfully.
'); $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 
", $row["tutorial_id"], $row["tutorial_author"], $row["tutorial_count"]); > > else < printf('No record found.
'); > mysqli_free_result($result); $mysqli->close(); ?>

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.

Читайте также:  Float values in javascript

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.

Источник

inner join in php – How to Use Joins in PHP?

inner join in php – A join lets you combine columns from two or more tables into a single result or say a SQL statement that retrieves data from two tables is called a join.

Inner joins let us select rows that have same value in both tables for specified columns thereby returns matching rows.

inner join in php – inner join program code in PHP, MySQL

Inner join shows results from both tables where there is any match between columns in both tables. here you will learn to MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS.

INNER JOIN

 mysql_select_db("Memberloyees", $link); $qry="SELECT Member.id,Member.MemberNm, group.group_name FROM Member INNER JOIN group on Member.id = group.group_id"; $result=mysql_query($qry)ordie(mysql_error()); echo"  MemberId MemberNm groupName "; while($row = mysql_fetch_array($result))< echo ""; echo "" . $row['id'] . ""; echo "" . $row['MemberNm'] . ""; echo "" . $row['group_name'] . ""; echo ""; > echo ""; mysql_close($link); ?>

RIGHT OUTER JOIN

 mysql_select_db("Memberloyees", $link); $qry="SELECT Member.id, Member.MemberNm,group.group_name from Member RIGHT OUTER JOIN group on Member.id=group.group_id"; $result=mysql_query($qry)ordie(mysql_error()); echo"  MemberId MemberNm groupName "; while($row = mysql_fetch_array($result))< echo ""; echo "" . $row['id'] . ""; echo "" . $row['MemberNm'] . ""; echo "" . $row['group_name'] . ""; echo ""; > echo ""; mysql_close($link); ?>

MySQL INNER JOIN clause 

SELECT select_list FROM t1 INNER JOIN t2 ON join_condition1 INNER JOIN t3 ON join_condition2 . ;

SELF JOIN

 mysql_select_db("Memberloyees", $link); $qry="select a.team_name,b.team_name,a.team_city from Member a, Member b where a.team_city=b.team_city and a.team_name<>b.team_name"; $result=mysql_query($qry)ordie(mysql_error()); echo"  MemberName MemberCity "; while($row = mysql_fetch_array($result))< echo ""; echo "" . $row['team_name'] . ""; echo "" . $row['team_city'] . ""; echo ""; > echo ""; mysql_close($link); ?>

PHP SQL Inner Join

Source Code of innerJoin.php 

 mysql_select_db("test", $link); $result = mysql_query("SELECT team.name, comment.comments FROM team INNER JOIN comment ON team.team_id = comment.team_id"); echo " "; while ($row = mysql_fetch_array($result)) < echo ""; echo ""; echo ""; echo ""; > echo "
 
Name Comments 
" . $row['name'] . "" . $row['comments'] . "
"; mysql_close($con); ?>

I hope you get an idea about inner join in php.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.

Источник

Оцените статью
© 2023 Программирование