PhP & mysql insert help.

I have been fighting with this for half an hour now and can not figure it out. i have used SET instead of values and anything else i can think of. Here's the sql Code.

PHP Code:

---


try 
        {
            $queryCheck = 'SELECT * FROM users WHERE email=:email';
            $s = $dbconnect->prepare($queryCheck);
            $s->bindValue(':email', $email);
            $emailAllreadyInUse = $s->rowCount();
        }
        
        catch(PDOException $e) 
        {
            $error = 'Error adding your account: ' . $e->getMessage();
            exit();
        }
        
        if ($emailAllreadyInUse > 0)
            {
                echo 'That email is allready in use. Please Use a different email';
            }
        else {

    try
    {
        $query = 'INSERT INTO users ( 
               `email`, 
               `password`, 
               `nameFirst`, 
               `nameLast`, 
               `farmName`, 
               `joinDate`, 
              `groupNum` 
        ) VALUES ( 
               :email, 
               :password, 
               :nameFirst, 
               :nameLast, 
               :farmName, 
              :date, 
              :groupNum 
         )'; 
        
        $s = $dbconnect->prepare($sql);
        
        // Bind the Values

        $values = array('email' => $email, 'password' => $password, 'nameFirst' => $nameFirst, 'nameLast' => $nameLast, 'farmName' => $farmName, 'date' => $date, 'groupNum' => $groupNum);
        
        $s->execute($values);
    }

catch(PDOException $e)
    {
        echo '<p class=\"DB_Error\">There was an error adding your account please try again latter</p>';
    }
    echo '<p class=\"username\">You can now log in with the email '.$email.' and your chosen password'; 
        } 


---

The only thing i think it may be is the checking to see if the email already exists. Not sure. I am sure some one well be able to pick what ever is. Unless GroupNum is causing the issue with mysql?

The only thing i can see wrong is that all your array index names are missing the colon at the beginning.

Eg. 'email' should be ':email'

Quote:

---

Originally Posted by chris.upjohn

The only thing i can see wrong is that all your array index names are missing the colon at the beginning.

Eg. 'email' should be ':email'

---

Even After Adding the colons the data is not inserted.

Here's the full script (minus the salt)

PHP Code:

---

<?php

require_once('../../lib/connect.php');
    
        if(strlen(trim($_POST['email'] == 0)))
            {
                $emptyEmail = TRUE;
            }
        if(strlen(trim($_POST['nameFirst'] == 0)))
            {
                $emptyNameFirst = TRUE;
            }
        if(strlen(trim($_POST['nameLast'] == 0)))
            {
                $emtpyNameLast = TRUE;
            }
        if(strlen(trim($_POST['farmName'] == 0)))
            {
                $emptyFarmName = TRUE;
            }
        

        $email = strip_tags(trim($_POST['email']));
        $nameFirst = htmlspecialchars(strip_tags(stripslashes($_POST['nameFirst'])));
        $nameLast = htmlspecialchars(strip_tags(stripslashes($_POST['nameLast'])));
        $farmName = htmlspecialchars(stripslashes(strip_tags($_POST['farmName'])));
        $group = '2';
        $date = date("d/m/y");


        $passWdRaw = strtolower(stripslashes(htmlspecialchars(trim($_POST['password']))));
        $passwd = hash('sha512',$passWdchar);
        $salt['0'] = '';
        $salt['1'] = '';
        $password = $salt['1']."".$passwd."".$salt['0'];
        
        try 
        {
            $queryCheck = 'SELECT * FROM users WHERE email=:email';
            $s = $dbconnect->prepare($queryCheck);
            $s->bindValue(':email', $email);
            $emailAllreadyInUse = $s->rowCount();
            $s->execute();
        }
        
        catch(PDOException $e) 
        {
            $error = 'Error adding your account: ' . $e->getMessage();
            exit();
        }
        
        if ($emailAllreadyInUse > 0)
            {
                echo 'That email is allready in use. Please Use a different email';
            }
        else {

    try
    {
        $query = 'INSERT INTO users ( 
               `email`, 
               `password`, 
               `nameFirst`, 
               `nameLast`, 
               `farmName`, 
               `joinDate`, 
              `groupNum` 
        ) VALUES ( 
               :email, 
               :password, 
               :nameFirst, 
               :nameLast, 
               :farmName, 
              :date, 
              :groupNum 
         )'; 
        
        $s = $dbconnect->prepare($sql);
        
        // Bind the Values

        $values = array(':email' => $email, ':password' => $password, ':nameFirst' => $nameFirst, ':nameLast' => $nameLast, ':farmName' => $farmName, ':date' => $date, ':groupNum' => $groupNum);
        
        $s->execute($values);
    }

catch(PDOException $e)
    {
        echo '<p class=\"DB_Error\">There was an error adding your account please try again latter</p>';
    }
    echo '<p class=\"username\">You can now log in with the email '.$email.' and your chosen password'; 
        }

?>

---

I think your mistake is simply:

PHP Code:

---

        $s = $dbconnect->prepare($sql); 


---

should be

PHP Code:

---

        $s = $dbconnect->prepare($query); 


---

did you test your query outside of php first?

Quote:

---

Originally Posted by ParkinT

I think your mistake is simply:

PHP Code:

---

        $s = $dbconnect->prepare($sql); 


---

should be

PHP Code:

---

        $s = $dbconnect->prepare($query); 


---

---

Yah updated that still no luck

Quote:

---

Originally Posted by Cmarenburg

...still no luck

---

did you test your query outside of php first?

Yes it executed fine in PMA. ( supplied the variables instead of :email etc)

How about echoing the actual error instead of a generic 'something went wrong' message? Just like you do with the select query.

Quote:

---

Originally Posted by guido2004

How about echoing the actual error instead of a generic 'something went wrong' message? Just like you do with the select query.

---

Well a there is no error given. So even if the error echo code was there it would be of no help. As PHP is displaying the code that makes it seem like everything went trhough

Ok so i ran it using mysql in php and it gave me this error However in PDO i did not receive any error

here's the error

PHP Code:

---

Error: (1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@gmail.com, a456ad5fa2f1d5g6f45h4sde3b0c44298fc1c149afbf4c8996fb92427ae4' at line 10 


---

and here's the (gimped up pdo is so much better then mysql commands) version of the query

PHP Code:

---

$query = 'INSERT INTO users ( 
               `email`, 
               `password`, 
               `nameFirst`, 
               `nameLast`, 
               `farmName`, 
               `joinDate`, 
              `groupNum` 
        ) VALUES ( 
               '.$email.', 
               '.$password.', 
               '.$nameFirst.', 
               '.$nameLast.', 
               '.$farmName.', 
              '.$date.', 
              '.$group.'
         )'; 


---

you need quotes around string values

standard sql calls for single quotes, but mysql allows the use of doublequotes to delimit strings (which otherwise would be used to delimit identifiers such as column names)

Yah i got it working!

To make that clear i got the PDO version working