[SOLVED] Searching a mysql database for a key

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
XxLaSeRxX
Forum Newbie
Posts: 7
Joined: Sat Jun 19, 2004 8:24 pm

[SOLVED] Searching a mysql database for a key

Post by XxLaSeRxX »

Hi I need a little help with my sql code and php. I made script that adds a username to my mysql database and it checks for illegal characters which works. Now I want to also check the database to see if this username is already in the database. Heres what I have:

Code: Select all

<?php
$temp = "SELECT * FROM userdata WHERE MATCH (user) AGAINST ('$user'); ";
if (mysql_query($temp,$lk) > 0){
     //Code that redirects to previous page with an argument telling    
     //me this user name already exists
}
else
     echo mysql_error();
?>
Where userdata is my table name, user is the column, and $user is the username that Im passing from the previous page.

I think that my mysql syntax is correct but I dont know what to set in my if statement to know if the user is in there or not. Ive tried echoing the query but all I get is Resource id#3. If Im going at this the wrong way please tell me. I just want to search a database for a username and Ive been working on this for 3 days now, and nothing works. Any help would be appreciated.

Thanx in advance.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

<?php

$query = mysql_query("SELECT * FROM `userdata` WHERE `user` = '$user' LIMIT 1") or die(mysql_error());

if(mysql_num_rows($query))
die("$user already taken");

// perform insert

?>
that code will only check for exactly duplicate names. So if "Foo" is a user, and "foo" is this new user, it'll be allowed to insert. If you wish to disallow case, use the LIKE keyword instead of =.. You'll have to sanitize/escape mysql wildcard characters in $user.

alternately, if the `user` field is an index, I believe trying to insert will fail if already taken..
Last edited by feyd on Sat Jun 19, 2004 8:32 pm, edited 1 time in total.
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post by tim »

mysql_num_rows is the command you are looking for.

=]
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post by tim »

lol, beat again by feyd, however I will provide an alternate solution.

Code: Select all

<?php
$sql = "SELECT * FROM users_table WHERE username='$name'";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$user = $row['username'];
 if (!$user == "") {
echo "The username you have choosen has already been taken.";
}
?>
XxLaSeRxX
Forum Newbie
Posts: 7
Joined: Sat Jun 19, 2004 8:24 pm

Post by XxLaSeRxX »

Thank you so much feyd! and tim I got an error from your code. Said that mysql_fetch_array wasnt correct somehow..

But thanx again guys.
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post by tim »

lol well u would have to add in your exact information so it can query to the correct table, but mysql_num_rows is perfect with exception to the cases (upper n lower)
Post Reply