arrays

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
IGGt
Forum Contributor
Posts: 173
Joined: Thu Nov 26, 2009 9:22 am

arrays

Post by IGGt »

Maybe I'm making this more complicated than it needs to be, but:

I am running a MySQL query, out of this I want to put the headers into an array. I then want to put the result of the query into another array, but using the headers as the reference in the array, so that when I come to look at the data later I can reference it by the original column names.

what I have is:

Code: Select all

$dbs = "schema";

$query_array = array();
$query_array[] = array(	'query' => "SHOW SLAVE STATUS",
        				'name' => "slaveStatus");

$slaveState_array = array();
$slaveHeader_array = array();
$printed_headers = false;

for($a = 0; $a <sizeof($slaveRes_array); $a++) {
	$con = mysql_connect($slaveRes_array[$a]['server'], $slaveRes_array[$a]['user'], $slaveRes_array[$a]['password']); 
	mysql_select_db($dbs, $con);
	$dbName = $slaveRes_array[$a]['database'];        
	for($b = 0; $b <sizeof($query_array); $b++) {
		$result = mysql_query($query_array[$b]['query'], $con) or print("<p>ERROR:</p>".mysql_error());
		if ( !$result ) { goto end;}        
			while($row = mysql_fetch_assoc($result)) 	{
			
				if ( !$printed_headers )
    			{
//print the headers once:
        			//for($c = 0; $c <sizeof($row); $c++) {

					for($c = 0; $c <sizeof($row); $c++) {
					foreach ( array_keys($row) AS $header )
					
        			{
            			$slaveHeader_array[$c] = array($header);
            			print sizeof($row);
            			print $header; 
    				}
        				$printed_headers = true;
    			}}
			
				
				
				
				foreach ($row as $attribute)
				$slaveState_array[] = array($query_array[$b]['name'] => $attribute);								
													}
												
	mysql_close($con); }}
end:	
and I think I'm just going round in circles with it. any help appreciated
IGGt
Forum Contributor
Posts: 173
Joined: Thu Nov 26, 2009 9:22 am

Re: arrays

Post by IGGt »

Thinking about it:

Code: Select all

$result = mysql_query($query_array[$b]['query'], $con);
is probably what I am after, if I rename $result to $slaveState, and then I can just reference that when I need to later on.
IGGt
Forum Contributor
Posts: 173
Joined: Thu Nov 26, 2009 9:22 am

Re: arrays

Post by IGGt »

No, That doesnt seem to work. It is only giving me one result when there should be 10.

Am I missing something really obvious?

Code: Select all

//slaveQuery.php
//n.b. that $slaveRes_array is an array with the details of 10 database connections in it

for($a = 0; $a <sizeof($slaveRes_array); $a++) 	{
	$con = mysql_connect($slaveRes_array[$a]['server'], $slaveRes_array[$a]['user'], $slaveRes_array[$a]['password']); 
	mysql_select_db($dbs, $con);
	$dbName = $slaveRes_array[$a]['database'];        
	for($b = 0; $b <sizeof($query_array); $b++) {
		$slaveState = mysql_query($query_array[$b]['query1'], $con); 
												}
query2 goes here . . 
												}

Code: Select all

//slaveResult.php
for($i = 0; $i <sizeof($slaveRes_array); $i++) {
    $db = $slaveRes_array[$i]['database'];
		while($row = mysql_fetch_assoc($slaveState)) {
			if ($row['Slave_IO_Running'] == "No")	{
				print "	<tr $colour1>\n";
                                   .....and so forth

Code: Select all

//CS.php
. . .
include ('slaveQuery.php');
include ('slaveResult.php);
...
My theory being that I can then connect to each database just once, and run a series of queries against each one in slaveQuery.php, and then take the results of those queries one at a time in separate files (e.g. slaveResult.php), which I insert in my main script (CS.php) at the location I need them.
IGGt
Forum Contributor
Posts: 173
Joined: Thu Nov 26, 2009 9:22 am

Re: arrays (now failing to loop)

Post by IGGt »

OK, I found this script, which looks promising, except that it doesn't loop properly.

Code: Select all

for($a = 0; $a <sizeof($slaveRes_array); $a++) 	{
	$con = mysql_connect($slaveRes_array[$a]['server'], $slaveRes_array[$a]['user'], $slaveRes_array[$a]['password']); 
	mysql_select_db($dbs, $con);
	//$dbName = $slaveRes_array[$a]['database'];        
	for($b = 0; $b <sizeof($query_array); $b++) {
		$slaveState = mysql_query($query_array[$b]['query1'], $con); 
		
		
		      for($i=0;$i<mysql_num_rows($slaveState);$i++){
               	           for($j=0;$j<mysql_num_fields($slaveState);$j++){
                                $retval[$i][mysql_field_name($slaveState,$j)] = mysql_result($slaveState,$i,mysql_field_name($slaveState,$j));
                }//end inner loop
        }//end outer loop
												}
												}	
IGGt
Forum Contributor
Posts: 173
Joined: Thu Nov 26, 2009 9:22 am

Re: arrays

Post by IGGt »

So frustrating, I fix one thing, and that causes another problem.

By replacing

Code: Select all

for($i=0;$i<mysql_num_rows($slaveState);$i++){
                           for($j=0;$j<mysql_num_fields($slaveState);$j++){
                                $retval[$i][mysql_field_name($slaveState,$j)] = mysql_result($slaveState,$i,mysql_field_name($slaveState,$j));
                }//end inner loop
        }//end outer loop
with

Code: Select all

while(($slave_array[] = mysql_fetch_assoc($slaveState)) || array_pop($slave_array));
I now have a fully looping array. But, that made me realise that I had no way of identifying the individual lines. SO, I figured, I could simply add an entry from the slaveRes_array. I now have:

Code: Select all

if (!$slaveState) {}
			else {
                                $slave_array[]['name'] = $slaveRes_array[]['database']; 
				while(($slave_array[] = mysql_fetch_assoc($slaveState)) || array_pop($slave_array));
				}
The idea being that it runs a query against a server, and if it returns a result, then it would add $slaveRes_array[]['database'] to the end of each array. But it doesn't.
What it does do is:

a) adds the $slaveRes_array[]['database'] whether or not there was a $slaveState (e.g. the first database on the list will never return a result for this particular query, but still gets added to the array.

b) the name gets added as a separate entry to the data e.g.
Array ( [0] => Array ( [name] => MySQL01_5083 )
[1] => Array ( [name] => MySQL02_5083 )
[2] => Array ( [Slave_IO_State] => Waiting for master to send event
)
as you can see [0] => Array ( [name] => MySQL01_5083 is a database with no data returned for the query. but it its still in the array.

[1] => Array ( [name] => MySQL02_5083 )
[2] => Array ( [Slave_IO_State] => Waiting for master to send event
these both refer to the same thing, and so should be:
[1] => Array ( [name] => MySQL02_5083 )
( [Slave_IO_State] => Waiting for master to send event)...

Is there a way to achieve this?
Post Reply