help with displaying data php/mysql

[miz_luvly@hotmail.com]

Hi all,

I have a system where reports are added by people. I have a textarea in the form where people write outstanding/follow-ups and updates it as and when required. I have created another table where updates are added with datetime, name, and status.

the main report is displayed on the table (columns are Account No: Customer Name: Date of Approach: Report: Outstanding: Type of Approach etc) but currently follow-up are on a seperate page in a threaded discussion like format (can be accessed by clicking on a hyperlink).

Problem
if possible, I want follow-ups to be displayed in a column on the same table as the rest of the report. Also I want to keep the existing data when more info is added with datetime.
I would really appreciate if someone could help me. thanks

Heres the code for follow-ups
[code]
<?php

//Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// get value of id that sent from address bar
$activity_id=$_GET['activity_id'];

//$sql="SELECT view $tbl_name.*, customer_table.customer_name, customer_table.account_no from $tbl_name, customer_table where $tbl_name.account_no='$account_no' ORDER BY DATE_OF_ENTRY DESC";
$sql="select * from $tbl_name where activity_id='$activity_id'";
$result=mysql_query($sql);

$rows=mysql_fetch_array($result);
?>
<a href="logout.php">Logout</a><br>
<FORM><INPUT TYPE="button" VALUE="Back" onClick="history.go(-1);return true;"> </FORM>
<table width="400" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td><table width="100%" border="0" cellpadding="3" cellspacing="1" bordercolor="1" bgcolor="#FFFFFF">


<tr>
<td bgcolor="#F8F7F1"><strong>Followup : </strong><? echo $rows['outstanding']; ?></td>
</tr>


<td bgcolor="#F8F7F1"><strong>By : </strong> <? echo $rows['full_name']; ?></td>
</tr>

<tr>
<td bgcolor="#F8F7F1"><strong>Date/time : </strong><? echo $rows['DATE_OF_ENTRY']; ?></td>
</tr>
</table></td>
</tr>
</table>
<BR>
<?php
$tbl_name2="forum_answer"; // Switch to table "forum_answer"

$sql2="SELECT * FROM $tbl_name2 WHERE question_id='$activity_id'";
$result2=mysql_query($sql2);

while($rows=mysql_fetch_array($result2)){
?>
<table width="400" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td><table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td bgcolor="#F8F7F1"><strong>Update</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><? echo $rows['a_id']; ?></td>
</tr>
<tr>
<td width="18%" bgcolor="#F8F7F1"><strong>Name</strong></td>
<td width="5%" bgcolor="#F8F7F1">:</td>
<td width="77%" bgcolor="#F8F7F1"><? echo $rows['a_name']; ?></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><strong>Answer</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><? echo $rows['a_answer']; ?></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><strong>Date/Time</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><? echo $rows['a_datetime']; ?></td>
</tr>
</table></td>
</tr>
</table><br>

<?
}

$sql3="SELECT $tbl_name.*, customer_table.customer_name, customer_table.account_no from $tbl_name, customer_table where customer_table.account_no=$tbl_name.account_no ORDER BY DATE_OF_ENTRY DESC";
$result3=mysql_query($sql3);

$rows=mysql_fetch_array($result3);
$view=$rows['view'];


// if have no counter value set counter = 1
if(empty($view)){
$activity_id=$_GET['activity_id'];
$view=1;
$sql4="INSERT INTO $tbl_name(view) VALUES('$view') WHERE activity_id='$activity_id'";
$result4=mysql_query($sql4);
}


// count more value
$addview=$view+1;
$sql5="update $tbl_name set view='$addview' WHERE activity_id='$activity_id'";
$result5=mysql_query($sql5);

