Skip to main content

What is INNER JOIN in MySQL and how to use with PHP?

 







Database structure of practical Example:

-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jan 28, 2025 at 01:20 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `mytest`
--

-- --------------------------------------------------------

--
-- Table structure for table `categories`
--

CREATE TABLE `categories` (
  `category_id` int(11) NOT NULL,
  `category_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`category_id`, `category_name`) VALUES
(1, 'Samsung'),
(2, 'Apple'),
(3, 'Nokia');

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `product_name` varchar(100) NOT NULL,
  `unit_price` int(11) NOT NULL,
  `unit_in_stock` int(11) NOT NULL,
  `category_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `product_name`, `unit_price`, `unit_in_stock`, `category_id`) VALUES
(1, 'Samsung tx-30', 10500, 20, 1),
(2, 'Samsung red-40', 11200, 25, 1),
(3, 'Samsung blue-55', 25000, 15, 1),
(4, 'Nokia star-tz', 15000, 12, 3),
(5, 'Nokia tiptop', 16000, 26, 3),
(6, 'Apple mega4', 160000, 10, 2),
(7, 'Apple superX', 200000, 24, 2);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `categories`
--
ALTER TABLE `categories`
  ADD PRIMARY KEY (`category_id`);

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `categories`
--
ALTER TABLE `categories`
  MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Practical Example 1:index1.php code


<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<style type="text/css">
body{
display: flex;
justify-content: center;
align-items: center;
flex-direction: column;
}


table{
border-collapse: collapse;
width:50%;
}

h2{
color:red;
font-weight: bold;
}
</style>
<title>INNER JOIN</title>
</head>
<body>

<?php
$_GET['category'] = 2;
// Parse the query string
 if (isset($_GET['category'])) {
 $category_num = $_GET['category'];
 } else {
  echo 'The "category" parameter is missing!<br>';
    echo 'We are done here, sorry.';
 exit(0);


 }

 // Store the database connection parameters
 $host = 'localhost';
 $user = 'root';
 $password = '';
 $database = 'mytest';

 // create a new mysqli object with the database connection
 $mysqli = new mysqli($host, $user, $password, $database);

 // create select query
 $sql = "SELECT products.product_name,
 products.unit_price,
 products.unit_in_stock,
 categories.category_name
 FROM products
 INNER JOIN categories
 ON products.category_id = categories.category_id
 WHERE products.category_id = $category_num";
  $result = $mysqli->query($sql);
 $result2 = $mysqli->query($sql);
 $Category = $result2->fetch_array();
 ?>



 <h2>Product Category:<?php echo $Category['category_name']; ?> </h2>



  

 <table border="1">
  <thead>
  <tr>
  <th>Products </th>
  <th>Unit Price </th>
  <th>Stock Units </th>
  </tr>
  </thead>
  <tbody>


 <?php

 // Get the query result

 while($rows = $result->fetch_assoc()){?>
       <tr>
      <td><?php echo $rows['product_name']; ?> </td>
      <td><?php echo $rows['unit_price']; ?> </td>
      <td><?php echo $rows['unit_in_stock']; ?> </td>
       </tr>
<?php
 }

 

 




?>

</tbody>

</table>

</body>
</html>
 
Application Output:
Practical Example 2:index2.php

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<style type="text/css">

body{
display: flex;
justify-content: center;
align-items: center;
flex-direction: column;
}


table{
border-collapse: collapse;
width:50%;
}

h2{
color:red;
font-weight: bold;
}
</style>
<title>INNER JOIN</title>
</head>
<body>

