MySQL not recording

[unfthrea]

I'm using this tutorial
http://www.freewebmasterhelp.com/tutorials/phpmysql

When I enter data and submit, the data does not get recorded into the database. What is going on here? I want to make a survey for a charity I am working with but I think i'm missing some code or something.

 

[descalzo]

How should we know?

Seriously.

Maybe you miscopied something. Maybe you are using the wrong password or database name or whatever.

 

[misson]

When asking for help, include minimal sample code. State what you expect/want to happen, and what actually happens, including all error messages. Problems must be reproducible for them to be solvable. Read my sig for more.

That tutorial uses the outdated and softly-deprecated mysql extension. What's worse, the section on inserting user data is widely open to SQL injection, which is a very serious security risk. Drop that tutorial and find one that covers PDO, prepared statements, SQL injection and how prepared statement parameters can protect against injection.

Some links to get started, but don't stop with these:

• Why you Should be using PHP's PDO for Database Access
• PDO Tutorial for MySQL Developers
• Writing MySQL Scripts with PHP and PDO
• PHP tutorial that is security-, accuracy- and maintainability-conscious?

Once you've got DB access in hand, it'll be time to start learning how to separate concerns (e.g. DB access, core logic, data display) into different modules. I've already gone over this in quite a few other posts in these threads (e.g. "Re: Page redirection problem"), so I won't repeat myself.

 

[unfthrea]

Thank you for the information! I am not yet finished reading it. Is there some code available that is known to work? I have found the best way for me to learn code is to take something that works then play with it until it breaks, then I know what shouldn't be changed.

 

[misson]

"What shouldn't be changed" isn't a good way of going about it, as "breaks" isn't the best measurement. It completely ignores things like best (and worst) practices. Even code that gives the correct result can be broken in that it can be inefficient, or broken from a development perspective (hard to debug, hard to share, unmaintainable, unextendable, unreusable). While breaking a program can tell you about what the code does, don't mistake that for what you should do.

In any case, I've already supplied links to alternative tutorials, and a web search will turn up plenty more.

 

[unfthrea]

Good advice! I did a web search and found this tutorial http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html

Then I copy and pasted it and modified the variables to get this

<?php
/*** mysql hostname ***/
$hostname = 'localhost';


/*** mysql username ***/
$username = 'unfthrea_foo';


/*** mysql password ***/
$password = '*******';


$dbname = 'unfthrea_foo' ;


try {
    $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
    /*** echo a message saying we have connected ***/
    echo 'Connected to database<br />';


    /*** INSERT data ***/
    $count = $dbh->exec("INSERT INTO unfthrea_foo(first, last) VALUES ('kiwi', 'troy')");


    /*** echo the number of affected rows ***/
    echo $count;


    /*** close the database connection ***/
    $dbh = null;
    }
catch(PDOException $e)
    {
    echo $e->getMessage();
    }
?>

It's supposed to display
"Connected to database
1 "
I get the "Connected to database" but not the "1". I noticed that the original code does not actually create DBs. I had to change the name from the one the creator used to the one I had made before hand. I tried changing the values for the INSERT part but I still do not get the "1" when I access the page at http://unfthreads.x10.mx/survey2/pdotest.php

There's no explanation for this on the sites. They assume I get it right the first time and move on to the next lesson.

 

[misson]

Insertion doesn't (indeed, can't, since it includes no type information) create databases or tables. INSERT statements are part of the DML; you need to use a DDL statement (specifically, CREATE TABLE) to create a table. See How to Create a MySQL Database and User for info on creating a DB on X10.

Any tutorial on PHP + MySQL will teach you very little about SQL and relational databases. Chances are, you'll miss out on the core concepts, causing no end of trouble. Better to find a source that deals with the relational model explicitly. "Introduction to Databases and Relational Data Model" by Maurer and Scherbakov is basic and not perfect, but decent enough introduction (there's an older, plain-HTML version of the text). See also:

• Good Resources for Relational Database Design
• Relational database theory and SQL book recommendations?
• How should a programmer learn great database design?
• Database Design Best Practices
• Best Book for a new Database Developer
• Relational database theory and SQL book recommendations?

Having the user, database, and table names all the same is a smell, if not being outright incorrect (is there a table named "unfthrea_foo" in database "unfthrea_foo"?). Also, while databases and DB users on X10 must have a prefix, tables don't; you're free to name tables whatever you want.

For PDO to use exceptions, you must set the appropriate error mode. Otherwise, you need to use PDO->errorInfo to get error information. Note the tutorial that you linked to goes over this. This is also a good example of why an interactive debugger is superior to debug scaffolding: the output is limited to what you explicitly record, while an interactive debugger lets you examine any data in scope at almost every step of the program.

The PHPPro tutorial (like many tutorials) does many things in the examples that aren't suitable for production code. In one respect, this is fine, as the code isn't intended for production. However, it must make explicit exactly what is only shown for example purposes and not suited for production. For example, the PHPPro tutorial doesn't go far enough when stating that "Normally we would not show [DB error messages] to the end user". It's not just not normal, it's bad practice, as it discloses too much information, which is bad from both security and usability perspectives. The PHPPro tutorial also makes liberal use of SELECT * without going into why it's only suitable for examples and not production code. It also doesn't go into why DB access and data display should be handled separately in production code.

