另一个动态mysql表

How can i link two mysql tables. One that (already) displays customers records And another table specifically for a commenting system about each record entered.?

I.e Account #1 // displays that particular customer info.. My need is.... I need to have a system to post comments only about that record (Account # 1).. and so on and so on with all future ones.

I have two tables Customers table and Comments table...

Conclusion. .

How can I link them so that I can comment on each customers tables record individually?

Thank you very much

this is my code below....

<?php
include('../includes/mysql_connect.php');

$query = "CREATE TABLE `customers` (

`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

`cust_name` VARCHAR(50) NOT NULL,

PRIMARY KEY (`id`)

)engine=innodb";
if(@mysql_query($query,$dbc)){
echo '<p>Table for customers has been successfully created!</p><br/>';
} else {
echo '' . mysql_error($dbc)  .'<br/>';
}
mysql_close($dbc);
?>

<?php 
include('../includes/mysql_connect.php');
$query = "CREATE TABLE `comments` (

`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

`customer` INT(10) UNSIGNED NOT NULL,

`comment` VARCHAR(255)NOT NULL,

PRIMARY KEY  (`id`),

INDEX `customer` (`customer`)

)engine=innodb";

if(@mysql_query($query,$dbc)){
echo '<p>Table for comments has been successfully created!</p><br/>';
} else {
echo '' . mysql_error($dbc)  .'<br/>';
}
mysql_close($dbc);
?>
/*  ABOVE CODE CREATES THE TABLES */

/* CODE BELOW INSERTS*/

    <?php


if($_SERVER['REQUEST_METHOD'] == 'POST'){
// NEEDS DATABAE CONNECTION
include('includes/mysql_connect.php');
//CREATE VARIBLE IF PROBLEM DOES NOT OCCUR 
$problem =  FALSE;
// VALIDATION
if(!empty($_POST['cust_name'])){
// REAL ESCAPE STRING AGAINST SQL INJECTION
$cust_name = mysql_real_escape_string(trim(strip_tags($_POST['cust_name'])), $dbc);
} else { // IF PROBLEM
echo '<p style="color:red;">Please enter name!</p>';
$problem = TRUE;
}
// IF NO PROBLEM
if(!$problem){
//RUN QUERY
$query = "INSERT INTO `customers` (`cust_name`) values ('$cust_name')";
// EXECUTE QUERY
if(@mysql_query($query, $dbc)){ // NO PROBLEM
echo '<p style="color:blue;">This name has been added!<p>';
} else {// IF PROBLEM
echo '<p style="color:red;">This name was not successfully added!</p>';
}
} // END OF VARIABLE IN NO PROBLEM

mysql_close($dbc); // CLOSING CONNECTION

} // END OF MAIN IF

?>

/* CODE BELOW RECEIVES */

<?php
include('includes/mysql_connect.php');

//$query = 'SELECT * FROM customers';

$query = 'SELECT * FROM customers';
if($y = mysql_query($query,$dbc)){
while($row = mysql_fetch_array($y)){
//echo " {$row['id']} <br/>";


echo "<div id='container'>
<div id='first_wrapper'>
<a style='text-decoration:none'; href=\"fetch_join.php?id={$row['id']}\">
Stolve # " . $row['user_id'] ."
</a></div>";

}
}

mysql_close($dbc);
?>

/* CODE BELOW SELECTS */  

<?PHP 

include('includes/mysql_connect.php');
 /* Select records for specific user based upon their ID */
$query = "SELECT c.`id` as 'customer_id', ct.`id` as 'comment_id', c.`cust_name` as 'customer', ct.`comment` from `customers` c
    inner join `comments` ct on ct.`customer`=c.`id`
     where c.`id`=1";

if($y = mysql_query($query,$dbc)){
while($row = mysql_fetch_array($y)){

echo "{$row['customer']} {$row['comment']}<br/>";

//echo " {$row['id']} <br/>";

}
}

mysql_close($dbc);  

?>

But no matter how many records I enter, I keep on getting all of them instead of just the one for the particuar ID.

Below each record I have entered but the user ID still shows as one.

    1 This is a test for joining tables
    1 this is a second test
    1 third test
    1 another comment here
    1 fourth test
    1 last test 4

I should be able to see only one record per click.

This is my interpretation of your MySQL tables. From what I can see, you are trying to have a system in which you can easily click on a customer and write/view written comments about them. This query will individually get comments along with the customers username from the database for the customer with the 'id' = 1.

SELECT comments.comment, users.username FROM comments, users WHERE comments.customerid = users.customerid AND users.customerid = 1

This is a very generic answer due to the amount of information given. If you have any questions or concerns, please comment!

To answer your question "do both tables have to be created at the same time so their Id's start both from 1?" ~ No.

The sql below creates two tables - the comments table has a keyed field ( customer ) which links to the id field in the customers table.

create table `customers` (
    `id` int(10) unsigned not null auto_increment,
    `cust_name` varchar(50) not null,
    primary key (`id`)
)engine=innodb;

create table `comments` (
    `id` int(10) unsigned not null auto_increment,
    `customer` int(10) unsigned not null,
    `comment` varchar(255) not null,
    primary key (`id`),
    index `customer` (`customer`)
)engine=innodb;

