Selecting two field in MySQL with PHP

Question

Hi guys, i'm relatively new to php and mysql and would like to know how to select two value in mysql with php.

What i have is

$query = sprintf("SELECT COUNT(id) FROM table WHERE UPPER(username) = UPPER('%s') AND password='%s'"...

in this case, i'm only selecting and count if the id exist and i use

list($count) = mysql_fetch_row($result);
        if($count == 1)

and by using cookies, i would like to retrieve two value from the database, namely user (the user's name) and power (which has value of 1,2 or 3, indicating the menu they would be able to see) basically it is to differentiate if you're admin or normal user, but i wonder if i could do

SELECT COUNT(id) AND power FROM table WHERE ...

is this possible? or is there any other way?

Please guide me, thanks.

Answer 1

Hello Crays.

You will have to do something like this:

SELECT username, power FROM table WHERE UPPER(username) = UPPER('%s') AND password='%s'"...

So, if it gives you a result, it means the username and password match, and you will have the username and power of that row.

Bye!

Answer 2

You could just do

SELECT power FROM table WHERE ... LIMIT 1

and then:

$numRows = mysql_num_rows($result);
if($numRows == 1){
    $pow = mysql_fetch_row($result)[0];
}

(not tested, but should work I think :))

EDIT:

If you want to select more than one field:

SELECT power, somethingelse, yetanotherfield FROM table ...

$row = mysql_fetch_row($result);
$pow = $row[0];
$stelse = $row[1];
...

Answer 3

Why do you need to count the number of 'id' fields?

If you want to just pull only two fields from a database, you should use 'SELECT id,power FROM table WHERE ...'

Answer 4

AND is a logical operator that returns true iff all its arguments are true. If you're talking about having more than one column in a result, simply separate them by commas:

SELECT COUNT(id), power 
  FROM table
  WHERE ...

However, if power isn't functionally dependent on id and there are more than one row with a given id, you could get any of the power values (on DBMSs other than MySQL, you'd need to GROUP BY power for the query to even work).

Instead of enforcing uniqueness in PHP, declare a UNIQUE index on column username. Column id should be a primary key, which implies that it's unique.

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(64) UNIQUE NOT NULL,
    ...
);

Or, if the table already exists,

CREATE UNIQUE INDEX username ON users (username);

Off Topic

By calling UPPER on column username, you're preventing any index from being used, causing MySQL to have to scan the entire table to execute the query. If you want your user names to be case insensitive, convert them before storing them. This is easily accomplished with triggers, which you can read about in the MySQL reference manual.

delimiter ;;
CREATE TRIGGER upcase_username_insert
  BEFORE INSERT
  ON TABLE users
FOR EACH ROW
  NEW.username=UPPER(NEW.username)
END;;
CREATE TRIGGER upcase_username_update
  BEFORE UPDATE
  ON TABLE users
FOR EACH ROW
  NEW.username=UPPER(NEW.username)
END;;
delimiter ;

While using sprintf to combine strings will work fine, it's not standard practice. Variables are interpolated into double quoted strings, so you can simply write "SELECT ... WHERE ".

Make sure you're storing hashed and salted passwords (using a cryptographically secure hash, which MD5 isn't, these days) rather than plain passwords.

Finally, but most importantly, depending where the values interpolated into the query come from and what other processing is done on them, your query could be vulnerable to SQL injection. Use PDO and prepared statements instead (prepared statement parameters are invulnerable to SQL injection). Read "Writing MySQL Scripts with PHP and PDO" for a PDO tutorial.