mysql_fetch_assoc question

[master82]

I looked this up on the PHP Manual and looked at the first example (below)

[code]
<?php

$conn = mysql_connect("localhost", "mysql_user", "mysql_password");

if (!$conn) {
  echo "Unable to connect to DB: " . mysql_error();
  exit;
}
 
if (!mysql_select_db("mydbname")) {
  echo "Unable to select mydbname: " . mysql_error();
  exit;
}

$sql = "SELECT id as userid, fullname, userstatus
      FROM  sometable
      WHERE  userstatus = 1";

$result = mysql_query($sql);

if (!$result) {
  echo "Could not successfully run query ($sql) from DB: " . mysql_error();
  exit;
}

if (mysql_num_rows($result) == 0) {
  echo "No rows found, nothing to print so am exiting";
  exit;
}

// While a row of data exists, put that row in $row as an associative array
// Note: If you're expecting just one row, no need to use a loop
// Note: If you put extract($row); inside the following loop, you'll
//      then create $userid, $fullname, and $userstatus
while ($row = mysql_fetch_assoc($result)) {
  echo $row["userid"];
  echo $row["fullname"];
  echo $row["userstatus"];
}

mysql_free_result($result);

?>
[/code]

Could someone please explain this section:

// Note: If you put extract($row); inside the following loop, you'll
//      then create $userid, $fullname, and $userstatus

Does that mean if I can simply call the value by simply using [b]$userid [/b] or do I have to use something like [b]extract($userid)[/b] to call the value?

[master82]

Does not matter - can just use [b]$row["userid"][/b] to call up values

thanks for looking anyway  ;D

[akitchin]

for the record, extract() simply takes an array and places all of its values into local variables, named by their indeces.  have a look in the manual for what it does.

[master82]

Actually I thought I could use [b]$row['FIELD'][/b] to call up the values but I cant.

It only echo/prints the values if its between the following:

[code]
while ($row = mysql_fetch_assoc($result)) {
  echo $row["userid"];
  echo $row["fullname"];
  echo $row["userstatus"];
}
[/code]

anything I try to echo/print after this returns a null values...

so how can i edit the code at the top so that i can call $row['THEFIELD'] further down the page (or on another via an include/require)

[akitchin]

you'd need to assign every row into a local variable.  the $row variable only exists for as long as the while() loop is running.

[code]$all_rows = array();
while ($this_row = mysql_fetch_assoc($resource))
{
  // assign to a local var
  $all_rows[] = $this_row;
}[/code]

you would then go through each later in the script as:

[code]foreach ($all_rows AS $row_values)
{
  echo $row_values['FIELD'];
}[/code]

however, if you know your query will only return one row, you don't even need a while loop.  you can just run:

[code]$row = mysql_fetch_assoc($resource);[/code]

and $row will contain that one row's values.  while() is only used to run through a resource that has returned multiple rows.

[master82]

My query will only bring a single row, so can I remove the while line and replace it with your example changing $resource to $result.

Also am I correct in thinking I should remove the:

mysql_free_result($result);

so that I can use it further in the page?

[akitchin]

no no, that frees the resource.  if you have already extracted the information from that resource (ie. already used $row = mysql_fetch_assoc($result) to assign that row's info to a local var), then you don't really need it anymore.  you can leave it in.

you would only need to remove the mysql_free_result line if you needed to use the resource again (which is the case when you operate multiple while()s on the same resource).

[master82]

Good stuff - working great!

Thanks for the info and help [u]akitchin [/u] ;D