- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to write PHP script by using MySQL JOINS inside it to join two MySQL tables?
We can use the syntax of MySQL JOIN for joining two tables into the PHP function – mysql_query(). This function is used to execute the SQL command and later another PHP function – mysql_fetch_array() can be used to fetch all the selected data.
To illustrate it we are having the following example −
Example
In this example, we are using two MySQL tables which have the following data −
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)
Now, the following is a PHP script that will join the tables to select all the authors from table tutorials_tbl and will pick up the corresponding number of tutorials from the tcount_tbl.
<?php $dbhost = 'localhost:3036'; $dbuser = 'root'; $dbpass = 'rootpassword'; $conn = mysql_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Could not connect: ' . mysql_error()); } $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'; mysql_select_db('TUTORIALS'); $retval = mysql_query( $sql, $conn ); if(! $retval ) { die('Could not get data: ' . mysql_error()); } while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) { echo "Author:{$row['tutorial_author']} <br> ". "Count: {$row['tutorial_count']} <br> ". "Tutorial ID: {$row['tutorial_id']} <br> ". "--------------------------------<br>"; } echo "Fetched data successfully
"; mysql_close($conn); ?>
Advertisements