Retrieve database entries to an appended list that was used to submit data to database

Question

Not sure if this is the best method for updating database info, but essentially, I have a div that allows a user to create an appended list of options that they can move up and down (rank) prior to submitting to the database.

Upon submission, I query the database to post those results to another div.

Now, in order to update the queried list from the database, I would like to be able to click an edit button and have the retrieved database options to be displayed back into the appended list that was created originally. I know the method for updating database entries in php, but not quite sure how to display these back to the appended list.

I would like to do it this way, so users are using the same "module" each time - whether to create the original list or update their list.

Appended List jQuery:

$(document).ready(function() {

        var counter = 1;
        var maxAppend = 0;

        $("#addItem").click(function() {
            if (maxAppend >= 10) return;
                var task = $('#searchresults').val();
                var sports = $('#sportsresults').val();
                var src = $('#searchresults').find(":selected").attr('data-src');

                var $newLi = $("<li><div1><img src='" + src + "'/></div1><div2><input type='text' id='college" + counter + "' name='college" + counter + "' value='" + task + "' readonly/></div2><button class='up'>&#x2191</button><div3><input type='text' id='sports" + counter + "' name='sports" + counter + "' value='" + sports + "' readonly/></div3><button class='down'>&#x2193</button></li>")
                    $newLi.attr("id", "newLi" + counter++);
            maxAppend++;
                    $("#tasks").append($newLi);

                    $('#searchresults').find(":selected").remove();
                });

Appended List HTML - where option selections reside prior to submit to dB:

<div class="items">
    <ul id="tasks">
    </ul>
</div>

PHP query to post database entries:

$sql = "SELECT college1, college2, college3, college4, college5, college6, college7, college8, college9, college10, sports1, sports2, sports3, sports4, sports4, sports5, sports6, sports7, sports8, sports9, sports10, updated FROM colleges";
        $result = mysqli_query($conn, $sql);

        if (mysqli_num_rows($result) > 0) {
            // output data of each row
            while($row = mysqli_fetch_assoc($result)) {
                if ($row['college1'] != null) {
                    echo '<div class="row">' . '<div1 style="background-color: #e0e0e0;"></div1>' . '<div2>' . $row["college1"]. '</div2>' . '<div3>' . $row["sports1"]. '</div3>' . '</div>';
                }
                if ($row['college2'] != null) {
                    echo '<div class="row">' . '<div1 style="background-color: #e0e0e0;"></div1>' . '<div2>' . $row["college2"]. '</div2>' . '<div3>' . $row["sports2"]. '</div3>' . '</div>';
                }
                if ($row['college3'] != null) {
                    echo '<div class="row">' . '<div1 style="background-color: #e0e0e0;"></div1>' . '<div2>' . $row["college3"]. '</div2>' . '<div3>' . $row["sports3"]. '</div3>' . '</div>';
                }
                if ($row['college4'] != null) {
                    echo '<div class="row">' . '<div1 style="background-color: #e0e0e0;"></div1>' . '<div2>' . $row["college4"]. '</div2>' . '<div3>' . $row["sports4"]. '</div3>' . '</div>';
                }
                if ($row['college5'] != null) {
                    echo '<div class="row">' . '<div1 style="background-color: #e0e0e0;"></div1>' . '<div2>' . $row["college5"]. '</div2>' . '<div3>' . $row["sports5"]. '</div3>' . '</div>';
                }
                if ($row['college6'] != null) {
                    echo '<div class="row">' . '<div1 style="background-color: #e0e0e0;"></div1>' . '<div2>' . $row["college6"]. '</div2>' . '<div3>' . $row["sports6"]. '</div3>' . '</div>';
                }
                if ($row['college7'] != null) {
                    echo '<div class="row">' . '<div1 style="background-color: #e0e0e0;"></div1>' . '<div2>' . $row["college7"]. '</div2>' . '<div3>' . $row["sports7"]. '</div3>' . '</div>';
                }
                if ($row['college8'] != null) {
                    echo '<div class="row">' . '<div1 style="background-color: #e0e0e0;"></div1>' . '<div2>' . $row["college8"]. '</div2>' . '<div3>' . $row["sports8"]. '</div3>' . '</div>';
                }
                if ($row['college9'] != null) {
                    echo '<div class="row">' . '<div1 style="background-color: #e0e0e0;"></div1>' . '<div2>' . $row["college9"]. '</div2>' . '<div3>' . $row["sports9"]. '</div3>' . '</div>';
                }
                if ($row['college10'] != null) {
                    echo '<div class="row">' . '<div1 style="background-color: #e0e0e0;"></div1>' . '<div2>' . $row["college10"]. '</div2>' . '<div3>' . $row["sports10"]. '</div3>' . '</div>';
                }
                echo '<div class="row2">' . '<div2 style="font-style: italic; display: block; float: none; width: 275px; height: 20px; margin-top: 0px; margin-left: auto; margin-right: auto; font-family: Gotham Book, Verdana, Helvetica; font-size: 11px; line-height: 20px; text-align: right; overflow: hidden;">' . "Updated:&nbsp&nbsp" . $row["updated"]. '</div2>' . '</div>';
            }

Again, in sum, upon edit list button click, I'd like for the dB entries to be provided in the original append list so modifications/updates can be made.

My thought process is probably not the best for making this work, so I am more than open to listening to better/easier alternatives for updating the data.

Answer 1

I believe you need a little more information about how PHP and javascript interact.

PHP is server side. Meaning it prepares the web page (it can even write javascript) and then sends the web page to the client, and then stops. The only way to pull more PHP is to reload the page. When you submit your page, it actually reloads it while sending variables to the server, which tell the PHP code to add stuff to the database.

javascript is client side. There are more differences (like PHP executes one thing, finishes it, then does the next, while javascript will do lots of things at once) but they are discussions for another question.

So to do what you want: you need to write a sql query, that fires without the user doing anything. I'm not familiar with your database, or what information you want to pull. So you will have to construct that yourself.
Here is a decent link on constructing SELECT FROM WHERE queries.
https://www.w3schools.com/sql/sql_select.asp

it will look something like this.
<?php
mysql_connect("localhost", "mysql_user", "mysql_password") or
    die("Could not connect: " . mysql_error());
mysql_select_db("mydb");

$result = mysql_query("SELECT id, name FROM mytable");

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    $namesArray[$row[0]] = $row[1];
}

mysql_free_result($result);
?>

Then, elsewhere in your code, you would do something like this.

<?php
foreach($namesArray as $name){

    echo "$name , ";
}
?>

Per one of the comments, here is similar using mysquli. (copy / pasted from php.net manual. )

<?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 Name, CountryCode FROM City ORDER by ID DESC LIMIT 50,5";

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

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {
        printf ("%s (%s)\n", $row["Name"], $row["CountryCode"]);
    }

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

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