[PHP/MySQL] Displaying articles =/

[thijsnetwork]

Hello everybody,

I've encountered a problem while coding PHP: The users on my website have their own page where they can post articles. They have the opportunity to publish the articles for everybody (status=4) or publish them only for friends (status=2). Now, what I want is that the script automatically detects whether a user (who is logged in by cookie) is a friend of the author of the articles he is viewing. If so, the articles that are marked by the author with "only visible for friends" must ALSO appear among the other articles :).

Now, the script have to do things like:

- Select the articles that are public, from the author we requested the page from.
- also select the articles that are marked with "only visible to friends", and the logged in user IS a friend of the author.

Well, I've tried to do this with a MySQL query, but I wasn't able to fit all the conditions in one single query, I've got this so far..

"SELECT id FROM articles WHERE status='4' AND author='$USER' ORDER BY date DESC LIMIT $start, $perpage"
and:
// check if the user is a friend of the author...
$reader = $_COOKIE['username'];
"SELECT * FROM friends WHERE user='$USER' AND friend='$reader'"
// if numrows is more than 1, execute this query...
"SELECT id FROM articles WHERE status='2' AND author='$USER'"
// print articles.

Does anybodt knows how to solve this quite nasty problem? Maybe with if and while statements? I don't know where to start. =/

These are the tables I use in my script:

CREATE TABLE `articles` (
 `id` int(11) NOT NULL auto_increment,
 `author` varchar(50) NOT NULL default '',
 `subject` varchar(255) NOT NULL default '',
 `article` text NOT NULL,
 `date` int(10) NOT NULL default '0',
 `status` int(1) NOT NULL default '4',
) TYPE=MyISAM;
CREATE TABLE `friends` (
 `id` int(11) NOT NULL auto_increment,
 `user` varchar(50) NOT NULL default '',
 `friend` varchar(50) NOT NULL default '',
) TYPE=MyISAM;

I'm using Apache 1.3.29 in combination with PHP 4.3.4 and MySQL 3.23.x

[coopster]

Sure, but you already have it :)

"SELECT articles.id, 
 articles.author, 
 articles.status, 
 articles.date 
 FROM articles 
 LEFT JOIN friends ON (articles.author = friends.user) 
 WHERE status IN(1,4) 
 OR status=2 AND friend='$reader' 
 ORDER BY date DESC LIMIT 0, " . $howMany) 

You still need to JOIN the two tables in order to get the correct result set, you just don't do any author-specific row selection.

Fixed double WHERE clause

[edited by: coopster at 5:09 pm (utc) on Jan. 24, 2004]

[thijsnetwork]

When I execute the query it returned an error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE status=2 AND friend='admin' ORDER BY date DESC LIMIT

[coopster]

oops, cut and paste error. I fixed it. I had the WHERE clause typed in twice like this:

 WHERE status IN(1,4)  
 OR WHERE status=2 AND friend='$reader'  

Look back at the previous message, it is updated to work correctly.

[thijsnetwork]

This is the whole function I'm using to get the 5 latest articles:
ht*p://thijsnetwork.linux-box.nl/function.phps

[thijsnetwork]

I got very strange results :S Sometimes 5 times the same article, sometimes an article from all the same author, sometimes 4 the same articles and one other...
Here you can find the function I'm using:
h*tp://thijsnetwork.linux-box.nl/function.phps

[coopster]

What is the

id

column in the

friends

table? I have been wondering for awhile now... :)

Anyway, are you sure you don't have duplicate user<-->friend rows in your

friends

table?

[thijsnetwork]

What is the id column in the friends table? I have been wondering for awhile now... :)

The ID column gives an id to every user-friend combination and every article. It's auto_increment.

Anyway, are you sure you don't have duplicate user<-->friend rows in your friends table?

No, I've not, because I've built in a check in the script if the user set someone as his/her friend.

[coopster]

The ID column gives an id to every user-friend combination and every article. It's auto_increment

OK, but they are not the same, correct? Meaning, when a new article is entered, a new

id

is assigned in the

articles

table. An author might add ten articles before getting a new friend and they decide to add the new friend to their list of authorized friends. The

friend.id

is completely separate and different from the

article.id

, right?

[thijsnetwork]

That's right.

[coopster]

...and the data looks something along these lines...

articles

id..author........status..rest of columns 
1...coopster...........1 
2...coopster...........2 
3...coopster...........1 
4...thijsnetwork.......1 
5...coopster...........4 
6...coopster...........2 
7...thijsnetwork.......2 

friends

id..author........friend 
1...coopster......thijsnetwork 
2...coopster......somebodyelse 
3...thijsnetwork..coopster 
4...thijsnetwork..somebodyelse 
5...thijsnetwork..andyetanother 

[thijsnetwork]

That's right, but "id..author........friend" must be "id..user........friend"

And these are all the status codes of the table articles:
=== articles
1 = news
2 = friends only
3 = private
4 = public
5 = locked

[coopster]

