mysql array help

[n0303]

I am trying to dump the contents of a single column into an array.

What I currently have is not working, I am new to this so please be patient.

If you could point out what I am doing wrong I would gladly appreciate it.

mysql_select_db("cacti");

$query = "select management_ip from host where management_ip is not null" ;

$array = mysql_fetch_array($query);

$num_results = mysql_num_rows($array);

/ now process each array element /
/ init value count and set to 0 /

$count = 0;

/ run loop until value count is equal to num_results /

while ($count != $num_results)

    {
     echo $array[$count++] ;
    }

/ temp line to show end of script, remove when completed /

echo "<br>End of script";

[jernhenrik]

A good thing to remember is to use

or die("Mysql Error:".mysql_error());

after all your mysql queries...that will help you find problems much easier...
eg:

mysql_select_db("cacti") or die("Mysql Error:".mysql_error()."\n");

..and you might have forgotten to do the actual query???
eg:

$result=mysql_query($query) or die ("Unable to do query: ".mysql_error()."\n");

there is an easier way of lopping throu your results:

while($row=mysql_fetch_array($result))
{
    echo "IP: ".$row[0];//or however
}

all in all:

		$servername	= "xxxxx";
		$username  	= "xxxxx";
		$password 	= "xxxxx";
		$db_navn   	= "xxxxx";
		if (!mysql_connect($servername, $username, $password))
		{
			echo mysql_error()."\n";
			die("Unable to connect\n");
		}
		mysql_select_db("$db_navn") or die("Unable to select database ".mysql_error()."\n");
		$query="SELECT management_ip FROM host WHERE management_ip is not null";
		$result=mysql_query($query) or die ("Unable to do query".mysql_error()."\n");
		while($row=mysql_fetch_array($result))
		{
			echo $row[0]."<br>";
		}
		mysql_close();

there still might be some errors but these will become clearer when you see the error messages from the or die functions!

hth

[n0303]

I am going to take those pieces of advice and ammend my basic script. However the actual problem I am running into is there is nothing in array elements 1-11 of a 12 element array. Only the first element gets populated. When I run the same sql query against the db in the mysql client it works just fine.

[n0303]

Well, here is what i have, php is balking on line 24 and 25. Any more ideas?

<?

/ define constant data /

$db_servername = "localhost" ;
$db_username = "cactiuser" ;
$db_password = "password" ;
$db_navn ="cacti" ;
$query = " select id, management_ip, snmp_username, snmp_password from host where id != '1' " ;

/ connect to db , check connection /

@ $db = mysql_pconnect("$db_servername" , "$db_username" , "$db_password");

if (!$db)
{echo "Error: Could not connect to database";
exit;
}

/ query db , store data in array, count number of entires /

mysql_select_db("$db_navn") or die ("Unable to select database \n");
$result = mysql_query($query) or die ("Unable to perform query \n");
$array = mysql_fetch_array($query);
$num_results = mysql_num_rows($array);

/ now process each array element /
/ init value count and set to 0 /

$count = 0;

/ run loop until value count is equal to num_results /

while ($count != $num_results)

    {
     echo $array[$count++] ;
     echo "!";
    }

/ temp line to show end of script, remove when completed /

echo "<br>End of script";

?>

[jernhenrik]

Originally posted by n0303
Well, here is what i have, php is balking on line 24 and 25. Any more ideas?
[snip]..

 $result = mysql_query($query) or die ("Unable to perform query \n");
 $array = mysql_fetch_array($query);//<-this should be $array=mysql_fetch_array($result);

try it again..
when you tell error line xx,xx please tell what lines it is in the code...it's a bugger counting to line 24 in this setup

hth

[n0303]

OK, it runs. But it stil only displays the first element. It does finish the script and loop as the other dozen bangs show up and the end of script comment shows up as well. Any ideas?

[jernhenrik]

sure here is one 🙂

