Page 1 of 1

PHP MYSQL Advanced Query - not working

Posted: Thu Mar 31, 2011 7:08 am
by ibrafiqb
Basically I have a table called orders:

Order_ID [PK]
Customer_ID[FK] --------------> Referencing a table called Proj_Customer_Details
Event_ID[FK]------------------> Referencing a table called Proj_Event


When someone selects an event and checkout, it adds it to the orders table correctly as it should.

When I echo the contents of the orders table on a "view orders page" it echo's out the contents of the orders table too.

But instead of it echoing out the Event_ID, I would like it to be able to echo out the Event_ID's corresponding Event_Title. I have tried over several days now and to no avail. I have posted my code below if you can help it would be great as it would help for a customer to see their previous order with meaningful data rather than the resultant set of id's.

Thanks

Code: Select all

<?php
session_start(); 
if (isset($_SESSION["authenticatedUser"])) 
{ 
  $_SESSION["message"] = "You are already logged in as ". $_SESSION['authenticatedUser']; 
} 

?>
<?php
require_once('inc/mysql.class.php'); 
require_once('inc/global.inc.php'); 
require_once('inc/functions.inc.php'); 
require_once('connection.php');
?> 
<?php
$query111 = "select Customer_ID from Proj_Customer_Details where email = '$_SESSION[authenticatedUser]'"; 
//echo "$query111";
$result111 = mysql_query ($query111) or die (mysql_error()); 
if (mysql_num_rows($result111)>0)  
{ 
while($row111=mysql_fetch_array($result111)) 
{ 
$customer_idt = $row111['Customer_ID']; 
} 
}
$query1121 = "select * from Proj_Order where Customer_ID = '$customer_idt'";
$result1121 = mysql_query ($query1121) or die (mysql_error());
echo "<table>";
	echo"<tr><td>OrderID</td><td>EventID</td><td>CustomerID</td><td>QTY</td><td>TotalPrice</td><td>Date</td></tr>";
while ($row22 = @ mysql_fetch_array($result1121)) {
	
	echo "<tr>";
	echo "<td>".$row22["Order_ID"]."</td>"; 
   echo "<td>".$row22["Event_ID"]."</td>"; 
   echo "<td>".$row22["Customer_ID"]."</td>"; 
   echo "<td>".$row22["qty"]."</td>";
   echo "<td>&pound;".$row22["Order_TotalPrice"]."</td>";
   echo "<td>".$row22["Order_Date"]."</td>";
   echo "</tr>"; 
} 
echo "<tr><td></td><td></td><td></td><td></td><td></td><td></td></tr>";
echo "</table>";
?>

Re: PHP MYSQL Advanced Query - not working

Posted: Thu Mar 31, 2011 7:55 am
by Darhazer
You need to join the tables:
$query1121 = "select * from Proj_Order INNER JOIN Proj_Customer_details USING (CustomerID) INNER JOIN Proj_Event USING (EventID) where Proj_Order.Customer_ID = '$customer_idt'";

Re: PHP MYSQL Advanced Query - not working

Posted: Fri Apr 01, 2011 5:23 am
by ibrafiqb
Darhazer wrote:You need to join the tables:
$query1121 = "select * from Proj_Order INNER JOIN Proj_Customer_details USING (CustomerID) INNER JOIN Proj_Event USING (EventID) where Proj_Order.Customer_ID = '$customer_idt'";
Thanks mate... it worked a treat, i have ran into another problem at the moment, basically i want a user to select a seat from a seat map for that order where they are sitting:

however my problem lies in when the seat is actually selected, it is finding the users id but not the order id; as the user id being the same for the current logged in user but the order ids are different with it being that a customer can have multiple orders.

I will post the code for the orders page where the user will click a particular order which takes you to a seatmap page, once the user clikcs the seat and clicks reserve it sends you to a seat confirmation page with correctly echo's out the seat id and the customer id but not the order id for the order that the seat has been selected, any help would be appreciated:

