PHP - Problem With Arrays In Url Query

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?

Given the following query,

SELECT t1.a, t1.b, t2.c FROM t1 INNER JOIN t2 ON t2.id=t1.t2_id WHERE t1.pk=123;

I get the following three records:

array(
  array('a'=>1,'b'=>2,'c'=>4),
  array('a'=>1,'b'=>2,'c'=>5),
  array('a'=>1,'b'=>2,'c'=>8)
)

What would be the best way to get just one record such as the following?

array('a'=>1,'b'=>2, 'c'=>array(4,5,8))

My thoughts were to use MySQL's GROUP_CONCAT, and then use implode() to turn it into an array, but didn't know if there was a better way.   Thanks

Here is my code snippets:

<form name="form2" method="post" action="<?php $_SERVER['PHP_SELF']; ?>">
<?php   
     $i=1;
   while($i<=floor($_REQUEST['numpack']))
  {
  ?>
  <table width="325" border="0" cellspacing="0" cellpadding="0" style="float: left;" class="aluminium">
  <tr>
    <td align="center" rowspan="2"><span class="transparent" style="color: #FFF; font-weight: bold;"><?=$i; ?>.</span></td>
    <td align="center">L</td>
    <td align="center">x</td>
    <td align="center">W</td>
    <td align="center">x</td>
    <td align="center">H</td>
  </tr>
  <tr>
    <td align="center"><input name="length[]" type="text"  size="4" maxlength="3" /></td>
    <td align="center">x</td>
    <td align="center"><input name="width[]" type="text" size="4" maxlength="3" /></td>
    <td align="center">x</td>
    <td align="center"><input name="height[]" type="text"  size="4" maxlength="3" /></td>
  </tr>
</table>
    <?php
  $i++;
  }
?>
<input type="hidden" name="numarea" value="<?=$_REQUEST['numpack']; ?>" />
<input name="" type="submit" value="Submit" />
</form>

The problem is getting the data from the array set in the above form...

This is what I have and it comes back with errors:

<?php    
}
If (isset($_REQUEST['numarea'])) {
 echo $_REQUEST['numarea'];
 $vol=0;
 $i=1;
   while($i<=ceil($_REQUEST['numarea']))
  {
    $volume[$i] = $_REQUEST['width'] * $_REQUEST['height'] * $_REQUEST['length'];
    echo $volume[$i];
    $vol = $vol + $volume[$i];  
    $i++;
  }
  echo $vol;  
}
?>

The echos are to put out the data when its calculated...

Hey guys. I am creating an application that require dynamic fields generated by javascript. I have managed to create the forms the database and pretty much may things. I come to hit a roadblock with this array problem. I am trying to get the data from the browser by checking which one contain more than one value and then transverses through another array this one

$nyingi = [ 'location_' => ['text' => 'loc_txt','priority' => 'loc_order'], 
'photo_' => ['path' => 'p_txt'], 
'video_' => ['path' => 'v_txt'], 
'product_' => ['p_id' => 'pt_q', 'text' => 'pt_txt', 'expire' => 'pt_xpr', 'title' => 'pt_tt'], 
'service_' => ['p_id' => 'st_q', 'text' => 'st_txt', 'expire' => 'st_xpr', 'title' => 'st_tt'], 
'hours_' => ['time1' => 't1', 'time2' => 't2', 'type' => 'tt'], 
'map_' => ['text' => 'm_txt']
];