PHP MySQL help needed...

[AndyBlack]

Hi,

I'm new to php and am a bit stuck on the following. The first mysql query works fine, but the second query just returns 'Error updating database'.

So I am guessing it cannot execute the mysql command. I have used the same mysql statement on other scripts/pages and it works fine. I assume that I only have to connect to the database once.

<?php
$email=$_POST['email'];
$password=$_POST['password'];
$count = 0;

mysql_connect('localhost', 'myusername', 'mypassword') or die ('Error: '.mysql_error());
mysql_select_db('mydatabase');

# Check to see if email address already exists
$query = mysql_query("SELECT * FROM members WHERE email LIKE '$email'");

while ($row = mysql_fetch_array($query)) {
$count++;}
echo $count;

if ($count = 0) {echo "Sorry! The email address you entered already exists in our database."; break;} else {
$addemail="INSERT INTO members (ID, email, password) VALUES ('NULL', '".$email."', '".$password."')";

mysql_query($addemail) or die ('Error updating database');

echo '<meta http-equiv="refresh" content="4; url=index.php">';
echo "Your account has been created! Please wait while we redirect you.";}


?>

Any help would be greatly appreciated.

Thanks!

Andy

 

[glencooley.com]

Is the ID column and identity column? (does it add a new idea on each inser?) if so you dont need to insert null in to it.

What are the column datatypes and lenghts?

The Syntax is right for the SQL

 

[C.Pearse]

Try changing:

mysql_query($addemail) or die("Error updating the database.");

To:

mysql_query($addemail) or die(mysql_error());

Just so we can see a little more info...

Cheers
Chris

 

[awebapart.com]

$email=$_POST['email'];
...
$query = mysql_query("SELECT * FROM members WHERE email LIKE '$email'");

Using SQL code in this way will open your site and database up to SQL injection attacks, which could allow people to delete your database complete or access other people's personal information.

You need to protect against this by wrapping the substitution variables in functions using either addslashes or better still mysql_real_escape_string functions.

 

[Jeewhizz]

...using either addslashes or better still mysql_real_escape_string functions.

addslashes won't stop everything unfortunately Best to use mysql_real_escape_string.

Going back to the code, you can replace this:

while ($row = mysql_fetch_array($query)) {
$count++;}
echo $count; 

if ($count = 0) {echo "Sorry! The email address you entered already exists in our database."; break;} else {

with

if (mysql_num_rows($query)==0) {echo "Sorry! The email address you entered already exists in our database."; break;} else {

In your INSERT query, change

'NULL'

to

NULL

I.e. no quotes. (Or just leave that out, and the ID column reference)

 

[davidshaw89]

just wondering why you are using "LIKE" without any wildcard operators? Would it not be better to use "=" for e-mail addresses anyway?

 

[AndyBlack]

just wondering why you are using "LIKE" without any wildcard operators? Would it not be better to use "=" for e-mail addresses anyway?

I am just learning php so am just grasping the basics. I did use "=" before, but thought the error might have been down to that so changed it to 'LIKE'.

Changed it back now. Thanks.

Andy

 

[glencooley.com]

Use the like statement Andy.

If you use equals if someone types the same email address in mixed case = wont return that unless you convert both the email column and the email variable to lower or upper case in the comparrision

Jee is right about the sql injection.

I use a regex to strip all of the characters out that could be used for injection.

I dont have a php one but do have an ASP one but there is an article about it on sitepoint : ref http://www.sitepoint.com/article/regular-expressions-php

 

[AndyBlack]

Thanks, I changed the or die () error message. The problem was I didn't set the id to auto increment!! Doh!! Thought I did.

I will look into the earlier suggestions regarding making it more secure. May ask some questions on that later.

Thanks people!!

Andy

 

[noidea]

Is the password encrypted client side?