MySQL JOIN + OR to show my posts and my friends' posts?

[zac1987]

Basically I just want to show my posts and my friends' posts on to website. Please refer to the 2 tables from the picture below :

 

Codes :

<?php
$username1 = "zac1987";

$query41 = "SELECT  p.message FROM friendship f
JOIN messageslive p
ON p.username = f.frenusername OR p.username = f.username
WHERE f.frenusername = '{$username1}'
ORDER BY
p.id DESC
LIMIT 0,16";

$result41 = mysql_query($query41,$connection) or die (mysql_error());
confirm_query($result41);
while($msginfo = mysql_fetch_assoc($result41)){
	$msg = $msginfo['message'];
	echo $msg . "<br/>";
}
?>

 

Output :

live

sss

ssa

ddd

asa

(dance2)

asaaa

ssa

(bluek2)

(bluek2)

(bluek2)

jjj

jjj

jjj

vv

(comp2)

 

Problem 1 - It shows 3 x duplicated zac1987's post because there are 3 of "zac1987" in the field of "frenusername" in friendship table. How to fix it?

Problem 2 - when I change $username1 == "zac1987" to $username1 == "micellelimmeizheng1152013142", output is blank. How to fix it?

[Psycho]

Your JOIN is backwards. You should be selecting on the message table and then JOIN the friends table. To be blunt, your database structure sucks. But, with what you have, this query shoudl work:

 

SELECT  p.message
FROM messageslive p
JOIN friendship f
  ON p.username = f.frenusername OR p.username = f.username
WHERE p.username     = '{$username1}'
   OR f.username     = '{$username1}'
   OR f.frenusername = '{$username1}'
ORDER BY p.id DESC
GROUP BY p.id
LIMIT 0,16

[zac1987]

Your JOIN is backwards. You should be selecting on the message table and then JOIN the friends table. To be blunt, your database structure sucks. But, with what you have, this query shoudl work:

 

SELECT  p.message
FROM messageslive p
JOIN friendship f
  ON p.username = f.frenusername OR p.username = f.username
WHERE p.username     = '{$username1}'
   OR f.username     = '{$username1}'
   OR f.frenusername = '{$username1}'
ORDER BY p.id DESC
GROUP BY p.id
LIMIT 0,16

 

Perfect. It works like charm. Thank you very much. Anyway, your codes has some error which GROUP BY must appear before ORDER BY. May I know why my table suck?

[Psycho]

Anyway, your codes has some error which GROUP BY must appear before ORDER BY.

Yeah, I forget the proper order and I just wrote that on-the-fly without testing it since I don't have your database.

 

May I know why my table suck?

 

Sure. First of all you should have a unique ID for all the user records and use that as the foreign key reference in the other tables. I assume you have a user table, but I don't know if you have an id column in that table. If so, THAT is what you should use in the two tables above to reference the user - not the username. Also, I would not name the id fields as "id" since it becomes ambiguous as to which id is which when joining tables. Instead, I would name the fields "user_id", "message_id", "friend_id", etc. Then use that same name when using that id in another table. It then becomes intuitive as to the associations.

 

Also, I think there is a way to simplify the query above a little bit, but not 100% sure it would work. Why don't you give this a try:

SELECT  p.message
FROM messageslive p
JOIN friendship f
  ON p.username IN (f.frenusername, f.username)
WHERE '{$username1}' IN (p.username, f.username, f.frenusername)
ORDER BY p.id DESC
GROUP BY p.id
LIMIT 0,16

[zac1987]

THAT is what you should use in the two tables above to reference the user - not the username.

May I know why should save id of user instead of username? For me, it is easy to know who is the user by looking at his username in table in database, and it save 1 more step when I want to show the username, I don't need to query user table anymore since friend table has the username already.

 

Also, I think there is a way to simplify the query above a little bit, but not 100% sure it would work. Why don't you give this a try:

ON p.username IN (f.frenusername, f.username)
WHERE '{$username1}' IN (p.username, f.username, f.frenusername)

I have tested it, it works! WOW. I never know IN can function like this, learn a good stuff from u, thanks.

[Psycho]

THAT is what you should use in the two tables above to reference the user - not the username.

May I know why should save id of user instead of username? For me, it is easy to know who is the user by looking at his username in table in database, and it save 1 more step when I want to show the username, I don't need to query user table anymore since friend table has the username already.

A forum post isn't really an appropriate place to teach you all the best practices for database design. I would suggest you read up on database normalization to start. But, I'll provide some details.

 

When designing a database design, the goal is not to make the data easy to understand for someone looking directly into the database, the goal is to make sure it works efficiently. One of the main purpose (if not the only purpose) of having an auto-incrementing, unique id is to use that value as the foreign key in other tables. That value should typically never be exposed to the user and should never be changed. UserIDs, on the other hand, can be changed based upon the business requirements of the application. If you are using the UserID as the foreign key you could have many tables to "fix" in order for the user ID to be changed. But, even if your requirements are that the User ID can't be changed, doing a JOIN on an integer ID will be much, much more efficient than varchar fields.

 

Now, I understand why you did what you did. It makes it easier to display records, such as the messages and who created the message, without having to do a JOIN. But, that is just being lazy. If there is a piece of "data" that should be associated with a record, that data should only be stored in one place. If I wanted to display a list of messages along with the user and the user's join date, I wouldn't add a column to the messages table to copy the user's join date into. The join date should be a field in the user table.

 

I did a quick search and this seems to be a good, concise 1-page tutorial to give you some basic info: http://www.atlasindia.com/sql.htm