<?php
// $_GET['category'] = 2;
// // Parse the query string
 // if (isset($_GET['category'])) {
 // $category_num = $_GET['category'];
 // } else {
 // echo 'The "category" parameter is missing!<br>';
 //    echo 'We are done here, sorry.';
 // exit(0);


 // }

 // Store the database connection parameters
 $host = 'localhost';
 $user = 'root';
 $password = '';
 $database = 'mytest';

 // create a new mysqli object with the database connection
 $mysqli = new mysqli($host, $user, $password, $database);

 // create select query
 $sql = "SELECT products.product_name,
 products.unit_price,
 products.unit_in_stock,
 categories.category_name
 FROM products
 INNER JOIN categories
 ON products.category_id = categories.category_id
 ";
  $result = $mysqli->query($sql);
 $result2 = $mysqli->query($sql);
 $Category = $result2->fetch_array();
 ?>



 <h2>Products and its categories </h2>



  

 <table border="1">
  <thead>
  <tr>
  <th>Products </th>
  <th>Unit Price </th>
  <th>Stock Units </th>
  <th>Category </th>
  </tr>
  </thead>
  <tbody>


 <?php

 // Get the query result

 while($rows = $result->fetch_assoc()){?>
       <tr>
      <td><?php echo $rows['product_name']; ?> </td>
      <td><?php echo $rows['unit_price']; ?> </td>
      <td><?php echo $rows['unit_in_stock']; ?> </td>
      <td><?php echo $rows['category_name']; ?> </td>
       </tr>
<?php
 }

 

 




?>

</tbody>

</table>

</body>
</html>

Application Output:



 Download source code:

Labels:

Comments

Post a Comment

Popular posts from this blog

Creating a Student Marksheet using Microsoft Excel.

 Creating a Student Marksheet using Microsoft Excel.  Creating a student marks sheet in MS Excel is straightforward. Here's a step-by-step guide to design one: Step 1: Open MS Excel Launch MS Excel and open a new workbook. Step 2: Structure the Sheet Header Section: Use the first few rows to include the title, such as "Student Marks Sheet" and relevant details like the class, semester, or term. Column Headings: In Row 5 (or below the title), define your column headers: Column A: Roll Number Column B: Student Name Column C onward: Subjects (e.g., Mathematics, Science, English, etc.) Final Columns: Include Total Marks , Percentage , and Grade . Example: Roll No.Student Name Mathematics Science English Total Marks Percentage Grade Step 3: Input Data Enter the roll numbers, student names, and their marks under respective columns. Step 4: Add Formulas Total Marks: In the "Total Marks" column, use the formula to sum marks: =SUM(C2:E2) Copy this ...
Post a Comment
Read more

What is Kruti Dev and how to download ?

  Kruti Dev is a popular typeface or font used primarily for typing in the Devanagari script, which is the script for Hindi, Marathi, and other Indian languages. It is widely used in government offices, printing presses, and other organizations in India for Hindi typing. Key Features of Kruti Dev: It is a non-Unicode font, meaning it does not follow the modern Unicode standard. Instead, it uses legacy encoding, which is different from Unicode fonts like Mangal. Kruti Dev fonts are available in various styles and sizes (e.g., Kruti Dev 010, Kruti Dev 020). It requires specific software or a keyboard layout to type effectively. How to Download Kruti Dev Font Official Sources: The font can often be downloaded from trusted websites offering Hindi typing tools or fonts. Ensure the site is reputable to avoid downloading malicious software. Steps to Download: Search for "Download Kruti Dev font" online. Visit a trusted source, such as: www.indiatyping.com www....
Post a Comment
Read more

Creating Wages Sheet using Microsoft Excel

  Creating wages sheet using Microsoft Excel The DAYS360 function in Microsoft Excel calculates the number of days between two dates based on a 360-day year (commonly used in financial calculations). A 360-day year assumes each month has 30 days, simplifying interest and payment schedules. Syntax: DAYS360(start_date, end_date, [method]) Parameters: start_date : The starting date (required). Enter it as a valid date or reference a cell containing a date. end_date : The ending date (required). Enter it similarly to the start_date. method : Logical value (optional). FALSE (default): Uses the U.S. (NASD) method for date calculations. TRUE : Uses the European method. Key Differences Between Methods: U.S. (NASD) : Adjusts the start and end dates depending on whether they fall on the 31st of a month. European : Always treats the start and end dates as the 30th of the month if they are the 31st. Example Usage: Example 1: Calculate days using the U.S. method =DAYS360("01/01/2024", ...
1 comment
Read more