[SOLVED] Please help with MySQL select query

[desibhupi]

	$from = $_POST[from];
	$to = $_POST[to];
	$cid = $_POST[cid];
	$process = $_POST[process];

	if($from != "" || $to !="" || $cid!="" || $process!="") {
		$where = ' where ';
	}

	if($from != "") {
		$f_qry = "collection.created_date>='".$from."' ";
	}

	if($to != "") {
		$f_qry = "date(collection.created_date) between '".$from."' and '".$to."'";
	}
	if($cid != "") {
		if($f_qry || $t_qry) {
			$c_qry = ' and ';
		}
		$c_qry .= "outbound.customer_id='$cid'";
	}
	if($process != "") {
		if($f_qry || $t_qry || $c_qry) {
			$p_qry = ' and ';
		}
		$p_qry .= "collection.process='$process'";
	}

	if($f_qry || $t_qry || $c_qry || $p_qry) {
		$sub_qry = ' and ';
	}
	$sub_qry .= " (cust.custmer_id=coll.company_id) ";

	$sql = "select ";
	$sql .= "custmer_details.company_name, ";
	$sql .= "outbound.challan_no, ";
	$sql .= "outbound.quantity, ";
	$sql .= "outbound.total_amount, ";
	$sql .= "outbound.date, ";
	$sql .= "collection.amount, ";
	$sql .= "collection.created_date, ";
	$sql .= "collection.process, ";
	$sql .= "inbound.entry_id ";
	$sql .= "from ";
	$sql .= "((custmer_details inner join outbound on custmer_details.custmer_id=outbound.customer_id) ";
	$sql .= "inner join collection on custmer_details.custmer_id=outbound.customer_id) ";
	$sql .= "inner join inbound on custmer_details.custmer_id=outbound.customer_id $where $f_qry $c_qry $p_qry";

	$qry = mysql_query($sql) or $content = mysql_error().'<br />'.$sql;

	if(mysql_num_rows($qry) > 0 ) {
		$content .= '
		<table width="100%" cellpadding="0" cellspacing="0">
			<tr bgcolor="#d2d2d2">
				<th>Company</th>
				<th>Ch#</th>
				<th>Qty.</th>
				<th>O/B Amt.</th>
				<th>Date</th>
				<th>Process</th>
				<th>Collection</th>
			</tr>

			';
		while($d = mysql_fetch_object($qry)) {
			$custName = $d->company_name;
			$obCh = $d->challan_no;
			$obQty = $d->quantity;
			$obAmt = $d->total_amount;
			$obDate = $d->date;
			$ibProcess = $d->process;
			$collAmt = $d->amount;
			$collDate = $d->created_date;
			$collProcess = $d->collProcess;

			$process = $ibProcess!="" ? $ibProcess : $collProcess ;
			$date = $obDate!="" ? $obDate : $collDate;

		$content .= '
			<tr>
				<td>'.$custName.'</td>
				<td>'.$obCh.'</td>
				<td>'.$obQty.'</td>
				<td>'.$obAmt.'</td>
				<td>'.$date.'</td>
				<td>'.$process.'</td>
				<td>'.$collAmt.'</td>
			</tr>
		';

		}

		$content .= '</table>';
//			$content = $sql;
	} else {
		$content .= 'No record to display.';
	}

 

it results with 8 duplicate rows:

 

I know the problem is with SQL Syntax. Is there any other method to solve this problem.

 

Please help

 

Regards,

Bhupinder

[suma237]

 

check this

$sql="select distict..."

[mmarif4u]

 

check this

$sql="select distict..."

 

$sql="select distinct....."

not distict.

[desibhupi]

I got it fixed with the following code:

 

	$sql = "select ";
	$sql .= "custmer_details.company_name, ";
	$sql .= "outbound.challan_no, ";
	$sql .= "outbound.quantity, ";
	$sql .= "outbound.total_amount, ";
	$sql .= "outbound.date, ";
	$sql .= "collection.amount, ";
	$sql .= "collection.created_date, ";
	$sql .= "collection.process, ";
	$sql .= "inbound.entry_id ";
	$sql .= "from ";
	$sql .= "((custmer_details inner join outbound on custmer_details.custmer_id=outbound.customer_id) ";
	$sql .= "inner join collection on custmer_details.custmer_id=collection.company_id) ";
	$sql .= "inner join inbound on custmer_details.custmer_id=inbound.custID $where $f_qry $c_qry $p_qry ";
	$sql .= "group by collection.collection_id";

 

Thanks for your quick response.

 

Regards,

Bhupinder