adding data from a form to a database

Question

I have created a webform to allow a user to request to see my CV, once the user hits submit I want to store their input inside a database in phpMyAdmin. Since I am new to PHP and using databases within a html document, I was given some code to copy and change to match my form fields. When I hit submit it goes straight to the else statement within the process_CV_request.PHP file.

My database consists of userid which auto increments, firstname, surname, emailid, companyname, usercomment and cvtype(Long or Short)

My Form

    <body>
        <div class="contact-title">
            <h1 >CV Request</h1>
        </div>
        <div>
            <form id="contact-form" action="process_CV_requests.php" method="post" action="">

                <input type="text" name="FirstName" class="form-control" placeholder="Your First Name"><br>

                <input type="text" name="Surname" class="form-control" placeholder="Your Surname"><br>

                <input type="text" name="CompanyName" class="form-control" placeholder="Your Company Name"><br>

                <input type="text" name="EmailAddress" class="form-control" placeholder="Your Email Address"><br>

                <textarea name="comment" class="form-control" placeholder="Leave a Comment" rows="5"></textarea><br>

                <p class="cvType">CV: Short <input type="radio" name="cvType" value="Short" checked> Long <input type="radio" name="cvType" value="Long"><br></p>

                <input type="submit" class="form-control submit" value="Submit">
            </form>
        </div>
    </body>
    </html>

My db.php which I use to connect to my database

<?php

    error_reporting( error_reporting() & ~E_NOTICE);

    $db_location = "localhost";
    $db_username = "Username";
    $db_password = "password";
    $db_database = "nameofmydatabase";
    $db_connection = new mysqli("$db_location", "$db_username", "$db_password");

    if ($db_connection->connect_error){
        die("Connection failed: " . $db_connection->connect_error);
    }
    $db = mysqli_select_db($db_connection, $db_database)
        or die ("Error - could not open database");


?>

process_CV_request.PHP file

<?php

require_once "db.php";

    if($SERVER["REQUEST_METHOD"] == "POST")
    {
        $erremail = $errfirstname = $errsurname = $errCVtype = $errCompanyname = "";
        $email = $firstname = $surname = $usercomment = $cvtype = $companyname = "";

        $firstname = mysqli_real_escape_string($db_connection, $_POST["firstname"]);
        $surname = mysqli_real_escape_string($db_connection, $_POST["surname"]);
        $companyname = mysqli_real_escape_string($db_connection, $_POST["company"]);
        $email = mysqli_real_escape_string($db_connection, $_POST["emailid"]);
        $cvtype = mysqli_real_escape_string($db_connection, $_POST["cvchoice"]);

        $usercomment = mysqli_real_escape_string($db_connection, $_POST["usercomment"]);
    }
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
    <title>PHP AND MySQLi Thank you message.</title>
</head>
<body>