/* Create a dummy / test user in the customers table */
insert into `customers` (`cust_name`) values ('Bob');

/* Insert a dummy / test record in the comments table */
insert into `comments` (`customer`,`comment`) values (1,'Bob is the best customer in the World, ever! However, he rarely pays on time and has bad body odour');

/* Select ALL records from both tables using the customer id as a key */
select c.`id` as 'customer_id', ct.`id` as 'comment_id', c.`cust_name` as 'customer', ct.`comment` from `customers` c
    inner join `comments` ct on ct.`customer`=c.`id`;

/* 
   Note the JOIN type is `inner` - this shows only records where there is
   a value in both. If the JOIN type was `left outer` there would be records 
   for all regardless of whether or not a comment exists for that customer
*/

 /* Select records for specific user based upon their ID */
select c.`id` as 'customer_id', ct.`id` as 'comment_id', c.`cust_name` as 'customer', ct.`comment` from `customers` c
    inner join `comments` ct on ct.`customer`=c.`id`
     where c.`id`=1;

@AndyChavarria: The sql was intended for illustration purposes of how you might link the comments to the customers and was not intended to be run the final version! That said, I tidied up the php a little and made some minor corrections... don't be surprised if there are comments about sql injection though or about using the deprecated mysql_* family of functions. If this is still in the early stages of production / construction then you really ought to think about implementing either mysqli or PDO ~ there would be fewer changes needed with mysqli but there are several subtle syntax differences. PDO is probably more robust and definitely more flexible in the long term - both of which though, when implemented correctly, will prevent SQL injection whereas your code, even with the escaping, is still susceptible to it.

For example, one of the many subtle differences between mysql & mysqli:

mysqli_query( $conn, $sql ) as opposed to mysql( $sql, $conn )

<?php
    include('../includes/mysql_connect.php');

    /* To build the tables: this should only need to be run once! */
    $query = "CREATE TABLE `customers` (
                `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                `cust_name` VARCHAR(50) NOT NULL,
                PRIMARY KEY (`id`)
                )engine=innodb";
    $res=mysql_query( $query, $dbc );
    echo $res ? '<p>Table for customers has been successfully created!</p><br/>' : mysql_error( $dbc )  .'<br/>';

    $query = "CREATE TABLE `comments` (
                `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                `customer` INT(10) UNSIGNED NOT NULL,
                `comment` VARCHAR(255)NOT NULL,
                PRIMARY KEY  (`id`),
                INDEX `customer` (`customer`)
                )engine=innodb";
    $res=mysql_query( $query, $dbc );
    echo $res ? '<p>Table for comments has been successfully created!</p><br/>' : mysql_error($dbc)  .'<br/>';
    mysql_close( $dbc );
?>



<?php
    /* Insert comment */
    if( $_SERVER['REQUEST_METHOD'] == 'POST' ){

        include('includes/mysql_connect.php');

        $problem =  FALSE;

        if( isset( $_POST['cust_name'] ) && !empty( $_POST['cust_name'] ) ){
            $cust_name = mysql_real_escape_string( trim( strip_tags( $_POST['cust_name'] ) ), $dbc );
        } else {
            echo '<p style="color:red;">Please enter name!</p>';
            $problem = TRUE;
        }

        if( !$problem ){
            $query = "INSERT INTO `customers` (`cust_name`) values ('$cust_name')";
            $res=mysql_query( $query, $dbc );
            echo $res ? '<p style="color:blue;">This name has been added!<p>' : '<p style="color:red;">This name was not successfully added!</p>';
        }
        mysql_close($dbc);
    }
?>



<?php
    /* Fetch customers and create hyperlink */
    include('includes/mysql_connect.php');

    $query = 'SELECT * FROM `customers`';
    $res=mysql_query( $query, $dbc );
    if( $res ){

        /* Assumed that the container does not get repeated */
        echo "<div id='container'>";

        while( $row = mysql_fetch_array( $res ) ){

            /* Here you have the same id used for each iteration of the loop.... this is not valid html */
            echo "
                <div id='first_wrapper'>
                    <a style='text-decoration:none' href='fetch_join.php?id={$row['id']}'>Stolve # {$row['user_id']}</a>
                </div>";            
        }
        echo "</div>";
    }

    mysql_close($dbc);
?>



<?php 
    /* Show specific customer ( based upon id ) and associated comments */
    include('includes/mysql_connect.php');

    $id=isset( $_GET['id'] ) ? intval( mysql_real_escape_string( trim( strip_tags( $_GET['id'] ) ), $dbc ) ) : 1;

    $query = "SELECT c.`id` as 'customer_id', ct.`id` as 'comment_id', c.`cust_name` as 'customer', ct.`comment` from `customers` c
    inner join `comments` ct on ct.`customer`=c.`id`
    where c.`id`='{$id}';";

    $res=mysql_query( $query, $dbc );
    if( $res ){
        while( $row = mysql_fetch_array( $res ) ){
            echo "{$row['customer']} {$row['comment']}<br/>";
        }
    } else {
        echo 'No comments for selected user';
    }
    mysql_close( $dbc );  
?>