Forum Moderators: coopster
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
couldn't you use WHERE status IN()
check for cookie and depending on that either SELECT WHERE status = 4
or
SELECT WHERE status IN(2, 4)?
Are your variables $USER and $reader (which is $_COOKIE['username']) the same value? It would seem so to me, unless I'm confused by your post. Here is what I think you may be after:
"SELECT id FROM articles
LEFT JOIN friends ON (articles.author = friends.friend)
WHERE status='4' AND author='$USER'
OR status='2' AND friend = '$reader'
ORDER BY date DESC LIMIT $start, $perpage"
ONUSINGNULLstatus='4'
hi,couldn't you use WHERE status IN()
check for cookie and depending on that either SELECT WHERE status = 4
or
SELECT WHERE status IN(2, 4)?
Hi, I don't understand what you mean? Could you explain it to me? I'm quite a n00b in PHP/MySQL scripting :)
[edited by: thijsnetwork at 5:37 pm (utc) on Jan. 23, 2004]
$USER = the author of the article (comes from a get var: $_GET['user'] ) and
$reader = the reader who is logged in.
if ($isFriend == 1) $determinedStatus = "status>=2";
else $determinedStatus = "status=4";
$query = "SELECT id FROM articles WHERE $determinedStatus AND author='$USER' ORDER BY date DESC LIMIT $start, $perpage;";
In any case, because in the table you've defined the status column as int(1) I would lose the quotes around the status number in your query because that represents it as a string, and I would expect it to break the query.
I wish you well,
Salsa
id
"SELECT articles.id FROM articles
LEFT JOIN friends ON (articles.author = friends.friend)
WHERE status='4' AND author='$USER'
OR status='2' AND friend = '$reader'
ORDER BY date DESC LIMIT $start, $perpage"
cheers coopster, forgot my manners. been a long day :-)
thijsnetwork says
>> If so, the articles that are marked by the author with "only visible for friends" must ALSO appear among the other articles
doesn't this mean that both articles have to appear. the ones marked status=2 and status=4?
in which case wouldn't an IN() clause work?
The code:
$reader = $_COOKIE["username"];
$author = $_GET["user"];// See if the user is a friend.
$query = "SELECT * FROM friends WHERE user = \'$author\' AND friend = \'$reader\'";
$results = mysql($query);
if(!$results)
{
//if not a friend get status=4
$query = "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...
$query = "SELECT id,subject, article, date FROM articles WHERE
author=\'$author\' AND status=2 AND status=4"
The two variables are not the same:$USER = the author of the article (comes from a get var: $_GET['user'] ) and
$reader = the reader who is logged in.
"SELECT
articles.id,
articles.subject,
articles.article,
articles.date
FROM articles
LEFT JOIN friends ON (articles.author = friends.user)
WHERE status=4 AND author='$USER'
OR status=2 AND author='$USER' AND friend='$reader'
ORDER BY date DESC LIMIT $start, $perpage"
In regards to the integer value being represented as a string:
Actually, it won't break the query because of the way MySQL handles comparison operations [mysql.com].
Also, at least keep the idea of JOINs in the back of your mind, and plan to bone up on them when you would otherwise have to make queries from within the result sets of previous queries.
I wish you well,
Salsa
Yeah, that's right, I've modified the query to (status = 2 OR status = 4)
I'm glad it worked, but are you sure? It would seem that in the friend query it should be "status=2 OR status=4". Using "satus=2 AND status=4" would seem to select nothing because status cannot be both 2 AND 4.
As far as placing integers in quotes, you can't also do that for INSERTs and UPDATEs can you? That always breaks for me, and I'm very careful to never do it. Maybe I've been unnecessarily cautious when making SELECTions and never noticed that it would work.
I wish you well,
Salsa
But, I got another problem!
If the author himself wants to read his own article! With the query you gave me, he cannot see his own article, because he don't has himself as a friend :)
But, I got another problem!
If the author himself wants to read his own article! With the query you gave me, he cannot see his own article, because he don't has himself as a friend
IN
$query1 =
"SELECT
articles.id,
articles.author,
articles.status,
articles.date
FROM articles
LEFT JOIN friends ON (articles.author = friends.user)
WHERE status=4 AND author='$USER'
OR status=2 AND author='$USER' AND friend='$reader'
ORDER BY date DESC LIMIT $start, $perpage";
$query2 =
"SELECT
articles.id,
articles.author,
articles.status,
articles.date
FROM articles
LEFT JOIN friends ON (articles.author = friends.user)
WHERE status=IN(2,4) AND author='$USER'
ORDER BY date DESC LIMIT $start, $perpage";
if ($USER == $reader) {
$query = $query2;
} else {
$query = $query1;
}
$results = mysql_query($query);
if (isset($_COOKIE['username']) && $_GET['user'] == $_COOKIE['username']) {
$result = $sqldb->query("SELECT id, author, status, date FROM articles WHERE author = '$USER' AND (status = 2 OR status= 4) ORDER BY date DESC LIMIT $start, $perpage");
} else {
$result = $sqldb->query("SELECT articles.id, articles.author, articles.status, articles.date FROM articles LEFT JOIN friends ON (articles.author = friends.user) WHERE status= 4 AND author='$USER' OR status=2 AND author='$USER' AND friend='$reader' ORDER BY date DESC LIMIT $start, $perpage");
}
INOR
if (isset($_COOKIE['username']) && $_GET['user'] == $_COOKIE['username']) {
$result = $sqldb->query("SELECT id, author, status, date FROM articles
WHERE author = '$USER' AND status IN(2,4) ORDER BY date DESC LIMIT $start, $perpage");
} else {
.
.
.
[edited by: coopster at 8:31 pm (utc) on Jan. 23, 2004]
thijsnetwork: Can't you just make all authors their own friends by default?
Also, if you were to use your original query instead of the JOIN, I think you'll have to put the OR part of the statement in () or you will select all articles from all authors where status=4.
I wish you well,
Salsa
You're right, we wouldn't need to perform a LEFT JOIN when the USER is also the author. Good catch. You could also use the IN clause where you have the OR, as jamie suggested:
Salsa: By doing that, I have to change a lot of code, (signup script/"friends add"-page/"friends-view"page, etc.) So, this solution is good for me! :)
I wish you well.
Salsa
Anybody know the ANSI SQL standard?
forgive me if you already understand, but I started the response as soon as you posed the question back in msg#9. I decided to post it anyway :)
INThere is a level of separation involving two tables that can't be accomplished using an
IN
"SELECT articles.id FROM articles
LEFT JOIN friends ON (articles.author = friends.user)
WHERE status IN(2,4) AND author='$USER' AND friend='$reader'
ORDER BY date DESC LIMIT $start, $perpage"
it's often tricky getting your head round someone else' problem - and no i didnt completely understand the question.
now i do :-)
having sat down and looked at it, i realise the IN wouldn't work either - probably would have taken me a frustrating few tries though ;-)
thanks for explaining
I got another issue with the new friends system ;) I always had a list of the 5 newest articles, which I generate with a query like this:
$this->query('SELECT * FROM articles WHERE status IN(1,4) ORDER BY date DESC LIMIT 0, ' . $howMany);
Well, now I want that also articles appear who are written only for friends, but not ALL the articles that have status=2, but ONLY the articles of which the reader is a friend of the author. And if not, the article will not appear in the list. So the difference here is, you got to do with more than onw author.
Does somebody knows how to solve this, and what kind of query I need?
WHERE status=2 AND friend='$reader'
