What is wrong my mySQL query?

Question

I am trying to get my query to work and I can't seem to find what is wrong with it. It is for a from btw.

//Connection
$dbhandle = mysql_connect($hostname, $username, $password) or die("Unable to connect.");
$selected = mysql_select_db("fblaWebsite",$dbhandle) or die("Could not select the database");

//execute the SQL query 
        $insertQuery = "INSERT INTO Bookings (band, occasion, placeName, address, city, state, email, firstName, lastName, comments, ticketsForSale, phoneNumber, ticketPrice, date, time)
        VALUES ('$band', '$occasion', '$placeName', '$address', '$city', '$state', '$email', '$firstName', '$lastName', '$comments', '$ticketsForSale', '$phoneNumber', '$ticketPrice', '$date', '$time')";
        if(mysql_query($insertQuery)){
            echo "Form Successfully Submited!";
        }
        else{
            echo "Error Submiting Form!";
        }

I can't seem to find the error. I just keep getting "Error Submiting Form!"

Yeah PDO is really the way to go - (PHP Data Objects) - http://php.net/manual/en/book.pdo.php — dudewad, Nov 26 '13 at 21:52
As @JustinE says, PDO is better for these queries. But, to find your error, you could add: `die($insertQuery);` just before your `if` and let us know what is printed. — Oscar Pérez, Nov 26 '13 at 21:52
For troubleshooting, though you should output the mysql error: http://us2.php.net/mysql_errno and http://us2.php.net/manual/en/function.mysql-error.php I'd start there! — dudewad, Nov 26 '13 at 21:54
**By building SQL statements with outside variables, you are leaving yourself wide open to SQL injection attacks.** Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. http://bobby-tables.com/php has examples to get you started, and [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has many examples in detail. — Andy Lester, Nov 26 '13 at 21:58
Does nobody read the text in the big red box in the manual? http://au1.php.net/mysql_query — ta.speot.is, Nov 27 '13 at 00:27

score 2 · Answer 1 · answered Nov 26 '13 at 22:01

As others have recommended, PDO would be very helpful for this.

However, since your question isn't using PDO, and I don't see the values for your variables, I would recommend that you call mysql_error() in your else condition, and output it to the screen (in development only!) in order to see what the last error from MySQL was.

That will give you a starting point for solving your particular problem.

I also second the recommendation about making sure you sanitize your input values, and ensure that no harmful values are being substituted into your sql statement. If you aren't using it, may I recommend mysql_escape_string.

Keep in mind that vanilla mysql drivers are slated for deprecation in 5.5. Seriously consider upgrading to MySQLi or PDO to avoid your code breaking in PHP 5.5 environments!

Here is PHP's FAQ on the deprecation and changing to one of the new mechanisms: Why is the MySQL extension (ext/mysql) that I've been using for over 10 years discouraged from use? Is it deprecated? What do I use instead? How can I migrate?

All mysql_* functions are deprecated. He needs to consider updating to PDO or MySQLi Now. — Justin E, Nov 26 '13 at 22:03
Yes, they are deprecated in 5.5.0 (we are at 5.5.6 so yep, currently deprecated) I do think I pointed out the need to switch, and even linked the OP to the FAQ about it. Does the fact that I didn't only tell them how to fix it in PDO rate a downvote? I assumed perhaps they would like to fix it now, and slate migrating to PDO/MySQLi as part of their next round of improvements...so I pointed out how to get more info with their current environment and setup. — JC., Nov 26 '13 at 22:08

Justin E · Answer 2 · 2013-11-26T21:59:49.777

The below code should get you started with connecting to PDO, and executing a simple insert query as with your example.

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

try {
    //connect as appropriate as above
     $stmt = $db->prepare("INSERT INTO Bookings (band, occasion, placeName, address, city, state, email,     firstName, lastName, comments, ticketsForSale, phoneNumber, ticketPrice, date, time)     Values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?");
     $stmt->execute(array($band, $occasion, $placeName, $address, $city, $state, $email, $firstName, $lastName,     $comments, $ticketsForSale, $phoneNumber, $ticketPrice, $date, $time));
     $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch(PDOException $ex) {
    echo "An Error occured!"; //user friendly message
    //The line below will echo the error message from the query if there is one.
    //echo $ex->getMessage();
}

score 1 · Answer 3 · edited May 23 '17 at 11:49

You don't need pdo to get an answer. Just use something like this. Use it for testing and it'll give you an idea where the error is.

$result = mysql_query($query) or die("Bad query 1: " .mysql_error());

Now... watch how many down votes I get for saying you don't need PDO.

You can do prepared statements in procedural code too. It is a good idea to learn that method or you'll be forever going back protecting against sql injection a dozen different ways.

edit:

I forgot to say that you should move over to mysqli statements and away from the older depreciated mysql statements. Chances are the example you found was 10 years old. Learning php and sql is a voyage of discovery, it helps if you get off on the wrong foot. I'd take a look at this for some examples. http://us1.php.net/manual/en/mysqli.query.php

EDIT:

As Dennis pointed out, you should probably learn Mysqli Prepared Statements instead. It would be a LOT smarter at this juncture and you can still use Procedural code. Lots of examples here on Stackoverflow about how to do those. mysqli_stmt_fetch returns number

What is wrong my mySQL query?

3 Answers3