Array from database outputs only the first entry?

[supertroels]

I have a script that is supposed to get all entries in a certain table and output it in an associative array. Then it's supposed to be passed to a PDF creater function. All this is good and is working fine, except that the array only holds the first entry in the databse and not the rest. CODE:

$result = mysql_query("SELECT * FROM $what WHERE belong_to_year = ".$year."");
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {

   $i=0;
    foreach ($line as $col_value) {
  if ((mysql_field_name($result, $i) != "belong_to_year")&&
      (mysql_field_name($result, $i) != "pay_mgmt_chk")
      )
      
      {
			$field=realWords(mysql_field_name($result, $i));
			  if (mysql_field_name($result, $i) == "date") {
        			$array[$field] = date("d/m Y", strtotime($col_value));
        				$i++;
        		  } elseif (mysql_field_name($result, $i) == "amount_num") {
        			$array[$field] = showMoney($col_value);
        				$i++;
        		  } else {
        			$array[$field] = $col_value;
        				$i++;
  			}
  	}	
  }
}	

$out = array($array);

print_r($out);

 

I do not get it. Can anyone help me please?

 

[allworknoplay]

I think because your $i=0 is within your WHILE loop...I think you have to put that above the loop....like this:

 

 

$result = mysql_query("SELECT * FROM $what WHERE belong_to_year = ".$year."");

$i=0;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {

  
    foreach ($line as $col_value) {
     if ((mysql_field_name($result, $i) != "belong_to_year")&&
         (mysql_field_name($result, $i) != "pay_mgmt_chk")
         )
        
         {
            $field=realWords(mysql_field_name($result, $i));
              if (mysql_field_name($result, $i) == "date") {
                 $array[$field] = date("d/m Y", strtotime($col_value));
                    $i++;
                } elseif (mysql_field_name($result, $i) == "amount_num") {
                 $array[$field] = showMoney($col_value);
                    $i++;
                } else {
                 $array[$field] = $col_value;
                    $i++;
           }
     }   
  }
}   

$out = array($array);

print_r($out);

[supertroels]

Nope, not it. Does the same thing still. :-\

UPDATE: Something happens all right. When I move $i=0 outside the While loop the array contains the second entry in the database???

[allworknoplay]

Nope, not it. Does the same thing still. :-\

UPDATE: Something happens all right. When I move $i=0 outside the While loop the array contains the second entry in the database???

 

It just seems that there's a problem with the incrementing. That's why you are only getting one result into the DB, whether it's the 1st entry, 0 entry, etc....

 

I would step back a little and forget about updating the DB, and outputting the results so that you can see what is EXPECTED to go into the DB....

[supertroels]

Nothing is going into the DB. I am trying to pull data from DB to an array, but as you know I am only getting the first row and not the rest of them (there are around fifty or so in the specific table). I had suspected the incrementing to be the problem, but I can't see exactly what's wrong with it. Anyone?

[allworknoplay]

Nothing is going into the DB. I am trying to pull data from DB to an array, but as you know I am only getting the first row and not the rest of them (there are around fifty or so in the specific table). I had suspected the incrementing to be the problem, but I can't see exactly what's wrong with it. Anyone?

 

Oh my apologies....

 

Let me take a second look....

[Mark Baker]

You're fetching the array using MYSQL_ASSOC, so why not use

foreach ($line as $col_key => $col_value)

This gives you each column name in $col_key, so no need for the calls to mysql_field_name()

[supertroels]

You're fetching the array using MYSQL_ASSOC, so why not use

foreach ($line as $col_key => $col_value)

This gives you each column name in $col_key, so no need for the calls to mysql_field_name()

 

Good point. Thank you, changed it. I am not that skilled in using arrays, so advice like this is much appreciated :-)

[Ken2k7]

What does the $i variable do? Seems useless to me. Can you tell me what columns your table have?

[supertroels]

What does the $i variable do? Seems useless to me.

You're right. It was useless. As I said: "not that skilled"

 

Can you tell me what columns your table have?

id, date, descript, payee, amount_num, pay_mgmt_chk and belong_to_year

 

The code now looks like this, but with the same problem:

$result = mysql_query("SELECT * FROM $what WHERE belong_to_year = ".$year."");
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {


    foreach ($line as $col_key => $col_value) {
  if (($col_key != "belong_to_year")&&
      ($col_key != "pay_mgmt_chk")
      )
      
      {
			$field=realWords($col_key);
			  if ($col_key == "date") {
        			$array[$field] = date("d/m Y", strtotime($col_value));
        		  } elseif ($col_key == "amount_num") {
        			$array[$field] = showMoney($col_value);
        		  } else {
        			$array[$field] = $col_value;
  			}
  	}	
  }
}

 

 

 

[Ken2k7]

$result = mysql_query("SELECT id, DATE_FORMAT(date, '%m/%d %Y') AS date, descript, payee, amount_num FROM $what WHERE belong_to_year = ".$year."");
while ($row = mysql_fetch_assoc($result)) {
     $id = $row['id'];
     $date = $row['date'];
     $descript = $row['descript'];
     $payee = $row['payee'];
     $amount_num = $row['amount_num'];
}

I don't know what the function showValue does, but use the code above. You don't have to store everything in an array, do you? I'm sure you can do whatever you need to do in that loop.

[supertroels]

Thank you for taking the time, but I have to output everything as an array, as the PDF function I am using, requires just that. Furthermore, I need the script to be able to adjust to different tables with different columns, so that's why I am not doing it the way you explain here. Thanks anyway :-)

[Ken2k7]

Then work on DB design first. I can't imagine you'll be adding too many columns to it. It'll just be a simple edit anyways, right?

[supertroels]

Ken2k7: I think you misunderstand me. I need the entries from the table to be outputted to an array and it should be possible this way. I can't see what I am doing wrong, but to use another solution, that doesn't involve the creation of an array that holds all the data pulled, is not an option. Thanks for your help.

[Ken2k7]

Then put them in an array! Do you need me to do that for you?

 

As for the other columns, change the SQL to

SELECT id, DATE_FORMAT(date, '%m/%d %Y') AS date, descript, payee, amount_num FROM $what WHERE belong_to_year = ".$year."

 

And then use a foreach loop on $row inside the while loop I have there. From there, store the values into an array.

[supertroels]

Don't get pissed with me. I thought that I was doing just what you are describing, but in a way where the same script could be used on different table structures. What I do not get, is that the original solution does not work, though it (at least in my mind) makes logical sense.

 

Obviously I can't see why your solution is easier. I am not that skilled, as I said earlier, and arrays is one of the things I can't seem to get my head around. Don't spend any more time on this, as you will only be further frustrated with me. I'll find a way to fix this.

[supertroels]

I fixed it.

 

This works the way I wanted it to.

 

$result = mysql_query("SELECT * FROM $what WHERE belong_to_year = ".$year." ORDER BY date DESC");
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

  foreach ($row as $col_key => $col_value) {
  if (($col_key != "belong_to_year")&&($col_key != "pay_mgmt_chk"))	{

			$col_key = realWords($col_key);
						  
			  if ($col_key == "date") {
        				$array[$col_key] = date("d/m Y", strtotime($col_value));
        		  } elseif ($col_key == "amount_num") {
        				$array[$col_key] = showMoney($col_value);
        		  } else {
        				$array[$col_key] = $col_value;
  				  }
  	   		}	
  }

$out[] = $array;

}

 

Now $out holds the info i wanted it too.