Bigger Query Instead of Using arrays, and Pagination Help

imperium2335 · September 13, 2011

Hi,

I am trying to create a view where the user can sort by any column.

What I have made so far works great, but when it comes to pagination, which I will be trying soon, I foresee a problem .

The thing is, my code gets data out of a database and puts it into a multidimensional array, which is sorted using afew array sorting functions.

It is a query that drives the main loop, and inside that loop are other queries that get various data from other tables, which is where the problem lies when coming to sort with pagination.

What I would like to know is how would I build a myslq query that can accomplish the same thing as my code, but do away with the need for the arrays?

My code is here, sorry about it being so long:

<?PHP



if($_POST['targetid']) {



$box = array() ;



if($_POST['type']) {

	$extra = 'AND entity_details.typeRef = ?' ;

	$targetType = $_POST['type'] ;

}



$sortMode = $_POST['sortmode'] ;



$targetUser = $_POST['targetid'] ;



include('pdoconnect.php') ;



$result = $dbh->prepare("SELECT entity_details.name, entity_contacts.name AS cName, entity_contacts.id, entity_details.countryRef

						 FROM entity_details, entity_contacts

						 WHERE entity_contacts.isPrimary = 1

						 AND entity_contacts.entityRef = entity_details.id

						 $extra

						 AND entity_details.ownerRef = ?") ;

if($extra) {

	$result->bindParam(1, $targetType, PDO::PARAM_INT) ;

	$result->bindParam(2, $targetUser, PDO::PARAM_INT) ;

}

else 

	$result->bindParam(1, $targetUser, PDO::PARAM_INT) ;



	$result->execute() ;



$count = $result->rowCount() ;



if($count > 0) {

	echo "Showing " ;

	if($targetType == 2)

			echo "prospects!" ;

		elseif($targetType == 3)

			echo "customers!" ;

		elseif($targetType == 4)

			echo "leads!" ;

		else

			echo "everything." ;

	echo '<br />' ;

}

if($count > 0) {

	$mode = $_GET['sort'] ;

	echo "<div class='row'>\n" ;

	echo "<div class='cell'>" ;

	if($sortMode != 'name')

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'name');\">Name</a>" ;

	else

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rrname');\">Name</a>" ;

	echo "</div>" ;

	echo "<div class='cell'>" ;

	if($sortMode != 'contact')

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'contact');\">Contact</a>" ;

	else

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rrcontact');\">Contact</a>" ;

	echo "</div>" ;

	echo "<div class='cell'>" ;

	if($sortMode != 'email')

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'email');\">Email</a>" ;

	else

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rremail');\">Email</a>" ;

	echo "</div>" ;

	echo "<div class='cell'>" ;

	if($sortMode != 'tel')

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'tel');\">Tel</a>" ;

	else

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rrtel');\">Tel</a>" ;

	echo "</div>" ;

	echo "<div class='cell'>" ;

	if($sortMode != 'payment')

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'payment');\">Payment Terms</a>" ;

	else

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rrpayment');\">Payment Terms</a>" ;

	echo "</div>" ;

	echo "<div class='cell' style='width:30px;'>" ;

	if($sortMode != 'currency')

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'currency');\">Cur</a>" ;

	else

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rrcurrency');\">Cur</a>" ;

	echo "</div>" ;

	echo "<div class='cell'>" ;

	if($sortMode != 'country')

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'country');\">Country</a>" ;

	else

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rrcountry');\">Country</a>" ;

	echo "</div>" ;

	echo "<div class='cell' style='width:100px;'>" ;

	if($sortMode != 'lastc')

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'lastc');\">Last Contacted</a>" ;

	else

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rrlastc');\">Last Contacted</a>" ;

	echo "</div>" ;

	echo "<div class='cell' style='width:100px;'>" ;

	if($sortMode != 'lastm')

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'lastm');\">Last Marketed</a>" ;

	else

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rrlastm');\">Last Marketed</a>" ;

	echo "</div>" ;

	echo "<div class='cell' style='width:100px;'>" ;

	if($sortMode != 'lastd')

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'lastd');\">Last Deal</a>" ;

	else

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rrlastd');\">Last Deal</a>" ;

	echo "</div>" ;

	echo "<div class='cell'  style='width:50px;'>" ;

	if($sortMode != 'cltv')

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'cltv');\">CLTV</a>" ;

	else

		echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rrcltv');\">CLTV</a>" ;

	echo "</div>" ;

	echo "</div>\n" ;

}

