[RESOLVED] Proper MySQL extraction?

[gwerner]

I've been looking around and have noticed there are a lot of different ways to extract data from a database query. I'm just trying to get an understanding as to what is the right and/or best approach (secure/efficient that sort of thing). I'm sure there are other methods to add below, but would like to know of the options below which is best.

This is how I've currently been doing it (Keep in mind that I've abbreviated the code so you can get the basic idea.):

<?php
$query = "SELECT company_id, company_name, company_address FROM company";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {

$company_id= $row["company_id"];
$company_name = $row["company_name"];
$company_address = $row["company_address"];

etc..
?>

<li><a href="#"><?php echo $company_name; ?></a></li>

<?
/*** end the loop ***/
}
?>

Another method I've seen using the same query is to not assign the variables and just output the html like:

<li><a href="#"><?php echo $row['company_name']; ?></a></li>

Yet, another method I've seen using extract:

<?php
$query = "SELECT company_id, company_name, company_address FROM company";
$result = mysql_query($query);
$row  = mysql_fetch_assoc($result);
extract($row);
?>

Output html as:

<li><a href="#"><?php echo $company_name; ?></a></li>

Any insight is greatly appreciated. Thanks.

[jazz_snob]

There is nothing wrong and probably no advantage to any of those methods...it all depends on the situation at hand. My 2 cents would be do whatever makes the code more easily understandable.

In general I don't see an advantage to making an assignment of each array element to its own variable $company = $row['company']...but then again it depends on the circumstances of the particular situation. Using extract() has burned me a couple times in large script files so I never use that anymore. I only used it out of lazyiness.

I always fetch as associative array. I'll typically do something like $row = mysql_fetch_assoc($res) or to be more meaningful put what kind of row this is in the variable name: $account_row = mysql_fetch_assoc($res).

Overall coders are more likely to encounter performance issues from the SQL statements themselves rather than how they work your way through the results. A proper understanding of indices and table joining is important.

[gwerner]

Thanks, for the info. It's hard to know sometimes what is the right or best solution. It's good to hear what others best practices are.

Thanks again.