search database using php

[aran87]

hello people ive got a signup form which allows people to signup and login but to signup they enter there email address in which is then used as there login name, its all working but when the next user signs in they mite type the same email address in so i need the sql statement to search the database and check if that username already exists if it does display a message and then re enter new username, is it some sort of search statement??

[somedude3488]

this is really easy one.

use this:

//$email is the variable with the email address

$sql = "SELECT COUNT(*) FROM `table` WHERE `email` = '" . $email . "'";
$query = mysql_query($sql);
$res = mysql_fetch_row($query);
$num = $res[0];
if ($num > 0) {
//display error
}
else {
//process
}

[aran87]

hello ive put this code when the user signs up when they fill in the details then they click submit which should then display this username is already taken. The signup page is an html page so ive put the php code at the top of the page by using <?

<?
//connect to database
//select database ive got the connections by i aint put them on here
$result = mysql_query($query, $connection);

//is $email the textbox name?
$sql = "SELECT COUNT(*) FROM people WHERE `email` = '" . $email . "'";
$query = mysql_query($sql);
$res = mysql_fetch_row($query);
$num = $res[0];
if ($num > 0) {
//display error
}
else {
//process
}


// Close connection ! (please !)
mysql_close($connection);
?>

[aran87]

still not working

[somedude3488]

post your code and i will integrate my code for you. it would take to long to explain everything and find the problem.

[aran87]

post your code and i will integrate my code for you. it would take to long to explain everything and find the problem.

The code works but its just inserting the same username even if it exists S_POST comes from the previous page

<?php
database connection here

//$email is the variable with the email address

$sql = "SELECT COUNT(*) FROM members WHERE username = '$_POST[myusername]'";

$query = mysql_query($sql);
$result = mysql_fetch_row($query);
$num = $result[0];
if ($num > 0) {
echo "Already exists";
}
else {
$sql = sprintf(
  "INSERT INTO StudentRecords (StudentName, Email) ".
  "VALUES ('%s','%s')"
$result=mysql_query($sql) or die(mysql_error());

$sql= sprintf ("INSERT INTO members (password,username) VALUES ('%s','%s')",
  $result=mysql_query($sql) or die(mysql_error());
}

?>

[aran87]

no errors but dont check for some reason

[somedude3488]

you can make that column of the mysql table 'unique'. that will make sure the same username cannot be inserted twice. as for the code you posted, i need the form also so I can see the whole process so I can fix it.

[sukhy_1]

Hello if i use the mysql unquie column can i add a error in php saying username already taken?? im doing the same thing

[somedude3488]

no, it only prevents the same username being used by throwing an error.

here is an example of validating a username.

FORM:

<form action="index.php" method="post">
Username:<input type="text" name="user" /><br />
Password:<input type="password" name="pass" /><br />
<input type="submit" name="submit" value="Submit" />
</form>

PHP CODE:

<?php

if (isset($_POST['submit'])) {
  $user = $_POST['user'];
  $pass = $_POST['pass'];
  $sql = "SELECT COUNT(*) FROM `members` WHERE `username` = '" . $user . "'";
  $query = mysql_query($sql);
  $res = mysql_fetch_row($query);
  $num = $res[0];
  if ($num > 0) {
    echo 'Username already exists';
  }
  else {
    $sql = "INSERT INTO `table` (username,password) VALUES ('" . $user . "','" . $pass . "')";
    $query = mysql_query($sql);
  }
}

?>