DaniWeb Forum Index > Web Development > PHP

search database using php

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Mar 2008

Posts: 42

Reputation: aran87 is an unknown quantity at this point

Solved Threads: 0

aran87

Offline

Light Poster

search database using php

Apr 12th, 2008

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??

•

Join Date: Jun 2007

Posts: 1,227

Reputation: kkeith29 has a spectacular aura about

Solved Threads: 167

kkeith29

Offline

Nearly a Posting Virtuoso

Re: search database using php

Apr 13th, 2008

this is really easy one.

use this:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
//$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
}
//$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
}

•

Join Date: Mar 2008

Posts: 42

Reputation: aran87 is an unknown quantity at this point

Solved Threads: 0

aran87

Offline

Light Poster

Re: search database using php

Apr 13th, 2008

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);
?>
[/code]

•

Join Date: Mar 2008

Posts: 42

Reputation: aran87 is an unknown quantity at this point

Solved Threads: 0

aran87

Offline

Light Poster

Re: search database using php

Apr 13th, 2008

still not working

•

Join Date: Jun 2007

Posts: 1,227

Reputation: kkeith29 has a spectacular aura about

Solved Threads: 167

kkeith29

Offline

Nearly a Posting Virtuoso

Re: search database using php

Apr 13th, 2008

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

•

Join Date: Mar 2008

Posts: 42

Reputation: aran87 is an unknown quantity at this point

Solved Threads: 0

aran87

Offline

Light Poster

Re: search database using php

Apr 14th, 2008

•

Originally Posted by kkeith29

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

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
 
<?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());
}
 
?>
<?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());
}

?>

•

Join Date: Mar 2008

Posts: 42

Reputation: aran87 is an unknown quantity at this point

Solved Threads: 0

aran87

Offline

Light Poster

Re: search database using php

Apr 14th, 2008

no errors but dont check for some reason

•

Join Date: Jun 2007

Posts: 1,227

Reputation: kkeith29 has a spectacular aura about

Solved Threads: 167

kkeith29

Offline

Nearly a Posting Virtuoso

Re: search database using php

Apr 14th, 2008

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.

Last edited by kkeith29; Apr 14th, 2008 at 7:22 pm.

•

Join Date: Mar 2008

Posts: 63

Reputation: sukhy_1 is an unknown quantity at this point

Solved Threads: 3

sukhy_1

Offline

Junior Poster in Training

Re: search database using php

Apr 14th, 2008

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

Last edited by sukhy_1; Apr 14th, 2008 at 7:23 pm.

•

Join Date: Jun 2007

Posts: 1,227

Reputation: kkeith29 has a spectacular aura about

Solved Threads: 167

kkeith29

Offline

Nearly a Posting Virtuoso

Re: search database using php

#10

Apr 14th, 2008

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

here is an example of validating a username.

FORM:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
<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>
<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:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
<?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);
  }
}
 
?>
<?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);
  }
}

?>