MySQL: Get a certain row

Question

I have this table on MySQL. I want to query the port number for user_ids 1 and 2.

umeta_id  user_id  meta_key     meta_value
------------------------------------------
       1        1  nickname     admin
       8        1  userDir      D
       9        1  port         8080
      10        1  paymentopt   bankin
      13        2  port         8081
      20        2  a_renew      1240098300
      21        2  userDir      D
      22        2  paymentopt   paypal

How do I do it in PHP?

Thank you!

Answer 1

SELECT * FROM table  
WHERE user_id = 1
    AND meta_key = 'port'

Do the same for user_id = 2 or if you want to retrieve the ports of both users simultaneously do this in one query:

SELECT * FROM table 
WHERE (user_id = 1 OR user_id = 2)
    AND meta_key = 'port'

This answer reflects just the SQL-part - I assume that you know how to send queries to a MySQL server using the desired MySQL library (ext/mysql, ext/mysqli or ext/PDO).

Answer 2

A very simple bit of example code should work based on example from http://uk.php.net/manual/en/mysqli-result.fetch-row.php

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query = "SELECT `umeta_id`, `user_id`, `meta_key`, `meta_value` FROM TableName WHERE (`user_id` = 1 OR `user_id` = 2) AND (`meta_key` = 'port')"; //You will need to change TableName to whatever your table is called.

if ($result = $mysqli->query($query)) {

    /* fetch object array */
    while ($row = $result->fetch_row()) {
        echo $row[3];
    }

    /* free result set */
    $result->close();
}

/* close connection */
$mysqli->close();
?>

Answer 3

I'd use this instead of the ones proposed above:

SELECT * FROM table WHERE user_id IN (1,2) AND meta_key = 'port'

This will also make it easier and prettier when working with arrays etc:

<?php
$users = array(1,2,3,4,5);
$sql = sprintf("SELECT * FROM table WHERE user_id IN (%s) AND meta_key = 'port'", implode(",", $users));
?>

NB! Remember to check the input if the users array is not safe. For example if it is the result of a user input. In that case you'd like to do something similar to this:

<?php
$users = array_map($users, 'mysql_real_escape_string');
?>