PHP /MySQL update form from within results

Question

I've got a search function written in PHP/MySQL which works fine. What I want to happen is that when a user produces a search they can click a button which will submit the $id from the output to a table in my database.

I've copied my code below, the error is within the php echo in the form, it just displays the plain text of the php code.

Everything else works fine, I've tested this by setting value to "" and entering the id myself and then it works. I want it though to be a hidden input in future where the id automatically comes through from the search result. Multiple searches can be returned on the same page and this form is underneath each individual search result.

<?php
$conn = mysqli_connect("localhost","root","","users");
$output = '';

if(isset($_POST['search'])) {
    $search = $_POST['search'];
    $search = preg_replace("#[^0-9a-z]i#","", $search);

    $query = mysqli_query($conn, "SELECT * FROM users WHERE main LIKE '%".$search."%'") or die ("Could not search");
    $count = mysqli_num_rows($query);

    if($count == 0){
        $output = "There was no search results!";
    }else{
        while ($row = mysqli_fetch_array($query)) {

            $id = $row ['id'];
            $main = $row ['main'];
            $postcode = $row ['postcode'];
            $available = $row ['available'];
            $email = $row ['email'];


            $output .='<div><br><b>Player ID: </b>'.$id.'<br><b>Main: 
                </b>'.$main.'<br><b>Postcode: </b>'.$postcode.'<br><b>Available: 
                </b>'.$available.'<br>
                <br>
                <form action="request_player.php" action="post">
                    <input type="text" name="id" value="<?php echo $id ?>">
                    <input type="submit" value="Request Player">
                </form>
                </div>';
        }

    }
}
echo $output;
?>

<br>    <a href="index.php">Back to your account</a>

Answer 1

The reason it is happening is because you have put <?php echo $id ?> inside a string. You want to do the same thing you did elsewhere in your example: value="' . $id . '" It can quickly get confusing when you have single and double quotes happening together. You might be best off learning how to use PHPs multiline strings.

Also, <?= $id ?> is a useful shorthand for <?php echo $id ?> (although you don't want to use either here)

Answer 2

The issue Jay Blanchard highlighted and which you took a bit lightly - perhaps b/c you fear the distraction from your current problem - is actually pretty related to the issue you highlight in your question.

This btw. is nothing uncommon. In this little script you deal with at three languages: HTML, SQL and PHP. And all these are intermixed. It can happen that things jumble.

There are methods to prevent these little mistakes. What Jay highlighted was about how to encode a SQL query correctly.

The other problem is to encode a HTML string correctly. Let me highlight the part:

$output = '... <input type="text" name="id" value="<?php echo $id ?>"> ...';

In this PHP string you write "<?php echo $id ?>" verbatim, that means, this will echo'ed out then.

What you most likely meant was to write it this way:

$output = '... <input type="text" name="id" value="' . $id . '"> ...';

So this seems easy to fix. However, it's important that whether it is SQL or HTML, you need to properly encode the values if you want to use them as SQL or HTML. In the HTML case, you must ensure that the ID is properly encoded as a HTML attribute value. In PHP there is a handy function for that:

$output = '... <input type="text" name="id" value="' . htmlspecialchars($id) . '"> ...';

Or as the ID is numeric:

$output = '... <input type="text" name="id" value="' . intval($id) . '"> ...';

works similarly well.

You need to treat all user-data, that is all input - which includes what you get back from the database (!) - needs to be treated when you pass it into a different language, be it HTML, SQL or Javascript.

For the SQL Jay has linked you a good resource, for the HTML I don't have a good one at hand but it requires your own thoughtfulness and the will to learn about what you do (write) there. So sharpen your senses and imagine for each operation what happens there and how this all belongs together.

One way to keep things more apart and therefore help to concentrate on the job is to first collect all the data you want to output and then process these variables in a template for the output. That would prevent you to create large strings only to echo them later. PHP echoes automatically and a benefit of PHP is that you can use it easily for templating.

Another way is to first process the form input - again into your own variable structure - which is the programs input part and run first. Then follows the processing of the input data, in your case running and processing the database query. And after that you care about the presentation. That way you have common steps you can become more fluent in.

I hope this is understandable. It's full of further obstacles, but it pays to divide and conquer these programming problems. It will also help you to write more while you need to write less for that.

And btw., you don't need to switch to PDO, you can stick with Mysqli.