I'm working on a script that handles a member sign up. Upon submission of new member info the script queries the users table to check for duplicate user name (in this case an email) and return an error if duplicate is found. If not I want it to just submit the original new member info. The check for duplicates executes okay, but INSERT new info does not.

$sql = "SELECT * FROM users WHERE username = '{$email}' " ;
	
	$result = mysql_query($sql);
 
if ( mysql_num_rows ( $result ) > 0 )
{
       // Username already exists 
    echo 'That Email is already in use.' ;
	exit;
}

else
{

$sql = "INSERT INTO users (username, password, first_name, last_name) VALUE ('$email', '$pw', '$fname', '$lname')";
		
		echo "Thank you for becoming a member ". $fname . "!"; 
}

I thought that maybe assigning a different variable to the INSERT query would work but I had no luck. Also tried to free $results and that didn't work either.

Isn't it 'VALUES'? I must admit I very rarely use that syntax, I tend to use 'SET', so I may be wrong.

I use 'VALUE' all the time and it works, but for the sake of my own sanity I changed it to 'VALUES' to no avail. I used to use 'SET' as well until I ran into problems where a query would not work with 'SET' but did with 'VALUES'. Never thought it would make a diff.

How about:

$sql = "INSERT INTO users (username, password, first_name, last_name) VALUES ('{$email}', '{$pw}', '{$fname}', '{$lname}')";

Although, I tend to do this:

$sql = "INSERT INTO users SET username='{$email}', password='{$pw}', first_name='{$fname}', last_name='{$lname}'";

The way I do it ensures that I don't forget a value of put the values in the wrong order. I'm just not organized enough! However it's a bit more difficult to do batch inserts.

Unfortunately neither option worked. I'm new to php but I have handled multiple INSERT to multiple tables and the same with SELECT all in one shot. This is the first I've attempted to go from a SELECT right into an INSERT. Could this be the problem?

I must be overlooking something somewhere. I broke the script down to it's simplest form and put it on a page all by itself and it still won't write to the table.

Here is the form page:

<form action="write.php" type="submit" method="post">

                        <tr>
                        	<td align="left"></td>
                          	<td align="left">First Name:</td>
                          	<td align="left"><input name="first_name" type="text" /></td>
                          	<td align="left"></td>
                      	</tr>        
                        
                        <tr>
                        	<td align="left"></td>
                          	<td align="left">Last Name:</td>
                          	<td align="left"><input name="last_name" type="text" /></td>
                          	<td align="left"></td>
                      	</tr>   
                        
                        <tr>
                        	<td align="left"></td>
                          	<td align="left">Email:</td>
                          	<td align="left"><input name="email" type="text" /></td>
                          	<td align="left"></td>
                      	</tr>        
                        
                        <tr>
                        	<td align="left"></td>
                          	<td align="left">Password:</td>
                          	<td align="left"><input name="password" type="password" /></td>
                          	<td align="left"></td>
                      	</tr>     
                        
                        <tr>
                        	<td colspan="4"><img src="images/spacer_20px_high_white.jpg" /></td>
                        </tr>   
                        
                        <tr>
                        	<td colspan="4"><div align="center"><input name="create" type="submit" value="Create My Account !" /></div></td>
                        </tr>

And this is the 'write.php' the form submits to:

<?php 

        $fname = $_POST['first_name'];
	$fname = ucwords($fname);
	$lname = $_POST['last_name'];
	$lname = ucwords($lname);
	$email = $_POST['email'];
	$email = strtolower($email);
	$pw = $_POST['password'];

   // I omitted my connection info 

$sql = "INSERT INTO users SET

username = '{$email}',
password = '{$pw}',
first_name = '{$fname}',
last_name = '{$lname}' ";

		if (!$sql)
		{
		exit;
		}
		echo "Thank you for becoming a member ". $fname . "!"; 

// Close connection
mysql_close($dbcnx);
?>

And of far as I can see everything is fine with the table. I have no problems with anything else writing to other tables in the database.

My table is set up as follows:

table name: users

fields in order:

id > int, auto increment, primary key
username > varchar, 255
password > varchar, 255
first_name > varchar, 255
last_name > varchar, 255

Holy Moley, you'll hate your self for this :P You forgot a crucial line: mysql_query($sql); which should go on line 16. As your code stands, it doesn't execute the new SQL command, just sets the $sql variable.

Regarding the VALUE syntax, VALUE and VALUES are interchangeable.

I've changed the actual code a few times since what you've seen so if you could be more specific as to what line 16 is regarding what you see now?

Wow, you are totally right, I do hate myself for that one. How on earth I could spend so much time and still be overlooking the same darned thing is beyond me. Alas, the joys of being a newb! Thanks Humbug!

Don't worry CFROG, I still do it. You skim over your code and remember what you should be there instead of reading what is there. Another set of eye's can spot it in no time. :P

This question has already been answered. Start a new discussion instead.