<?php
    if($_SERVER["REQUEST_METHOD"] == "POST")
    {
        $qry ="insert into cv_requests(firstname, surname, companyname, emailid, usercomment, cvrequested)
            values('$firstname','$surname','$companyname','$usercomment','$email',$cvtype');";
        $res = $db_connection->query($qry);
        if($res)
        {
            echo "<p>Thank you for requesting to see my CV</p>";
            echo "<p>Your company name: <strong>".$companyname."</strong></p>";
            echo "<p>Your comment: <strong>".$usercomment."</strong></p>";
            echo "<p><a href='files/";
            if($cvtype === 'short')
                echo "Short_CV";
            else
                echo "Long_CV";
            echo ".pdf' target='_blank'>view my ".$cvtype." CV</a></p>";
            exit();
        }
        else
        {
            echo "<p>Error occured, please try again.</p>";
            exit();
        }

    }
$db_connection->close();    
?>
</body>
</html>

If all works then I want to display to the user their company name that they entered, their comment and a link to download the cvtype that they selected. Thanks

Since you're new to PHP, this is a good time to learn how to do SQL queries properly and safely. See [here](https://phpdelusions.net/pdo#prepared) or [here](https://stackoverflow.com/questions/1457131/php-pdo-prepared-statements) for some randomly selected links from search — miken32, Apr 03 '19 at 22:51
It looks like you're using the `$_POST['cvchoice']` variable - should this be `$_POST['cvType']` instead? — D. Winning, Apr 03 '19 at 22:59
Add `echo mysqli_error($db_connection)` to your else part to see any possible error(s) with the query. — , Apr 03 '19 at 23:02
@catcon this is the error Parse error: syntax error, unexpected 'exit' (T_EXIT), expecting ',' or ';' in 'MyDirectory' on line 50 — Darren_D19, Apr 03 '19 at 23:05
I think you miss a semicolon at the end of newly added `echo` — , Apr 03 '19 at 23:08
@catcon Sorry about that, this is the error I get now You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '')' at line 2 — Darren_D19, Apr 03 '19 at 23:10
I just take a look at your SQL command, you missed the open single quote at `$cvtype` — , Apr 03 '19 at 23:12
@catcon This is my error now, Data truncated for column 'cvtype' at row 1 — Darren_D19, Apr 03 '19 at 23:15
On this line: `$qry ="insert into cv_requests(firstname, surname, companyname, emailid, usercomment, cvrequested) values ('$firstname','$surname','$companyname','$usercomment','$email',$cvtype');";` — D. Winning, Apr 03 '19 at 23:19
You specified the column `cvrequested` in your SQL command. Check your database structure, make sure you got all the columns' name right. — , Apr 03 '19 at 23:19
@catcon I realised after, I changed I get Data truncated for column 'cvtype' at row 1. I have that column set to Enum with two values of either short or long. The if statement must not be working properly — Darren_D19, Apr 03 '19 at 23:21
@D.Winning Thanks, I got it, but now it wont insert the cvtype as stated in the above comment, thanks — Darren_D19, Apr 03 '19 at 23:22
Cool, hope that help you to fix your issue. Since you are new to PHP, take a look at @miken32 suggestion about using prepare statement to prevent SQL injection. Also you are mixing between mysqli's OOP and procedural, even though it won't really affect PHP's functionality, but try to stay consistent, it will be beneficial when you move to bigger projects. — , Apr 03 '19 at 23:29
@Darren_D19 I've just created a fairly simple one page working solution of this, using PDO instead of mysqli - not sure if it will help if I posted it as an answer? — D. Winning, Apr 03 '19 at 23:31
@catcon In my form I have a radio button to switch between short or long version of my CV and in my database I have used the datatype 'ENUM' and set it to hold either short or long, but when I try submit it says 'Data truncated for column 'cvtype' at row 1' Do you know how to fix this? — Darren_D19, Apr 03 '19 at 23:32
@D.Winning Might be a silly question but will it work on PHPMyAdmin? — Darren_D19, Apr 03 '19 at 23:32
@Darren_D19 yes, it will work with a mysql database, it just uses object oriented methods instead :) I'll post it now — D. Winning, Apr 03 '19 at 23:36
Okay I will give your solution a try and see if I am still getting the 'Data truncated for column 'cvtype' at row 1' error — Darren_D19, Apr 03 '19 at 23:41

D. Winning · Accepted Answer · 2019-04-04T00:58:45.373

1

So I recreated a simple one page version of this using PDO instead of mysqli. Hopefully it helps towards your predicament. I would encourage learning more about PDO if you find this method easier to understand.

Create a basic table in your db with SQL (phpmyadmin):

create table cv_requests (
    userid int not null auto_increment primary key,
    firstname varchar(255),
    surname varchar(255),
    companyname varchar(255),
    emailid varchar(255),
    usercomment text,
    cvrequested tinyint(1)
);

The html form (index.php):

<div>

    <form id="contact-form" action="index.php" method="post">

        <input type="text" name="first_name" class="form-control" placeholder="Your First Name"><br>

        <input type="text" name="surname" class="form-control" placeholder="Your Surname"><br>

        <input type="text" name="company_name" class="form-control" placeholder="Your Company Name"><br>

        <input type="text" name="email" class="form-control" placeholder="Your Email Address"><br>

        <textarea name="comment" class="form-control" placeholder="Leave a Comment" rows="5"></textarea><br>

        <p class="cvType">CV: Short <input type="radio" name="cv_type" value="Short" checked> Long <input type="radio" name="cv_type" value="Long"><br></p>

        <input type="submit" class="form-control submit" value="Submit">

    </form>

</div>

The PHP - in index.php just below the form:

<?php

// function to connect to the database
function connect($dbhost, $dbname, $dbuser, $dbpassword) {

    // try to connect, if not end the script
    try {

        return new PDO('mysql:host=' . $dbhost . ';dbname=' . $dbname, $dbuser, $dbpassword);

    } catch (PDOException $e) {

        die($e->getMessage());

    }

}

// a new PDO instance - enter db credentials
$pdo = connect('localhost', 'test_db', 'root', '');

// if somethings been posted to the page
if ($_POST) {

    // set variables to post values - for use binding paramaters
    $first = $_POST['first_name'];
    $last = $_POST['surname'];
    $company = $_POST['company_name'];
    $email = $_POST['email'];
    $comment = $_POST['comment'];

    if (isset($_POST['cv_type'])) {

        if ($_POST['cv_type'] == 'Short') : $cv_type = 0; endif;

        if ($_POST['cv_type'] == 'Long') : $cv_type = 1; endif;

    }

    // prepare a new sql query
    $insert = $pdo->prepare('insert into cv_requests (firstname, surname, companyname, emailid, usercomment, cvrequested) values (:first, :last, :company, :email, :comment, :type)');

    // binds all of the parameters to be inserted into the db to the vars we set earlier
    $insert->bindParam(':first', $first);
    $insert->bindParam(':last', $last);
    $insert->bindParam(':company', $company);
    $insert->bindParam(':email', $email);
    $insert->bindParam(':comment', $comment);
    $insert->bindParam(':type', $cv_type);

    // insert into the database
    $insert->execute(); ?>

    <p>Thank you for requesting to see my CV</p>
    <p>Your company name: <strong><?= $company; ?></strong></p>
    <p>Your comment: <strong><?= $comment; ?></strong></p>

    <?php $cv = $cv_type ? 'Long' : 'Short'; ?>

    <a href='files/<?= $cv; ?>.pdf' target='_blank'>View my <?= $cv; ?> CV</a>

    <?php

}

