1

I am coding a PC repair application using MySQLi. I have an ENUM row in my database called "item_for_repair". In the ENUM list is Laptop, Desktop etc etc.

On my front end html table, when i click edit on a specific job card, under "item for repair" there is a drop down that always has the first value (for example, Laptop) selected and not the actual item that was stored (for example, Desktop), i need the drop down to be pre-selected with the item for repair that was originally stored in the database when storing the job card.

Here is an example image of what is happening. The item for repair in the databse is Desktop, but here you can clearly see its showing Laptop.

Item For Repair Example Image

My code to connect to database as well as select all from the database:

<?php include 'settings.php'; //This file has all the database connection settings etc.

?>
 <?php
$eid=$_GET['editid'];
$ret=mysqli_query($conn,"select * from pcrepairs where job_number='$eid'");
while ($row=mysqli_fetch_array($ret)) {
?>

And here is the select box code:

<select name="item_for_repair" id="item_for_repair">
    <option value="Laptop" <?= $item_for_repair === 'Laptop' ? 'selected' : '' ?>>Laptop</option>
    <option value="Desktop" <?= $item_for_repair === 'Desktop' ? 'selected' : '' ?>>Desktop</option>
    <option value="Television" <?= $item_for_repair === 'Television' ? 'selected' : '' ?>>Television</option>
    <option value="Washing Machine" <?= $item_for_repair === 'Washing Machine' ? 'selected' : '' ?>>Washing Machine</option>
    <option value="Tumble Dryer" <?= $item_for_repair === 'Tumble Dryer' ? 'selected' : '' ?>>Tumble Dryer</option>
    <option value="Dishwasher" <?= $item_for_repair === 'Dishwasher' ? 'selected' : '' ?>>Dishwasher</option>
    <option value="Microwave" <?= $item_for_repair === 'Microwave' ? 'selected' : '' ?>>Microwave</option>
    <option value="Fridge" <?= $item_for_repair === 'Fridge' ? 'selected' : '' ?>>Fridge</option>
    <option value="Printer" <?= $item_for_repair === 'Printer' ? 'selected' : '' ?>>Printer</option>
    <option value="Other" <?= $item_for_repair === 'Other' ? 'selected' : '' ?>>Other</option>
</select>

Where have i gone wrong?

Lee-Roy Christianson
  • 19
  • 4
  • Just teaching myself both. I will update the question and add my PDO code in. – Lee-Roy Christianson Dec 05 '21 at 16:26
  • @Dharman, see **EDIT 2**. It is a bit messy, but it's an internal application just for my own personal learning. I am aware of the security flaws. – Lee-Roy Christianson Dec 05 '21 at 16:32
  • Constant [FILTER_SANITIZE_STRING](https://stackoverflow.com/questions/69207368/constant-filter-sanitize-string-is-deprecated) is deprecated. Please stop using it. – Dharman Dec 05 '21 at 16:33
  • What security flaws? Anyway, I think it's still unclear which part of this is causing you problems when converted to mysqli. Please be more specific. I see a huge PDO prepared statement, but I am not sure which part of it you are confused about. We don't need to see all of the code, just the part that is related to this question. – Dharman Dec 05 '21 at 16:35
  • Always `exit()` after `header('Location: ...');` – Dharman Dec 05 '21 at 16:35
  • Try-catch is useless when you are not recovering from it. You can safely remove it. – Dharman Dec 05 '21 at 16:35
  • @Dharman I restarted my question from scratch in an attempt to make it more straight to the point. – Lee-Roy Christianson Dec 05 '21 at 17:07
  • **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Dec 05 '21 at 17:10
  • What happened to the prepared statement? Why did you replace it with `mysqli_query()` and a `while` loop? That seems like ten steps backwards – Dharman Dec 05 '21 at 17:11
  • Where is `$item_for_repair` defined? How is the HTML part connected to your `while` loop? "*Where have i gone wrong?*" It seems you have gone wrong by not connecting the two pieces of code together. – Dharman Dec 05 '21 at 17:12

1 Answers1

-1

Here is a complete example of how to populate an HTML select retrieving data from a MySQL database using MySQLi

<?php

class Country {
    public $code;
    public $name;
    
    function __construct($code, $name) {
        $this->code = $code;
        $this->name = $name;
    }
}

define("HOST", "localhost");
define("USERNAME", "root");
define("PASSWD", "");
define("DBNAME", "shop");

function country_select_list() {
    $array = null;
    $link = mysqli_connect(HOST, USERNAME, PASSWD, DBNAME);
    $stmt = mysqli_prepare($link, "select code, name from country order by name");
    if (!mysqli_execute($stmt)) {
        throw new Exception(mysqli_stmt_error($stmt));
    }
    $code = null;
    $name = null;
    mysqli_stmt_bind_result($stmt, $code, $name);
    while (mysqli_stmt_fetch($stmt)) {
        $array[] = new Country($code, $name);
    }
    mysqli_stmt_close($stmt);
    mysqli_close($link);
    return $array;
}

$user_country = 'CHE';

?>
<select id="user_country" name="user_country">
    <option value=""></option>
<?php

foreach (country_select_list() as $country) {
    echo '    <option value="' . $country->code . '"';
    if (isset($user_country) && $user_country == $country->code) {
        echo ' selected="selected"';
    }
    echo ">" . $country->name . "</option>\r\n";
}

?>
</select>

This is the output

<select id="user_country" name="user_country">
    <option value=""></option>
    <option value="ITA">Italy</option>
    <option value="CHE" selected="selected">Switzerland</option>
</select>
Fabio Scagliola
  • 338
  • 2
  • 11