IF's with mySQL?

PicoDeath

I'm kind of new to this and i'm trying to create some simple registration system so i can grasp forms with mysql and understand some of the functions/commands used.

Heres my problem
I want to check the Database for the username entered into the form and display a error message if the username already exists otherwise add it into the DB, doesnt seem to work and just adds the user, pass and email anyway.

So here's my scripts.

register.php

<form action="confirm_register.php" method="POST">
<tr><td>Username:</td><td><input type="text" name="Username"><br></td></tr>
<tr><td>Password:</td><td><input type="password" name="Password"><br></td></tr>
<tr><td>Email:</td><td><input type="text" name="Email"><br></td></tr>
<tr><td></td><td><input type="Submit" value="Register"> <input type="reset" value="Clear"></td></tr>
</form>

confirm_register.php

<?php
mysql_connect("localhost", "picodeath", "password") or die(mysql_error());
mysql_select_db("register") or die(mysql_error());
$user=$_POST['Username'];
$password=$_POST['Password'];
$email=$_POST['Email'];


$result = mysql_query('SELECT Username FROM registration');
if (!$result==$user) {echo ("Sorry, $user is already a registered member.");}
else { $query = mysql_query("INSERT INTO registration VALUES('', '$user', '$password', '$email')");
echo ("Thank you for registering, you can now login.");}
?>

Like i said, i'm just learning. So any insight on how to fix this then i would seriously appriciate it.

TobyRT

my first thought is in your (!$result==user) line

Obviously you're saying if $result is not the same as $user, there is a problem. the correct syntax for this is:

if ($result !== $user) {do stuff}

I think you may also need to add some more security and error checking in this script too...I'll look at that now, but in the meantime, that's my instant response.

TobyRT

OK - in more detail 🙂

Your form looks fine!

Your php is ok, but not very secure.

$password = $_POST['password'];

It would be worth encoding this using an md5 hash so the password is not stored 'as is' in the db. Try this:

$password = md5($_POST['password']);

Your next problem is you're not really validating the username. Your current query basically says "select all the usernames from the database", but you don't really check anything.

In logic, you need to run a comparison: Select a usernname from the registration table where the username (in the table) is the same as the username that the person wants. If there is a result, then obviously the username is taken, so you can't use it, otherwise you can add it. So. In code:

//perform the query
$result = mysql_query('SELECT * FROM registration WHERE username LIKE "$user"') or die ("Error in query " mysql_error()); // add the 'or die' for error checking purposes

//if there is a result, the name must exist, so you can't do it, otherwise add it
if (mysql_num_rows($result) >0)
{
echo 'Sorry, '.$user.'is already a registered username. Please choose another';
}
else
{$query = mysql_query("INSERT INTO registration VALUES('', '$user', '$password', '$email')") or die ("Error in query " mysql_error()); // add the 'or die' for error checking purposes;
}

//Now confirm that the query happened, and if it did, say so:
if (!$query)
{
echo 'oops - it didn\'t work for some reason';
}
else
{
echo 'Thank you for registering, you may now log in';
}

Hope this makes sense!

TF_TheMoon

You'll need to change one part of TobyRTs code. You need to swap the " and the '

you rather than

$result = mysql_query('SELECT * FROM registration WHERE username LIKE "$user"')

It needs to be
$result = mysql_query("SELECT * FROM registration WHERE username LIKE '$user'; ")

Or else PHP will keep $user as the string $user and not convert it to its value

TobyRT

I thought that at the time actually, I usually do it your way, i was just working in pico's style.

I've actually found that it works both ways round some of the time and others it doesn't. weird.

laserlight

hmm... why use LIKE here?

$result = mysql_query("SELECT COUNT(username) FROM registration WHERE username='$user'");

Now, instead of using mysql_num_rows(), use mysql_result() to check what COUNT(username) returned.

PicoDeath

Yeah about the security part, i was just getting used to how everythings transfered and displayed first then i was going to fix the security end.

I also got help from another site, they suggested this and it works.

$result = mysql_query("SELECT Username FROM registration WHERE Username = '{$user}' LIMIT 1");
if (mysql_num_rows($result)==1) {echo ("Sorry, $user is already a registered member.");}

Anyway i book marked this place because you all seem to know alot and i will need help again.

Thanks alot.