[RESOLVED] Need help with forms and adding to MySQL Database

RtK_Evasion

Hi guys. As you can probably see this is my first post on PHPBuilder. Loving the site already. Great sense of community.

So, down to the work. I have recently started learning PHP so I grant you permission to call me a noob. 🙂

I am having issues adding variables filled with text (in this case customer information) into a localhost (wampserver) MySQL Database. Ill show you what I have so far.

Config.php: Connects to my MySQL Database:

<?php

// Initiate MYSql Details
$MYSQL_Host = "localhost"; // Where the MySQL Database is located.
$MYSQL_Username = "root"; // MySQL Username.
$MYSQL_Password = ""; // MySQL Password.
$MYSQL_DbName = "iprint"; // Database name to connect to.

// Connect to MySQL Database (with OR DIE statement).
$MYSQL_Connection = mysql_connect($MYSQL_Host, $MYSQL_Username, $MYSQL_Password) or die ('MySQL Error: Failed to Connect to Database. Please see Cconfig.php. mysqli_connec() reported this error.');

// Select Database.
mysql_select_db($MYSQL_DbName, $MYSQL_Connection);

?>

addcustomer.php: Shows the form to fill in customer details.

<html>
    <head>

</head>
<body>
    <h2>Add Customer</h2>

    <form action="doinsertcustomer.php" method="post">
        Business Name: <input type="text" name="bname" size="16" maxlength="32" /><br />
        Email: <input type="text" name="email" size="16" maxlength="32" /><br />
        Phone: <input type="text" name="phone" size="16" maxlength="32" /><br />
        Contact Name: <input type="text" name="cname" size="16" maxlength="32" /><br />
        Melway Ref: <input type="text" name="melway" size="16" maxlength="32" /><br />

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


</body>
</html>

doinsertcustomer.php: Gets variables via $_POST[] from addcustomer.php and tries to insert them into the MySQL database:

<?php

include_once "config.php"; // Connects to MySQL Database. See Config.php for details.


$name = $_POST['bname'];
$email = $_POST['email'];
$phone = $_POST['phone'];
$cname = $_POST['cname'];
$melway = $_POST['melway'];

$sql = "INSERT INTO `iprint`.`customers` (`Name`, `Email`, `Phone`, `Contact Name`, `SORCDD`, `Melway`, `Comments`, `CID`) VALUES (\'$name\', \'$email\', \'$phone\', \'$cname\', \'\', \'$melway\', \'\', NULL);";
mysql_query($sql); // Insert information from form to MySQL Database.

?>

When I insert information into the form and then press submit it runs 'doinsertcustomer.php' but when I check my PHPMyAdmin it is not there. I really need help on this as this is a very important task. Thanks for all your help guys. I really appreciate it.

bradgrafelman

Welcome to PHPBuilder.

Here are a couple of general tips to follow when debugging SQL-related problems:

Ensure that display_errors is On and error_reporting set to E_ALL.
Check to see if [man]mysql_query/man returned false; if it did, I like to output not only the error message (e.g. from [man]mysql_error/man) but also the query that was sent as well (e.g. $sql in your case) - that way you can visually inspect the query yourself rather than guessing at what it looked like.

Some other suggestions:

Don't reference external data ($POST, $GET, $_COOKIE, etc.) without first checking that it exists; otherwise, you'll generate an E_NOTICE error message.

Normally, this is done by using [man]isset/man or [man]empty/man.
User-supplied data should never be placed directly into a SQL query. Instead, it must first be sanitized with a function such as [man]mysql_real_escape_string/man.

RtK_Evasion

bradgrafelman;10941541 wrote:
Welcome to PHPBuilder.

Thank You. Great to be here.

bradgrafelman;10941541 wrote:
Ensure that display_errors is On and error_reporting set to E_ALL.

How would I go about changing this setting? If this is in the PHP.ini file, where can i find this?

bradgrafelman;10941541 wrote:
Check to see if mysql_query() returned false;

To test if it did return false would i do a simple if statement such as:

<?php
if(mysql_query($sql) = "false") {
// Report error.
}
?>

Thanks for your reply.

Weedpacket

if(mysql_query($sql) = "false")

Not quite; that (attempts) to assign the string "false" to the result of mysql_query.

if(mysql_query($sql) == false)

Or, for short

if(!mysql_query($sql))

bradgrafelman

RtK Evasion wrote:
How would I go about changing this setting? If this is in the PHP.ini file, where can i find this?

Well, yes, this is a PHP directive so it can be found in the PHP.ini file, but if you're using Apache and PHP is installed as an ISAPI module, you can also use .htaccess to modify PHP directives. For more info on this, see this manual page: [man]configuration.changes[/man].

