php to MySQL not working and not sure why

Question

Its a really simple script, or at least it should be. I am kinda not sure around pHp so Im not sure where I am going wrong.

This page is called from a submit button on a form, all it is supposed to do is capture the name, email address and date of submission and add it to my database.

I can connect to the database without issue but cannot add to the database.

For some reason, everytime I load this page I also get a blank screen. pHp / SQL doesnt look like it has friendly bug reporting.

Here is the code with obvious info take outs.

<html>
<head>  
</head>
<?php

$db_host = "localhost";
$db_username = "root";
$db_pass = "123";
$db_name = "emailtest";

$conn = @mysql_connect($db_host,$db_username,$db_pass,$db_name);

if ($conn->connect_error) 
{
    die("Connection failed: " . $conn->connect_error);
} else {
    echo "Good connection ";
}

if(!empty($_REQUEST['name']))
{
    $name = $_REQUEST['name'];  
    echo "hello, $name ";

    if(!empty($_REQUEST['email']))
    { 
        $email = $_REQUEST['email'];    
    }
    else
    {
        $email = NULL;
    }

    if($email)
    {       
        if ($conn->query($sql) === TRUE) 
        {
            $dateTime = date("Y/m/d");
            $sql = "INSERT INTO Newsletter_signup (name, email, sign_up_date) VALUES('$name','$email','$dateTime')";
            echo "Record updated successfully <br/>";
            echo "The email address, $email , has been added to the newsletter.";
        } else {
            echo "Error updating record: " . $conn->error;
        }

        $conn->close();
    }
    else
    {
        echo 'Please go back and insert an email address.';     
    }

?>

<body id="body">
 // body style stuff
</body>
</html>

Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). You will also want to [Prevent SQL Injection!](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Nov 12 '14 at 15:28
Remove the `@` from the mysql connection line and you might know why :) — AnchovyLegend, Nov 12 '14 at 15:30
"I can connect to the database without issue but cannot add to the database". Are you sure that the user has been GRANTed write access? Also, can you post the exact error returned by MySql? — Mawg says reinstate Monica, Nov 12 '14 at 15:31
As a tip, PHP can produce friendly error's. Get rid of the @ in front of mysql and put in the upper most up of your file (eg, line 1) error_reporting("E_ALL"); — Chilion, Nov 12 '14 at 15:32
You miss a ```}``` at the end of code. Try to reindent the code for better debug. — ChoiZ, Nov 12 '14 at 15:33
Apart from `$sql` not being defined where you first use it, `$dateTime` doesn't seem to be defined either. — RST, Nov 12 '14 at 15:35

score 2 · Answer 1 · answered Nov 12 '14 at 15:30

2

because you define $sql after the $conn->query($sql) and $sql is empty.

answered Nov 12 '14 at 15:30

LTasty

2,008
14
22

score 1 · Accepted Answer · answered Nov 12 '14 at 15:35

You're mixing up MySQL and MySQLi mysqli_connect returns an instance for mysqli.

Your script should throw an error for trying to call a method on a non-object, because mysql_connect returns a resource.

You should enable error reporting at first (See: this SO question + answer)

The second thing is what @LTasty said: You use $sql, which is not defined at the point you want to execute the query.

When you changed these things, you should have a look at prepared statements, because your script is vulnerable against SQL injection.

score 0 · Answer 3 · answered Nov 12 '14 at 15:52

Thanks all. I would like to say that you all gave credit to the answer.

For people learning from my mistakes here is the code that now works.

I will only put up the php side code.

<?php 

$db_host = "localhost";
$db_username = "root";
$db_pass = "123";
$db_name = "emailtest";

$conn = new mysqli($db_host,$db_username,$db_pass,$db_name);

$dateTime = date("Y/m/d");

if ($conn->connect_error) 
{
die("Connection failed: " . $conn->connect_error);
} else {
        echo "Good connection ";
    }

if(!empty($_REQUEST['name']))
{
$name = $_REQUEST['name'];  
echo "hello, $name ";
}
if(!empty($_REQUEST['email']))
{ 
$email = $_REQUEST['email'];    
}
else
{
$email = NULL;
}

if($email)
{       
    $sql = "INSERT INTO Newsletter_signup (name, email, sign_up_date) VALUES('$name','$email','$dateTime')";
    if ($conn->query($sql) === TRUE) 
    {
        echo "Record updated successfully <br/>";
        echo "The email address, $email , has been added to the newsletter.";
    } else {
                echo "Error updating record: " . $conn->error;
            }

    $conn->close();
}
else
{
    echo 'Please go back and insert an email address.';     
}

?>

I havent included the error reporting that can be activiated via pHp into my script yet, but thanks for the link I will include it now.

php to MySQL not working and not sure why

3 Answers3