Simple MYSQL PHP task Inserting Data

Question

This is probably as basic as you can get for creating a dynamic site but as you can see I am a beginner.

IT DOESN'T WORK but I'm sure you will be able to see why. I have tried various iterations but it returns a string that says it cant find welcome.php.*

I created a form on a page called register.php. From what I can tell you are supposed to pass the information to a page that can process the database insertion.

<form action=”welcome.php” method=”post”>

        <label for="first_name">First Name:</label><br>
        <input type="text" id="first_name" name="first_name"><br>
        <label for="last_name">Last Name:</label><br>
        <input type="text" id="last_name" name="last_name"><br>

        <input type="checkbox" id="agent" name="agent" value="1">
        <label for="agent"> I am an Agent</label><br>

        <label for="address1">Address 1:</label><br>
        <input type="text" id="address1" name="address1"><br>
        <label for="address2">Address 2:</label><br>
        <input type="text" id="address2" name="address2"><br>

        <label for="city">City:</label><br>
        <input type="text" id="city" name="city"><br>
        <label for="state">State:</label><br>
        <input type="text" id="state" name="state"><br>
        <label for="zip">Zip Code:</label><br>
        <input type="text" id="zip" name="zip"><br>

        <label for="email">EMail Address:</label><br>
        <input type="text" id="email" name="email"><br>
        <br>

        <input type="submit" value="Submit">
      </form>