RtK_Evasion

Weedpacket;10941543 wrote:
if(mysql_query($sql) = "false")
Not quite; that (attempts) to assign the string "false" to the result of mysql_query.
if(mysql_query($sql) == false)
Or, for short
if(!mysql_query($sql))

Wow, I knew that. Thanks for putting up with my noobiness. 🙂

RtK_Evasion

Ok. I did this:

if(!mysql_query($sql))
    {
        print "MySQL insert query failed.";
    }

It does fail. What would be the reason and how could I fix it?

halojoy

This is what I would try.
Normally you do not need to insert NULL into a column. Because any column not specified will automatically insert NULL as a default value.
We shoul NOT put a semicolon after the Query
Not like this:
$sql = "SELECT * FROM table;";

I also added echo of $sql in the errror message:
exit('<br>'.$sql.'<br>'.mysql_error());

$sql = "INSERT INTO `customers` 
(`Name`, `Email`, `Phone`, `Contact Name`, `SORCDD`, `Melway`, `Comments`)
 VALUES 
('$name', '$email', '$phone', '$cname', '', '$melway', '')";
if(!mysql_query($sql))
	exit('<br>'.$sql.'<br>'.mysql_error());

RtK_Evasion

Your suggestion works. Thank you very much Halojoy.

Hmm, I have never used the Exit statement before. I shall look into it, thank you. I will also make sure I don't make the semicolon mistake again. 🙂

halojoy

exit() is the same as die()
It is just another name for the same function.
But as I am afraid of death and dying, I prefer [man]exit[/man] 😃

bradgrafelman

Just for some clarification...

halojoy;10942197 wrote:
This is what I would try.
Normally you do not need to insert NULL into a column.

That's pretty much true; you'd only need to explicitly define a column's value as NULL if a) it's default value was anything other than NULL, b) the column can contain the NULL value, and c) you desire the row to have NULL in that column.

halojoy;10942197 wrote:
Because any column not specified will automatically insert NULL as a default value

That's definitely not true (at least, not usually). In phpMyAdmin, the default setting is for a column to be "NOT NULL", so for something like a VARCHAR column the default value is most likely an empty string.

halojoy;10942197 wrote:
We shoul NOT put a semicolon after the Query

Why not? Placing a semicolon after a SQL query is required - otherwise it doesn't know where the end of the query is. Sure, most of the SQL libraries in PHP I've seen will automatically add this semicolon if you don't explicitly state it, but it definitely doesn't hurt to add it yourself (in fact, it may help in that it reminds you that it must be there - if you switch to using the CLI and forget to put the semicolon, it'll just sit there and wait for you to key in the rest of the query no matter how many times you hit Enter).

halojoy

bradgrafelman
Thanks for some very good detailed information regarding columns and NULL.

About the ending semicolon
Like for example

$sql = "SELECT * FROM table;";
// versus
$sql = "SELECT * FROM table";

I have read the information that we do not need this ending semicolon at some website.
Since then I have never added it.
And this has not yet caused any trouble for me.
Not when using it in PHP-Scripts, that is.

My guess is that maybe there are differences between different Databases.
Even if here we talk 95% MySQL.
It may also be different when using SQL Commands via other sources then PHP.

Right now I am not sure what we should use for MySQL + PHP.
But as this works fine I will continue omit the semicolon, until I see I really need it.
If some other like to use it, it will probably work as good.
Even if I see not many here at forum using any extra semicolon .......

Regards

bradgrafelman

Right, the semicolon isn't necessary when writing PHP scripts. As I said, that's most likely because of some pre-processing done by whichever DB library you're using (be it mysql, mysqli, PDO, etc. etc.) that checks to see if you included the semicolon at the end of the query. If not, it adds it before interfacing with the SQL server.

RtK_Evasion

Right, now I understand.

The reason I did the INSERT NULL thing was because I pressed Create PHP Code in phpMyAdmin and was guessing that was the right way to do it. Also, I just had a look and phpMyAdmin puts the ending semicolon, I don't know if thats an error with the software or something. Thank You so much for your help.

halojoy

RtK Evasion
The bottomline is:
You can add a semicolon at the end.
Or you can take it away. It will work both cases.
At least when working with php_mysql.dll (or new driver: mysqlnd) extension

I have had some errors when usings semicolon.
But this was with php_sqlite.dll = another database.
I had to remove those semicolons to not get ERROR.

When working with MySQL Commandline programs
you must end with semicolon.
As bradgrafelman told, this is the signal you SQL Commandline is at the end
and ready to be executed.

RtK_Evasion

Right. So really the error in my INSERT command was the NULL's. Ok, thank you. Also quick question, when you were using SQLite, does it use the same basic commands as MySQL?