MySQL Join Query

MySQL Join Query in PHP

Create_Table

<?php

//create table : table name: example

// Make a MySQL Connection
mysql_connect(“localhost”, “root”, “root”) or die(mysql_error());
mysql_select_db(“test”) or die(mysql_error());

// Create a MySQL table in the selected database
mysql_query(“CREATE TABLE Family(
Position VARCHAR(20) primary key,
Age INT(2)NOT NULL)”)or die(mysql_error());

mysql_query(“CREATE TABLE Food(
Meal VARCHAR(20),
Position VARCHAR(20) NULL,
CONSTRAINT FOREIGN KEY (Position) REFERENCES family (Position) ON DELETE CASCADE ON UPDATE CASCADE)”)or die(mysql_error());
echo “Table Created!”;

?>

Insert_Rows

<?php
mysql_connect(“localhost”, “root”, “root”) or die(mysql_error());
mysql_select_db(“test”) or die(mysql_error());

mysql_query(“INSERT INTO Family (Position, Age) VALUES (‘Dad’, ’41’), (‘Mom’, ’45’), (‘Daughter’, ’17’),(‘Dog’,’3′)”) or die(mysql_error());
mysql_query(“INSERT INTO Food (Meal,Position) VALUES (‘Pizza’, ‘Dad’)”) or die(mysql_error());
mysql_query(“INSERT INTO Food (Meal,Position) VALUES (‘Salad’ ,’Mom’)”) or die(mysql_error());
mysql_query(“INSERT INTO Food (Meal,Position) VALUES (‘Spinach Soup’, NULL)”) or die(mysql_error());
mysql_query(“INSERT INTO Food (Meal,Position) VALUES (‘Tacos’,’Dad’)”) or die(mysql_error());
mysql_query(“INSERT INTO Food (Meal,Position) VALUES (‘Biscuits’,’Dog’)”) or die(mysql_error());
echo “Data Inserted”;
?>

Join

<?php
// Make a MySQL Connection

mysql_connect(“localhost”, “root”, “root”) or die(mysql_error());
mysql_select_db(“test”) or die(mysql_error());

// Construct our join query
$query = “SELECT family.Position, food.Meal FROM family, food WHERE family.Position = food.Position”;

$result = mysql_query($query) or die(mysql_error());
// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
echo $row[‘Position’]. ” – “. $row[‘Meal’];
echo “<br />”;
}
?>

 

Share

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.