Jump to content

PHP/MYSQL Question/Support

sanchez77

By sanchez77
in PHP Coding Help

Recommended Posts

sanchez77 Member

sanchez77

Posted
Posted

Hey Everyone, I was hoping someone out there could help me figure something out. I am trying to create a ticket database, when I create a ticket, I give the option to upload a file, which works ok. But I am stumped at trying to display mulitply files to one ticket. If I have a ticket no 0001 and two files associted to 0001, i end up printing two 0001 tickets each with a single file. I want to print one 0001 with both files listed under it. I'm not sure if it's possible to do a while within a while so I hoping someone can help me figure that out.

 

Thanks for your help

PHP Code:

<HTML><BODY><table align="center" width="600"><tr><td>

<?php

include 'connect.php';

// how many rows to show per page
$rowsPerPage = 3;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

$query = " SELECT tickets.ticketno, tickets.tickettitle, tickets.customer, tickets.status, tickets.dateresolved, tickets.datecreated, tickets.description, files.name, files.content FROM tickets LEFT JOIN files ON tickets.ticketno=files.ticketno ORDER BY ticketno LIMIT $offset, $rowsPerPage";


$result = mysql_query($query) or die('Error, query failed');

while($row = mysql_fetch_array($result))
{
     echo "<table align='center'><tr><td colspan='2'><img src='images\line_v9.gif'></td></tr>";
  echo "<tr><td>     <b>Ticket No -- " . $row['ticketno'] . "</b></td><td>     Ticket Title -- " . $row['tickettitle'] . "</td></tr>";
  echo "<tr><td>     Customer -- " . $row['customer'] . "</td><td>     Description -- " . $row['status'] . "</td></tr>";
  echo "<tr><td>     Date Resolved -- " . $row['dateresolved'] . "</td><td>     Date Created -- ". $row['datecreated'] ."</td></tr>";
  echo "<tr><td colspan='2'>     Description -- " . $row['description'] . "</td></tr>";
  echo "<tr><td colspan='2'> </td></tr><tr><td colspan='2'>     <b>Reference Documents</b> </td></tr>";
  echo "<tr><td>     File -- " . $row['name'] . "</td><td>     <a href='http://www.dbitpro.com/csp/files/" . $row['name'] . "'>Download Here</a></td></tr></table>";
}



$query = " SELECT files.*, tickets.* FROM files,tickets WHERE tickets.ticketno=files.ticketno";
$result = mysql_query($query) or die('Error, query failed');

while($row = mysql_fetch_array($result))
{
  echo "<table><tr><td>     File -- " . $row['name'] . "</td><td>     Ticketno" . $row['ticketno'] . "</td></tr></table>";
}


// how many rows we have in database
$query   = "SELECT COUNT(ticketno) AS numrows FROM tickets";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

// print the link to access each page
$self = $_SERVER['PHP_SELF'];

// creating previous and next link
// plus the link to go straight to
// the first and last page

if ($pageNum > 1)
{
   $page  = $pageNum - 1;
   $prev  = " <a href=\"$self?page=$page\">[Prev]</a> ";

   $first = " <a href=\"$self?page=1\">[First Page]</a> ";
} 
else
{
   $prev  = ' '; // we're on page one, don't print previous link
   $first = ' '; // nor the first page link
}

if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = " <a href=\"$self?page=$page\">[Next]</a> ";


   $last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
} 
else
{
   $next = ' '; // we're on the last page, don't print next link
   $last = ' '; // nor the last page link
}

// print the navigation link
echo $first . $prev . 
" Showing page $pageNum of $maxPage pages " . $next . $last;


// and close the database connection
mysql_close($con)


?>
</td></tr></table>
</BODY></HTML>

 

 

Link to comment
https://forums.phpfreaks.com/topic/215084-phpmysql-questionsupport/
Share on other sites
coupe-r Regular Member

coupe-r

Posted
Posted

Your table where you keep your files will need a ticket_id column.  So if ticket 0001 has 2 attachments, you can call them based on ticket_id 0001.

 

SELECT * FROM files f LEFT JOIN tickets t on t.ticket_id = f.ticket_id WHERE f.ticket_id = '0001'.

 

Will that work?

sanchez77 Member

sanchez77

Posted
  • Author
Posted

Thanks coupe-r

 

I do have a ticketno field in the files table. When I upload the file to the files table, I also input the ticketno in the field.

 

But I'm trying to query both tables and return the ticket info from tickets table (1 record) and more than 1 record from the files table. How would you do that?

 

Thanks for your help.

joel24 Member

joel24

Posted
Posted

do a join and then cycle through the results and for each result corresponding to a new ticket echo the new ticket header/text

i.e.

$sql = @mysql_query("SELECT t.*, f.name AS filename FROM tickets t JOIN files f ON t.ticket_id = f.ticket_id");

$currentTicket = '';

//table
echo "<table>";

while ($row = mysql_fetch_array($sql))
{
//if not the same ticket as last loop, echo ticket name etc
if ($currentTicket != $row['ticket_id'])
{
echo "<tr><th>{$row['ticket_id']}: {$row['tickettitle']}</th></tr>";
$currentTicket = $row['ticket_id'];
}

echo "<tr><td>{$row['filename']}</tr></tr>";
}

Archived

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

Go to topic listing
×
×
  • 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.