SQL Query + Ignore Duplicates.

[Reece S]

Hi, I have a database, that creates a new row for every public chat room that gets created (Gets deleted when chat finishes) and even if a user enters the same chat room, it creates a new row, both with same room, but 2 users. this results in "2 Row Count" and when printed, looks like 2 chat rooms, when it is really one.

 

Is there a way to run the query but class all duplicates as one? (on a selected Column)

 

Here is my current code:

 

$result = mysql_query("SELECT * FROM ajaxim_chats");
$num_rows = mysql_num_rows($result);
echo "<strong>Public Chatrooms: </strong>$num_rows";

[cooldude832]

What exactly are you trying to do.

 

If I was writing an ajax chat room base system I'd have a few tables

 

1) Users (login info user info etc)

2) Chat rooms (The actual room Name)

3) Chats (Postings that are defined to a given chat room ID in table 2)

4) User_in_rooms (Puts a user in a room by adding a row)

 

Then for your question you only need query off the second item.  If a table hasn't had a post added or use in it in 2 minutes destroy the room and its chat and remove all users in it (similar to a users online script)

[Reece S]

I do have other tables, and I know exactly how each one works, but The part I am focusing on is this. I have a number of successfully queries running like this, but for this particular one, I need to ignore a columns duplicates. Is this possible?

[cooldude832]

you can use UNIQUE() but I'm trying to save you from killing yourself with poor strucutre

[Reece S]

Yeah true, but I am just trying to get a basic system working. I really appreciate your intentions, BTW thanks for the UNIQUE() thing, I will try it now. When my system collapses, al bear your warning in mind. Cheers again.

[Reece S]

Um... where exactly do I put this??? Does it need a WHERE statement?

[cooldude832]

<?php
$q = "Select UNIQUE('FIELDNAME') as 'FIELDVALUES' from `TABLE` Where This = that";
?>

 

Generic useage

[Reece S]

ahh, thanks.

I am useless with PHP, I was experienced with C/C++.

Cheers man.

[Reece S]

Quick Notice:

This problem was solved with this:

 

SELECT DISTINCT(column name) FROM table name

 

Cheers anyway. Hope this helps other people with same problem.

[cooldude832]

DISTINCT AND UNIQUE are similar but different