Page 1 of 1

SQL results into array

Posted: Sun Apr 15, 2007 2:09 am
by facets
Hi All,

I'm having some trouble putting my results into an array. Any suggestions?

Code: Select all

function getAllShops(){
	
	global $linkID, $clientsID , $companyName, $shopArray;
	
    $clientQuery = 'select id, company_name from clients';
    $clientResult = mysql_query($clientQuery, $linkID) or die("Data not found.");
    
    while(list($id, $company_name) = mysql_fetch_array($clientResult)) {
        echo "<br>" . $id, $company_name;
        $shopArray = array($id, $company_name);
    }
    
    var_export($shopArray);
    
	return $shopArray;
}
tia, Will

Posted: Sun Apr 15, 2007 3:06 am
by Ollie Saunders

Code: Select all

while(list($id, $company_name) = mysql_fetch_array($clientResult)) {
Here you are separating out the columns returned from the query from an array of columns to individual values. I'm not sure it is necessary to separate them out if your main intention is to return them as an array. Also list() returns void, which means false, which means your while loop will never be entered. - In short, drop the list().

Code: Select all

$shopArray = array($id, $company_name);
Even if list wasn't to blame you would still only see the last row returned from the query in $shopArray because you are reassigning it a fresh each time the loop executes.

Posted: Sun Apr 15, 2007 4:08 am
by timvw

Code: Select all

$shopArray = array();

while(...) {
$shopArray[] = array(...);
}

Posted: Sun Apr 15, 2007 4:38 am
by facets
Thanks for the replies.

I ended up using

Code: Select all

$shopArray[] = array($row['id'] => $row['company_name']);
Are the ways you are suggesting 'better/cleaner/faster' ? (For choice of a better word)

Also, regarding calling the array in the second function, is what I have below 'the best way to access the array?'
(Best practice etc?)

Code: Select all

#--------------------------------------------
# Get all shops
#--------------------------------------------
function getAllShops(){
	
	global $linkID, $clientsID , $companyName, $shopArray;
	
    $clientQuery = 'select id, company_name from clients order by id';
    $clientResult = mysql_query($clientQuery, $linkID) or die("Data not found.");
          
	for($x = 0 ; $x < mysql_num_rows($clientResult) ; $x++){
 		$row = mysql_fetch_assoc($clientResult);
 		$shopArray[] = array($row['id'] => $row['company_name']);
	}    
	return $shopArray;
}

#--------------------------------------------
# Get debt from shops
#--------------------------------------------
function getDebt() {
    
	global $linkID, $clientResult, $shopArray;
	getAllShops($shopArray);
    
	foreach ($shopArray as $outer_key => $single_array) {          
		foreach ($single_array as $inner_key => $value) {
       		echo $inner_key;   
   		}        
   }
}

Posted: Sun Apr 15, 2007 4:52 am
by Ollie Saunders
How about

Code: Select all

$shopArray[$row['id']] = $row['company_name'];
This should simplify your other code.

Posted: Sun Apr 15, 2007 5:26 am
by facets
Is there a way to sort total?
I can only get the sort to error, badically because i'm not sorting the 'array' but trying to sort each iteration.
I have tried

Code: Select all

foreach ($single_array as sort($inner_key) => $value) {
but that errors. (You can't use that function here.)

Any ideas?

Code: Select all

function getDebt() {
    
	global $linkID, $clientResult, $shopArray;
	getAllShops($shopArray);
    
	foreach ($shopArray as $outer_key => $single_array) {          
		foreach ($single_array as $inner_key => $value) {
       		$debtQuery = "select sum(total) as total from statements where client_id = '$inner_key'";
       		$debttResult = mysql_query($debtQuery, $linkID) or die("Data not found.");
			for($x = 0 ; $x < mysql_num_rows($debttResult) ; $x++){
				$row = mysql_fetch_assoc($debttResult);
				echo "<br>Total for : " . $inner_key . " = " . $row['total'];
   			}
		}        
   }
}

Posted: Sun Apr 15, 2007 6:19 am
by Ollie Saunders
I'm not quite sure what you are asking but at a guess:

Code: Select all

foreach (ksort($single_array) as $inner_key => $value) {

Posted: Sun Apr 15, 2007 7:16 am
by facets
that's exactly what i'm trying to do but it throws the following error :
Warning: Invalid argument supplied for foreach() in newStats.php

Posted: Sun Apr 15, 2007 7:18 am
by Ollie Saunders
oh yeah, my mistake.

Code: Select all

ksort($pah);
foreach ($pah as ...

Posted: Tue Apr 17, 2007 6:23 am
by facets
Sorry peoples. I'm retracting this question!
I am extracting the totals I wish to sort after the foreach array functions.

javascript:emoticon(':oops:')

tia, will


I'm still not having any luck with a sort.
Is it actually possible to sort $value as it's in a foreach loop?

Code: Select all

function getDebt() {
    
	global $linkID, $clientResult, $shopArray;
	getAllShops($shopArray);
    
	foreach ($shopArray as $outer_key => $single_array) {        
		foreach ($single_array as $inner_key => $value) {  
       		$debtQuery = "select sum(total) as total from statements where client_id = '$inner_key'";
       		$debttResult = mysql_query($debtQuery, $linkID) or die("Data not found.");
			for($x = 0 ; $x < mysql_num_rows($debttResult) ; $x++){
				$row = mysql_fetch_assoc($debttResult);
				echo "<br>Total for : " . $inner_key . " = " . $row['total'];
   			}
		}        
   }
}
:oops:

Posted: Tue Apr 17, 2007 6:45 am
by Ollie Saunders
I'm still not having any luck with a sort.
Explain how so. What are you getting? What would you like to get?
Is it actually possible to sort $value as it's in a foreach loop?
Of course.