every time you call the function mysql_fetch_array($result) you extract some values from the results of you query. What you are doing is actually to extract the value index $array[0], $array[1], $array[2], $array[3], from the same array!!!!! You need to call the mysql_fetch_array() the number of times that you have record sets. Or else the only thing you'll get printed is one result set!!

that was why I suggested in my first post that you use a while loop:

eg:

while($array=mysql_fetch_array($result))
{
echo "ID: ".$array[0]."<br />";
echo "management ip: ".$array[1]."<br />";
echo "snmp username: ".$array[2]."<br />";
echo "snmp password".$array[3]."<br />";
echo "<hr>";
}

so what i suggest is that you loose these lines here and replace them with the while just above above, and see if that works as intended:

$array = mysql_fetch_array($query); 
$num_results = mysql_num_rows($array); 
/* now process each array element */ 
/* init value count and set to 0 */ 

$count = 0; 

/* run loop until value count is equal to num_results */ 

while ($count != $num_results) 

{ 
echo $array[$count++] ; 
echo "!"; 
} 

..and besides the $num_results = mysql_num_rows($array);
is incorrect the mysql_num_rows need to be used on $result;
eg:

$num_results = mysql_num_rows($result); 

but try and drop it and do as i explained

hth

[n0303]

I suppose this needs to be in a 3d array. If so , how would i take a query and make it so that the following is true,

id = array[0][0]
ip_address = array[0][1]
hostname = array[0][2]

After I figure this out and I verify it by printing the array contents with the loop I plan to use this information to telnet to those devices and get information from them, then dump this info to a file.

Thanks again for your help.

[n0303]

Also, can you elaborate on how that while function works? Something is not quite making sense to me.
Thanks

[jernhenrik]

oki...if I understand you correctly here is a suggestion:

$telnet_array=array();	//declare outside while-loop
$count=0;
while($array=mysql_fetch_array($result))
{
	/*killing the echos we dont need them now
	echo "ID: ".$array[0]."<br />";
	echo "management ip: ".$array[1]."<br />";
	echo "snmp username: ".$array[2]."<br />";
	echo "snmp password".$array[3]."<br />";
	echo "<hr>";
	*/

$telnet_array[$count][0]=$array["id"];
$telnet_array[$count][1]=$array["management_ip"];
$telnet_array[$count][2]=$array["host_name"];	//or what it is called in your database
$count++;
}

//after this you can play with the $telnet_array to do whatever

and the while loop works by walking throu the result, one array at a time. It'll go throu it the amount of times that you have number of result sets, when it reaches the end of the result set it simply ends.

http://www.php.net/manual/en/control-structures.while.php

hth

[n0303]

I know this may sound stupid, but I have an easier time visualizing the while loop the way I have it, is it OK to use it this way?
Thanks

[n0303]

if i wanted to echo a single element of the array to test to see if its working, such as element $telnet_array[1][2] , how do i do that. It appears to not be evalutating the statement I wrote properly.

[jernhenrik]

just

echo $telnet_array[1][2];

it's oki that you do it the way you like to do, if you can't get it filled correctly remember to debug by echoing your variables as you try and put them into the $telnet_array...

there is a debuggin 1 on 1 thread written by bunkermaster i think, take a look at it....

http://www.phpbuilder.com/board/showthread.php?s=&threadid=10240608&highlight=debugging

[n0303]

Well, this is what I have so far and the array is only
populating in [0][0-3] . There is nothing in the array
when i echo anything else. So for example,
[0][0] " some result "
[0][1] " some result "
[0][2] " some result "
[0][3] " some result "

[1][0] no result
[1][1] no result
etc...
Any ideas?

---

<?

/ define constant data /

$db_servername = "localhost";
$db_username = "cactiuser";
$db_password = "password";
$db_navn = "cacti";
$query = "select id, management_ip,
snmp_username, snmp_password, hostname from host" ;
$telnet_array = array();
$count_0 = 0;
$count_1 = 0;
$num_0 = 0;
$num_1 = 0;

