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
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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.