FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Prevent double outcome in MYSQL





Talk2Tom11
I am trying to retrieve a last name and Id number. With the query that i currently have...

SELECT guest.ID, guest.Lastname FROM Guest, registered WHERE registered.stid='$user_id' AND registered.gstid=guest.id

it retrieves the id and last name from all the guest that the user has registered in the database.

the problem is... if the user has this guest more then once... then they are entered into the registered database more then once.

registered database includes user_id guest_id event and date

I want the guest to only be displayed once....

does anyone know how i can alter the query that i have in order to obtain the result i am looking for??


if my explanation was not clear enough of what is going on please let me know.
Star Wars Fanatic
Yea, you can add the "LIMIT 1" to it:

Code:
SELECT guest.ID, guest.Lastname FROM Guest, registered WHERE registered.stid='$user_id' AND registered.gstid=guest.id LIMIT 1
powers1983
Star Wars Fanatic wrote:
Yea, you can add the "LIMIT 1" to it:

Code:
SELECT guest.ID, guest.Lastname FROM Guest, registered WHERE registered.stid='$user_id' AND registered.gstid=guest.id LIMIT 1


That will only give you one row returned which is not quite what you need I don't think.

I assume the query is meant to return ALL the guests the user has but you just need them to be unique?

Not sure if there is a MySQL command but once you have all the returned value in an array then PHP has an array_unique function that will delete duplicates:

http://uk3.php.net/array_unique

It is pretty simple - just returns an array with duplicates removed
Code:

$result = array_unique($input);


Edit:
Actually MySQL does have a function for it:
http://www.plaino.com/mysql/index.html#distinct
http://dev.mysql.com/doc/refman/4.1/en/distinct-optimization.html

so in your case I think you would need:
Code:

SELECT DISTINCT guest.ID, guest.Lastname FROM Guest, registered WHERE registered.stid='$user_id' AND registered.gstid=guest.id


Hope that helps.

David.
Talk2Tom11
powers1983 wrote:
Star Wars Fanatic wrote:
Yea, you can add the "LIMIT 1" to it:

Code:
SELECT guest.ID, guest.Lastname FROM Guest, registered WHERE registered.stid='$user_id' AND registered.gstid=guest.id LIMIT 1


That will only give you one row returned which is not quite what you need I don't think.

I assume the query is meant to return ALL the guests the user has but you just need them to be unique?

Not sure if there is a MySQL command but once you have all the returned value in an array then PHP has an array_unique function that will delete duplicates:

http://uk3.php.net/array_unique

It is pretty simple - just returns an array with duplicates removed
Code:

$result = array_unique($input);


Edit:
Actually MySQL does have a function for it:
http://www.plaino.com/mysql/index.html#distinct
http://dev.mysql.com/doc/refman/4.1/en/distinct-optimization.html

so in your case I think you would need:
Code:

SELECT DISTINCT guest.ID, guest.Lastname FROM Guest, registered WHERE registered.stid='$user_id' AND registered.gstid=guest.id


Hope that helps.

David.




Thanks very much.... the 'DISTINCT' was all i needed.... works perfectly now.
Related topics
mysql connection question
php admin and mysql admin console
Mysql And PHP HELP PLZ
phpBB Error, seems to be a MYSQL DB error...
A very good PHP MySQL Tutorial
Suggestions of new forums, and subforums
how to prevent double post when user klik F5 *Refresh* ?
how to prevent double post when user klik F5 *Refresh* ?
MySQL and PHP Question
Difference of single quote and double quote in mysql
A real-world moral analysis
The Beast Revelation
MYSQL Normalization double join
MySQL query limit?
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.