2
\$\begingroup\$

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.

\$\endgroup\$

2 Answers 2

4
\$\begingroup\$

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).

\$\endgroup\$
3
  • \$\begingroup\$ 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? \$\endgroup\$
    – NewToCode
    Nov 10, 2012 at 2:25
  • \$\begingroup\$ 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. \$\endgroup\$
    – Paul
    Nov 10, 2012 at 3:09
  • \$\begingroup\$ @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. \$\endgroup\$
    – Chris
    Nov 27, 2012 at 20:15
0
\$\begingroup\$

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.

\$\endgroup\$
2
  • \$\begingroup\$ 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. \$\endgroup\$
    – Corbin
    Nov 10, 2012 at 10:36
  • \$\begingroup\$ Ok. I agree with you. \$\endgroup\$
    – Kinjal
    Nov 10, 2012 at 16:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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