How to use data of one table in 'where' clause of another table?
Posted
by sahar
on Stack Overflow
See other posts from Stack Overflow
or by sahar
Published on 2010-06-14T06:39:53Z
Indexed on
2010/06/14
6:42 UTC
Read the original article
Hit count: 196
hello, i need ur help guys..i m making website for 'home docor ideas'..i have a log in form(login-form.php) in which when 'log in' and 'password' is inserted,after verification through login-execute.php, redirected to viewOrder.php where user can view all of the orders ordered by clients.. all is fine up till here.. but what i want is,when user get logged in ,he view only that order which is ordered by him not all customer's orders.. two tables are there in database: members and order_insert.. in 'members' table, login and password is stored and in 'order_insert',orders of customers is stored.. codes of these three pages is as follows..
.........................
login-form.php
.........................
<form id="loginForm" name="loginForm" method="post" action="login-exec.php">
<table width="300" border="0" align="center" cellpadding="2" cellspacing="0">
<tr>
<td width="112"><b>Login</b></td>
<td width="188"><input name="login" type="text" class="textfield" id="login" /></td>
</tr>
<tr>
<td><b>Password</b></td>
<td><input name="password" type="password" class="textfield" id="password" /></td>
</tr>
<tr>
<td> </td>
<td><input type="submit" name="Submit" value="Login" /></td>
</tr>
</table>
</form>
......................... login-execute.php .........................
<?php
//Start session
session_start();
//Include database connection details
require_once('config.php');
//Array to store validation errors
$errmsg_arr = array();
//Validation error flag
$errflag = false;
//Connect to mysql server
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if(!$link) {
die('Failed to connect to server: ' . mysql_error());
}
//Select database
$db = mysql_select_db(DB_DATABASE);
if(!$db) {
die("Unable to select database");
}
//Function to sanitize values received from the form. Prevents SQL injection
function clean($str) {
$str = @trim($str);
if(get_magic_quotes_gpc()) {
$str = stripslashes($str);
}
return mysql_real_escape_string($str);
}
//Sanitize the POST values
$login = clean($_POST['login']);
$password = clean($_POST['password']);
//Input Validations
if($login == '') {
$errmsg_arr[] = 'Login ID missing';
$errflag = true;
}
if($password == '') {
$errmsg_arr[] = 'Password missing';
$errflag = true;
}
//If there are input validations, redirect back to the login form
if($errflag) {
$_SESSION['ERRMSG_ARR'] = $errmsg_arr;
session_write_close();
header("location: login-form.php");
exit();
}
//Create query
$qry="SELECT * FROM members WHERE login='$login' AND passwd='".md5($_POST['password'])."'";
$result=mysql_query($qry);
//Check whether the query was successful or not
if($result) {
if(mysql_num_rows($result) == 1) {
//Login Successful
session_regenerate_id();
$member = mysql_fetch_assoc($result);
$_SESSION['SESS_MEMBER_ID'] = $member['member_id'];
$_SESSION['SESS_FIRST_NAME'] = $member['firstname'];
$_SESSION['SESS_LAST_NAME'] = $member['lastname'];
session_write_close();
header("location: viewOrder.php");
exit();
}else {
//Login failed
header("location: login-failed.php");
exit();
}
}else {
die("Query failed");
}
?>
............................. viewOrder.php ..............................
<html>
<body bgcolor="#FFFFFF" >
<?
$host="localhost"; // Host name
$username="root"; // Mysql username
$password=""; // Mysql password
$db_name="mydatabase"; // Database name
$tbl_name="order_insert"; // Table name
$tbl_name2="members";
// connect to server and databases
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$result = mysql_query("SELECT * FROM $tbl_name ");
print "<center>";
$output .= "<table width=1100 border=1 bordercolor=black>";
$output .= "<tr align=center><td>ID</td><td>First Name</td><td>Last Name</td><td>E Mail</td><td> City </td><td> Country </td><td> Phone</td><td>Decoration Type</td><td>Service Description</td><td>Budget</td><td>Update</td><td>Delete</td></tr>";
$output .= "<th></th><th></th>";
$output .= "</tr>\n\n";
while ($row = mysql_fetch_assoc($result)){
$output .= "<tr>\n";
foreach ($row as $col=>$val){
$output .= " <td>$val</td>\n";
} // end foreach
$keyVal = $row["id"];
$output .= "<td><a href='update.php?ID=$row[orderId]' >Update </a></td>";
$output .= "<td><a href='delete.php?ID=$row[orderId]' >Delete </a></td>";
$output .= "</tr>\n\n";
}// end while
$output .= "</table></center>";
print "$output";
?> <br>
<br>
<center><table > <tr><td>
<form action="home.php"><font color="#FF0000"><input type="submit" name="btn" style="color:#CC0000" value="<--Back" ></font></form></td></tr></table></center>
</body>
</html>
..... your help and suggestions will be appreciated
© Stack Overflow or respective owner
