Jump to content

Using multiple tables in one query


3raser

Recommended Posts

 

$query = mysql_query("SELECT COUNT(tickets.id),tickets.id,tickets.status,tickets.date,tickets.question,tickets.title,users.position FROM tickets,users WHERE tickets.id='$existing' users.username='$username'");

 

How do I get it so it selects data from the table tickets where id='$existing', but I also want to get a user's (who is viewing the ID) position (rank). $username is a session. So, any thoughts on how I would do so?

 

My updated code:

 

<?php
session_start();
include("../includes/mysql.php");
include("../includes/config.php");

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link href="../style.css" rel="stylesheet" type="text/css" />
<title><?php echo $title; ?></title>
</head>

<body>
<div id="container">
<div id="content">
	<div id="left">
		<div class="menu">
			<?php include("../includes/navigation.php"); ?>
			<div class="menufooter"></div>
		</div>
		<?php include("../includes/menu.php"); ?>
	</div>
	<div id="middle">
		<?php
		$existing = $_POST['existing'];
		$ip = $_SERVER['REMOTE_ADDR'];
		$username = $_SESSION['user'];

		$query = mysql_query("SELECT COUNT(tickets.id),tickets.id,tickets.status,tickets.date,tickets.question,tickets.title,users.position FROM tickets,users WHERE tickets.id='$existing' OR users.username='$username'");
		$get = mysql_fetch_assoc($query);

		if(!$existing)
		{
		echo 
		'
		<div class="post">
			<div class="postheader"><h1>Error</h1></div>
			<div class="postcontent">
				<p>You have not enetered in a ticket ID. Please go back and do so.</p>
			</div>
			<div class="postfooter"></div>
		</div>
		';
		}
		elseif($get['COUNT(id)'] < 1)
		{
		echo 
		'
		<div class="post">
			<div class="postheader"><h1>Error</h1></div>
			<div class="postcontent">
				<p>The ticket ID you are trying to use doesnt exist. Please go back or submit another ticket.</p>
			</div>
			<div class="postfooter"></div>
		</div>
		';
		}
		elseif($get['tickets.ip']==$ip || $get['user.position'] >= 1)
		{
		$status["tickets.status"]["0"] = "Waiting for support...";
		$status["tickets.status"]["1"] = "Waiting for user...";
		$status["tickets.status"]["2"] = "Ticket Closed...";
		$status["tickets.status"]["3"] = "Ticket Opened...";

		echo 
		'
		<div class="post">
			<div class="postheader"><h1>View Ticket Status - ID '. $get["id"] .'</h1></div>
			<div class="postcontent">
				<p>Title: '. $get["tickets.title"] .' - Posted on: '. $get["tickets.date"] .'</p>
				<p>Ticket Status: '. $status[$get["tickets.status"]] .'</p>
				<p>Question: '. nl2br($get["tickets.question"]) .'</p>
			</div>
			<div class="postfooter"></div>
		</div>
		';

		}
		else
		{
		echo 
		'
		<div class="post">
			<div class="postheader"><h1>Error</h1></div>
			<div class="postcontent">
				<p>This is not your ticket. You only have permission to view your tickets. Please go back.</p>
			</div>
			<div class="postfooter"></div>
		</div>
		';
		}
		?>
		</div>
	</div>
</div>
</body>
</html>

Link to comment
https://forums.phpfreaks.com/topic/209841-using-multiple-tables-in-one-query/
Share on other sites

How are the tickets related to the user? You need to use an ON clause in your join.

 

Because I need to check if and see if the users position (rank) is higher than 1, or equal to it. And I wanted to use it all in one query.

It can all be within one query. An example (untested) query would be

SELECT t.id,
       t.status, 
       t.date, 
       t.question, 
       t.title, 
       u.position 
FROM tickets t
LEFT JOIN users u ON t.id = u.ticket_id
WHERE (t.id='$existing' OR u.username='$username')
    AND u.position > 1

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.