I have this design on my website. My modules are: news, articles, videos, photos, downloads, reviews, quizzes, polls, etc etc. All in separate tables. I have a likes table where users can like or dislike a post (in your case favorites). The query to get these isn't that complicated.
First off for the most part MOST of my tables for the modules are structured the same way:
- id
- title
- content
- user_id (author)
- date
- etc
with a few exceptions being that sometimes title is called question or there is no content column. That does not cause any issues.
My likes tables is set up like this:
- id
- page_id
- module_id (what table did it come from...I have a modules table where each module has a title, associated id, directory, etc)
- post_id (corresponds to the module table id)
- user_id (user who did the liking or posting)
- status (0 = like, 1 = dislike)
- date (when the liking/disliking took place)
Modules table example:
- id
- title
- directory
- post_type
Example
id title directory post_type
1 News news news
2 Episode Guide episodes episode
3 Albums discography/albums album
Essentially yours would have a similar set up, modifying the table structure as necessary for your needs.
Query to get all the likes or favorites for a particular user:
$getlikes = mysql_query("SELECT DISTINCT post_id, module_id, page_id FROM likes WHERE user_id = $profile_id ORDER BY id DESC LIMIT $offset, $likes_limit", $conn);
$likes = mysql_num_rows($getlikes);
if($likes == "0"){
echo "<br><Center>$profile_username does not have any liked posts at this time.</center><BR>";
}
else {
echo "<table width='100%' cellspacing='0' cellpadding='5'>
<Tr><th>Post</th><th align='center'>Module</th><th align='center'>Page</th><tr>";
while ($rowlikes = mysql_fetch_assoc($getlikes)) {
// echo data
$like_page_id = $rowlikes['page_id'];
$like_module_id = $rowlikes['module_id'];
$like_post_id = $rowlikes['post_id'];
// different modules have different fields for the "title", most are called title but quotes is called "content" and polls is called "questions"
if($like_module_id == "11"){
$field = "question";
}
elseif($like_module_id == "19"){
$field = "content";
}
else{
$field = "title";
}
// FUNCTIONS
PostURL($like_page_id, $like_module_id, $like_post_id);
ModTitle($like_module_id);
ModTable($like_module_id);
ModURL($like_page_id, $like_module_id);
fpgURL($like_page_id);
$getpostinfo = mysql_query("SELECT $field AS field FROM $mod_table WHERE id = $like_post_id", $conn);
$rowpostinfo = mysql_fetch_assoc($getpostinfo);
$like_post_title = $rowpostinfo['field'];
// Using my "tiny" function to shorten the title if the module is "Quotes"
if($like_module_id == "19"){
Tiny($like_post_title, "75");
$like_post_title = "\"$tiny\"";
}
if(!$like_post_title){
$like_post_title = "<i>Unknown</i>";
}
else {
$like_post_title = "<a href='$post_url'>$like_post_title</a>";
}
echo "<tr class='$altrow'>
<td>$like_post_title</td>
<td align='center'><a href='$mod_url'>$mod_title</a></td>
<td align='center'>$fpg_url</td>
</tr>";
$altrow = ($altrow == 'altrow')?'':'altrow';
} // end while
echo "<tr><Td align='center' colspan='3'>";
// FUNCTIONS - Pagination links
PaginationLinks("$cs_url/users/$profile_id", "likes");
echo "</td></tr></table>";
} // end else if no likes
Ok that may be hard for you to understand since I have alot of my own variables, but basically it gets the module id and post id from the likes table and then runs a query to get the title of the post and any other info I want like the original author.
I have "module" functions set up that will return the url or the title of the module given you provide an id for it.