OK, I see the issue now. I think we need to return DISTINCT [mysql.com]

articles.id

...

The

JOIN

itself, prior to any

WHERE

clause, would give us something like this...

id..author........status..id..user..........friend  
1...coopster...........1..1...coopster......thijsnetwork 
1...coopster...........1..2...coopster......somebodyelse 
2...coopster...........2..1...coopster......thijsnetwork 
2...coopster...........2..2...coopster......somebodyelse 
3...coopster...........1..1...coopster......thijsnetwork 
3...coopster...........1..2...coopster......somebodyelse 
4...thijsnetwork.......1..3...thijsnetwork..coopster 
4...thijsnetwork.......1..4...thijsnetwork..somebodyelse 
4...thijsnetwork.......1..5...thijsnetwork..andyetanother 
5...coopster...........4..1...coopster......thijsnetwork 
5...coopster...........4..2...coopster......somebodyelse 
6...coopster...........2..1...coopster......thijsnetwork 
6...coopster...........2..2...coopster......somebodyelse 
7...thijsnetwork.......2..3...thijsnetwork..coopster 
7...thijsnetwork.......2..4...thijsnetwork..somebodyelse 
7...thijsnetwork.......2..5...thijsnetwork..andyetanother 

...and after the

WHERE

clause is applied, it would look as follows (let's say you were the $reader):

id..author........status..id..user..........friend  
1...coopster...........1..1...coopster......thijsnetwork 
1...coopster...........1..2...coopster......somebodyelse <-- Duplicate 
2...coopster...........2..1...coopster......thijsnetwork 
2...coopster...........2..2...coopster......somebodyelse <-- Not Returned! 
3...coopster...........1..1...coopster......thijsnetwork 
3...coopster...........1..2...coopster......somebodyelse <-- Duplicate 
4...thijsnetwork.......1..3...thijsnetwork..coopster 
4...thijsnetwork.......1..4...thijsnetwork..somebodyelse <-- Duplicate 
4...thijsnetwork.......1..5...thijsnetwork..andyetanother <-- Duplicate 
5...coopster...........4..1...coopster......thijsnetwork 
5...coopster...........4..2...coopster......somebodyelse <-- Duplicate 
6...coopster...........2..1...coopster......thijsnetwork 
6...coopster...........2..2...coopster......somebodyelse <-- Not Returned! 
7...thijsnetwork.......2..3...thijsnetwork..coopster <-- Not Returned! 
7...thijsnetwork.......2..4...thijsnetwork..somebodyelse <-- Not Returned! 
7...thijsnetwork.......2..5...thijsnetwork..andyetanother <-- Not Returned! 

Notice the last three rows here are not going to be returned either. Are you going to want them to be returned? Well, to get the

DISTINCT

rows returned:

"SELECT DISTINCT articles.id,  
 articles.author,  
 articles.status,  
 articles.date  
 FROM articles  
 LEFT JOIN friends ON (articles.author = friends.user)  
 WHERE status IN(1,4)  
 OR status=2 AND friend='$reader'  
 GROUP BY articles.id 
 ORDER BY date DESC LIMIT 0, " . $howMany) 

[thijsnetwork]

it's still not working properly :S

I got now a listing of 10 (I've temporary set $howMany to 10) items that looks something like this:

- by thijsnetwork (24-1)
- by test (23-1)
- by user1 (07-1)
- by user2 (07-1)
- by user3 (07-1)
- by user4 (06-1)
- by user4 (06-1)
- by user1 (06-1)
- by user5 (06-1)
- by user5 (06-1)

But, normally something like:
(06-1) means for example 6th of January.

- Subject1 by username1 (date)
- Subject2 by username2 (date)
....

[coopster]

The same author couldn't post more than one article on any given date?

[thijsnetwork]

IT WORKS! Thanks coopster! :) The above problem: the column articles.subject was not selected, so it couldn't be displayed. :))

[coopster]

Excellent, congratulations!

You never did answer the question from msg#42 though.

Notice the last three rows here are not going to be returned either. Are you going to want them to be returned?

You see, if you were the $reader, it wouldn't return your rows because you aren't registered as your own friend. However, if you do want those rows returned, you could use some of the logic and SQL from msg#19 - msg#22 and get where you need to go.

Come on back if you run into issues -- coopster

[thijsnetwork]

Notice the last three rows here are not going to be returned either. Are you going to want them to be returned?

Yeah, I used the query you gave me.

[thijsnetwork]

Strange.. I made a page with a list of all the weblogs/articles and I discovered, it's still not working properly.. Maybe you can find the problem by seeing the script in action.

Note that the tables "articles" and "friends" are being renamed in the script to "thijs_blogs" and "thijs_friends".

The articles are written Dutch.

Example:
h*tp://thijsnetwork.linux-box.nl/list.phps

Source:
h*tp://thijsnetwork.linux-box.nl/list.phps

The site is using a template script to generate the HTML output. (ETS - ets.sourceforge.net)

[coopster]

What is not working?

[thijsnetwork]

Well, some dates are: 01-01-70 :S and if you sort by number of reactions, 14 is highest while there are some of the articles with more than 80 reactions

[coopster]

The

thijs_blogs.date

column is stored as

int(10)

, not a

date

column type, therefore the sort is correct. Why not store the column as type

date

? Either that, or you will have to get a bit more fancy with your SQL to peel apart the integer to get the month, day and year parts of the date to do your sort.

The comments sort worked for me,

list.php?sort=comments

. Am I missing something?

[thijsnetwork]

> Why not store the column as type date?
Because I don't need it, alle the dates of the articles are stored by unix timstamp, so it don't have to be a problem to sort it.

>The comments sort worked for me, ..
No, you're not missing something, nothing was wrong with it :S :)

[coopster]

If you are sure your UNIX timestamps are being written and stored correctly (which I believe they probably are as many other rows show the correct date), you'll have to have a look at your

NLDate

user-defined function.

I did notice, however, the date you are referring to looks to me like the Unix Epoch (January 1 1970 00:00:00 GMT). Therefore, check your row for that record. If it does not have a date in it, then your

NLDate

user-defined function must be doing something with 0 or NULL entries, possibly applying the Unix timestamp to it?

[thijsnetwork]

> If you are sure your UNIX timestamps are being written and stored correctly..
Yeah, they are. I've checked it again.
The function I'm using:
ht*p://thijsnetwork.linux-box.nl/date.phps

> Therefore, check your row for that record.
All the rows have a date formatted as the unix timestamp.

And if you want to sort by date, it won't work as I expected it to work.

Maybe, the problem is the key I defined, because the full table looks like this:

CREATE TABLE `thijs_blogs` (
 `id` int(11) NOT NULL auto_increment,
 `author` varchar(50) NOT NULL default '',
 `subject` varchar(255) NOT NULL default '',
 `blog` text NOT NULL,
 `date` int(10) NOT NULL default '0',
 `comments` int(4) NOT NULL default '0',
 `mood` varchar(255) default NULL,
 `custom` varchar(255) default NULL,
 `status` int(1) NOT NULL default '4',
 `html` int(1) NOT NULL default '1',
 `bb` int(1) NOT NULL default '1',
 `smiles` int(1) NOT NULL default '1',
 `last_comment` int(10) NOT NULL default '0',
 `last_comment_by` varchar(50) NOT NULL default '',
 `private_for` text NOT NULL,
 KEY `id` (`id`),
 FULLTEXT KEY `subject` (`subject`,`blog`)
) TYPE=MyISAM AUTO_INCREMENT=268 ;

I've stripped out some fields at the beginning, bevause I thought you were confused by them.

[Salsa]

Hi thijsnetwork!

It looks like you've made a lot of progress on this project.

I haven't been following along too closely, so all the issues aren't too clear to me, but I think what Coopster is saying is that you appear to be saving your dates in int(10) fields instead of date or timestamp fields. If you use date/time specific data types in your tables, sorting will likely be less of a chore--and they provide an endless variety of formatting options. The two types that I usually use for dates are:

createDate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
and
updateDate timestamp(14),

If you change to the timestamp data type, though, be wary. My opinion is that it's best to use the timestamp data type only for recording updates of records and not for permanent creation dates because the timestamp type will automatically update every time anything in the record is updated. So, to use timestamp types to store permanent data you must query the field before the update is made and then reupdate it with the old data each time any other field is updated. So, for dates that you want to keep permanently it's a lot easier to use the datetime or date types.

For update dates, however, the cool thing about the timestamp type is that you don't even have to include it in your update query. It just gets updated automatically.

I wish you well,
Salsa

[coopster]

Well, the issue may not be the key, but your DISTINCT keyword. Originally, we were looking for a

DISTINCT thijs_blogs.id

. I did notice when you changed that to a

DISTINCT *

, but after looking at the rest of your fields at that time I didn't see an issue so I didn't correct/instruct you that you were applying the DISTINCT keyword to every field in your row, which might not return what you expect.

Now, knowing that you have more than these columns in the table, I think you are going to have to name them in your query for those indexes that you intend to use in your $uData result set:

"SELECT DISTINCT thijs_blogs.id, thijs_blogs.subject, thijs_blogs.author,  
thijs_blogs.comments, thijs_blogs.date 
 FROM thijs_blogs..." 

I'm not quite sure this is it, but let's start with that. What version of MySQL are you using? 3.23.?

[thijsnetwork]

Yeah! Thanks coopster it works! Check the result at

You see, there wasn't anything wrong with the date-column. I've tried to convert the date column to type TIME or TIMETAMP or DATE, but all the data in the column 'date' was lost and replaced by a lot of zeros.. (yeah, I've got a backup)

The MySQL version I'm using: at my own localhost server: 4.0.17-nt, and at my hosting company: 3.23.56

[edited by: jatar_k at 8:04 am (utc) on Jan. 27, 2004]
[edit reason] sorry no urls [/edit]