confirmedorders.php

Code: Select all

<?php
session_start(); 
if (isset($_SESSION["authenticatedUser"])) 
{ 
  $_SESSION["message"] = "You are already logged in as ". $_SESSION['authenticatedUser']; 
} 

?>
<?php
require_once('inc/mysql.class.php'); 
require_once('inc/global.inc.php'); 
require_once('inc/functions.inc.php'); 
require_once('connection.php');
?> 
<?php
$query111 = "select Customer_ID from Proj_Customer_Details where email = '$_SESSION[authenticatedUser]'"; 
//echo "$query111";
$result111 = mysql_query ($query111) or die (mysql_error()); 
if (mysql_num_rows($result111)>0)  
{ 
while($row111=mysql_fetch_array($result111)) 
{ 
$customer_idt = $row111['Customer_ID']; 
} 
}
$query1121 = "select * from Proj_Order where Customer_ID = '$customer_idt'";
$result1121 = mysql_query ($query1121) or die (mysql_error());
echo "<table>";
	echo"<tr><td>OrderID</td><td>EventID</td><td>CustomerID</td><td>QTY</td><td>TotalPrice</td><td>Selected Seat</td><td>Date</td></tr>";
while ($row22 = @ mysql_fetch_array($result1121)) {
	
	echo "<tr>";
	echo "<td>".$row22["Order_ID"]."</td>"; 
   echo "<td>".$row22["Event_ID"]."</td>"; 
   echo "<td>".$row22["Customer_ID"]."</td>"; 
   echo "<td>".$row22["qty"]."</td>";
   echo "<td>&pound;".$row22["Order_TotalPrice"]."</td>";
   echo "<td>".$row22["Selected_Seat"]."</td>";
    echo "<td><a href=seats.php?Order_ID=".$row22['Order_ID'].">Select Seat</a></td>";
   echo "<td>".$row22["Order_Date"]."</td>";
   echo "</tr>"; 
} 
echo "<tr><td></td><td></td><td></td><td></td><td><td></td></td><td></td></tr>";
echo "</table>";
?>
seats.php ------> where the actual seat is selected

Code: Select all

<?php 
//Start - so we can use session variables 
session_start(); 
// Check if we have already created a authenticated session 
if (isset($_SESSION["authenticatedUser"])) 
{ 
  $_SESSION["message"] = "You are already logged in as ". $_SESSION['authenticatedUser']; 
   
} 
// No session established, no POST variables 
 //Display the login page  
