Assigning variables to mysql_fetch_assoc returned values

[seniramsu]

I'm pretty new to php and I'm trying to figure out a better way to query a database for, say, a row of data, and then assign the values returned to variables. Here's what I've tried so far...it seems like there's gotta be a more efficient way out there...

 

Here's a Barney-style example...

 

//example

$query = "SELECT * FROM `database`.`table` WHERE `id` = 1";
$result = mysql_query($query, $connection);

while (isset($result)) {
     $row = mysql_fetch_assoc($result);

     $id = $row['id'];
     $name = $row['name'];
     $stuff = $row['stuff'];

break; // I have to put a break, otherwise the page will never load
}

 

the thing that's screwing me up is having to use that break...it doesn't allow me to access the values of those variables for any other code throughout my page. Any ideas would be greatly appreciated.

 

Cheers!

[trq]

$result will always be set so your loop will never end. if your only looking for one result, you don't need a loop at all.

 

$query = "SELECT id, name, stuff FROM `database`.`table` WHERE `id` = 1 LIMIT 1";
if ($result = mysql_query($query, $connection)) {
  if (mysql_num_rows($result)) {
    $row = mysql_fetch_assoc($result);
    $id = $row['id'];
    $name = $row['name'];
    $stuff = $row['stuff'];
  }
}

[seniramsu]

Thanks for your help. Let's say I wanted to query an entire table, would a foreach loop be more efficient?

 

e.g.

 

//example

$query = "SELECT * FROM `database`.`table` WHERE `id` > 1";
$result = mysql_query($query, $connection);
$row = mysql_fetch_assoc($result);

foreach ($row as $key => $value) {
     
     $id = $row['id'];
     $name = $row['name'];
     $stuff = $row['stuff'];
}

 

?

[trq]

If you want all results you would use a while, but you'll need to store your results within an array.

 

$query = "SELECT * FROM `database`.`table` WHERE `id` > 1";
if ($result = mysql_query($query, $connection)) {
  if (mysql_num_rows($result)) {
    $results = array();
    while ($row = mysql_fetch_assoc($result);
      $results[] = $row;
    }
  }
}

 

Note the if statements as well. you should always check your queries succeed and return results.