Check a record exists

[nathj]

Hi,

I'm fairly new to MySQL and learning as I go, generally speaking so far so good.

At present I have a question to which I cannot find an answer, so I was hoping someone may be able to help me out.

For the site I am writing the user my apply to become a member (this is for a leadership forum) in doing so they have to supply a user name. I want to check the database to see if the user name is available and if it is great but if it is not then I need to tell the applicant.

I was hoping to do this without returning a bunch of data, for security reasons. I would like to be able to simply seek for the record based on the supplied user name and return true if it's there and false if it's not.

What is the syntax for this? I used to write Visual FoxPro databases and it was simply
llRecordThere = seek(username, tablename, indexname)

and that did the trick. Is there something similar in MySQL?

Many thanks
Nathan

[code green]

SELECT 1 FROM `tablename` WHERE `username` LIKE 'name'

[nathj]

SELECT 1 FROM `tablename` WHERE `username` LIKE 'name'

Ok I have played around with this, am I correct in thinking that this simply returns one column, and the number of rows indicates how many times the search expression was found?

This will really help me out. i better go see if there's someone out there I can help.

Many thanks
Nathan

[SkinHead]

Hey nathj

Try A Stored Procedure That Returns 0/1.

IF EXISTS (SELECT 1 FROM `tablename` WHERE `username` = 'name')
RETURN 1
ELSE
RETURN 0

I Don't See The Need To Use LIKE 'Username'. A Straight Equivalence Check Is More Appropriate

[nathj]

Hey nathj

I Don't See The Need To Use LIKE 'Username'. A Straight Equivalence Check Is More Appropriate

I have now used a straight equivalence check, it does make sense. What I have got now is:

SELECT COUNT(*) as test FROM table where condition
 

This means that in the PHP I can check to see if test is greater than 0, if it the value in question is already in use.

I was playing around with all this yesterday (changed to straight equivalence this morning) and all is now working fine.

I must admit I am intrigued by the idea of stored procedure, how would this interact with PHP and JavaScript? Them aim of this check is to verify if a username has already been selected as a user fills in the membership application form.

Cheers
nathj

[code green]

Are you looking for the most efficient query to check if a record exists in a table.Your function call hides a lot of overhead so may not be as efficient as you think.

llRecordThere = seek(username, tablename, indexname)

I am not sure if COUNT() is better than SELECT 1 because COUNT is a function call. I read somewhere that SELECT 1 was the fastest.

[nathj]

Are you looking for the most efficient query to check if a record exists in a table.Your function call hides a lot of overhead so may not be as efficient as you think.

llRecordThere = seek(username, tablename, indexname)

I am not sure if COUNT() is better than SELECT 1 because COUNT is a function call. I read somewhere that SELECT 1 was the fastest.

That seek line looks exactly like what I had in mind. How do I then submit that from my php page to the database?

Many thanks
Nathan

[code green]

That seek line looks exactly like what I had in mind.

That seek line is your seek line. I quoted it to explain there is a lot more code behind the scenes than seen in this one line.

How do I then submit that from my php page to the database

What you need to do is create a MySql query as a string, then submit the query using the php function mysql_query().
I suggest you learn the PHP MySql basics first.

[nathj]

That seek line is your seek line. I quoted it to explain there is a lot more code behind the scenes than seen in this one line. What you need to do is create a MySql query as a string, then submit the query using the php function mysql_query().
I suggest you learn the PHP MySql basics first.

I didn't realise you were quoting my desired result line in your post. That was not a UDF in the language I mentioned it was built in data manipulation that was fully optimized and a very effecient way to check if a record existed in a table without having to return a full data set. I was also not asking for a way to write a user defined function I was presenting it as an eample of what I was ultimatley after. I am sorry if that cased you confusion in helping me.

I have got code working just fine now to do exactly what I want using PHP and MySQL taking advantage of a data abstraction layer to fully modularise the code. Now my form will validate against format rule and database contents as the user completes it, in this way they can only proceed once each page of the form is valid.

Thanks for your help on this.
nathj