mysql_close();
?>
<BR>
<table width="400" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<form name="form1" method="post" action="add_answer_reports.php">
<td>
<table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td width="18%"><strong>Name</strong></td>
<td width="3%">:</td>
<td width="79%"><input name="a_name" type="text" id="a_name" size="45"></td>
</tr>
<tr>
<td valign="top"><strong>Reply</strong></td>
<td valign="top">:</td>
<td><textarea name="a_answer" cols="45" rows="3" id="a_answer"></textarea></td>
</tr>
<tr>
<td valign="top"><strong>Status</strong></td>
<td valign="top">:</td>
<td valign="top"><select name="status"><?php echo $myrow["status"] ?>
       <option value="" selected>Select</option>
       <option value="Complete">Complete</option>
       <option value="Ongoing">Ongoing</option>
       <option value="Discussed with Ray">Discussed with Ray & closed</option>
      </select>
</td>
</tr>
<tr>
<td>&nbsp;</td>
<td><input name="activity_id" type="hidden" value="<? echo $activity_id; ?>"></td>
<td><input type="submit" name="Submit" value="Submit"> <input type="reset" name="Submit2" value="Reset"></td>
</tr>
</table>
</td>
</form>
</tr>
</table>
<? /*
if($_POST["Submit"])
{
$activity_id=$_GET['activity_id'];
$sql6= "update table reactivity set status=(select status from forum_answer) where activity_id='$activity_id'";
      $result = mysql_query($sql);
} */
?>
[/code]

[miz_luvly@hotmail.com]

pcan someone help plzzz

[HuggieBear]

Do you have an example somewhere that we can see?

Regards
Huggie

[miz_luvly@hotmail.com]

hi huggiebear

thanks for the reply, i think i havent explained it properly. basically I have one script which displays a table (columns are Account No: Customer Name: Date of Approach: Report: Outstanding: Type of Approach etc) and want to display follow-up reports in the same table, but I have a seperate script for follow-up reports where name, follow-up report and time can be added. How can I show follow-up reports in a column on the same table.
e.g.
[quote]
Account No|CustomerName|DateofApproach|  Report |  followup
0000             xyz                 00-00-0000          abc       name> report>date
[/quote]

I hope it makes more sense. Please advise me of any other ways to make this possible.

Thanks

[HuggieBear]

OK, with the code that you have you could use an include I guess, but there's probably better ways of doing it by including it on one page...

[code]<?php
foreach($approach_id as $id){
  echo "$column_1 | $column_2 | $column_3 | " . include('followup.php?app_id=$id');
}
?>[/code]

But like I say, it would be better on one page.

Huggie

[miz_luvly@hotmail.com]

can you give an example how to put it on one page.

BTW i have tried the following but im getting error:unexpected T_VARIABLE, expecting ',' or ';'  ...  on line ->

[quote]
      <td>".$row['name]."</td>
      <td>".$row['DATE_OF_ENTRY']."</td>
      <td>".$row['status']."</td>
             ->  <td> "$row['include "followup.php?activity_id=$activity_id";']."</td>
[/quote]

[HuggieBear]

Try this for the current setup and I'll come up with an example of a single page...

[code]
<?php
echo "<td>".$row['name']."</td>" .
"<td>".$row['DATE_OF_ENTRY']."</td>" .
"<td>".$row['status']."</td>" .
"<td>";
include('followup.php?activity_id=$activity_id');
echo "</td>";
?>
[/code]

And also try to use [b][nobbc][code]...[/code][/nobbc][/b] tags as opposed to quote tags when displaying code :)

Huggie

[miz_luvly@hotmail.com]

now i am getting this error:

Warning: include(view_topic_report.php?activity_id=$activity_id) [function.include]: failed to open stream: No such file or directory in /home/vhost/farmiloesinjamaica.co.uk/html/final/process_advance_search_admin.php on line 78

[HuggieBear]

OK, I'm off home now, I'll take a look when I get in.

Regards
Huggie

[HuggieBear]

Sorry Miz_L,

Only just got in.  Can you possibly post the code for the original page too, not just the follow up code.

Regards
Huggie

[miz_luvly@hotmail.com]

here is the code:

[code]
<?php
require 'conn.php';
require 'page_header.php';
$end=time();
include "start_timer.php";
include "end_timer.php";

echo '<a href="logout.php">Log out</a><br><br>';

