SQL results into array

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

SQL results into array

Post 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
Last edited by facets on Sun Apr 15, 2007 4:43 am, edited 1 time in total.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Code: Select all

$shopArray = array();

while(...) {
$shopArray[] = array(...);
}
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post 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;   
   		}        
   }
}
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

How about

Code: Select all

$shopArray[$row['id']] = $row['company_name'];
This should simplify your other code.
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post 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'];
   			}
		}        
   }
}
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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) {
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post 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
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

oh yeah, my mistake.

Code: Select all

ksort($pah);
foreach ($pah as ...
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post 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:
Last edited by facets on Tue Apr 17, 2007 6:45 am, edited 1 time in total.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
Post Reply