Use select in PHP, MySQL when I know exactly which column i need

Question

Hello,

now i use this code:

$welcome_text = mysql_query("SELECT * FROM `text` WHERE `name` = 'welcome'");
while ($row = mysql_fetch_array($welcome_text, MYSQL_ASSOC)) {
 echo $row['content'];
}

Is it possible to use it without WHILE if i know exactly which one column i need? Something like this:

$welcome_text = mysql_query("SELECT 'content' FROM `text` WHERE `name` = 'welcome'");
echo $welcome_text;

Thanks

Answer 1

mysql_query makes the query, returns a result set.

mysql_fetch_array fetches the first row from the result set.

$welcome_text = mysql_query("SELECT * FROM `text` WHERE `name` = 'welcome'");
$row = mysql_fetch_array($welcome_text, MYSQL_ASSOC);
echo $row['content'];

Of course, you can shorten your code if you want, but this may make your code more difficult to debug and maintain.

Verbose, clear code > one-liner 'show-off' code.

Including 'just-in-case' checking:

$welcome_text = mysql_query("SELECT * FROM `text` WHERE `name` = 'welcome'");
if($row = mysql_fetch_array($welcome_text, MYSQL_ASSOC)){
    echo $row['content'];
}

Good practice to make doubly sure you have what you need before printing it.

Finally, please make sure you sanitize user-submitted data before it goes into your database. If you're not going to use prepared statements, at least use mysql_real_escape_string.

Practice safe SQL, wear a prepared statement to prevent SQL Injections.

Answer 2

That's not entirely the same thing. In your first sample, you're iterating over multiple rows, not columns.

Unless you're certain that there's only one row with the name welcome, you're still going to need the loop.

You are right that you shouldn't select * when you only need some of the columns, it's wasteful.

In other words, you should use the only slightly modified:

$welcome_text = mysql_query("SELECT `content` FROM `text` WHERE `name` = 'welcome'");
while ($row = mysql_fetch_array($welcome_text, MYSQL_ASSOC)) {
    echo $row['content'];
}

Answer 3

$welcome_text = mysql_fetch_row(mysql_query("SELECT 'content' FROM `text` WHERE `name` = 'welcome'"));
echo $welcome_text[0];

Answer 4

Well, if you know exactly that your result will have one row only, you can just omit the while:

$welcome_text = mysql_query("SELECT * FROM `text` WHERE `name` = 'welcome'");
$row = mysql_fetch_array($welcome_text, MYSQL_ASSOC));
echo $row['content'];

That, of course, can be contracted:

$row = mysql_fetch_array(mysql_query("SELECT * FROM `text` WHERE `name` = 'welcome'"));
echo $row['content'];

Which you could turn into

list($welcome_text) = mysql_fetch_array(mysql_query("SELECT content FROM `text` WHERE `name` = 'welcome'"));
echo $welcome_text;

Of course, the more you get into this, the less readable your code will be.

Answer 5

I would write a function for this:

// Returns content of first column in first result and 
// returns null if query returns no records
function mysql_get_result($sql) {

   $query = mysql_query($sql); // you may add error handling ...
   if (mysql_num_rows($query) != 0) {
      $row = mysql_fetch_array($query, MYSQL_NUM));         
      return $row[0];
   } else {
      return null;
   }
}

And now you can use:

$welcome_text = mysql_get_result("SELECT `content` FROM `text` WHERE `name` = 'welcome'");

Note: You may throw an exception instead of returning the null value. But what is better is hard to say and it may depend on your programming style.