0

What's the difference between

    $query = "SELECT email FROM users WHERE username='$username' ";
    $result = mysqli_query($db, $query);
    $row = mysqli_fetch_array($result);
    $email = $row['email'];
    $_SESSION['email'] = $email;

and

    $query = "SELECT * FROM users WHERE username='$username' ";
    $result = mysqli_query($db, $query);
    $row = mysqli_fetch_array($result);
    $email = $row['email'];
    $_SESSION['email'] = $email;

They both return the same value, is there any more effective way to do that?

PHP Newbie
  • 33
  • 10
  • 1
    I think all the answers covered it pretty thoroughly, but for a concrete example; imagine the performance difference if `users` had a large blob field that stored a high-resolution profile pic. – Uueerdo Sep 18 '18 at 22:31
  • 1
    Basically, never use evil `SELECT *`, and you will do just fine. – Strawberry Sep 18 '18 at 22:32
  • See also https://stackoverflow.com/questions/3639861/why-is-select-considered-harmful – Don't Panic Sep 18 '18 at 22:58

4 Answers4

1

select * will return values for all the columns in the table that have rows that match your predicate, while select column_name will only return the values in the column_name column for the rows that match your predicate.

RKitson
  • 2,003
  • 1
  • 18
  • 21
1

SELECT column returns only the value of that column. SELECT * returns the value of every column in the table. If you were to print_r($row) in your second code block you would see a lot more elements in the array than just $row['email'].

There is a good Q&A here which talks about the relative performance differences of SELECT * and SELECT column. @Uueerdo's point about one of the other fields having a huge blob causing performance issues is also very valid.

You can also run into issues with SELECT * when you are JOINing tables, if both tables have identically named columns then the values from one table can overwrite the other when data is returned to your application framework.

Nick
  • 138,499
  • 22
  • 57
  • 95
0

* is least efficient if you only want email because you are returning every columns value.

You also should parameterize your query.

$query = "SELECT email FROM users WHERE username= ? ";

http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

user3783243
  • 5,368
  • 5
  • 22
  • 41
0
  • Selects All which means it will return all columns But "select email" will return the column "email" which is more effecient. You can also select many columns like: "select id,email from users" .
monsieur_cs
  • 21
  • 3