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);

?>

Recommended Answers

All 6 Replies

You've made life difficult for yourself by using the same field name for fields that can contain different values. I am referring to file_name and file. If you put a prefix on these, so you can tell which table it's coming from, you could then just select the whole lot in a single SELECT statement. This would be constructed differently depending on some factors, like for example, which is the "top" table, and can the other tables contain multiple items, all connected to the same pro_id.

true..

Member Avatar for LastMitch

@missy_mi

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?

Did you write this code? It's very unorganized. Are you connected to the database? You already have a JOIN? In your case, you want to merge 4 tables and in order to do that you need a JOIN which you have already. This very complicate to print columns from different tables.

i already replace my database name the file..everything is good now..thanks.. :)

@LastMitch :

yes, i write the codes..some i get from friend and the internet..and yes, i know its unorganize..what to do..i am trying to improve though..

Member Avatar for LastMitch

@missy_mi

i already replace my database name the file..everything is good now..thanks.. :)

So, you correct the database and everything is working. It's good that you trying to improve this script.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.