PhP & mysql insert help

PHP
1

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.



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

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

Even After Adding the colons the data is not inserted.

Here’s the full script (minus the salt)


<?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

I think your mistake is simply:


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

should be


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

did you test your query outside of php first?

6

Yah updated that still no luck

7

did you test your query outside of php first?

8

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

9

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

10

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

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


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


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

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)

13

Yah i got it working!

To make that clear i got the PDO version working