[RESOLVED] Using mysql_fetch_array or alternative

[lerro]

How can i use mysql_fetch array or an alternative to display output in an MVC structure?

Model - Connects to the database and a function that queries the table for details and returns the result

Controller - receive result from function and passes it to view

View - displays the table values, item_id and item_name in a drop-down list using a loop

Without an MVC structure, the process would look like this

<?php
$con = mysql_connect("localhost", "peter", "abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

$db_selected = mysql_select_db("test_db",$con);
$sql = "SELECT * from Person WHERE Lastname='Refsnes'";
$result = mysql_query($sql,$con);
print_r(mysql_fetch_array($result));

mysql_close($con);
?> 

How can the same be achieved in an MVC. The challenge is how to get the entire array stored in result, to be displayed correctly in view.

[dagon]

instead of
print_r(mysql_fetch_array($result));
assign it to a var.

[NogDog]

A typical solution might be to have the model provide a method that returns an array of the query result data which could then be used in the view. Or you could be more OOP about it and return a Person object.

[lerro]

I have passed the array to a variable but now cannot get the "foreach" statement to output it correctly.

When i try to loop through , this is what i get in the browser

I have tried different arrays that's why it's so

1 1 F F Array ( [0] => 1 [property_type_id] => 1 [1] => Flat [property_type] => Flat ) -----Key: 0; Value: 1
Key: property_type_id; Value: 1
Key: 1; Value: Flat
Key: property_type; Value: Flat

The arrays used are

<form>

<?php

 /*** loop over the results ***/
 foreach($property_type_list as $row)
 {
     /*** create the options ***/
     echo '<option value="'.$row['property_type_id'].'"';
     if($row['property_type_id']==1)
     {
         echo ' selected';
     }
     echo '>'. $row['property_type'] . '</option>'."\n";
 }


print_r($property_type_list);

echo "-----";

foreach($property_type_list as $key => $value){
	 echo "Key: $key; Value: $value<br />\n";

}

?>

</form>

What is the best way to construct a foreach statement that would loop through correctly, displaying all items from the array in a drop-down?

The array is based on a table that has 2 fields selected in the query
Field 1 - property_id
Field 2 - property_type

"SELECT property_type_id, property_type FROM property_type"

[johanafm]

You'd need to have a select element to begin with.

I also noticed that your form element is lacking required attributes.

Validate your html output here.

[lerro]

When i use the following function

print_r($result);

I get this

Array ( [0] => 1 [property_type_id] => 1 [1] => Flat [property_type] => Flat ) 

But instead of the array having fieldname and value, it seems to have an unfamiliar structure, which is 0 -> 1 then fieldname -> Value as shown below

[0] => 1 
[property_type_id] => 1 
[1] => Flat 
[property_type] => Flat 

Why is it not just

[property_type_id] => 1 
[property_type] => Flat
 

[bradgrafelman]

lerro wrote:

it seems to have an unfamiliar structure

It's not unfamiliar at all - read the manual for [man]mysql_fetch_array/man to learn how it returns data.

Also note that it is more common to use [man]mysql_fetch_assoc/man to only retrieve an associative array rather than a numerically indexed array (or both, which is what you're getting now).

[lerro]

Thanks.

How can i then loop through the associative array once it's returned and stored in a variable as when i use "foreach", it only seems to loop once, that is, it only shows one item.

I want the array to be fetched, stored in a variable, then used elsewhere by looping through it.

[NogDog]

$result = mysql_query($sql);
$data = array();
while($row = mysql_fetch_assoc($result)) {
   $data[] = $row;
}
// later on in output:
foreach($data as $row) {
   echo "<ul>\n";
   foreach($row as $key => $value) {
      echo "<li>" . htmlspecialchars($key) . ": " . htmlspecialchars($value) . "</li>\n";
   }
   echo "</ul>\n";
}

[Derokorian]

Why do you loop through the results, assign it to an array then loop through the array? couldn't you just put the innerds of the outer foreach in the while and negate 2 foreach statements? As so:

while( $row = mysql_fetch_assoc() ) {
   echo "<ul>\n";
   foreach( $row as $k => $v ) {
      echo "<li>". htmlspecialchars($k) .": ". htmlspecialchars($v) ."</li>\n";
   }
   echo "</ul>\n";
}

[bradgrafelman]

Derokorian wrote:

couldn't you just put the innerds of the outer foreach in the while and negate 2 foreach statements?

Only if you want to dump the output at the same time/place you query the DB and get the results, and only if you have no other need for the data (since in your method, once you output a row you simply move on and forget about its contents).

[lerro]

Thanks everyone!!!

Nogdogs suggestion was quite helpful and it produced the desired results.

One small hitch though. When i try to loop through the results using the key, as shown below

	foreach($result as $row)
	{ 
       foreach($row as $key => $value) {  

		  echo $row['property_type_id'];
		  echo $row['property_type'] . "<br/>";
       } 
       echo "\n"; 
   } 

I get duplicates as shown below

1: Flat
1: Flat
2: Bungalow
2: Bungalow
3: Detached
3: Detached

Whereas if i just use "$key $ value" , there are no duplicates.

What causes this?

[bradgrafelman]

This:

       foreach($row as $key => $value) {   

          echo $row['property_type_id']; 
          echo $row['property_type'] . "<br/>"; 
       } 

doesn't make sense. You loop through $row, visiting each $key and $value respectively, yet you're echo'ing out items from the $row array directly.

In other words, you completely defeat the purpose of that foreach() loop altogether and might as well remove it if you aren't going to use $key and $value instead.

[NogDog]

Derokorian;10978954 wrote:

Why do you loop through the results, assign it to an array then loop through the array? couldn't you just put the innerds of the outer foreach in the while and negate 2 foreach statements? As so:

while( $row = mysql_fetch_assoc() ) {
   echo "<ul>\n";
   foreach( $row as $k => $v ) {
      echo "<li>". htmlspecialchars($k) .": ". htmlspecialchars($v) ."</li>\n";
   }
   echo "</ul>\n";
}

Which could be a good argument for returning an object that implements the Iterator interface, and use whichever technique seems best to you in that class, and the client code that uses the object can be blissfully ignorant of how it's actually being iterated upon. 🙂

[Derokorian]

Yeah i guess depends on how your using it, but I figure if he's echoing it right there I don't see a reason why not to do it that way.

[lerro]

Thanks everyone, i have solved it and got rid of the duplicates.

It's now working as desired.