[PHP/MySQL] Displaying articles =/

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
thijsnetwork
Forum Newbie
Posts: 3
Joined: Fri Jan 23, 2004 11:18 am

[PHP/MySQL] Displaying articles =/

Post by 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..

Code: Select all

"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:

Code: Select all

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
Sym
Forum Newbie
Posts: 3
Joined: Fri Jan 23, 2004 1:05 pm
Location: Vancouver, B.C.

Post by Sym »

My suggestion would be that you first query the database to see if the view is a friend and then using an if/else statment and have two different mysql query's.

The one problem I see is the getting the author however you could easily pass that from the site url.

ex: "http://yoursite.com/articles.php?author=someguy"

Code: Select all

$reader = $_COOKIEї"username"];
$author = $_GETї"author"];

// See if the user is a friend.
$query = "SELECT * FROM friends WHERE author = ''$author'' AND friend = ''$reader''";
$results = mysql($query);

if(!$results)
{
    //if not a friend get status=4
    author=SELECT id,subject, article, date FROM articles WHERE author =''$author'' AND status=4"
   $results = mysql_query($query);
}
else
{
    //if a friend get status=4 and 2.
    $query = "SELECT id,subject, article, date FROM articles WHERE author=''$author'' AND status=2 AND status=4"
   $results = mysql_query($query);
}

//use the results...
edit:
I also noticed in the above code you used '$user' in the mysql query, becareful with ' (single quotes) php doesn't resolve variables inside single quotations.
Last edited by Sym on Fri Jan 23, 2004 1:09 pm, edited 1 time in total.
thijsnetwork
Forum Newbie
Posts: 3
Joined: Fri Jan 23, 2004 11:18 am

Post by thijsnetwork »

Thanks! You've helped me a lot! :D It all works fine.
Post Reply