/ connect to db , check connection /

@ $db = mysql_pconnect("$db_servername" ,
"$db_username" , "$db_password");

if (!$db)
{echo "Error: Could not connect to database";
exit;
}

/ query db , store data in array, count number of
entires /

mysql_select_db("$db_navn") or die ("Unable to select
database \n");
$result = mysql_query($query) or die ("Unable to
perform query \n");
$array = mysql_fetch_array($result) or die ("Unable
to perform fetch \n");
$num_results = mysql_num_rows($result) or die
("Unable to count rows \n");

/ build arrays for telnet /
while ($count_0 <= $num_results)
{
$telnet_array[$num_0][0] = $array["id"];
$telnet_array[$num_0][1] = $array["management_ip"];
$telnet_array[$num_0][2] = $array["snmp_username"];
$telnet_array[$num_0][3] = $array["snmp_password"];
$telnet_array[$num_0][4] = $array["hostname"];
$count_0++;
}

/ temp line to show end of script, remove when
completed /
echo $telnet_array[2][2];
echo "<br>End of script";

?>

[n0303]

Any ideas or am I going to have to get the elements one row at a time and merge the array?

[n0303]

OK, so i have mod'ed the script to make several queries to the db and place each set of results into a seperate array. I then want to join the arrays into one. However before I proceeded to do that I tried to echo a few of the values and they are empty. Can anyone see what is wrong with this?

Thanks

---

$db_servername = "localhost";
$db_username = "cactiuser";
$db_password = "password";
$db_navn = "cacti";
$query_id = "select id from host" ;
$query_name = "select hostname from host";
$query_ip = "select management_ip from host";
$query_user = "select snmp_username from host";
$query_pass = "select snmp_password from host";
$telnet_array = array();
$id_array = array();
$name_array = array();
$ip_array = array();
$user_array = array();
$pass_array = array();
$count_0 = 0;
$count_1 = 0;
$num_0 = 0;
$num_1 = 0;
$Error_query = "Unable to perform query";
$Error_fetch = "Unable to perform fetch";

/ connect to db , check connection /

@ $db = mysql_pconnect("$db_servername" , "$db_username" , "$db_password");

if (!$db)
{echo "Error: Could not connect to database";
exit;
}

/ query db , store data in array, count number of entires /

mysql_select_db("$db_navn") or die ("Unable to select database \n");

$result = mysql_query($query_id) or die ("$Error_query"."\n");
$id_array = mysql_fetch_array($result) or die ("$Error_fetch"."\n");
$num_results = mysql_num_rows($result) or die ("Unable to count rows \n");

$result = mysql_query($query_name) or die ("$Error_query"."\n");
$name_array = mysql_fetch_array($result) or die ("$Error_fetch"."\n");

$result = mysql_query($query_ip) or die ("$Error_query"."\n");
$ip_array = mysql_fetch_array($result) or die ("$Error_fetch"."\n");

$result = mysql_query($query_user) or die ("$Error_query"."\n");
$user_array = mysql_fetch_array($result) or die ("$Error_fetch"."\n");

$result = mysql_query($query_pass) or die ("$Error_query"."\n");
$pass_array = mysql_fetch_array($result) or die ("$Error_fetch"."\n");

/ build arrays for telnet /
/ while ($count_0 <= $num_results) /
/i { /
/ $telnet_array[$num_0][0] = $array["id"]; /
/ $telnet_array[$num_0][1] = $array["management_ip"]; /
/ $telnet_array[$num_0][2] = $array["snmp_username"]; /
/ $telnet_array[$num_0][3] = $array["snmp_password"]; /
/ $telnet_array[$num_0][4] = $array["hostname"]; /
/( $count_0++; /
/ /

/ temp line to show end of script, remove when completed /
echo $name_array[3];
echo $pass_array[1];

echo "<br>End of script";