Connection to the DB is successful, but not visible in MySQL

Question

I've viewed a couple of questions related to this and most seem to be answered by simple syntax errors. I don't think my problem is syntax however.

I am connecting to my db successfully, but I cannot seem to see my entries in phpmyadmin (where I am viewing MySQL). I can echo my entries on another page as a variable, but I believe my input isn't going into the database.

Here is my html code:

 <!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>student info</title>
    </head>
    <body>
        <br>
        Enter your first name and last name in the corresponding boxes.
        <br>
        <form  action="submit.php" method="POST">
            First: <input type="text" name="firstname"/>
        <br>
            Last: <input type="text" name="lastname"/>
        <br>
        <input type="submit">
        </form>


    </body>
</html>

My php for the database connection:

<?php
echo 'here';
    $dsn = 'mysql:host=localhost;dbname=practice_students';


    try {
        $db = new PDO($dsn);
        echo 'db connection success';
    } catch (PDOException $e) {
        $error_message = $e->getMessage();
        include('database_error.php');
        exit();
    }

?>

And my php for the submission page:

<?php
echo 'here ';
    $dsn = 'mysql:host=localhost;dbname=practice_students';


    try {
        $db = new PDO($dsn);
        echo 'db connection success';
        $firstname = filter_var($_POST['firstname'], FILTER_SANITIZE_STRING, 
                FILTER_SANITIZE_SPECIAL_CHARS);
        $lastname = filter_var($_POST['lastname'], FILTER_SANITIZE_STRING,
                FILTER_SANITIZE_SPECIAL_CHARS);
        echo "Now we know your name! Hi," . " " . $firstname . " " . $lastname;
    } catch (PDOException $e) {
        $error_message = $e->getMessage();
        include('database_error.php');
        exit();
    }
?>

All of which prompt the response successfully in my local host

here db connection successNow we know your name! Hi, Maggie Bowen

However, MySQL shows no entries when I try to CHECK or SELECT *.

How can I see my entries? I know some of my sanitizing etc. can be improved, but I would really just like to know how to see my entries and ensure they are entered into the table. Thank you!

What did you with `$db` after `$db = new PDO($dsn);` ? Seems nothing, Or? — JustOnUnderMillions, Nov 30 '16 at 17:02
At no point do you actually try to *query* the database. No inserts, updates, selects, nothing. `$db` is created then ignored. — aynber, Nov 30 '16 at 17:03
I took out $username and $password variables which were a part of the PDO but having that code and my current both had the same problems — Curiosity List, Nov 30 '16 at 17:05
I do use a POST method on my variables, how is that an ineffective query? — Curiosity List, Nov 30 '16 at 17:06
`POST` only posts to your submission page. It has nothing to do with MySQL or PDO. You need to actually write the code to submit it to the database. Here, read up on [PDO's prepared statements](http://php.net/manual/en/pdo.prepared-statements.php). — aynber, Nov 30 '16 at 17:08
*"I took out $username and $password variables which were a part of the PDO but having that code and my current both had the same problems"* - You should have included that in your question and you probably did an error somewhere that would have more than likely be easy to spot. — Funk Forty Niner, Nov 30 '16 at 17:41

Jacopo · Accepted Answer · 2016-11-30T18:21:04.223

3

You have the data $firstname and $lastname. Now you have to insert them into the database submitting a query using PDO::query().

Something like this:

$q = "INSERT INTO people (column1, column2) VALUES ('$firstname', '$lastname')";

$db->query($q);

EDIT Use prepared statements to avoid SQL Injection Attacks

Wikipedia says

Prepared statements are resilient against SQL injection, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.

So the reviewed code

$stmt = $db->prepare("INSERT INTO people (column1, column2) VALUES (:firstname, :lastname)";

$stmt->bindParam(':firstname', $firstname);
$stmt->bindParam(':lastname', $lastname);

$stmt->execute();

Thanks to the guys from the comments!

edited Nov 30 '16 at 18:21

answered Nov 30 '16 at 17:26

Jacopo

130
1
1
9

1

`VALUES ($firstname, $lastname)` - I doubt those are integers here. Strings require to be quoted. Otherwise, what you posted will throw syntax errors. This for future readers to the Q&A who may think that what you wrote is valid syntax; which it isn't. – Funk Forty Niner Nov 30 '16 at 17:38
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Nov 30 '16 at 17:44
1

Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) – Jay Blanchard Nov 30 '16 at 17:45
@JayBlanchard is right though. A prepared statement is much better than basic escaping (PHP.net didn't see the future back then) ;-) Your answer would attract more votes when referencing prepared statements. Just trying to help ;-) – Funk Forty Niner Nov 30 '16 at 17:48
Really thanks for the help guys, i really appreciated it! – Jacopo Nov 30 '16 at 18:24
@JacopoBontà *prego* ;-) e grazie! – Funk Forty Niner Nov 30 '16 at 20:21

Connection to the DB is successful, but not visible in MySQL

1 Answers1