Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

I'm working on an exercise where I"m supposed to display all data contained in a table. Here's the solution I can up with:

<?php
$result = mysql_query("SELECT * FROM contacts ORDER BY id DESC");
    while($user= mysql_fetch_array($result)) {
        echo $user['first'] . " " . $user['last'] . "<br />" . $user[phone] . "br />";
    }
?>

Here's the solution the tutorial gave:

<?php 
$result=mysql_query("SELECT * FROM contacts ORDER BY id DESC");

$num=mysql_num_rows($result); 

$i=0;
while ($i < $num) {

$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");
$phone=mysql_result($result,$i,"phone");


echo "<b>$first $last</b><br>Phone: $phone<br><hr><br>";

$i++;
}
?>

The database fields are:

id
first
last
phone

Can someone explain WHY one is better than the other. They both work as far as I can tell.

*EDIT: I've rewritten the query based on the advice given by @Paul below: *

<?php 
$pdo_connect = new PDO('mysql:host=localhost;dbname=db', 'root', 'pass');

$statement = $pdo_connect ->query("SELECT * FROM users ORDER BY id DESC");
    while($user = $statement->fetch(PDO::FETCH_ASSOC)) {


        echo "{$user['first']} {$user['last']} <br /> {$user['phone']} <br /><hr>";
    }

?>
share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

Yours is better. Both are bad.

Don't use either of them

The mysql_* functions aren't a good way to learn how to interact with databases. It is a shame that there are still tutorials teaching how to interact with databases using these functions. Here is a snippet that explains more about why you shouldn't use those functions and what you can use instead:

Please, don't use mysql_* functions in new code. They are no longer maintained and the deprecation process has begun on it. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.
Snippet Source

Minor Comments

You missed quotes around phone.

In pure PHP don't use an end tag ?>. It can cause output if you have blank lines after it causing headers to be sent prematurely (see this).

share|improve this answer
    
Hi @paul, thanks for the response. I've saved all the links you've provided and will work on the PDO tutorial you provided. Is my understanding correct that PDO & MYSQLi strictly relate to accessing and manipulating database data and are not another method of writing PHP? –  NewToCode Nov 10 '12 at 2:25
    
PDO & mysqli are both extensions to PHP which are included with the standard installation in PHP > 5.1. Both are just for accessing databases. I like PDO because it also works with non-mysql databases. –  Paul Nov 10 '12 at 3:09
    
@NewToCode - Another note: don't use SELECT * for anything. Always list the columns you want to get from the database. That way, if the structure of the table changes you can catch it as a database error (which it is) rather than have your code roll on assuming it has all the data it expects when in fact it does not. –  Chris Nov 27 '12 at 20:15
add comment

You are doing it as an exercise but still it is good to pay attention to your sql queries. This is where it gains/loose performance (let's assume your contacts table has more than 100000 contacts ).

Here you have "SELECT * FROM users ORDER BY id DESC". First thing, do you really need that order by clause? if not, then remove it else make sure that id column in contacts table is indexed. Note that sort is always expensive.

order by id can also be achieved in php side using a custom sort algorithm or php sort function. However mysql sort wins over php sort whereas I believe it is opposite in case of java.

share|improve this answer
    
During the learning stage is way to earlier to be paranoid about performance (though I do agree that the sort seems odd). Also, as for your last paragraph, that could be a rather bad idea. If you already happen to have the data in memory, yeah, sure, I suppose sorting application side might not be a bad idea, but pulling the data from the DB and then immediately sorting could have huge performance implications. Tree-based indexes can be walked in order in linear time (compared to nlgn sorting), and try doing ORDER BY col LIMIT 10, 30 without pulling the entire table. –  Corbin Nov 10 '12 at 10:36
    
Ok. I agree with you. –  Kinjal Nov 10 '12 at 16:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.