Normalization is the process where a database is designed in a way that removes redundancies, and increases the clarity in organizing data in a database. [/quote[

 

Also, I think there is a way to simplify the query above a little bit, but not 100% sure it would work. Why don't you give this a try:

ON p.username IN (f.frenusername, f.username)
WHERE '{$username1}' IN (p.username, f.username, f.frenusername)

I have tested it, it works! WOW. I never know IN can function like this, learn a good stuff from u, thanks.

Yeah, it is unintuitive based on how we normally use the condition operators. It's usually the field followed by the values, such as: field = $somevalue. I actually stumbled upon doing it in reverse when I was trying to use a LIKE in reverse. I wanted to store values to be used in comparisons and have those values use replacement characters. The purpose was for restricting emails. I could either have an explicitly defined email that would be allowed, such as "me@here.com" or I could use wildcard characters such as "*@here.com". To make it work I had to use the LIKE condition in the opposite way as I normally would.

SELECT * FROM allowed_emails WHERE $user_value LIKE email_field

[zac1987]

UserIDs, on the other hand, can be changed based upon the business requirements of the application. If you are using the UserID as the foreign key you could have many tables to "fix" in order for the user ID to be changed. But, even if your requirements are that the User ID can't be changed, doing a JOIN on an integer ID will be much, much more efficient than varchar fields.

My system doesn't allow user to have same username with other user, and user cannot change/modify username, so unique username is just the same as unique auto increment user_id, so unique username can actually act like auto increment user_id, so username can be foreign key.

 

If there is a piece of "data" that should be associated with a record, that data should only be stored in one place. If I wanted to display a list of messages along with the user and the user's join date, I wouldn't add a column to the messages table to copy the user's join date into. The join date should be a field in the user table.

I don't need to add a column to messages table for user's join date info, because user table already has the column for user's join date. Normally we don't show user's join date on messaging section, we only show user's join date on user profile section. In user profile, I just user normal MySQL query to retrieve username, user-full-name, age, photo and join date info from user table. No need to use JOIN. I save username instead of user_id onto message table can faster the query since no need to JOIN user table, the username act like foreign key. No harm at all.

 

Normalization is the process where a database is designed in a way that removes redundancies, and increases the clarity in organizing data in a database. [/quote[

Regarding removes redundancies, store user_id in message table has redundancies too, there will be many duplicated user_id in message table. So there is no different if I store many duplicated username. Both user_id and username are also redundancies. Thus  removes redundancies is not a point to convince me to store user_id instead of username in message table.

 

I wanted to store values to be used in comparisons and have those values use replacement characters. The purpose was for restricting emails. I could either have an explicitly defined email that would be allowed, such as "me@here.com" or I could use wildcard characters such as "*@here.com". To make it work I had to use the LIKE condition in the opposite way as I normally would.

SELECT * FROM allowed_emails WHERE $user_value LIKE email_field

Wow, normally in this case I will get all emails from database, then use javascript to check which are allowed. Your reverse IN function make it easier and save query resource, cool stuff. Thanks for sharing. Just curious why don't you block all unallowed_email at first place? Just pop up a message "sorry, your email is incorrect", then don't store his email onto your database.

[Psycho]

You "hear" me, but you are not "listening" to me. I was only providing some examples of why it is a poor design to use the user ID as the foreign key. Just because the example doesn't apply to your particular situation doesn't make it a good solution.

 

I have spent several years involved in the development of a multi-million dollar application. We have encountered numerous issues because of poor decisions related to database schema issues. For example, the software will support multiple customers using different accounts. So, all the data needs to be separated by account. There is a table to hold the basic account info: acct no, company name, etc. The table also contains a unique guid for each company. However, some teams used the account number as the foreign key reference instead of the unique guid since the account number will also be unique. There are two problems with that. 1) The account number can have a leading zero so it should be treated as a string. Otherwise you lose the leading zero. There have been numerous application errors when this has happened. 2) Initially all the account numbers would all be 6 digit numbers. But, now we need to support longer account numbers and/or characters. That is now becoming a very expensive project as there are numerous tables and queries that need to be modified. Had the unique guid been used as the foreign key then changing the allowed account numbers would have only required changing one field in one table and a handful of queries.

 

However, you only want to rationalize your poor implementation. You state that using the user_id would be a redundancy. But, that is not what that statement is about. It is talking about removing the redundancies of "data". The user_id is not part of the data per se, the user_name is. You obviously didn't read the article I linked, so there is no use trying to go into details. But, one issue I didn't mention was performance. JOINing tables on a primary index will take much less time than the username. You would now need to index that column in all your tables which is a waste. From the linked document

The entity should already be in the 2NF and no column entry should be dependent on any other entry (value) other than the key for the table.

A foreign key is a field in a table which is also the Primary Key in another table. This is known commonly as 'referential integrity'.

 

Additionally, MySQL does support foreign key constraints for InnoDB databases. That would only work by using the primary index from one table in another. There are tremendous benefits of that such as cascading updates and deletes. I.e. you delete a record and all references to that record in other tables are also deleted. And, lastly let's not forget storage size. An integer value will take much, much less space to store than even a short username.

 

Just curious why don't you block all unallowed_email at first place?

This isn't what you think. The list of allowed emails is enforced in the development environments to prevent applications emails from being sent to unintended users. We have a white list of domains and also the ability to add additional allowed email addresses. So, now we can test with live data without emails going to a customer. For example, if a customer has a bug we cannot reproduce we can copy their data to a dev environment and replicate the issue without affecting their production data and ensuring they don't get emails from the dev environment.

 

It's your project. Do what you want. I only provided some constructive criticism for you to consider.

[zac1987]

You obviously didn't read the article I linked.

 

Finally finish reading the article u linked. I took few days to read the article, so many terms that didn't understand until I google search for more info. I will normalize my database. Thanks for sharing and helping.