There's no explanation for this on the sites. They assume I get it right the first time and move on to the next lesson.

That's because a tutorial is, by necessity, incomplete due to length constraints. For a work on MySQL and PHP to be complete, it would be at least book length. Also, a tutorial on PHP+MySQL is about interfacing the two, not about how to use each by themselves. The tutorial doesn't go into PHP language topics (such as control statements and OOP), so why should it go into SQL language topics and relational design? (This, by the way, is another issue I have with the PHPPro tutorial: it covers too much about SQL statements, making the tutorial unfocused and murky. SQL should be learned separately from PHP.) Before you can use PHP+MySQL, you must understand how to use them separately so that you don't confuse things. Also, declarative and procedural languages are different beasts, and so learning SQL and PHP separately helps maintain this distinction. It shouldn't be until you've mastered the language paradigms that you should learn about equivalencies between them.

 

[unfthrea]

Thank you for the information. I have done much research. I'm still having the same damn issue though. I was successfully able to add data to MySql (YAY!). Unfortunately, when I want data from a form to be entered it doesn't work. I'm doing this in xampp.

The following is the code I used, which should work. But it doesn't. No error messages, just doesn't record.

<?php$host = "localhost";
$user = "username";
$db   = "webdev1";
$pass = "password";


$odb  = new PDO("mysql:host=" . $host . ";dbname=" . $db, $user, $pass);


if(isset($_POST['name'])) {
    $name = $_POST['name'];
    $age  = $_POST['age'];
    $q    = "INSERT INTO user (name, age) VALUES(:name, :age);";
    $query = $odb->prepare($q);
    $results = $query->execute(array(
        ":name" => $name,
        ":age"  => $age
      ));          
}
?>

This is the other piece of the code

<?php require_once("header.php");?><!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Wev Dev 1</title>
    </head>
    <body>
        <form method="post" action="">
            Name: <input type="text" id="name" name="name" /><br />
            Age: <input type="text" id="age" name="name" /><br />
            <input type="submit" value="add" />
        </form>
        <?php
       
            $query = "SELECT * FROM user";
            $result = $odb->query($query);
            if($result->rowCount() > 0) {
                foreach($result as $item) {
                    echo($item['name'] . " is " . $item['age'] . "years old.<br />\n");
                }
            }
         
         ?>
            
    </body>
</html>

 

[descalzo]

TEST EVERYTHING.

if(isset($_POST['name']))

And if it isn't? Have it print out something.
Print out $name, $age
Do you test $result?

 

[gomarc]

Hi unfthrea,

In your 'other' piece of the code,

Change:

Age: <input type="text" id="age" name="[B][COLOR="#FF0000"]name[/COLOR][/B]"

To:

Age: <input type="text" id="age" name="[B][COLOR="#FF0000"]age[/COLOR][/B]"

 

[unfthrea]

gomarc: I love you sir. I can't believe I missed that. Why was neither name, nor age recorded? I would think only name would be recorded and age wouldn't.

what do you recommend I do to spot errors like this on larger pieces of code? I'm doing this for a survey which may end up being 100 questions long. If the code won't record anything because of a single incorrect html name, that could be very troublesome.


descalzo: I had put else echo 'not working'. It echoed 'not working'.


Thank you all for your help. Hopefully I'll have the survey done by tonight!

 

[misson]

The following is the code I used, which should work. But it doesn't. No error messages, just doesn't record.

Note that there are no error messages because you've implemented no error handling. In general, programs don't just do things; you need to explicitly implement any behavior you want. Sometimes the platform may handle certain tasks automatically, but you can't assume this will be the case and the default behavior may not be appropriate (displaying internal error messages to non-admins, for example, would inappropriately disclose too much information). Check the documentation and run tests if you want to know if the platform will handle some aspect.

Form labels should be placed in <label> elements.

<br/> isn't being used semantically; use something more appropriate, such as a paragraph or list element, or apply styling to existing elements, such as <label> and <input /> elements.

As for debugging code, there are a number of standard tools & practices.

• Use validators; W3C has HTML and CSS validators. Any decent program editor/IDE should support syntax highlighting, which is another form of validation.
• Use interactive debuggers. Most modern web browsers have them built in (they're often a part of the browser's developer tools). For Firefox, there's also the Firebug extension. Browser debuggers let you step through JS code, monitor network requests (which you could have used to catch the error gomarc pointed out, as there would have been two "name" keys in the request) and offer a slew of other useful information. To debug PHP, you need to install a debugging extension (XDebug is a decent one) on your dev computer and get a client compatible with the debug extension. The XDebug site has a list of compatible clients.

Eclipse is a powerful IDE that offers syntax highlighting, interactive debugging with XDebug or Zend Debugger (and possibly others, though those are the only two I know of), but has a steep learning curve. In any case, the above should have enough keywords to get you started with web searches. There's much to say on the topics, and much has already been said elsewhere, so I won't repeat it here.