Php database help

[Aronn00]

Hello! I need to make a sign up page with php and databases. so far i made the connection and the data insert into database:

<?php
$con = mysql_connect("localhost","******","******");
mysql_select_db("Inregistrare", $con);



$sql="INSERT INTO inregistrare (Nume, Prenume, Localitate, Varsta, Sex, Username, Parola)
VALUES
('$_POST[nume]','$_POST[prenume]','$_POST[localitate]','$_POST[varsta]','$_POST[sex]','$_POST[username]','$_POST[parola]')";
mysql_query($sql,$con);
echo "Felicitari! Ati fost inregistrat! Asteptati 5 secunde sau apasati <a href='conectare.html'>aici</a> pentru a va putea loga.";


mysql_close($con);

I know that it is not in english. Sorry for that. And the html page looks like this:

<form action="inregistrare.php" method="post">
<fieldset>
	<legend>Inregistrare</legend>
	<table>
		<tr>
			<td>Nume</td>
			<td><input type="text" name="nume"></td>
		</tr>
		<tr>
			<td>Prenume</td>
			<td><input type="text" name="prenume"></td>
		</tr>
		<tr>
			<td>Localitate</td>
			<td><input type="text" name="localitate"></td>
		</tr>
		<tr>
			<td>Varsta</td>
			<td><input type="text" name="varsta"></td>
		</tr>
		<tr>
			<td>Sex</td>
			
			<td><input type="text" name="sex"></td>
		</tr>
		<tr>
			<td>Nume utilizator</td>
			<td><input type="text" name="username"></td>
		</tr>
		<tr>
			<td>Parola</td>
			<td><input type="password" name="parola"></td>
		</tr>
		<tr>
			<td><input type="submit" name="inregistrare" value="Inregistrare"></td>
		</tr>
	</table>
</fieldset>
</form>

Now what i want to do is to check if an username is already taken and I don't know how to check if $_POST[username]== with any username from the database.
Please help and thanks!

[bpat1434]

First things first being that when you insert into the database, you should either use mysqli and it's prepared statement or use mysql_real_escape_string to help prevent SQL injection attacks.

To see if a username is taken you can do it one of two ways. You can either alter your database to have a unique key on the username column which will result in a failed query when you try to insert a duplicate username; or, you can do a quick SELECT query (similar to the INSERT query you already wrote) which just looks for "WHERE username = '{$username}'".

One example would be:

<?php
$con = mysql_connect("localhost","******","******");
mysql_select_db("Inregistrare", $con);

$isUniqueQuery = "SELECT Username FROM inregistrare WHERE username = '%s'";
$query = sprintf($isUniqueQuery, mysql_real_escape_string($_POST['Username']));
$result = mysql_query($query);
if (mysql_num_rows($result) > 0)
{
    echo "That username is already taken.";
}
else
{
    $insertQuery = "INSERT INTO inregistrare (Nume, Prenume, Localitate, Varsta, Sex, Username, Parola) " .
        "VALUES ('%s','%s','%s','%s','%s','%s','%s')";
    $query = sprintf(
        $insertQuery,
        mysql_real_escape_string($_POST[nume]),
        mysql_real_escape_string($_POST[prenume]),
        mysql_real_escape_string($_POST[localitate]),
        mysql_real_escape_string($_POST[varsta]),
        mysql_real_escape_string($_POST[sex]),
        mysql_real_escape_string($_POST[username]),
        mysql_real_escape_string($_POST[parola])
    );
    $result = mysql_query($query);
    
    if (mysql_affected_rows($con) == 1)
    {
        echo "Felicitari! Ati fost inregistrat! Asteptati 5 secunde sau apasati <a href='conectare.html'>aici</a> pentru a va putea loga."; 
    }
    else
    {
        echo "Unable to save registration.";
    }
}

Hope that helps.

[sneakyimp]

First things first being that when you insert into the database, you should either use mysqli and it's prepared statement or use mysql_real_escape_string to help prevent SQL injection attacks.

This is very good advice but perhaps the first thing he should probably do is stop using mysql_* and instead use mysqli_*. (see the link in my signature).

To see if a username is taken you can do it one of two ways. You can either alter your database to have a unique key on the username column which will result in a failed query when you try to insert a duplicate username; or, you can do a quick SELECT query (similar to the INSERT query you already wrote) which just looks for "WHERE username = '{$username}'".

Bpat, can you describe what would happen in PHP if he tried to insert a duplicate username? I expect he'd receive an error. I must admit I have never written any code that would recognize this situation and deal with it properly.

[NogDog]

If you try to do an insert with a duplicate value for a field with a unique constraint, the query will fail, and if you check the mysql_errno() function (or whatever the mysqli equivalent is), it will be a 1062 error.

What's nice about this approach is that it effectively eliminates having to worry about race conditions where 2 separate but virtually simultaneous request each check the DB for a duplicate, don't find one, and then whichever one does its insert 2nd gets an error anyway, while also cutting the number of queries in half.

[sneakyimp]

What's nice about this approach is that it effectively eliminates having to worry about race conditions where 2 separate but virtually simultaneous request each check the DB for a duplicate, don't find one, and then whichever one does its insert 2nd gets an error anyway, while also cutting the number of queries in half.

Ooooh, nice! Thanks for pointing that out.

[Aronn00]

Thanks.It was very usefull.