Jump to content


Photo

mysql_fetch_assoc question


  • Please log in to reply
7 replies to this topic

#1 master82

master82
  • Members
  • PipPipPip
  • Advanced Member
  • 182 posts

Posted 20 October 2006 - 11:20 PM

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

<?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);

?> 

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 $userid or do I have to use something like extract($userid) to call the value?

#2 master82

master82
  • Members
  • PipPipPip
  • Advanced Member
  • 182 posts

Posted 20 October 2006 - 11:32 PM

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

thanks for looking anyway  ;D

#3 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 20 October 2006 - 11:37 PM

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.

#4 master82

master82
  • Members
  • PipPipPip
  • Advanced Member
  • 182 posts

Posted 20 October 2006 - 11:45 PM

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

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

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

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)

#5 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 20 October 2006 - 11:49 PM

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.

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

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

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

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

$row = mysql_fetch_assoc($resource);

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

#6 master82

master82
  • Members
  • PipPipPip
  • Advanced Member
  • 182 posts

Posted 20 October 2006 - 11:54 PM

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?

#7 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 21 October 2006 - 12:00 AM

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).

#8 master82

master82
  • Members
  • PipPipPip
  • Advanced Member
  • 182 posts

Posted 21 October 2006 - 12:02 AM

Good stuff - working great!

Thanks for the info and help akitchin ;D




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users