if (($_GET['account_no']!="") or ($_GET['customer_name']!="") or ($_GET['date']!="") or ($_GET['repname']!=""))
{
$sql = "select distinct repactivity.*, customer_table.CUSTOMER_NAME, DATE_FORMAT(DATE_OF_ENTRY, '%d-%m-%Y %r') AS DATE_OF_ENTRY, concat(outstanding, ' ', outstanding2, ' ') AS outstanding from repactivity, customer_table where customer_table.account_no=repactivity.account_no AND";
if ($_GET['account_no']!="")
{
$sql = $sql." repactivity.account_no LIKE '".$_GET['account_no']."%'";
}
if ($_GET['customer_name']!="")
{
if ($_GET['account_no']!="")
$sql = $sql." and";
$sql = $sql." CUSTOMER_NAME LIKE '".$_GET['customer_name']."%'";
}
if ($_GET['date']!="")
{
if (($_GET['account_no']!="") or ($_GET['customer_name']!=""))
$sql = $sql." and";
$sql = $sql." DATE_OF_APPROACH LIKE '%".$_GET['date']."%'";
}
if ($_GET['repname']!="")
{
if (($_GET['account_no']!="") or ($_GET['customer_name']!="") or ($_GET['date']!=""))
$sql = $sql." and";
$sql = $sql." full_name LIKE '".$_GET['repname']."' Group by DATE_OF_ENTRY DESC";
}
if ($_GET['rep2']!="")
{
if (($_GET['account_no']!="") or ($_GET['customer_name']!="") or ($_GET['date']!="") or ($_GET['rep2']!=""))
$sql = $sql." and";
$sql = $sql." rep2 LIKE '".$_GET['rep2']."%' group by DATE_OF_ENTRY DESC";
}


$result = mysql_query($sql);
$number=mysql_num_rows($result);
if (!$result)
{
echo 'Could not run query: ' . mysql_error();
}


echo "<table border=1 bordercolor='tan' class='rep' width ='150%' " .
    "cellspacing='5'"."cellpadding='5'>\n";
echo "<tr border=1><th class='searchHeader'><caption> <b>Search Results</b><br>Total Reports = $number</caption> </th></tr>\n";
echo "<tr><td><b>Account No:</b></td> <td><b>Customer Name:</b></td> <td><b>Date of Approach:</b></td> <td width='50%'><b>Report:</b></td> <td width='50%'><b>Outstanding:</b></td> <td><b>Type of Approach:</b></td> <td width='14%'><b>Rep Name: </b></td> <td width='14%'><b>Rep2: </b></td> <td><b>Date of entry: </b></td> <td><b>Updated Status:</b></td><!-- td><b>Updated On:</b></td --> <td><b>View/add Follow-Up</b></td></tr>";

if ($result and !mysql_num_rows ($result))
{
echo "<tr class='row1'><td>No results found that match the ";

  }
  else
  {
while($row = mysql_fetch_array($result, MYSQL_BOTH))
{
echo "<tr><!-- td>".$row['activity_id']."</td -->
      <td>".$row['account_no']."</td>
      <td>".$row['CUSTOMER_NAME']."</td>
      <td>".$row['DATE_OF_APPROACH']."</td>
      <td>".$row['report']."</td>
      <td>".$row['outstanding]."</td>
      <td>".$row['type_of_approach']."</td>
              <td>".$row['full_name']."</td>
      <td>".$row['rep2']."</td>
      <td>".$row['DATE_OF_ENTRY']."</td>
      <td>".$row['status']."</td>
      <!-- td>".$row['updated_on']."</td -->
      <td bgcolor='#FFFFFF'><a href='followup.php?activity_id=".$row['activity_id']."'>view here</a><BR></td></tr>";
  //    include ('view_topic_report.php?activity_id=$activity_id'); echo "</td>";
}
}
}
?>
[/code]

Thanks

[miz_luvly@hotmail.com]

can anyone help plz

thanks in advanced

[miz_luvly@hotmail.com]

hi guyz can some help solve the problem. Im really stuck :(


thanks