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 [man]mysql_real_escape_string[/man] 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

bpat1434;11022635 wrote:
First things first being that when you insert into the database, you should either use mysqli and it's prepared statement or use [man]mysql_real_escape_string[/man] 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).

bpat1434;11022635 wrote:
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

NogDog;11022673 wrote:
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.