edited Apr 04 '19 at 00:58

answered Apr 03 '19 at 23:51

D. Winning

302
1
13

My cv selection in my form is a radio button, would much of this change? also what datatype would I use in the cvtype field to store either the word short or long? – Darren_D19 Apr 04 '19 at 00:02
I've edited the post to include radios instead of the drop downs, nothing else needs to change for that to still work. As it's a one or the other value, I would personally use tinyint as above and use a conditional when returning the value to display either 'Short' or 'Long' - if you're sure you want to just store it as is a varchar or text field should be fine. – D. Winning Apr 04 '19 at 00:09
In which case, you could delete the conditional that sets the `$cv_type` variable, and set it above like how the other variables have been set. – D. Winning Apr 04 '19 at 00:11
2

Nice answer, but you do need to specifically enable exceptions on PDO (and then use them in try/catch blocks. ) As it is, you don’t check success or failure in anything. Also, no need to bind parameters. Just pass an array to execute. – miken32 Apr 04 '19 at 00:12
Where did you get ```action="index.php"``` in the form action?, Would I need to change my file from 'example.html' to 'example.php' – Darren_D19 Apr 04 '19 at 00:12
@Darren_D19 in this example I named the file index.php and both the form and PHP were in the same file. When the form is submitted, the values of the form are sent to the page specified in the action attribute of the form. In this case, it just sends data to itself and then executes the code within the `if ($_POST)` conditional. To set this up as per your OP you could do something like: form.html - just the HTML form. request-cv.php - all of the PHP code. Then you would change the action of the form to "request-cv.php". If your page includes php, you'll need to change ext from .html to .php – D. Winning Apr 04 '19 at 00:23
Is this line finished? or do I have to add something to it? ```return new PDO('mysql:host=' . $dbhost . ';dbname=' . $dbname, $dbuser, $dbpassword);``` also I don't understand what to do here ```$pdo = connect('localhost', 'test_db', 'root', '');``` – Darren_D19 Apr 04 '19 at 00:30
That lines finished, the parts you will need to change are on this line: `$pdo = connect('localhost', 'test_db', 'root', '');`. Change the parameters to your host name, your database name, your database username, your database password. – D. Winning Apr 04 '19 at 00:33
Fixed what I had said, thanks it added it to the database, I just need to change the final message to display the correct stuff :) – Darren_D19 Apr 04 '19 at 00:42
Just one last question, if I wanted the output tp be the same way that it was in my original post how would I do that? you have started it so that I can output the users first name, but I am unsure on how to add more things to that, thanks – Darren_D19 Apr 04 '19 at 00:47
Edited the answer so that it will match your original output – D. Winning Apr 04 '19 at 00:59
Thank you so much, the output always includes this ```?> };``` at the end How can I fix it? – Darren_D19 Apr 04 '19 at 01:02
Also if the user selects the Short_CV it is still displaying the long_CV at the end, would an if statement solve this? – Darren_D19 Apr 04 '19 at 01:08
There is a shorthand if statement just above the link to determine whether Long or Short is printed and which one to link to. Did you change anything or paste the above in the wrong place? I can't see why it's outputting `?> }` at the end - did you close off the php at the end of the file and before the closing `}`? If PHP continues to the end of the file, you don't need to close it. Also - if you mean that the link doesn't change when you toggle between short + long radios, the output won't change until the form is actually submitted. – D. Winning Apr 04 '19 at 01:16
I copied it exactly as you have it, and even if the user selects the Short option it still say ```View my Long CV }``` and the very last bracket in the file is also there EDIT - Got rid of the bracket but the CV still comes out wrong – Darren_D19 Apr 04 '19 at 01:25
Have you copied the form too? Check that the name attributes of your radio inputs match `name="cv_type"` – D. Winning Apr 04 '19 at 01:29

the.marolie · Answer 2 · 2019-04-03T23:01:08.137

0

You have a typo in the first if condition. It should be

if($_SERVER["REQUEST_METHOD"] == "POST")

This results in null values which fails to execute your query

edited Apr 03 '19 at 23:01

answered Apr 03 '19 at 22:55

the.marolie

1,032
2
7
14

adding data from a form to a database

2 Answers2