PHP/MYSQL Question/Support

sanchez77 · October 4, 2010

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>

sanchez77 · October 4, 2010

You might need to know that i store ticket information in one table called tickets and i store the files in another table called files.

I'm trying to get one record from tickets to display mulitply records from files.

coupe-r · October 4, 2010

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 · October 4, 2010

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 · October 4, 2010

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>";
}

sanchez77 · October 4, 2010

Thanks Joel, that's a different approach than the one I was taking, but it does what i want it to do. Now I just need to rewrite the form. Thanks again.

Cheers,

Sanchez

Sign In

PHP/MYSQL Question/Support

Recommended Posts

sanchez77

Link to comment

Share on other sites

sanchez77

Link to comment

Share on other sites

coupe-r

Link to comment

Share on other sites

sanchez77

Link to comment

Share on other sites

joel24

Link to comment

Share on other sites

sanchez77

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information