mysql_fetch_array() question

[Stinger51]

I typically query my database like :

$result = mysql_query($query, $conn . . . . );

Then, if I know only one record is coming back, I use:

$row = mysql_fetch_array($result);

The creates an array called $row. The problem is, there is no way to iterate thru this array. In order to extract the data from the array, you must use the exact field name to do this :

$my_var = $row['FirstName'];

$my_var now contains the value of the record for the field "FirstName".

Does anybody know how to iterate thru this array? This does not work :

$my_var = $row[0]; // returns a 0

Or perhaps, there is a better function to use than mysql_fetch_array (which in not even documented in the on-line manual)?

Thanks in advance.

[Vex]

Use:

array mysql_fetch_row ( resource result)

[Sxooter]

Sure, you can iterate the output. First, use the call like so:

$row = mysql_fetch_array($result,MYSQL_ASSOC)

The MYSQL_ASSOC flag will tell the function to return an associative array.

Now, here's some fun ways to play with such an array:

print implode(" : ",array_keys($row));
print implode(" : ",array_values($row));

foreach ($row as $key=>$val){
  print "The field called ".$key." has a value of: ".$val;
}

fun, huh?

[Stinger51]

I tried that but I get an error that says the supplied argument is not a valid resource.

Are you saying to use :

$row = mysql_fetch_row(resource, $result);

or something like that?

[Stinger51]

while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$my_var = $row[0];
echo "<BR>my var = $my_var<BR>";

---

But $my_var is equal to a nothing (NULL).

I didn't understand what the "array keys" were all about. Is this associative array some kind of a linked list or something?

[Vex]

$query = "SELECT col1, col2, col3 FROM table ";

$result = mysql_query($query, $DBconnection);

if ($row = mysql_fetch_row($result) ) {

   $count = 0;

   do {

   echo "Row ".$count++;
   echo "Column 1: ".$row[0];
   echo "Column 2: ".$row[1];
   echo "Column 3: ".$row[2];

   } while ( $row = mysql_fetch_row($result) );
}

HTH,

[Stinger51]

Now I am getting somewhere! I am able to pull the values out of the result set by using an array index. I am populating an HTML table with these values -- name, street, city, etc.

But now I see I've got another problem. Thru lack of planning, the order of my fields in my db table does not match the order of my fields in my HTML table! So the looping won't work unless I can make the order match.

I am using MySQL Front as my db interface. And I don't see any way of moving fields around with it (unlike MS Access, which makes this a piece of cake).

Do you know of any way to do this? Or would it be better just to use some kind of a view? Or would using an SQL query that orders the fields, e.g.

SELECT FROM $table Field_1, Field_2, Field_6, Field_3 . . .

do the trick? Not sure about this and how mysql_fetch_row would handle this. I've been using SELECT * for so long (as I need every field in the record) I've completely forgotten.

Thanks for your help.

[Wave]

SELECT FROM $table Field_1, Field_2, Field_6, Field_3 . . .

do the trick?

why you did not try this, before ask? :🙂

ok, ok... it does the trick for you 🙂

[Stinger51]

That was only part of the solution, Amigo.

mysql_fetch_row(), which the on-line manual does it's usual piss poor job of explaining (it's mostly C.I.P.U.), was the key to this whole issue, not ordering the fields correctly. It wouldn't matter to the looping routine one bit how I ordered the fields if I couldn't get the indexing to work while reading it from the record set.

What they need to do is put some short, to the point, non-egg headed snippets of code in the manual (as is done on this forum) to explain what is going on with functions like fetch_row. (And they don't even list fetch_array.)

Again, you never know who is reading these threads and who may have been helped by this discussion. Sometimes the most obvious questions lead to the most fruitful discussions. 😃

Cheers

[Wave]

right...

...🙂