mysql help

I wrote some script that when a prim is clicked, the avatar's name is entered into a mySQL database. This works fine.

However, I want to prevent the same username from being entered more than once. I guess the script needs to check the database. If their username isnt present, it lets them continue with the script. If it is already there, the script rejects them... keeping them from running the script more than once.

So, how do I search a mysql table in second and deny any repeat users?

Put a UNIQUE index on the table, then MySQL will toss an error on a duplicate entry. Catch this error in your server-side code and feed it back to your LSL script.

Alright, I think I've seen an example of this on this forum... I'll have to search for it.

If I can't find it I'm sure I'll need more help. I can't work on this until the morning.

Thanks

There is no direct MySQL interface in Second Life. It is up to you to write CGI code on the web server that will do these things (or to figure out how an existing web service interface can be utlized to do so).

I can suggest a framework for this, however. It sounds like you need one CGI call that will log a name and return whether that name was already in the database. The URL for such a call might look like:

http://my.domain/path/to/my/script?name=FistName%20LastName

Where FirstName%20LastName is the full name of the resident, with the space escaped for a URL query parameter. You could have the return value be one of two strings: "inserted" or "duplicate" (or whatever more suitable strings you desire).

The CGI language and library, and the MySQL library you use are going to have to be up to you. You can use SQL statements like:

CREATE TABLE resident_name (name VARCHAR(12 PRIMARY KEY) ENGINE InnoDB;

That creates your table, and only has to be run once when you setup your application. I suggest InnoDB tables because they offer transactions, and if you use transactions, you can get around the case where if your web server allows concurrent requests, you won't get a race condition where two requests both think the name isn't in the database. Then:

SELECT name FROM resident_name WHERE name="...";

Can be used to check whether the name is already in the database (in which case you could simply return your "duplicate" reply). Then to insert a new value, you can do:

INSERT INTO resident_name (name) VALUES ("...";

To insert a name that isn't already present. You COULD skip the SELECT and simply try to INSERT, relying on the uniqueness constraint of the primary key to tell you if the name is already present (possibly with INSERT IGNORE and a test on how many rows were inserted, but either way...), but sometimes the reason why an INSERT fails isn't easy to detect, so I would still recommend the transactional approach if it works for you.

basic idea in php

http://us2.php.net/manual/en/function.mysql-query.php

Wow, thanks! This worked like a charm. The avatar's name can only be entered once. If they try clicking again, they get an error message (Duplicate entry 'avatar_name' for key1) and they are not added to the database again.

Now, I want to combine this with a survey script. If they've taken the survey already, they get a rejection message saying they can't take it again. So how do I customize the error message in Second Life? And how would I allow them to continue taking the survey if they are not found in the database?

Okay, so I added the following code:

if (body == "Duplicate entry 'Sherman Gustafson' for key 1"){
 llSay(0, "Test worked!");   
}

This checks the body of the php file after the avatar's name has been sent to the database. if the user was already in the database, the page prints "Duplicate entry 'Sherman Gustafson' for key 1"

So, I have Second Life check what the body of the page says... if it's the error message, it says "Test Worked" in world.

But how do I make the if statement work for anyone who clicks it... not just my avatar?