php/mysql Sorting fields

[Thikho]

Hi All

I have to mysql tables below and i would like to output them(ticket,name,branch, date_add and Technician(which is on the other table) and sort the in desc order, i have been able to do that on this fields(ticket,name,branch, date_add) and it works fine but the issue is sorting the Technician since the field tech_id is not on the query table

mysql> describe tech;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| tech_id | int(10) | NO | PRI | NULL | auto_increment |
| username | varchar(50) | NO | | | |
| password | varchar(50) | NO | | | |
| email | varchar(50) | NO | | | |
| ext | varchar(15) | NO | | | |
| regdate | bigint(20) | NO | | | |
| lastlogin | bigint(20) | NO | | | |
+-----------+-------------+------+-----+---------+----------------+
7 rows in set (0.03 sec)

mysql> describe query;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| ticket | int(10) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | | |
| email | varchar(40) | YES | | NULL | |
| branch | varchar(25) | NO | | | |
| date_add | bigint(20) | NO | | | |
| ip | varchar(16) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
13 rows in set (0.00 sec)

MY CODE BELOW

<div class="adminColor">Assign Trouble Tickets
<div class="ticcolor1">Active Tickets</div>
<div class="ticcolor2">Busy Tickets</div>
<div class="ticcolor3">User Approval Tickets</div>
<div class="ticcolor4">Closed Tickets</div>

</div><form action="main.php" method="post">
<table cellpadding="2" cellspacing="0">
<tr><td>In order to view Main Tickets "<b>Main Tickets</b>" button:</td>
	<td><input type="submit" name="whatAction" value="Main" class="adminButton" style="margin-left: 10px">
</td></tr>
</table>
</form>
<div class="contentDiv">
Existing Trouble tickets  are listed with the option to assign to IT Technician.<br />
</div>

<?php
$font=2;
$head=2;
$sort=$_GET[sort];
if ($sort=="")
	$sort="status";
// Setup variables
$ticket=$_POST[ticket];
if ($ticket=="")
  $ticket=$_GET[ticket];
  $email=$_POST[email];
if ($email=="")
  $email=$_GET[email];
  $status=$_POST[status];
if ($status=="")
  $status=$_GET[status];

include("connection.php");
$sql="select * from query where status > 1 order by $sort";
$result=mysql_query($sql);
$num=mysql_num_rows($result);


  if($num>0)
  {
	//echo "<br>";
    echo "<table cellpadding='3' cellspacing='1' style='width:100%; margin-top: 10px'>";
    echo "<tr><td class='tableHeading'><a href='indexticket.php?sort=ticket&ticket=$ticket&email=$email&status=$status'><font size = $head>Ticket</font></a></td>";
    echo "<td class='tableHeading'><a class='headingLink' href='indexticket.php?sort=date_add&ticket=$ticket&email=$email&status=$status'><font size = $head>Time & Date</font></a></td>";
    echo "<td class='tableHeading'><a class='headingLink' href='indexticket.php?sort=name&ticket=$ticket&email=$email&status=$status'><font size = $head>User</font></a></td>";
    echo "<td class='tableHeading'><a class='headingLink' href='indexticket.php?sort=branch&ticket=$ticket&email=$email&status=$status'><font size = $head>Branch</font></a></td>";
    echo "<td class='tableHeading'><font size = $head>Technician</font></a></td>";

echo "<td class='tableHeading'><font size = $head>Edit</font></td>";

while($output=mysql_fetch_array($result))
{
     $sql2="select * from resolved where $output[ticket]=ticket_query";
     $result2=mysql_query($sql2);
     $num2=mysql_num_rows($result2);
      if($num2>0)
      {
      $list=mysql_fetch_array($result2);
      $sql3="select * from tech where $list[tech_id]=tech_id";
      $result3=mysql_query($sql3);
      $name=mysql_fetch_array($result3);
      $tech=$name[username];
      }
      else
      {
      $tech="N/U";
      }

  switch($output[status])
  {
   case 3:
$color="#CCDDF8";  
    $state="Active";
    break;
  case 2:
$color="#7090E8";
    $state="Busy";
    break;
  case 1:
    $color="#4477AA";
    $state="User approval";
    break;
  case 0:
    $color="#888888";
    $state="Closed";
    break;
  }

echo "<tr bgcolor='$color'><td align='center'><font size = '$font'>$output[ticket]</font></td>";
echo "<td align='center'><font size = '$font'>" . date("G:i d/m/Y",$output[date_add]) . "</font></td>";
echo "<td align='center'><font size='$font'>$output[name]</font></td>";
echo "<td align='center'><font size='$font'>$output[branch]</font></td>";
echo "<td align='center'><font size='$font'>$tech</font></td>";
  	echo "<td align='center'><font size='$font'><a href='edit_ticket.php?ticket=$output[ticket]'>Edit</font></a></td>";
}
echo "</table>";
  }

?>

[bogu]

You should check JOIN Syntax in MySQL.

[Thikho]

Hi Bogu

I dont have common fields in this two table can i be able to reference them using different fields like ticket and tech_id

I have tried this but it does not work even the ones which were working are not working now

select * from query join tech on tech.tech_id=query.ticket where status > 1

Thanks

[Weedpacket]

There has to be some sort of relationship between them, otherwise what you're asking makes about as much sense as wanting to sort the days of the week according to the populations of Australian capitals.

So what is the relationship?

PS: see this FAQ for suggestions about your formatting.

[Thikho]

The relationship is between the table resolved(tech_id) and query, maybe i should put my question like this how do u make a join of two table without relationship is it possible or not ??

[tcarnes]

Where do you store which tickets are assigned to which techs? You either need to have a Tech ID field on the ticket, or a separate table that pairs the two. (If I'm understanding your question correctly.)

[Thikho]

When the user submit the ticket it is added to the table query below
mysql> describe query;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| ticket | int(10) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | | |
| email | varchar(40) | YES | | NULL | |
| branch | varchar(25) | NO | | | |
| ext | varchar(15) | YES | | NULL | |
| date_add | bigint(20) | NO | | | |
| category | varchar(25) | YES | | NULL | |
| department | varchar(50) | YES | | NULL | |
| subject | varchar(150) | NO | | | |
| problem | text | NO | | | |
| status | tinyint(4) | NO | | | |
| priority | tinyint(4) | NO | | | |
| ip | varchar(16) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
13 rows in set (0.05 sec)

and when the technician assign the query it is added to this table resolved, which have the tech_id that is the relationship between the technician who solved the query and the table resolved

mysql> describe resolved;
+---------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+----------------+
| ticket_query | int(11) | NO | PRI | NULL | auto_increment |
| tech_id | int(11) | NO | | | |
| date_started | bigint(20) | NO | | | |
| date_finished | bigint(20) | YES | | NULL | |
| date_closed | bigint(20) | YES | | NULL | |
| tech_comment | text | NO | | | |
| user_comment | text | YES | | NULL | |
+---------------+------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

I want to display the result of the query and the technician info and sort technician by tech_id

mysql> describe tech;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| tech_id | int(10) | NO | PRI | NULL | auto_increment |
| username | varchar(50) | NO | | | |
| password | varchar(50) | NO | | | |
| email | varchar(50) | NO | | | |
| ext | varchar(15) | NO | | | |
| regdate | bigint(20) | NO | | | |
| lastlogin | bigint(20) | NO | | | |
+-----------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

I hope you find this in order

[tcarnes]

There appears to be nothing linking the query table with the tech table. Are the contents of query.problem (text) equal to anything in resolved? How do you know which resolved record goes with which query record?

[Weedpacket]

PS: see this FAQ for suggestions about your formatting.