else if (!isset($_SESSION["authenticatedUser"])) 
{ 
     //$_SESSION["message"] = "Please Login"; 
     header("Location: {$_SERVER['HTTP_REFERER']}"); //Go back and login 
} 
$_SESSION["OrderID"];
//If this page hasn't been redirected (we are allowed in) then we can display 
?> 
<?php
include ("connection.php");
?>
<html>
<head>
	<title>Tickets</title>
	<style>
		* {
			font-size: 11px;
			font-family: arial;
		}
	</style>
	<script>

		function reserveSeats() {
			
			var selectedList = getSelectedList('Reserve Seats');
			
			if (selectedList) {
				if (confirm('Do you want to reserve selected seat/s ' + selectedList + '?')) {
					document.forms[0].oldStatusCode.value=0;
					document.forms[0].newStatusCode.value=1;
					document.forms[0].action='bookseats.php';
					document.forms[0].submit();
				} else {
					clearSelection();
				}
			}
		}


		function cancelSeats() {
			
			var selectedList = getSelectedList('Cancel Reservation');
			
			if (selectedList) {
				if (confirm('Do you want to cancel reserved seat/s ' + selectedList + '?')) {
					document.forms[0].oldStatusCode.value=1;
					document.forms[0].newStatusCode.value=0;
					document.forms[0].action='bookseats.php';
					document.forms[0].submit();
				} else {
					clearSelection();
				}
			}
		}


		function confirmSeats() {
			
			var selectedList = getSelectedList('Confirm Reservation');
			
			if (selectedList) {
				if (confirm('Do you want to confirm reserved seat/s ' + selectedList + '?')) {
					document.forms[0].oldStatusCode.value=1;
					document.forms[0].newStatusCode.value=2;
					document.forms[0].action='bookseats.php';
					document.forms[0].submit();
				} else {
					clearSelection();
				}
			}
		}


		function getSelectedList(actionSelected) {
			
			// get selected list
			var obj = document.forms[0].elements;
			var selectedList = '';
			for (var i = 0; i < obj.length; i++) {
				if (obj[i].checked && obj[i].name == 'seats[]') {
					selectedList += obj[i].value + ', ';
				}
			}
			
			// no selection error
			if (selectedList == '') {
				alert('Please select a seat before clicking ' + actionSelected);
				return false;
			} else {
				return selectedList;
			}

		}
		
		function clearSelection() {
			var obj = document.forms[0].elements;
			for (var i = 0; i < obj.length; i++) {
				if (obj[i].checked) {
					obj[i].checked = false;
				}
			}
		}


		function refreshView() {
			clearSelection();
			document.forms[0].action='<?php echo $_SERVER['PHP_SELF']; ?>';
			document.forms[0].submit();
		}

	</script>
</head>
<body>
<table>
<tr><td width="100%" align="center">
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

<input type="hidden" name="oldStatusCode" value=""/>
<input type="hidden" name="newStatusCode" value=""/>
  
<table width='100%' border='0'>
	<tr><td align='center'>
		<input type='button' value='Refresh View' onclick='refreshView();'/>
	</td></tr>
</table>
</td></tr>
<tr><td width="100%" align="center">
<table width='100%' border='0'>
	<tr><td align='center'>
		<input type='button' value='Reserve Seats' onclick='reserveSeats()'/>
		&nbsp;</td></tr>
</table>
</td></tr>
<tr><td width="100%" align="center">
<table width='100%' border='0'>
	<tr><td align='center'>
		<input type='button' value='Clear Selection' onclick='clearSelection()'/></td>
	</tr>
</table>
</td></tr>
<tr><td width="100%" align="center">
<?php

$query111 = "select Customer_ID from Proj_Customer_Details where email = '$_SESSION[authenticatedUser]'"; 

$result111 = mysql_query ($query111) or die (mysql_error()); 
if (mysql_num_rows($result111)>0)  
{ 
while($row111=mysql_fetch_array($result111)) 
{ 
$customer_idt = $row111['Customer_ID']; 
} 
}

$query222 = "select Order_ID from Proj_Order where Customer_ID = '$customer_idt'"; 

$result222 = mysql_query ($query222) or die (mysql_error()); 
if (mysql_num_rows($result222)>0)  
{ 
while($row222=mysql_fetch_array($result222)) 
{ 
$order_idt = $row222['Order_ID']; 
} 
}

$cart = $_SESSION['OrderID'];
$cart = $_GET['Order_ID'];


