Query Two Tables With One Query

Welcome to Webune Support Forums.

Webune Offers MySQL and PHP hosting. If you are interested in putting PHP and MySQL on your website, signup with us today. We offer excellent support service.

In today's tutorial we are going to show you how you can SELECT fields from two tables with a common field. NOTE: for this to work you both tables must have a common field, meaning that the field must be named the same.

Lets start by creating two tables, the first table is going to be our CATEGORY table, the category table is going to have the name of the category for each topic. the second table is going to be the TOPIC table, the topic table will contain the topic information

step 1 - First we will need to create our database, so create a new open your database using phpmyadmin panel

step 2 - copy and paste the following mysqldump to create our two tables:

-- phpMyAdmin SQL Dump
-- version 2.6.0-pl3
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Jan 28, 2010 at 11:04 AM
-- Server version: 4.1.7
-- PHP Version: 5.0.3RC2-dev
-- 
-- Database: `test`
-- 

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

-- 
-- Table structure for table `category`
-- 

DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
  `category_id` int(11) NOT NULL auto_increment,
  `category_name` varchar(25) NOT NULL default '',
  PRIMARY KEY  (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

-- 
-- Dumping data for table `category`
-- 

INSERT INTO `category` VALUES (1, 'HOSTING');
INSERT INTO `category` VALUES (2, 'DOMAINS');

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

-- 
-- Table structure for table `topic`
-- 

DROP TABLE IF EXISTS `topic`;
CREATE TABLE `topic` (
  `topic_id` int(11) NOT NULL auto_increment,
  `category_id` int(11) NOT NULL default '0',
  `topic_name` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`topic_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

-- 
-- Dumping data for table `topic`
-- 

INSERT INTO `topic` VALUES (1, 1, 'This is a Hosting Category Topic');
INSERT INTO `topic` VALUES (2, 2, 'This is a Domains Category Topic')

step 3. now that we have created our database tables, we need a PHP file to query both tables at the same time.

step 4. I am using a windows PC to create this tutorial so i will be using notepad as my text editor. you can you whatever text editor you want.

so lets start by creating our file. for the purpose of this tutorial, we are going to call our file, webune-query.php

open a blank notepad, copy and paste the following PHP code:

<?php
# SCRIPT CREATED BY WEBUNE.COM
if($_GET['topic_id']){
	################ CONFIGURATION  ##############
	$Config['hostname'] = 'localhost';
	$Config['user'] = 'WebuneUser';
	$Config['pasword'] = 'MyPassword';
	$Config['dbname'] = 'webune';
	##### STOP CONFIGURATION HERE ################
	# CONNECT TO webune DATABASE 
	$db = mysql_connect($Config['hostname'], $Config['user'], $Config['pasword']);
	mysql_select_db($Config['dbname'],$db);
	$sql ="SELECT c.category_name, t.topic_name FROM category c, topic t WHERE c.category_id = t.category_id AND topic_id = '".$_GET['topic_id']."'";
		$result = mysql_query($sql ,$db);
		if ($row = mysql_fetch_array($result))  {
					echo 'Category Table : [category_name] value = <strong>'.$row['category_name'].'</strong><BR><BR>';
					echo 'Topic Table [topic_name]: value = <strong>'.$row['topic_name'].'</strong><BR><BR>';
		}else{
			echo '<br>SQL ERROR LINE: '.__LINE__.'<br><span style="color:red">'.mysql_error().'</span><hr><pre>';print_r($sql);echo '</pre>';exit;	
		}
	echo '<HR>';
}
	echo '<a href="'.$_SERVER['PHP_SELF'].'?topic_id=1">click here to test topic 1</a><BR><BR>';
	echo '<a href="'.$_SERVER['PHP_SELF'].'?topic_id=2">click here to test topic 2</a>';
echo '<BR><BR>For support visit this support page: <BR><a href="http://www.webune.com/forums/query-two-tables-with-one-query.html">http://www.webune.com/forums/query-two-tables-with-one-query.html</a><BR><BR>';
echo '<BR><BR><p>Script Provided By <a href="http://www.webune.com">Webune.com</a> <BR><BR><a href="http://www.webune.com"><img src="http://www.webune.com/images/headers/default_logo.jpg" border="0" alt="Script by Webune"></a></p>';
?>

step 5. save the file as webune-query.php

step 6. IMPORTANT: be sure to change the configuration to your. you will need:

$Config['hostname'] = 'localhost';
$Config['user'] = 'WebuneUser';
$Config['pasword'] = 'MyPassword';
$Config['dbname'] = 'webune';


$Config['hostname'] (this is usually localhost, but if you have special requirement, then put the hostname)

$Config['user'] ( this is your mysql username)

$Config['pasword'] (this is the username password)

$Config['dbname'] = 'webune'; (this is the database name)

step 7. now that you have created webune-query.php test it.

NOTE: you must have PHP and MYSQL on your website for this to work. If you dont have PHP/MySQL signup up today with Webune Hosting.