hi,
i have a problem to display my data. i have 4 table (proposals,quotation, po,simulation), in proposal there is pro_id, pro_name,file_name and file, in quotation there is pro_id, quo_name, file_name and file, in po there is pro_id, po_name, file_name and file, in simulation there is pro_id, simul_name, file_name and file. ** only the pro_id of each table is the same while others such as (file_name and file) have different value for each table.
i want to combine the 4 tables but only display file from table quotation,po and simulation while table proposal will display all. is it possible?
eg:
display- pro_id,pro_name,file_name,file(form table-proposals),file(from table-quotation),file(form table-po),file(from table-simulation)..
please help..i have try so many things but still did not work..
my code :
<?php
//open connection
$connectdb=mysql_connect("localhost","root");
if(!$connectdb)
{
die('Unable to connect to server:'.mysql_error());
exit;
}
//select database
$selectdb=mysql_select_db("custech", $connectdb);
if (!$selectdb)
{
die('Unable to select database custech:'.mysql_error());
exit;
}
if (!isset($_POST['levelbox']))
{
//Instruction if $_POST['value']doesnt exist
$_POST['levelbox']="%";
}
$levelbox = ($_POST['levelbox']);
?>
<form method="POST" name="frmSearch" action="quotationlist.php">
<center><table width="900" border="0" cellspacing="0" cellpadding="0">
<td width="70%"><p>
<select name="levelbox" class="inputbox2">
<option value=""/>-- ALL --</option>
<option value="pro_name"/>Project Name</option>
<option value="pro_status" />Status</option>
<option value="pro_company" />Company Name</option>
<option value="pro_date" />Date</option>
</select>
<input name="txtSearch" type="text" id="fullname" size="30" />
<input type="submit" name="cmdSearch" value="Search">
</td>
</tr>
</table>
</form>
<?php
// Before using $_POST['value'] add this - to avoid error on undefined index
if (!isset($_POST['txtSearch']))
{
//Instruction if $_POST['value']doesnt exist
$_POST['txtSearch']="%";
}
//to select all record in database and to display all records
if($levelbox=='')
{
$sql="SELECT * FROM proposals ORDER BY pro_id ASC";
//$sql="SELECT proposals.pro_id, proposals.pro_name, proposals.file, quotation.file, purchase_order.file FROM proposals, quotation, purchase_order, simulation WHERE proposals.pro_id=quotation.pro_id AND proposals.pro_id=purchase_order.pro_id ORDER BY proposals.pro_id ASC";
//$sql="SELECT proposals.pro_id, proposals.pro_name, proposals.pro_company, proposals.pro_by, proposals.pro_status, proposals.pro_date, proposals.file_name, quotation.file, purchase_order.file, simulation.file
//FROM proposals
//INNER JOIN quotation ON proposals.pro_id=quotation.pro_id
//INNER JOIN purchase_order ON quotation.pro_id=purchase_order.pro_id
//INNER JOIN simulation ON purchase_order.pro_id=simulation.pro_id WHERE proposals.pro_id = quotation.pro_id";
}
else {
//$sql="SELECT * FROM proposals WHERE $levelbox LIKE '%".$_POST['txtSearch']."%' ORDER BY pro_id ASC";
$sql="SELECT proposals.pro_id, proposals.pro_name, proposals.pro_company, proposals.pro_by, proposals.pro_status, proposals.pro_date, proposals.file_name, quotation.file, purchase_order.file, simulation.file
FROM proposals , quotation , purcahse_order , simulation
INNER JOIN quotation ON proposals.pro_id=quotation.pro_id
INNER JOIN purchase_order ON quotation.pro_id=purchase_order.pro_id
INNER JOIN simulation ON purchase_order.pro_id=simulation.pro_id WHERE $levelbox LIKE '%".$_POST['txtSearch']."%'";
}
// SELECT Code, MAX(Jan-Apr) as 'Jan-Apr', ...
//FROM tbl
//GROUP BY Code
// $sql="SELECT * FROM proposals WHERE $levelbox LIKE '%".$_POST['txtSearch']."%' ORDER BY pro_id ASC";
$result=mysql_query($sql);
//error message if query fails
if (!$result)
{
die;
exit;
}
//if no records found
if (mysql_num_rows($result)==0)
{
echo "<table cellspacing='0' cellpading='3'><tr><td>";
echo "No row found, no row to print, so, am exiting";
echo "</td></tr></table>";
exit;
}
?>
<table cellspacing='0' cellpading='3'>
<tr>
<td width='7%' bgcolor='#c9c9c9'><b>Proposal_ID</b></td>
<td width='17%' bgcolor='#c9c9c9'><b>Proposal Name</b></td>
<td width='17%' bgcolor='#c9c9c9'><b>Proposal Company</b></td>
<td width='7%' bgcolor='#c9c9c9'><b>Date</b></td>
<td width='10%' bgcolor='#c9c9c9'><b>File Name</b></td>
<td width='7%' bgcolor='#c9c9c9'><b>Quotation</b></td>
<td width='7%' bgcolor='#c9c9c9'><b>Purchase Order</b></td>
<td width='7%' bgcolor='#c9c9c9'><b>Simulation</b></td>
<td width='7%' bgcolor='#c9c9c9'><b>File</b></td>
<td width='8%' bgcolor='#c9c9c9'><b>Status</b></td>
<td width='8%' bgcolor='#c9c9c9'><b>Prepare By</b></td>
</tr>
<?php
//if records found, then display data
while($_POST = mysql_fetch_array($result))
{
echo "<tr>
<td>".$_POST['pro_id']."</td>
<td>".$_POST['pro_name']."</td>
<td>".$_POST['pro_company']."</td>
<td>".$_POST['pro_date']."</td>
<td>".$_POST['file_name']."</td>
<td><a href='".$_POST['file']."'>download</a></td>
<td><a href='".$_POST['file']."'>download</a></td>
<td><a href='".$_POST['file']."'>download</a></td>
<td><a href='".$_POST['file']."'>download</a></td>
<td>".$_POST['pro_status']."</td>
<td>".$_POST['pro_by']."</td>
</tr>";
}
echo "</table>";
echo "<br />";
//<a href="/documents/large_document.pdf">Download the large document</a>filename=\"".$path_parts["basename"]."\"
mysql_free_result ($result);
mysql_close($connectdb);
?>
