Getting Info Between Tables?

luxa

Hi there, not sure I phrased the question right, but...

I have a simple news script along with a simple members script that will allow members to save news articles (by adding the article IDs to a field under their username). My problem is, I'm not sure how to display the results of their saved articles. I'm able to display all of the articles, even broken up into pages :p But I can't for the life of me echo the headlines/links of the articles the user has saved.

Any help is greatly appreciated.

wilku

You're not very specific - do you use database to store your articles?
At least show the fragment of a script that displays articles for all users - then it will be possible to alter this a little to show articles for only one user.

luxa

Sure thing, here's the function that displays articles on the homepage...

	include("settings.php");

Connect();

$query = "SELECT * FROM articles ORDER BY id DESC LIMIT ".$limit ."";
$result = mysql_query($query);
$numrows = mysql_num_rows($result);

if (!$numrows == ""){
while($row = mysql_fetch_array($result)){

$id = $row['id'];
$name = Secure($row['name']);
$title = Secure($row['title']);
$content = stripslashes($row['content']);
$date = $row['date'];
$ip = $row['ip'];
$on = date('F j, Y', strtotime($row['date']));
$at = date('g:i a', strtotime($row['date']));

echo "
<div class='article'>
<h2><a href=\"article.php?id=$id\" title=\"Read more about $title\">$title</a></h2>
<span class='author'>By: $name on $on @ $at</span><span class='comments'><a href='article.php?id=$id'>";
echo CheckComments($id);
echo "</a></span>
<div class='post'><p>";

echo $content;

echo "</p></div></div>";
}
}

else{
echo "<p>No articles</p>";
}

mysql_close();

bpat1434

Okay, so what does the table that stores the articleID to userID look like? Or do you just have a CSV list value in a field in your database?

If your users table named user_to_article with a structure like:
id INT(11) auto_increment NOT NULL
articleID INT(11) NOT NULL
userID INT(11) NOT NULL

Then you could query like this:

SELECT a.*
FROM `user_to_article` AS u
LEFT JOIN `articles` AS a
    ON u.articleID=a.id
WHERE u.userID='$userid'
ORDER BY `id` DESC
LIMIT $limit

Hope that helps.

luxa

The users table looks like this:

userID
username
password
email
saved <-- this is where the ID numbers are added when a user "saves" an article... looks like "44,23,58,12,etc"

bpat1434

Okay, I'm going to say that you're working against the database in this case. Why store it like that when you can just create another table (a lookup table) to use as the intermediary between your users table and the articles table. Then you can query for info from the articles table and users table and join them on the user_to_article table (as my example above). This would work out better for you in the long run (including when users add or remove or update saved articles 😉 Trust me, I tried this once, and it didn't work out too well).

However, if you want to keep it that way, you could just query the database like so:

 SELECT a.*
FROM `articles` AS a
WHERE a.`id`IN (...)

The "..." would be replaced by the string in your saved column 😉