SELECT thread_id, content FROM comments
JOIN thread ON(thread_id = thread.id)
WHERE thread_id = 1<?php
// instantiation of MySQLi (does connecion, too)
$db = new mysqli( 'localhost', 'root', 'mysql root password', 'test' );
// get via thread ID
if( isset( $_GET['id'] ) )
{
$join_query = 'SELECT thread_id, title, content FROM post
JOIN thread on(thread.id = post.thread_id)
WHERE thread_id=?
order by post.id';
// get all threads and posts
} else {
$join_query = 'SELECT thread_id, title, content FROM post
JOIN thread on(thread.id = post.thread_id)
order by thread.id, post.id';
}
//pre-defined output.
$output = '';
// If this query cannot be prepared
if(! $stmt = $db->prepare( $join_query ) )
{
//Query cannot be prepared.. show why:
die( 'MySQLi Error:' . $db->error );
}
//Note: If we've Made it this far, query can be prepared.
if( isset( $_GET['id'] ) )
{
//put $_GET['id'] in query the right way:
$stmt->bind_param( 'i', $_GET['id'] );
}
//execute prepared query:
$stmt->execute();
//bind $title, and $content to the results of query
$stmt->bind_result( $thread_id, $title, $content );
//define last_thread_id to nothing..
$last_thread_id = 0;
//fetch results from prepared query.
while( $stmt->fetch() )
{
//If this is the first post in the thread:
if( $thread_id != $last_thread_id )
{
//Begin thread:
$output .= '<hr />';
$output .= '<fieldset>';
$output .= '<legend style="font-weight:bold">' . $title . '</legend>';
//not the first post, must be a reply:
} else {
$output .= '<fieldset>';
$output .= '<legend>Reply, </legend>';
}
//add the post itslef
$output .= $content;
//close the fieldset
$output .= '</fieldset>';
//define $last_thread_id as $thread_id for next loop.
$last_thread_id = $thread_id;
}
//close the prepared query
$stmt->close();
//close the connection, we're done with MySQLi.
$db->close();
?>
<html>
<head>
<title>Join Test</title>
<style type="text/css">
fieldset {
width:250px;
-moz-border-radius:1em;
-webkit-border-radius:1em;
border-radius:1em;
}
</style>
</head>
<body>
<h1>Join Test</h1>
<?php echo $output; ?>
</body>
</html>-- phpMyAdmin SQL Dump
-- version 3.3.9
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 24, 2011 at 07:23 PM
-- Server version: 5.5.8
-- PHP Version: 5.3.5
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `test`
--
-- --------------------------------------------------------
--
-- Table structure for table `post`
--
CREATE TABLE IF NOT EXISTS `post` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`thread_id` int(11) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `post`
--
INSERT INTO `post` (`id`, `thread_id`, `content`) VALUES
(1, 1, 'Here''s a post in thread one'),
(2, 1, 'Here''s another post in thread 1'),
(3, 2, 'Here''s a post in Thread TWo'),
(4, 2, 'Another in Thread TWo');
-- --------------------------------------------------------
--
-- Table structure for table `thread`
--
CREATE TABLE IF NOT EXISTS `thread` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `thread`
--
INSERT INTO `thread` (`id`, `title`) VALUES
(1, 'This is Thread One'),
(2, 'This is Thread Two');ORDER BY date(Note: You can order any query- join or not.)
http://www.techrepublic.com/article/sql-basics-query-multiple-tables/1050307 said:
:
