Ad:

PHP Discussion Thread
Marked Solved
Views: 808

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Jul 18th, 2009

Problems with multiple queries

Expand Post »

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.

php Syntax (Toggle Plain Text)
$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 . "!"; 
}
$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.

Similar Threads

dispalying data in grid from multiple queries in VB.NET
group by queries in PHP in PHP
Unable to preform queries within one button in PHP
How many queries can be preformed in one click? in PHP
Help with speeding up a query with included sub-queries in MySQL

CFROG

Reputation Points: 21

Solved Threads: 31

Posting Pro in Training

Offline

405 posts
since Jul 2009

Permalink

Jul 18th, 2009

Re: Problems with multiple queries

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

ardav

Sponsor

Featured Poster

Reputation Points: 1048

Solved Threads: 948

Sarcastic Poster

Offline

6,697 posts
since Oct 2006

Permalink

Jul 18th, 2009

Re: Problems with multiple queries

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.

CFROG

Reputation Points: 21

Solved Threads: 31

Posting Pro in Training

Offline

405 posts
since Jul 2009

Permalink

Jul 18th, 2009

Re: Problems with multiple queries

How about:

PHP Syntax (Toggle Plain Text)

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

Although, I tend to do this:

PHP Syntax (Toggle Plain Text)

$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.

Last edited by ardav; Jul 18th, 2009 at 6:51 pm.

ardav

Sponsor

Featured Poster

Reputation Points: 1048

Solved Threads: 948

Sarcastic Poster

Offline

6,697 posts
since Oct 2006

Permalink

Jul 18th, 2009

Re: Problems with multiple queries

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?

CFROG

Reputation Points: 21

Solved Threads: 31

Posting Pro in Training

Offline

405 posts
since Jul 2009

Permalink

Jul 18th, 2009

Re: Problems with multiple queries

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:

PHP Syntax (Toggle Plain Text)
<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>
<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 Syntax (Toggle Plain Text)
<?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);
?>
<?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

CFROG

Reputation Points: 21

Solved Threads: 31

Posting Pro in Training

Offline

405 posts
since Jul 2009

Permalink

Jul 19th, 2009

Re: Problems with multiple queries

Holy Moley, you'll hate your self for this

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.

humbug

Reputation Points: 20

Solved Threads: 13

Junior Poster in Training

Offline

93 posts
since Oct 2005

Permalink

Jul 19th, 2009

Re: Problems with multiple queries

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?

CFROG

Reputation Points: 21

Solved Threads: 31

Posting Pro in Training

Offline

405 posts
since Jul 2009

Permalink

Jul 19th, 2009

Re: Problems with multiple queries

Look at Humbug's answer again and insert the mysql_query. Should work then. Good spot Humbug.

ardav

Sponsor

Featured Poster

Reputation Points: 1048

Solved Threads: 948

Sarcastic Poster

Offline

6,697 posts
since Oct 2006

Permalink

Jul 19th, 2009

Re: Problems with multiple queries

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!

CFROG

Reputation Points: 21

Solved Threads: 31

Posting Pro in Training

Offline

405 posts
since Jul 2009

Page 1
2
Next

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: upload file thru php

Next Thread in PHP Forum Timeline: how to edit the uploaded file in php???

DaniWeb Message

Cancel Changes

User Name
Password