Frustrating = Sign Insert

[justlukeyou]

Hi,

I am trying to put together the insert code for a form however what ever I try the = sign here password = MD5 creates an error.

I have tried around 10 variations such as placing the line in brackets but whatever I try it creates an error. When I remove this code it enters the firstname and surname but I cant get the password part to enter. Any suggestions please?

            $query = mysql_query("INSERT INTO users SET email
            (firstname, surname) VALUES ('".$firstname."', '".$surname."')");
            "' . mysql_real_escape_string($registerEmail) . '";
            password = MD5("' . mysql_real_escape_string($registerPassword) . '");  
            date_registered = "' . date('Y-m-d H:i:s') . '"';  
              
            if(mysql_query($query)){  
                $success['register'] = 'Thank you for registering. You can now log in on the left.';  
            }else{  
                $errors['register'] = 'There was a problem registering you. Please check your details and try again.';  
                
                     }  
        }  
              
            }

[Cups]

You seem to me to be mixing your INSERT syntax with UPDATE syntax;

INSERT

INSERT INTO table_name
VALUES (value1, value2, value3,...)
 
// OR
 
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

UPDATE

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

Use INSERT or UPDATE but not a melange of the two.

[justlukeyou]

Im a bit confused, Im not updating am I?

The code without the firstname and surname worked fine but now I am trying to add this function. However now the code for inserting the password and date registered no longer works.

[justiworld002]

try this
$password = md5($registerPassword);
$query = mysql_query("INSERT INTO users SET email = '".mysql_real_escape_string($registerEmail)."',firstname = '".$firstname."', surname='".$surname."' .
password ='".$password ."',date_registered = "' . date('Y-m-d H:i:s') . '"';

<snip>

[justlukeyou]

Many thanks, I tried this. There is no closure on the first bracket and whatever I try just creates an error.

The follow attempt creates this error: T_CONSTANT_ENCAPSED_STRING

    $password = md5($registerPassword);
 $query = mysql_query("INSERT INTO users SET email = '".mysql_real_escape_string($registerEmail)."',firstname = '".$firstname."', surname='".$surname."' . password ='".$password ."',date_registered = "' . date('Y-m-d H:i:s') . '"); 

[Cups]

I think you have compounded problems here, and that is quite common when having PHP compute values to fit inside another syntax (Mysql/SQL).

You have to develop a means of eliminating these problems logically.

One simple strategy is to hard-code some test values and then, one by one, replace them with the computed ones.

I am guessing from your comments you are inserting a new row, and as it is not clear what the exact table schema is I will use the named columns method as an example which ought to work in order for you to see something being added to your table.

$query = mysql_query("
INSERT INTO users (
  email
, firstname
, surname
, password
, date_registered ) 
VALUES (
'emai@address.com'
, 'John'
, 'Doe'
, 'password123'
, '2012-09-22 00:00:00' )"; 

Format your sql statements like that and you will find it far easier for you (and others) to check you have the correct number of values and in future when scanning your code the SQL statements will jump right out at you.

IF that works, then as I say, one by one replace them with computed values.

If this system of incremental development works for you then try to remember to adopt it when doing similar tasks in future.

[justlukeyou]

Hi,

Im trying to create a login system. The frustrating thing is that I had them working seperately. Now I am trying to put firstname, surname together with registeremail, password and date.

With the example above it doesn't include all the security measures. Is that what is creating the errors?

[Cups]

With the example above it doesn't include all the security measures. Is that what is creating the errors?

Well, if you are answering my questions with a confirmation that using hard coded values does work then it means several things have happened.

a. The INSERT code I posted does work
b. You were using a mixture of INSERT and UPDATE syntaxes as I pointed out in post #2
c. You do not read my replies at all or
d. You did not understand what I said fully but did not bother to ask for confirmation

IF that works, then as I say, one by one replace them with computed values.

So what I mean is take the $registerEmail variable and insert just that one in, in place of the hard coded value.

$query = mysql_query("
INSERT INTO users (
  email
, firstname
, surname
, password
, date_registered ) 
VALUES (
'". mysql_real_escape_string($registerEmail) ."'
, 'John2'
, 'Doe2'
, 'password12345678'
, '2012-09-22 00:00:00' )"; 

Now go to you db and check, did it work?

Yes?

Move onto the next variable

No?

Go back to the registerEmail variable and work out where you went wrong.

Rinse.

Repeat.

Incrementally check your variables are hooked up one by one.

[justlukeyou]

"You were using a mixture of INSERT and UPDATE syntaxes as I pointed out in post #2"

I dont know what you mean by this.

I have had the code working for firstname and surname insert. And I have had it working for registerEmail, registerpassword and date insert. But I cant get it to work when I try to insert both.

If you had to insert plain text, an MD5 password, date and an email what code would you use? I'm new to this.

  $query = 'INSERT INTO users = ("(firstname, surname) VALUES ('".$firstname."', '".$surname."')");
                        "' . mysql_real_escape_string($registerEmail) . '";
                        password = MD5("' . mysql_real_escape_string($registerPassword) . '");  
                        date_registered = "' . date('Y-m-d H:i:s') . '"';

[justlukeyou]

This is the error part, I just need to sort this out.

 password = MD5("' . mysql_real_escape_string($registerPassword) . '");  
        date_registered = "' . date('Y-m-d H:i:s') . '"';

[John_Betong]

This is the error part, I just need to sort this out.

 password = MD5("' . mysql_real_escape_string($registerPassword) . '");  
        date_registered = "' . date('Y-m-d H:i:s') . '"';

Further to @Cups post this is the way I simplify SQL statements (which have first been tried and tested using "http://localhost/phpmyadmin/" )

An SQL statement is always a string and can be echoed first to ensure the string is valid.

Source:

if(1) // DEBUG:  replace 1 with 0 to try with real values
{
  $registerPassword ='123456';        
  $registerEmail    ='joe-bloggs@email.com';
  $firstname        ='Josephine';
  $surname          ='Bloggs';
  $password         ='Q-123456';
  $password         = md5($registerPassword); 
}

echo '<pre>';  // DEBUG: retains line-feeds 

  echo $sql =
  "
    INSERT INTO users
    (
        email
      , firstname
      , surname
      , password
      , date_registered
    ) 
    VALUES
    (
      '". mysql_real_escape_string($registerEmail) ."'
      , '" . $firstname ."'
      , '" . $surname ."'
      , '" . $password ."'
      , '" . date('Y-m-d H:i:s') ."'
    )
  "; 
echo '</pre>';  // DEBUG: retains line-feeds 

// $query = mysql_query($sql); 

<hr />

Output:

INSERT INTO users
(
email
, firstname
, surname
, password
, date_registered
)
VALUES
(
'joe-bloggs@email.com'
, 'Josephine'
, 'Bloggs'
, 'e10adc3949ba59abbe56e057f20f883e'
, '2012-09-24 05:55:44'
)

Once the SQL is doing what I want it to do then all echo statements are REMMED.

To save a fraction of a millisecond the line-feeds could removed.