$query = "SELECT * from seats order by rowId, columnId desc";
$result = mysql_query($query);
$prevRowId = null;
$seatColor = null;
$tableRow = false;
//echo $result;
echo "<table width='100%' border='0' cellpadding='3' cellspacing='3'>";
while (list($rowId, $columnId, $status, $updatedby) = mysql_fetch_row($result))
{
	if ($prevRowId != $rowId) {
		if ($rowId != 'A') {
			echo "</tr></table></td>";
			echo "\n</tr>";
		}
		$prevRowId = $rowId;
		echo "\n<tr><td align='center'><table border='1' cellpadding='8' cellspacing='8'><tr>";
	} else {
		$tableRow = false;
	}
	if ($status == 0) {
		$seatColor = "lightgreen";
	} else if ($status == 1 && $updatedby == 'user1') {
		$seatColor = "FFCC99";
	} else if ($status == 1 && $updatedby == 'user2') {
		$seatColor = "FFCCFF";
	} else if ($status == 2 && $updatedby == 'user1') {
		$seatColor = "FF9999";
	} else if ($status == 2 && $updatedby == 'user2') {
		$seatColor = "CC66FF";
	} else {
		$seatColor = "red";
	}

	echo "\n<td bgcolor='$seatColor' align='center'>";
	echo "$rowId$columnId";
	if ($status == 0 || ($status == 1 && $updatedby == '$customer_idt')) {
		echo "<input type='checkbox' name='seats[]' value='$rowId$columnId'></checkbox>";
	}
	echo "</td>";
		if (($rowId == 'A' && $columnId == 7) 
			|| ($rowId == 'B' && $columnId == 9) 
			|| ($rowId == 'C' && $columnId == 9) 
			|| ($rowId == 'D' && $columnId == 10) 
			|| ($rowId == 'E' && $columnId == 8) 
			|| ($rowId == 'F' && $columnId == 5) 
			|| ($rowId == 'G' && $columnId == 13) 
			|| ($rowId == 'H' && $columnId == 14) 
			|| ($rowId == 'I' && $columnId == 14) 
			|| ($rowId == 'J' && $columnId == 12) 
			|| ($rowId == 'K' && $columnId == 14) 
			|| ($rowId == 'L' && $columnId == 13) 
			|| ($rowId == 'M' && $columnId == 9)) {
			// This fragment is for adding a blank cell which represent the "center aisle"
			echo "<td>&nbsp;</td>";
		}
}

echo "</tr></table></td>";
echo "</tr>";
echo "</table>";

/* Close connection to database server. */
mysql_close();
?>
</td></tr>
<tr><td>&nbsp;</td></tr>
<tr><td width="100%" align="center">&nbsp;</td></tr>
<tr><td>&nbsp;</td></tr>
<tr><td width="100%" align="center">&nbsp;</td></tr>
</table>
</form>
</body>
</html>
and the bookseats.php which runs the query to add that seat to that order (supposedly!)

Code: Select all

<?php
//Start - so we can use session variables 
session_start();
// Check if we have already created a authenticated session 
if (isset($_SESSION["authenticatedUser"])) 

{ 
  $_SESSION["message"] = "You are already logged in as ". $_SESSION['authenticatedUser']; 
   
} 

// No session established, no POST variables 
 //Display the login page  
else if (!isset($_SESSION["authenticatedUser"])) 
{ 
     //$_SESSION["message"] = "Please Login"; 
     header("Location: {$_SERVER['HTTP_REFERER']}"); //Go back and login 
} 
 

//If this page hasn't been redirected (we are allowed in) then we can display 
?> 
<?php
//include ("connection.php");
require_once('inc/mysql.class.php');
// Include database connection
require_once('inc/global.inc.php');
// Include functions
require_once('inc/functions.inc.php');
//$orderid = $_GET['Order_ID'];
$query111 = "select Customer_ID from Proj_Customer_Details where email = '$_SESSION[authenticatedUser]'"; 