So I created a welcome.php page that only has this info on it. (My database has all these fields and is called Temp1 and table1 is where the data resides.

<?php
    
    $connect = mysql_connect(“localhost”, “root”, “MY_PASSWORD”); if (!connect) { die('Connection Failed: ' . mysql_error()); { mysql_select_db(“temp1”, $connect);
    
    
    $sql = “INSERT INTO table1 (first_name, last_name, address1, address2, city, state, zip, agent, email) VALUES ('$_POST[first_name]', '$_POST[last_name]')” '$_POST[address1]', '$_POST[address2]', '$_POST[city]', '$_POST[state]', '$_POST[zip]', '$_POST[agent]', '$_POST[email]', ; if (!mysql_query($user_info, $connect)) { die('Error: ' . mysql_error()); }
    
    echo “Your information was added to the database.”;
    
    mysql_close($connect); ?>
    
?>

As a beginner you are at the perfect stage to stop using bad practices such as embedding user supplied input directly in the sql and adopting `prepared statements` with bound parameters instead — Professor Abronsius, Aug 14 '22 at 12:55
Your sql statement is unfinished/unclosed. It needs a closing brace and quotes — Professor Abronsius, Aug 14 '22 at 12:56
As nearly as I can tell, you define the `$sql` variable but never use it. You need to actually run the query — Barry Carter, Aug 14 '22 at 12:58
@BarryCarter there is a `mysql_query` after the unterminated sql statement — Professor Abronsius, Aug 14 '22 at 13:00
@ProfessorAbronsius I actually saw the `if (!mysql_query($user_info, $connect)) { die('Error: ' . mysql_error()); }` but that doesn't use the `$sql` variable either — Barry Carter, Aug 14 '22 at 13:03
thanks for pointing out the problems but some of these answers are above me. Asking for solutions unless you feel I need to go elsewhere? Absolute beginner. I was following a tutorial but evidently that was wrong. — EdVinson, Aug 14 '22 at 13:07
My [Mysqli tutorial](https://phpdelusions.net/mysqli) that I consider a good one. — Your Common Sense, Aug 14 '22 at 13:11
Thank you...I will start over with that tutorial!! Looks great! — EdVinson, Aug 14 '22 at 13:20

Professor Abronsius · Accepted Answer · 2022-08-14T15:18:59.047

To handle the insert and mitigate the threat of SQL injection the following might be of interest.

<?php
    /*
        We are only interested if all the POST parameters
        are present.
    */
    if( $_SERVER['REQUEST_METHOD']=='POST' && isset(
        $_POST['first_name'],
        $_POST['last_name'],
        $_POST['address1'],
        $_POST['address2'],
        $_POST['city'],
        $_POST['state'],
        $_POST['zip'],
        $_POST['agent'],
        $_POST['email']
    )){
        # create / include db connection ( Forgot to include db name here - edited )
        $conn=new mysqli( 'localhost', 'root', 'MY_PASSWORD', 'temp1' );
        
        # create the basic sql command with placeholders for binding to
        $sql='insert into `table1` 
                ( `first_name`, `last_name`, `address1`, `address2`, `city`, `state`, `zip`, `agent`, `email` )
            values
                ( ?, ?, ?, ?, ?, ?, ?, ?, ?)';
                
        # create a prepared statement
        $stmt=$conn->prepare( $sql );
        
        # bind the placeholders to the variables. Using `s` for all parameter types is ok
        $stmt->bind_param('sssssssss',
            $_POST['first_name'],
            $_POST['last_name'],
            $_POST['address1'],
            $_POST['address2'],
            $_POST['city'],
            $_POST['state'],
            $_POST['zip'],
            $_POST['agent'],
            $_POST['email']
        );
        # commit/execute the statement, find the result and terminate.
        $stmt->execute();
        $rows=$stmt->affected_rows;
        $stmt->close();
        $conn->close();
        
        echo $rows==1 ? 'OK' : 'FAIL';#oops, used $row not $rows - edited
    }
    
?>

update

Potentially a suitable HTML form for the above process.php to interact with.

<!DOCTYPE HTML>
<html>
    <head>
        <meta charset='utf-8'>
        <title>Registration Form</title>
    </head>
    <body>
        <form action='process.php' method='post'>
            <fieldset>
                <legend>Personal details</legend>
                <label>Forename: <input type='text' name='first_name' /></label>
                <label>Surname: <input type='text' name='last_name' /></label>
                <label>Username: <input type='text' name='username' /></label>
                <label>Email: <input type='text' name='email' /></label>
            </fieldset>
            <fieldset>
                <legend>Address details</legend>
                <label>Address 1: <input type='text' name='address1' /></label>
                <label>Address 2: <input type='text' name='address2' /></label>
                <label>City: <input type='text' name='city' /></label>
                <label>State: <input type='text' name='state' /></label>
                <label>Zipcode: <input type='text' name='zip' /></label>
            </fieldset>
            <fieldset>
                <label>Agent: <input type='text' name='agent' /></label>
            </fieldset>
            
            <input type='submit' />
        </form>
    </body>
</html>

The dummy table schema and result after making changes and running.

mysql> describe table1;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(50) | YES  |     | NULL    |       |
| last_name  | varchar(50) | YES  |     | NULL    |       |
| address1   | varchar(50) | YES  |     | NULL    |       |
| address2   | varchar(50) | YES  |     | NULL    |       |
| city       | varchar(50) | YES  |     | NULL    |       |
| state      | varchar(50) | YES  |     | NULL    |       |
| zip        | varchar(50) | YES  |     | NULL    |       |
| agent      | varchar(50) | YES  |     | NULL    |       |
| email      | varchar(50) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+


mysql> select * from table1;
+------------+-----------+---------------------+----------+--------+-------+---------+----------+-----------------+
| first_name | last_name | address1            | address2 | city   | state | zip     | agent    | email           |
+------------+-----------+---------------------+----------+--------+-------+---------+----------+-----------------+
| Rusty      | Nail      | 23 West High Street | Forfar   | Forfar | Angus | DD8 1HR | geronimo | rusty@gmail.con |
+------------+-----------+---------------------+----------+--------+-------+---------+----------+-----------------+

thank you. I entered that on my welcome.php page and I get this in the browser: The requested resource /%E2%80%9Dwelcome.php%E2%80%9D?first_name=Ed&last_name=Vinson&address1=1209+Laird+Road&address2=&city=Crestview&state=FL&zip=32539&email=email%40edvinson.com was not found on this server. — EdVinson, Aug 14 '22 at 13:15
That particular string suggests the form is using GET as form parameters are appended to the querystring with GET whereas with POST they are not. That string also contains dubious quotes - was this done is WORD or similar? Never use any quotes other than standard double or single quotes ( ie: `"` or `'` ) — Professor Abronsius, Aug 14 '22 at 13:23

Simple MYSQL PHP task Inserting Data

1 Answers1

update