Hi,
I'm having a hard time trying to figure out a simple way to do this. Would really be glad of any and every bit of advise . Thanks . Swati
a. I have a db with some 70 odd tables.
b. 4-5 of the tables are Reports which include fields with text data - that run into several pages , so i define those fields as long text. This is getting unmanageable now
c. I would like to define the field where the text data is such that it points to a path on the server where i have created a folder where word files can be stored.
d. Basically, i would like to convert all the data in a text field into a word file and each time the record is called, the word file will also open up for updation. So the fields in the scripts below for problem, work done, status would all come in a single word document that would be stored in a folder say $file_dir="service/";
e. Thereafter, i would like to be able to both view and email the entire document as and when required.
f. My original scripts to call and update a record are given below.
g. How do i define the path for these text fields in the UPDATE statement in File 3 below ?
FILE1
<? include("protect.php"); ?>
<div align="left">
<font face="Tahoma" size="2" color="#0000FF">This is a list of the service calls made by you Choose one to update.</font><p>
<table border="1" style="border-collapse: collapse" bordercolor="#111111" width="100%">
<form method="post" action="update_chosen.php?userid=<? echo $userid ?>">
<tr bgcolor="#DEEDFE">
<td><font size=2 face=Tahoma color="#0000FF">Select</td>
<td><font size=2 face=Tahoma color="#0000FF">JobID</td>
<td><font size=2 face=Tahoma color="#0000FF">Request Received On</td>
<td><font size=2 face=Tahoma color="#0000FF">Work Started On</td>
<td><font size=2 face=Tahoma color="#0000FF">Type</td>
<td><font size=2 face=Tahoma color="#0000FF">Customer</td>
<td><font size=2 face=Tahoma color="#0000FF">Product</td>
<td><font size=2 face=Tahoma color="#0000FF">Problem Reported</td>
<td><font size=2 face=Tahoma color="#0000FF">Status</td>
</tr>
<?php
$current = getdate();
$current_month = $current["mon"];
$current_year = $current["year"];
mysql_connect("localhost", $dbname, $dbpasswd )
or die ("Unable to connect to server.");
mysql_select_db($database)
or die ("Unable to select database.");
$sql = "SELECT `services`.* FROM `services` LEFT JOIN `Service_Costs` ON `services`.serviceid=`Service_Costs`.serviceid WHERE `services`.userid='$userid' AND `Service_Costs`.serviceid IS NULL AND (`services`.Status = 'NI'|| `services`.Status = 'NTNS' ) ORDER BY `services`.`serviceid` asc ";
$result = mysql_query($sql);
if ($myrow = mysql_fetch_array($result)) {
do {
$temp=$myrow["Productid"];
$result1 = mysql_query("SELECT * FROM `Products` WHERE Productid='$temp' ");
$myrow2 = mysql_fetch_array($result1);
$Name=$myrow2["Name"];
printf("<tr>
<td><input type=\"radio\" name=\"choice\" value=\"%d\"></td>
<td><font face=\"Tahoma\" size=\"2\"> %s </td>
<td><font face=\"Tahoma\" size=\"2\"> %s </td>
<td><font face=\"Tahoma\" size=\"2\"> %s </td>
<td><font face=\"Tahoma\" size=\"2\"> %s </td>
<td><font face=\"Tahoma\" size=\"2\"> %s </td>
<td><font face=\"Tahoma\" size=\"2\"> %s </td>
<td><font face=\"Tahoma\" size=\"2\"> %s </td>
<td><font face=\"Tahoma\" size=\"2\"> %s </td>
</tr>",
$myrow["serviceid"], $myrow["serviceid"],
calculatedate($myrow["serviceRequestDate"]),
calculatedate($myrow["serviceDate"]),
$myrow["Type"],
$myrow["Company"],
$Name,
$myrow["ProblemReported"],
$myrow["Status"] );
} while ($myrow = mysql_fetch_array($result));
} else {
echo "Sorry, no records were found!";
}
?>
</table>
<p><font size=2 face=Tahoma color="#0000FF"><input type="submit" value="Proceed">
</form>
</td>
</tr>
</table>
</div>
FILE 2:
<?php
$serviceid = $_POST["choice"];
if ( !$serviceid) die("Please choose one service report to be updated. Use back button of browser");
?>
<p>
<?php
mysql_connect("localhost", $dbname, $dbpasswd )
or die ("Unable to connect to server.");
mysql_select_db($database)
or die ("Unable to select database.");
$result1 = mysql_query("SELECT * FROM `services` WHERE serviceid='$serviceid' ");
$myrow1 = mysql_fetch_array($result1);
$authorised_by = $myrow1["AuthorisedBy"];
$service_date = $myrow1["serviceDate"];
$model = $myrow1["Model"];
$customer = $myrow1["Company"];
$temp=$myrow1["Productid"];
$result2 = mysql_query("SELECT * FROM `Products` WHERE `Productid`= '$temp' ");
$myrow2 = mysql_fetch_array($result2);
$Product = $myrow2["Name"];
?>
<font face="Verdana" size="2" color="#FF8000">You can choose to :</font>
<hr>
<p align="left"><font color="#0000FF" size="2" face="Verdana"><i>(A) Prepare a summary service report
<?php
echo "<a href=\"update_service_summary_report.php?serviceid=$serviceid&Userid=$Userid\"> </a>";
?> </i></font></p>
<font face="Verdana" size="2" color="#FF8000">OR</font>
<hr>
<form method="post" action="update_machine_data.php?serviceid=<? echo $serviceid?>&Userid= <? echo $Userid?>"><font color="#0000FF" size="2" face="Verdana"onclick="highlight(event)">
<p align="left"><font color="#0000FF" size="2" face="Verdana"><i>(B) Choose a machine type if you need to add machine data :</td><td></i></font><select name="type">
<option value="">[Select One]
<option value="CgA">
<option value="CrB">
<option value="RFe">
</select></td></tr></p>
<p>
<input type="hidden" name="Userid" value="<? echo $Userid ?>">
<input type="hidden" name="serviceid" value="<? echo $serviceid ?>">
<input type="hidden" name="service_date" value="<? echo $service_date ?>">
<input type="hidden" name="model" value="<? echo $model ?>">
<p><input type="submit" value="Submit">
</form>
<font face="Verdana" size="2" color="#FF8000">OR</font>
<hr>
<p align="left"><font color="#0000FF" size="2" face="Verdana"><i>(C) Continue forward and prepare a work report for the day</b><p>
<form method="post" action="update_service_daily_report.php?serviceid=<? echo $serviceid ?>&Userid=<? echo $Userid ?>">
<h1 align="center"
<b><font face="Verdana" size="3" color="#FF8000">Daily Work Report for
<?php
echo $Product
?></font></b>
<hr>
<font face="Verdana" size="-1" color="#0000FF"><i>This report is for the visit made at the site of </i> </font>
<font face="Verdana" size="3" color="#FF8000">
<?php
echo $customer
?>
</font>
<hr>
<font face="Verdana" size="2" color="#0000FF">
Report for the day :<font size="2"><input type="text" name="date" size="12"> YYYY/MM/DD format </font> </td></p>
<hr>
<p><font size="2"><p align="left">Observations:</p></font><textarea
name="observe" rows="5" cols="100"></textarea></p>
<hr>
<p><font size="2"><p align="left">Work Done:</p></font><textarea name="work_done"
rows="5" cols="100"></textarea></p>
<hr>
<p><font size="2"><p align="left">Action Initiated:</p></font><textarea
name="action_init" rows="5" cols="100"></textarea></p>
<hr>
</font></td></p>
<p> </p>
<input type="hidden" name="customer" value="<? echo $customer ?>">
<input type="hidden" name="service_date" value="<? echo $service_date ?>">
<input type="hidden" name="Product" value="<? echo $Product ?>">
<input type="hidden" name="model" value="<? echo $model?>">
<input type="hidden" name="serviceid" value="<? echo $serviceid ?>">
<input type="hidden" name="id" value="<? echo $id ?>">
<input type="hidden" name="Userid" value="<? echo $Userid?>">
<input type="hidden" name="authorised_by" value="<? echo $authorised_by ?>">
<p><input type="submit" value="Submit">
</form>
</body>
</html>
FILE 3
<? include("protect.php"); ?>
<?php
$customer = $_POST["customer"];
$biz_city = $_POST["biz_city"];
$last_name = $_POST["last_name"];
$ireq_date = $_POST["ireq_date"];
$serial_number = $_POST["serial_number"];
$Service_date = $_POST["Service_date"];
$type = $_POST["type"];
$status = $_POST["status"];
$authorised_by = $_POST["authorised_by"];
$prob_reported = $_POST["prob_reported"];
$work_done = $_POST["work_done"];
$model = $_POST["model"];
$specs = $_POST["specs"];
$id = $_POST["id"];
mysql_connect("localhost", $dbname, $dbpasswd )
or die ("Unable to connect to server.");
mysql_select_db($database)
or die ("Unable to select database.");
$sql = "UPDATE `Services` SET `Productid`='$id', `Company`='$customer', `BusinessCity`='$biz_city', `LastName`='$last_name',`ServiceRequestDate`='$ireq_date',`ServiceDate`='$Service_date', `Model`='$model', `Specs`='$specs', `SerialNumber`='$serial_number',`Type`='$type',`ProblemReported`='$prob_reported',`WorkDone`='$work_done',`AuthorisedBy`='$authorised_by', `Status`='$status' WHERE `Serviceid`='$Serviceid' ";
$result1 = mysql_query($sql)
or die (mysql_error() );
?>
<input type="hidden" name="Serviceid" value="<? echo $Serviceid ?>">
<input type="hidden" name="customer" value="<? echo $customer ?>">
<input type="hidden" name="id" value="<? echo $id ?>">
<input type="hidden" name="payment_type" value="<? echo $payment_type ?>">
<input type="hidden" name="Userid" value="<? echo $Userid ?>">
</table>
<p><p><a href="index.php">Your Page</a>
<p><b><A HREF="<? echo $PHP_SELF ?>?action=logout">Sign Out</A></b>
<br><br>