else {

	echo "You have no " ;

	if($targetType == 2)

		echo "prospects!" ;

	elseif($targetType == 3)

		echo "customers!" ;

	elseif($targetType == 4)

		echo "leads!" ;

	else

		echo "nothing!" ;

}





while($row = $result->fetch(PDO::FETCH_ASSOC)) {

	$id = $row['id'] ;

	$countryid = $row['countryRef'] ;



	// Get primary contact data.

	$resultName = $dbh->prepare("SELECT entity_contacts_emails.email, entity_contacts_telephones.tel

						 FROM entity_contacts_emails, entity_contacts_telephones

						 WHERE entity_contacts_emails.contactRef = ?

						 AND entity_contacts_telephones.contactRef = ?

						 LIMIT 1") ;

	$resultName->bindParam(1, $id, PDO::PARAM_INT) ;

	$resultName->bindParam(2, $id, PDO::PARAM_INT) ;

		$resultName->execute() ;



	$rowName = $resultName->fetch(PDO::FETCH_ASSOC) ;

	///////////////////////////



	// Get country.

	$resultCountry = $dbh->prepare("SELECT country

						 FROM countries

						 WHERE id = ? LIMIT 1") ;

	$resultCountry->bindParam(1, $countryid, PDO::PARAM_INT) ;

		$resultCountry->execute() ;



	$rowCountry = $resultCountry->fetchColumn() ;

	///////////////////////////



	// Get currencies dealt with.

	$resultCurrencies = $dbh->prepare("SELECT currencies.symbol

						 FROM entity_currencies_dealt, currencies

						 WHERE entity_currencies_dealt.currencyRef = currencies.id

						 AND entity_currencies_dealt.entityRef = ?") ;

	$resultCurrencies->bindParam(1, $id, PDO::PARAM_INT) ;

		$resultCurrencies->execute() ;



	while($rowCurrencies = $resultCurrencies->fetch(PDO::FETCH_ASSOC)) {

		$currencyString .= $rowCurrencies['symbol'] . " " ;

	}

	///////////////////////////



	// Get payment terms.

	$resultTerms = $dbh->prepare("SELECT entity_payment_terms.term

						 FROM entity_details, entity_payment_terms

						 WHERE entity_details.paymentTermsRef = entity_payment_terms.id

						 AND entity_details.id = ?") ;

	$resultTerms->bindParam(1, $id, PDO::PARAM_INT) ;

		$resultTerms->execute() ;



	$resultTerms = $resultTerms->fetchColumn() ;

	///////////////////////////

	// Get last contacted.

	$resultLastCon = $dbh->prepare("SELECT DATE(date)

						 FROM contact_method_history

						 WHERE id = ?

						 ORDER BY date

						 LIMIT 1") ;

	$resultLastCon->bindParam(1, $id, PDO::PARAM_INT) ;

		$resultLastCon->execute() ;



	$resultLastCon = $resultLastCon->fetchColumn() ;

	///////////////////////////



	// Get last enquired.

	$resultLastEnq = $dbh->prepare("SELECT DATE(dateCreated)

						 FROM entity_enquiries

						 WHERE entityRef = ?

						 ORDER BY DATE(dateCreated)

						 LIMIT 1") ;

	$resultLastEnq->bindParam(1, $id, PDO::PARAM_INT) ;

		$resultLastEnq->execute() ;



	$resultLastEnq = $resultLastEnq->fetchColumn() ;

	///////////////////////////





	array_push($box, array(rawurldecode($row['name']), rawurldecode($row['cName']), $rowName['email'], $rowName['tel'], $resultTerms, trim($currencyString), $rowCountry, $resultLastCon, $resultLastEnq)) ;



	$currencyString = '' ;



}





}



$i = 0 ;



function subval_sort($a, $subkey, $mode) {

foreach($a as $k=>$v) {

	$b[$k] = strtolower($v[$subkey]);

}

if(strstr($mode, 'Rr'))

	arsort($b);

else

	asort($b);

foreach($b as $key=>$val) {

	$c[] = $a[$key];

}

return $c;

}



if($sortMode == 'name' || $sortMode == 'Rrname')

$x = 0 ;

if($sortMode == 'contact' || $sortMode == 'Rrcontact')

$x = 1 ;

if($sortMode == 'email' || $sortMode == 'Rremail')

$x = 2 ;

if($sortMode == 'tel' || $sortMode == 'Rrtel')

$x = 3 ;

if($sortMode == 'payment' || $sortMode == 'Rrpayment')

$x = 4 ;

if($sortMode == 'currency' || $sortMode == 'Rrcurrency')

$x = 5 ;

if($sortMode == 'country' || $sortMode == 'Rrcountry')

$x = 6 ;

if($sortMode == 'lastc' || $sortMode == 'Rrlastc')

$x = 7 ;

if($sortMode == 'laste' || $sortMode == 'Rrlaste')

$x = 8 ;

if($count > 0)

$box = subval_sort($box, $x, $sortMode); 



foreach($box as $row) {

echo "<div class='row'>\n" ;

echo "<div class='cell'>" ;

echo $row[0] ;

echo "</div>" ;

echo "<div class='cell'>" ;

echo $row[1] ;

echo "</div>" ;

echo "<div class='cell'>" ;

echo $row[2] ;

echo "</div>" ;

echo "<div class='cell'>" ;

echo $row[3] ;

echo "</div>" ;

echo "<div class='cell'>" ;

echo $row[4] ;

echo "</div>" ;

echo "<div class='cell' style='width:30px;'>" ;

echo $row[5] ;

echo "</div>" ;

echo "<div class='cell'>" ;

echo $row[6] ;

echo "</div>" ;

echo "<div class='cell'>" ;

echo $row[7] ;

echo "</div>" ;

echo "<div class='cell' style='width:100px;'>" ;

echo $row[8] ;

echo "</div>" ;

echo "<div class='cell' style='width:100px;'>" ;

echo $row[9] ;

echo "</div>" ;

echo "<div class='cell' style='width:100px;'>" ;

echo $row[10] ;

echo "</div>" ;

echo "<div class='cell' style='width:50px;'>" ;

echo $row[11] ;

echo "</div>" ;

echo "</div>\n" ;

}



?>

I know maybe it can be done using many subqueries(?) but I am pretty average at MySQL.

Can anyone tell me how?

imperium2335 · September 13, 2011

Sorry, I meant to post this in PHP coding help, would a Mod be so kind as to move it for me? Thanks.

imperium2335 · September 14, 2011

Anyone have any ideas?

cunoodle2 · September 14, 2011

The fact that the OP has posted 3 times with no responses in the past 25 hours makes me move on to the next thread...........

imperium2335 · September 14, 2011

Despite your highly intellectual efforts to help, I have solved it.

Here it is for reference:

#Returns the first customer
$dbh->query('SELECT entity_details.name, entity_contacts.name, entity_contacts_emails.email, entity_contacts_telephones.tel, countries.country, currencyTable.symbol, entity_payment_terms.term,
(SELECT DATE(date) FROM contact_method_history WHERE entityRef = entity_details.id ORDER BY DATE(date) DESC LIMIT 1) AS lastContacted,
(SELECT DATE(dateCreated) FROM enquiries WHERE entityRef = entity_details.id ORDER BY DATE(dateCreated) DESC LIMIT 1) AS lastEnquired
FROM entity_details
LEFT JOIN entity_contacts ON entity_details.id = entity_contacts.entityRef
LEFT JOIN entity_contacts_emails ON entity_contacts.id = entity_contacts_emails.contactRef
LEFT JOIN entity_contacts_telephones ON entity_contacts.id = entity_contacts_telephones.contactRef
LEFT JOIN countries ON entity_details.countryRef = countries.id
LEFT JOIN entity_payment_terms ON entity_details.paymentTermsRef = entity_payment_terms.id,
(SELECT currencies.symbol, entity_currencies_dealt.entityRef FROM currencies, entity_currencies_dealt WHERE entity_currencies_dealt.currencyRef = currencies.id) AS currencyTable
WHERE entity_details.id = 1
AND entity_contacts.isPrimary = 1
AND currencyTable.entityRef = entity_details.id
LIMIT 1') ;

Sign In

Bigger Query Instead of Using arrays, and Pagination Help

Recommended Posts

imperium2335

Link to comment

Share on other sites

imperium2335

Link to comment

Share on other sites

imperium2335

Link to comment

Share on other sites

cunoodle2

Link to comment

Share on other sites

imperium2335

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information