getting results of SQL query

I've seen there there are numerous ways to put the results of a SQL query into usable format (that is, variables).

If I have a targeted SQL query that I know will be returning a set of expected values, lets say querying a customer number to pull data, city, s开发者_开发知识库tate, first and last name, etc..

Code example follows:

$example = '50';
$result = mysql_query("SELECT * FROM customers WHERE customer_ID = '$example'",$db);
while ($row = mysql_fetch_assoc($result)) {
  foreach ($row as $col => $val) {
    if ($col == 'firstname') {
      $customerfirstname = $val;
    }
  }
}

or another way:

$result = mysql_query("SELECT * FROM customers WHERE customer_ID = '$example'",$db);
$myResultArray = array();

while ($row = mysql_fetch_assoc($result))
  $myResultArray = $row;

  foreach ($myResultArray as $val) {
    $customerfirstname = $val['firstname'];
  }

That's just two that I could think of.

Is one of the above methods preferable over the other? If so, why?

Is there an alternate method that is even more efficient than either of these?

---

Neither are preferred.

The foreach's are superfluous.

Since you know the fieldnames you need, you can just do:

while ($row = mysql_fetch_assoc($result)) {
    $customerfirstname = $row['firstname'];
}

If you do need to apply a conditional for some reason, you can test for the field's existence in the array:

while ($row = mysql_fetch_assoc($result)) {
    if (isset($row['firstname'])) {
        $customerfirstname = $row['firstname'];
    }
}

Finally, since you appear to be selecting by primary key, the while loop is also unnecessary:

if ($row = mysql_fetch_assoc($result)) {
    $customerfirstname = $row['firstname'];
}

---

I have used the first example that you posed in every website I have done that requires a database and it hasn't failed me yet. As far as if one is better than the other I'd say no. It's just a matter of taste.

---

There are many ways. Here's a quick one, but I would prefer to set it up using a DTO and accessing it that way... this will work though for your question.

$query = "SELECT first_name, last_name, city FROM customers WHERE customer_id = '$example'";
$result = mysql_query($query);

// If you are expecting only one row of data, then use this:
list($first_name, $last_name, $city) = mysql_fetch_row($result);

//several rows:
while(list($first_name, $last_name, $city) = mysql_fetch_row($result)){
     echo $first_name;
}

---

I seem to be missing something...

Why not this?

$result = mysql_query("SELECT * FROM customers WHERE customer_ID = '$example'",$db);
while ($row = mysql_fetch_assoc($result)) {
  $customerfirstname = $row['firstname'];
}

In the first example?