-1

I am trying to access MySQL via PHP but I get these error messages.

Notice: Undefined variable: stmt in /var/www/contact.php on line 60

Fatal error: Call to a member function bind_param() on a non-object in /var/www/contact.php on line 63

My code:

<?php

$servername = "localhost";
$username = "test";
$password = "test";
$dbname = "test";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// LINE 60
if(!$stmt = $conn->prepare("INSERT INTO MyGuests VALUES(?, ?, ?)") || !is_object($stmt)){
    die( "Error preparing: (" .$conn->errno . ") " . $conn->error);
}
//LINE 63
$stmt->bind_param("sss", $firstname, $lastname, $email) ;
Community
  • 1
  • 1
Pntt
  • 109
  • 1
  • 9
  • The error messages will lend a hand at figuring out what your problem is, they can be resourceful. What have you tried given your error messages? – Anthony Forloney Oct 25 '14 at 21:20
  • W3schools is not the best place to learn from. What you've done was use a mix of both methods in trying to make things work, while putting quotes around your column names, which by the way, isn't shown in their tutorial `INSERT INTO MyGuests (firstname, lastname, email)`. Always refer to the official manuals when it comes to learning. **http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php** – Funk Forty Niner Oct 25 '14 at 23:04
  • 1
    Whenever you find yourself reaching for the phrase "doesn't work" (and especially if wish to use it four times), please stop and choose something more descriptive. Here on Stack Overflow we often say it is the least helpful fault report possible. Instead, say what you expected, what you got, and what you have thus far done to investigate. – halfer Oct 26 '14 at 12:09
  • There's no need, incidentally, to update questions with solutions. If you would like to share your solution, feel free to make a self-answer containing it - this ensures questions and answers are separated out on the page. – halfer Oct 26 '14 at 12:11

3 Answers3

2

Simplify your error detection. Wrap the assignment statement in parentheses. Forget is_object(). Try this.

if( !( $stmt = $conn->prepare( "INSERT INTO MyGuests VALUES(?, ?, ?) " ) ) {
    die( "Error preparing: (" .$conn->errno . ") " . $conn->error);
}

It's usually best in an INSERT statement to spell out the column names, like so

INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)

If the table's columns aren't exactly what you thought, your INSERT can't work correctly without enumerating the columns. This happens to be the case with the example on w3schools. They define the MyGuests table with some more columns than you use in your INSERT.

Do error detection on bind_param, in the same manner as on prepare.

Define the variables you use in bind_param() before using them. Try this:

$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";

if (!$stmt->bind_param("sss", $firstname, $lastname, $email)) {
    die( "Error in bind_param: (" .$conn->errno . ") " . $conn->error);
}
$stmt->execute();

w3schools? Really? Friends don't let friends .... etc.

O. Jones
  • 92,698
  • 17
  • 108
  • 152
  • I did like you asked and got this: Error preparing: (1136) Column count doesn't match value count at row 1. Also is there something wrong with w3schools? What should I use instead. I found it just on google. – Pntt Oct 25 '14 at 21:31
  • The column count indeed doesn't match in the w3schools example. Please see my edited answer. It's a fairly common experience that w3schools advice is sometimes just a little "off." If you do use it, double check your work against the appropriate pages in the php manual. That manual has good examples for most things. – O. Jones Oct 25 '14 at 23:04
0

The first error

Notice: Undefined variable: stmt in /var/www/contact.php on line 60 You have to declare variables before using it. Like those variables:

$servername = "localhost";

$username = "test";

$password = "test";

$dbname = "test";

define stmt variable

$stmt = " ";

or remove it from the if statement

$stmt = $conn->prepare("INSERT INTO MyGuests VALUES(?, ?, ?)")

This will solve your errors

Mohammad
  • 668
  • 2
  • 10
  • 33
  • Solves the first error, not the second. or then I get this: Error preparing: (1136) Column count doesn't match value count at row 1 – Pntt Oct 25 '14 at 21:41
0

The object is the $conn, so if you want to include it in the if statement you can but not necessary. Am assuming that the MyGuests table as an id column and lastname, fisrtname, email column. You need to specify the actual column in the SQL, "INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)"

The variable $stmt is not defined yet. So you cant say !$stmt, the function not won't work. Do something like:

$stmt = "";
if(!($stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)"))){
    die( "Error preparing: (" .$conn->errno . ") " . $conn->error);
}

$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";

if(!($stmt->bind_param("sss", $firstname, $lastname, $email))){
    die( "Error in bind_param: (" .$conn->errno . ") " . $conn->error);
}


$stmt->execute();
Edward Manda
  • 544
  • 3
  • 6
  • Error preparing: (1136) Column count doesn't match value count at row 1, what next? Or then I don't get errors at all if I edit it a bit but still not working. – Pntt Oct 25 '14 at 21:37
  • Specify the actual columns so that the count matched – Edward Manda Oct 25 '14 at 21:46
  • Hmm... I got the error message away with five question marks. Weird because I did everything as the tutorial said. I report soon what happens. – Pntt Oct 25 '14 at 21:48
  • Because you have five columns in the database. You need to specify the actual columns like i showed you. "INSERT INTO MyGuests ('firstname', 'lastname', 'email') VALUES(?, ?, ?)" – Edward Manda Oct 25 '14 at 21:50
  • Okay so I did that. Check the update. I think it is very near to the right solution but for some reason I stopped seeing all error messages (but it still doesn't work). I got to update the MySql table when I prepared and bound 5 values into the table but that pisses over the id and reg_date fields. – Pntt Oct 25 '14 at 22:03
  • How many columns do you have in the database and what are there names – Edward Manda Oct 25 '14 at 22:06
  • id, firstname, lastname, email, reg_date. So 5 in total – Pntt Oct 25 '14 at 22:08
  • So it also needs red_date. Am assuming that u have set the definition to not null for definition or default now – Edward Manda Oct 25 '14 at 22:11
  • Also you I'd column must set to auto increase and as a primary key – Edward Manda Oct 25 '14 at 22:12
  • I'm sorry no I haven't. How do I do these changes? Like I said. I'm complete beginner on this :/ – Pntt Oct 25 '14 at 22:19
  • Also the command INSERT INTO MyGuests ("firstname") VALUES ("testname"); isn't working. It claims it isn't correct Syntax even it really should be? – Pntt Oct 25 '14 at 22:25
  • Just add it to the insert value. Check the update – Edward Manda Oct 25 '14 at 22:25
  • Look over your code. Make sure it has the right number of question marks. Your code is now correct – Edward Manda Oct 25 '14 at 22:27
  • It Finally works. I had INSERT INTO("firstname", "lastname", "email") when it really should be INSERT INTO(firstname, lastname, email) Thanks a lot for everything :) – Pntt Oct 25 '14 at 22:29
  • Go into the database and check the column definitions out also – Edward Manda Oct 25 '14 at 22:29
  • Ok I also fixed the answer to reflect your result – Edward Manda Oct 25 '14 at 22:33
  • 4x `s` and 3 variables `"ssss", $firstname, $lastname, $email` which should read as `"sss", $firstname, $lastname, $email` - hence the error `Error preparing: (1136) Column count doesn't match value count at row 1` – Funk Forty Niner Oct 25 '14 at 23:07
  • I added the date but removed it so forgot to remove those s and ? – Edward Manda Oct 25 '14 at 23:09