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?