Output one result if criteria matches

Question

I feel like this is probably an easy one but just can't see it!

I've built a test where results are saved into a database table. At the moment it's saving a row for every test taken. The test can be taken until it's passed.

At the moment it's outputting every result (as it's looping over every result in the table).

How can I only output $row['testname']; once if the output of $row['pass-fail']; is "Pass"?

<?php $current_user = wp_get_current_user();
$user = $current_user->user_login;

require("database.php");
// Opens a connection to a MySQL server
$con = mysql_connect("localhost", $username, $password);
if (!$con) {
    die('Could not connect: ' . mysql_error());
}

mysql_select_db("tester", $con);
$sql = mysql_query("SELECT * FROM test WHERE name='$user' ORDER BY Date DESC");
if (!$sql) {
  die('Error: ' . mysql_error());
} ?>

    <?php while($row = mysql_fetch_array($sql)) { ?>


        <p><?php $testname = $row['testname']; echo get_the_title( $testname ); ?> - <?php echo $row['pass-fail']; ?></p>


    <?php } ?>

<?php mysql_close($con); ?>

**Please**, don't use `mysql_*` functions for new code. They are no longer maintained and the community has begun the [deprecation process](http://news.php.net/php.internals/53799), and `mysql_*` functions have been officially removed in PHP 7. Instead you should learn about [prepared statements](https://en.wikipedia.org/wiki/Prepared_statement) and use either `PDO` or `mysqli_*`. If you can't decide, [this article will help to choose your best option](http://php.net/manual/en/mysqlinfo.api.choosing.php). — GrumpyCrouton, Aug 10 '17 at 15:56
*"How can I only output $row['testname']; once if the output of $row['pass-fail']; is "Pass"?"* - Add to the `WHERE` clause, or check if a row equals it in a loop. — Funk Forty Niner, Aug 10 '17 at 15:56
[Little Bobby](http://bobby-tables.com/) says **[you are at risk for SQL Injection Attacks](https://stackoverflow.com/q/60174/)**. Learn about [Prepared Statements](https://en.wikipedia.org/wiki/Prepared_statement) for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even **[escaping the string](https://stackoverflow.com/q/5741187)** is not safe! I recommend `PDO`, which I [wrote a function for](https://stackoverflow.com/a/45514591) to make it extremely **easy**, very **clean**, and way more **secure** than using non-parameterized queries. — GrumpyCrouton, Aug 10 '17 at 15:56
I wouldn't use this code neither if you're planning on going live with this. Or, is this for academic purposes? — Funk Forty Niner, Aug 10 '17 at 16:06

GrumpyCrouton · Accepted Answer · 2017-08-10T16:14:10.690

Use a WHERE clause in your query to only select the ones that have pass-fail equal to "Pass". Also, use prepared statements.

$current_user = wp_get_current_user();
$user = $current_user->user_login;

require("database.php");

// Opens a connection to a MySQL server
$conn = new mysqli("localhost", $username, $password, "tester");

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$stmt = mysqli_prepare($conn, "SELECT * FROM `test` WHERE `name`=? AND `pass-fail`='Pass' ORDER BY `Date` DESC");
$stmt->bind_param("s", $user);
$stmt->execute();
$result = $stmt->get_result();

while($row = $result->fetch_assoc()) {
    $testname_title = get_the_title($row['testname']);
    echo "<p>{$testname_title}</p>";
}

$conn->close();

For this to work you must have the mysqlnd driver.

If you don't have this, I suggest PDO (which, IMO, is also easier to use in general) - If you need help learning how to use PDO for safe and secure queries, then you can check out this answer that I wrote that demonstrates a function that I wrote that makes Prepared Statements easy, clean, and secure. Alternatively you can learn more about Prepared Statements for MySQLi

Side note about `get_result()`: That is a MySQL Native Driver Only, as per the manual http://php.net/manual/en/mysqli-stmt.get-result.php - Edit: This as per original post https://stackoverflow.com/revisions/45618604/1 — Funk Forty Niner, Aug 10 '17 at 16:08
You also have 2x `WHERE`'s, you need a logical operator instead. Edit: This as per original post https://stackoverflow.com/revisions/45618604/1 — Funk Forty Niner, Aug 10 '17 at 16:08

Output one result if criteria matches

1 Answers1