Jump to content

Archived

This topic is now archived and is closed to further replies.

master82

mysql_fetch_assoc question

Recommended Posts

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?

Share this post


Link to post
Share on other sites
Does not matter - can just use [b]$row["userid"][/b] to call up values

thanks for looking anyway  ;D

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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).

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.