$result111 = mysql_query ($query111) or die (mysql_error()); 
if (mysql_num_rows($result111)>0)  
{ 
while($row111=mysql_fetch_array($result111)) 
{ 
$customer_idt = $row111['Customer_ID']; 
} 
}
	
	
		$newStatusCode = $_POST['newStatusCode'];
		$oldStatusCode = $_POST['oldStatusCode'];

		

		// prepare select statement
		$selectQuery = "SELECT rowId, columnId from seats where (";
		$count = 0;
		foreach($_POST['seats'] AS $seat) {
			if ($count > 0) {
				$selectQuery .= " || ";
			}
			$selectQuery .= " ( rowId = '" . substr($seat, 0, 1) . "'";
			$selectQuery .= " and columnId = " . substr($seat, 1) . " ) ";
			$count++;
		}
		$selectQuery .= " ) and status = $oldStatusCode";
		if ($oldStatusCode == 1) {
			$selectQuery .= " and Customer_ID = '$customer_idt'";
			//$selectQuery .= " and Order_ID = '$orderid'";
		}
		
		
		//echo $selectQuery;
		
		// execute select statement
		$result = mysql_query($selectQuery);
		
		//echo $result;
		//echo $result;
		
		$selectedSeats = mysql_num_rows($result);
		//echo "<br/>" . $selectedSeats;
		
		if ($selectedSeats != $count) {
			$problem = "<h3>There was a problem executing your request. No seat/s were updated.</h3>";
			$problem .= "Possible problems are:";
			$problem .= "<ul>";
			$problem .= "<li>Another process was able to book the same seat while you were still browsing.</li>";
			$problem .= "<li>You were trying to Confirm an unreserved Seat.</li>";
			$problem .= "<li>You were trying to Cancel an unreserved Seat.</li>";
			$problem .= "<li>You were trying to Reserve a reserved Seat.</li>";
			$problem .= "<li>There was a problem connecting to the database.</li>";
			$problem .= "</ul>";
			$problem .= "<a href='seats.php'>View Seat Plan</a>";
			die ($problem);
		}
		
		// prepare update statement
		$newStatusCode = $_POST['newStatusCode'];
		$oldStatusCode = $_POST['oldStatusCode'];
		$confirmedseat ="UPDATE Proj_Order set Selected_Seat = '$seat' where Customer_ID = '$customer_idt' AND Order_ID = '21';";
		echo ("$confirmedseat");
		
		$updateQuery = "UPDATE seats set status=$newStatusCode, Customer_ID='$customer_idt' where ( ";
		$count = 0;
		foreach($_POST['seats'] AS $seat) {
			if ($count > 0) {
				$updateQuery .= " || ";
			}
			$updateQuery .= " ( rowId = '" . substr($seat, 0, 1) . "'";
			$updateQuery .= " and columnId = " . substr($seat, 1) . " ) ";
			$count++;
		}
		$updateQuery .= " ) and status = $oldStatusCode";
		if ($oldStatusCode == 1) {
			$updateQuery .= " and Customer_ID = '$customer_idt'";
		}
		
		// perform update
		$result = mysql_query($updateQuery);
		//echo ("$result");
		$updatedSeats = mysql_affected_rows();
		$result2 = mysql_query($confirmedseat);
		//echo ("$result2");
		$updatedSeats2 = mysql_affected_rows();

		if ($result && $updatedSeats == $count) {
			//$mysql->commit();
			echo "<h3>";
			echo "You have successfully updated $updatedSeats seat/s: ";
			echo "[";
			foreach($_POST['seats'] AS $seat) {
				$rowId = substr($seat, 0, 1);
				$columnId = substr($seat, 1);
				echo $rowId . $columnId . ", ";	
			}
			echo "]";
			echo "...</h3>";
			
		} else {
			//$mysql->rollback();
			echo "<h3>There was a problem executing your request. No seat/s were updated.</h3>";
			echo "Possible problems are:";
			echo "<ul>";
			echo "<li>Another process was able to book the same seat while you were still browsing.</li>";
			echo "<li>You were trying to Confirm an unreserved Seat.</li>";
			echo "<li>You were trying to Cancel an unreserved Seat.</li>";
			echo "<li>You were trying to Reserve a reserved Seat.</li>";
			echo "<li>There was a problem connecting to the database.</li>";
			echo "</ul>";
		}
		
		echo "<a href='confirmedorders.php'>View Seat Plan</a>";
		
		// Enable the autocommit feature
		//$mysqldb->autocommit(TRUE);
		
		// Recuperate the query resources
		//$result->free();
		
		mysql_close();
	
?>