SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2012
    Posts
    99
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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?

  2. #2
    SitePoint Wizard bronze trophy chris.upjohn's Avatar
    Join Date
    Apr 2010
    Location
    Melbourne, AU
    Posts
    2,189
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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'

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2012
    Posts
    99
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by chris.upjohn View Post
    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'
            }

    ?>

  4. #4
    Avid Logophile silver trophy
    ParkinT's Avatar
    Join Date
    May 2006
    Location
    Central Florida
    Posts
    2,329
    Mentioned
    191 Post(s)
    Tagged
    4 Thread(s)
    I think your mistake is simply:
    PHP Code:
            $s $dbconnect->prepare($sql); 
    should be
    PHP Code:
            $s $dbconnect->prepare($query); 
    Don't be yourself. Be someone a little nicer. -Mignon McLaughlin, journalist and author (1913-1983)


    Git is for EVERYONE
    Literally, the best app for readers.
    Make Your P@ssw0rd Secure
    Leveraging SubDomains

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    did you test your query outside of php first?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Apr 2012
    Posts
    99
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ParkinT View Post
    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

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Cmarenburg View Post
    ...still no luck
    did you test your query outside of php first?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Apr 2012
    Posts
    99
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes it executed fine in PMA. ( supplied the variables instead of :email etc)

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    How about echoing the actual error instead of a generic 'something went wrong' message? Just like you do with the select query.

  10. #10
    SitePoint Enthusiast
    Join Date
    Apr 2012
    Posts
    99
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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

  11. #11
    SitePoint Enthusiast
    Join Date
    Apr 2012
    Posts
    99
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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: (1064You have an error in your SQL syntaxcheck 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.'
             )'


  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Enthusiast
    Join Date
    Apr 2012
    Posts
    99
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yah i got it working!

    To make that clear i got the PDO version working


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •