INSERT INTO not working for 2nd table

Question

I'm new to PHP but normally able to solve most problems but this one has got me.

I'm trying to create a newsletter sign up (single field) with a single submit button. I have this working fine, sending out an email and inserting the form data into my table. However I want to add functionality to have a confirmation email send to the person who signs up. I've done extensive research and I know the method behind this but my code just is not inputting data into my 2nd table used to store the confirmation information.

I have 2 tables: Table 1 named 'newsletter' columns are:

idmail,emailaddress,datetime,state

idmail is set to AUTO_INCREMENT

Table 2 named 'confirm' columns are:

idconfirm,emailaddress,confirmkey

Here is my code (I've omitted the email part which goes after this as that all work OK):

//connect to database
include('admin/connection.php');
$email = mysqli_real_escape_string($dbc, $_POST['email']);
//check if value exists in table
$result = mysqli_query($dbc, "SELECT emailaddress FROM newsletter WHERE emailaddress = '$email'");
if (mysqli_num_rows($result)==0) {

//Insert value into database
$query1 = mysqli_query($dbc, "INSERT INTO newsletter(emailaddress, datetime, state) VALUES('$email','$now','0')");
mysqli_query($dbc, $query1);

// Get ID of last record
$id = mysqli_insert_id($dbc); 

//Create a random key
$hash = $email.date('mY');
$hash = md5($hash);
//Insert value into database
$query2 = mysqli_query($dbc, "INSERT INTO confrim(idconfirm, emailaddress, confirmkey) VALUES('$id','$email','$hash')");
mysqli_query($dbc, $query2);

When I submit an email address, the first table is populated correctly.

The goal here is to get the auto ID created in the first INSERT INTO query into a variable then to add that in the 2nd tables column named 'idconfim'.

I tried:

echo $id;
echo $email;
echo $hash;

and all of the variables hold the correct information.

Does anyone have any ideas? I've tried to many things to list here, but I've researched this and I just don't know where I'm going wrong.

Thanks in advance.

Answer 1

One of the problems is that you aren't showing the MySQL error, if there is one. So you need to either check the server logs for the error in PHP, you can force to print the error to the error log or do something else:

for example:

mysqli_query($dbc, $query2) or error_log(mysqli_error($dbc));

mysqli_query($dbc, $query2) or custome_error_handler(mysqli_error($dbc));

As well php should be returning an HTTP error to the client. You should be catching that error.

Once you see the SQL error it will be easy to figure out what you did wrong.

EDIT Fred ii caught the real error, but I think the error would have been thrown the first time the mistake is made:

mysqli_query($dbc, $query1);

$query1 isn't a string. And if you noticed you already executed the query on the line above. Reading the PHP error logs will show you exactly where the error is.

Answer 2

I'm posting my comment as an answer here:

The problem here is this $query2 = mysqli_query($dbc,... '$hash')"); mysqli_query($dbc, $query2); and you should have gotten an error about that. This besides the possible typo for the table name confrim.

You need to remove mysqli_query($dbc, $query2); here and replace it with:

if($query2){ echo "Success"; }

else{ echo "Error: " . mysqli_error($dbc);

(Another edit): You did the same error here:

$query1 = mysqli_query($dbc, "INSERT INTO newsletter ... '$now','0')");
mysqli_query($dbc, $query1);

and needs to be changed to:

if($query1){ echo "Success"; }

else{ echo "Error: " . mysqli_error($dbc);

---

As stated in comments by RiggsFolly; don't use MD5 to hash passwords, it's no longer safe. A lot of water's run under the bridge in over 30 years.

Use password_hash() http://php.net/manual/en/function.password-hash.php and a prepared statement.

Edit: It looks to me now that after looking at your code again, that you're not trying to save a password, but more as a confirmation key. If that is the case, then you can disregard the password stuff. However, if you do decide to use MD5 to store passwords with in the future, don't.

Answer 3

Sorry for wasting time.

Thanks to jeffery_the_wind for pointing me to the logs. I will use them in future.

The problem was TWO spelling mistakes, one in the column name in the php and one on the mysql database. confrim is not a word! I'm slightly lexdixlick!

Thanks for your prompt responses.