Okay.. this is the most updated information here. I need to generate comments for each ID as they increment. I.E I have customer 1. he/ she signs up and then I have the ability to work on their account for whatever I am doing with it.. I need to leave comments on that particular page. " THAT I CAN DO " A second customer signs up, then on the link for user 2 I can access their account and leave comments for admin purposes only..
"THIS I CAN NOT DO BECAUSE IN THE INSERT I AM ONLY POINTING AT ID # 1.. I DO NOT KNOW HOW TO POINT AT A NEW ID "
3rd customer signs up... and so on and so on.. The code is below. I have included all the PHP code here also.. you can test it on your end with your own data connection.
/* CREATING THE TABLE Customers*/
<?php
include('../includes/mysql_connect.php');
$query = "CREATE TABLE `Customers` (
`customer_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`cust_name` VARCHAR(50) NOT NULL,
INDEX (`customer_id`),
PRIMARY KEY (`customer_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);
?>
/* CREATING THE TABLE Comments*/
<?php
include('../includes/mysql_connect.php');
$query = "CREATE TABLE `Comments` (
`comment_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`customer_id` INT(10) UNSIGNED NOT NULL,
`comment` VARCHAR(255) NOT NULL,
PRIMARY KEY (`comment_id`),
INDEX (`customer_id`),
FOREIGN KEY (`customer_id`) REFERENCES `Customers`
(`customer_id`)
)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);
?>
/* So now that I have already created the tables, I work on getting the link for each customer. */
<?php
include('includes/mysql_connect.php');
$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=\"http://www.mywebsite.com/master/account.php?id={$row['customer_id']}\">
Customer # " . $row['customer_id'] ."
</a></div>";
}
}
mysql_close($dbc);
?>
/* The next step is to retrieve the info From the Customers table */
/* Select records for specific user based upon their ID */
<?php
include('includes/mysql_connect.php');
if(isset($_GET['id']) && is_numeric($_GET['id'])) {
$query = "SELECT * FROM Customers WHERE `customer_id`={$_GET['id']}";
if($x = mysql_query($query,$dbc)){
$row = mysql_fetch_array($x);
echo "{$row['cust_name']}<br/>";
echo '<input type="hidden" name="id" value="' . $_GET['id'] . '"/>';
}
}
mysql_close($dbc);
?>
/* My problem comes here on the comments table */
if($_SERVER['REQUEST_METHOD'] == 'POST'){
// NEEDS DATA CONNECTION
include('includes/mysql_connect.php');
// VARIABLE IF PROBLEM DOES NOT OCCUR
$problem = FALSE;
// VALIDATION HERE
if(!empty($_POST['comment'])){
$comment = mysql_real_escape_string(trim(strip_tags($_POST['comment'])), $dbc);
} else { // IF PROBLEM
echo '<p style="color:red;">Please enter comment!</p>';
$problem = TRUE;
}
//IF NO PROBLEM
if(!$problem){
// RUN QUERY
$query = "INSERT INTO `Comments`(`customer_id`,`comment`) VALUES (1,'$comment')";
// EXECUTE QUERY
if(@mysql_query($query,$dbc)){
echo '<p style="color:blue;">This comment has been added!<p>';
} else {// IF PROBLEM
echo '<p style="color:red;">Could not retrieve the information because <br/>
'. mysql_error($dbc) .'.</p><p>The query run was '.$query.' </p>';
}
} // END OF VARIABLE IN NO PROBLEM
mysql_close($dbc); // CLOSING CONNECTION
} // END OF MAIN IF
?>
<html>
<head><title>DUMMY INSERTS</title></head>
<body>
<form action="self.php" method="post">
<textarea name="comment" cols="70" rows="10"/></textarea>
<br/>
<input type="submit" name="submit" value="Submit!"/>
</form>
</body>
</html>
PLEASE HELP ME OUT ON THIS ONE.. I HAVE BEEN ALREADY A WEEK ON THIS AND I AM TIRED.. THX
Per davejal's comment: The FK constraint says every value in comments customers_id must be a value in customers customers_id.
So Sean said: First INSERT INTO customers (cust_name) values ('$cust_name') which uses customers' AUTO_INCREMENT for customers_id. Then INSERT INTO comments (customers_id, comment) VALUES (LAST_INSERT_ID(), '$comment') which uses LAST_INSERT_ID for the customers customers_id value chosen by that AUTO_INCREMENT and comments' AUTO_INCREMENT for comments_id.
Per Sean & philipxy: The customer insert AUTO_INCREMENT makes you a new customer id. If you want it right away then use LAST_INSERT_ID(); otherwise you get their id when they sign in or admin gives it. Per Ryan Vincent's sqlfiddle: A version modified to use LAST_INSERT_ID().
Per Sean & Ryan Vincent: As you are using PHP for i/o show it in your question. If you GET a customer id whose comments you want to process (echo '<input type="hidden" name="customer_id" value="' . $_GET['customer_id'] . '"/>';) inside your form) then that is the value to use in your comment INSERT or SELECT via $_GET['customer_id'].
All such minimal fiddled code, input, output (possibly including debug output during execution, although that can affect the manifestation of a bug) and error(s) should be in every question about fixing code. Read and act on How to create a Minimal, Complete, and Verifiable example .