0

I usually use this code to read/get the result of an prepared MySQL SELECT:

$sqlname = $conn->prepare("SELECT name FROM test1 WHERE test2 = ?");
$sqlname->bind_param('s',$test);
$sqlname->execute();
$result = $sqlname->get_result();
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {   
        $testname = $row['name'];
    }
}

But when I know that there will only be one row in the result: Do I have to use a while loop with fetch_assoc anyway or is there a better way?

mickmackusa
  • 43,625
  • 12
  • 83
  • 136
Till Meier
  • 7
  • 1
  • If you're certain there can only be one (or no) result you can just replace the `while` with an `if`. But it's not going to save you much and it's probably safer to keep using the same code so you don't accidentally use an `if` where you needed a `while`. – Nick Oct 09 '18 at 11:12
  • No, no loop is necessary. What do you want to happen if there is no row? Do you want `$testname` to be `null`, `false`, an empty string? – mickmackusa Oct 09 '18 at 11:22
  • Define **best**: Do you want the fastest way? the safest way? The easiest way to understand? The most crypic way? The way with the least lines of code? – Martin Oct 09 '18 at 11:50
  • Is this PDO or something else? – Salman A Oct 09 '18 at 12:01
  • @SalmanA this is something else (*mysqli*) – Martin Oct 09 '18 at 12:37

1 Answers1

0

When you know the name of the column that you are returning in the result set, you can bind the result to an output variable -- which makes things a little nicer to work with in my opinion.

if (!$stmt = $conn->prepare("SELECT name FROM test1 WHERE test2 = ?")) {
    echo "Prepare Syntax Error"; // $conn->error
} elseif (!$stmt->bind_param("s", $test) || !$stmt->execute() || !$stmt->bind_result($name)) {
    echo "Statement Error"; // $stmt->error
} else {
    $stmt->fetch();
    var_export($name);  // this will show the value or NULL
}

Or if you don't want to bind the result value to a variable:

...
} elseif (!$stmt->bind_param("s", $test) || !$stmt->execute() || !$result = $stmt->get_result()) {
    echo "Statement Error"; // $stmt->error
} elseif (!$row = $result->fetch_row()) {  // No need to use assoc() keys
    echo "empty result";
} else {
    var_export($row[0]);  // access the first column value
}
mickmackusa
  • 43,625
  • 12
  • 83
  • 136