Hi there everyone!
Not that it's hard to do, but I've really confused the hell out of myself.
I have social links. They have a primary category that they most closely relate to, then secondary categories that they can also belong to. For instance, a Rube Goldberg device that uses magnifying lenses and light is posted with a primary category of "Creative" and has the secondary categories of "Science" and "Interesting".
To set this system up, I apply the primary cat to the link's db entry and then I have a table which will have an entry associating a link_id with a cat_id. For my previous example, the link has a primary_cat of 17 and then in the linkcat table, the link's id can be found two times, identifying the secondary cats that the link belongs to.
But now I'm to the point at which I need to show results ordered properly and counted for pagination purposes and I've hit a brick wall.
My intention is to allow somebody to choose particular categories to show results for. So, if they were to flag "Science", the Rube Goldberg device link would show up with other links that might have their primary_cat as science or maybe a linkcat entry for a secondary category.
If this isn't confusing enough, it needs to be sorted by posted_date, which linkcats doesn't even have an entry for. It would either need to get added to the table or it would have to get pulled from the links table once a match was found.
How do I manage this? Can it even be done efficiently or do I need to scrap my method and try another way of handling this?
Here's my primary query:
$query_urls = "SELECT * FROM links WHERE primary_cat = '$cat_id' AND is_social = '1' AND active = '1' ORDER BY ".$sortBy." ".$sortOrder." $pages->limit";
$result_urls = mysqli_query ($link, $query_urls) or die('Catastrophic failure [Super secret code 615]');
$num_urls=mysqli_num_rows($result_urls);
while ($row_urls = mysqli_fetch_assoc ($result_urls)) {
$id = $row_urls['id'];
and here's where I grab links matching the cat id for secondaries:
$query_sc = "SELECT * FROM linkcats WHERE cat_id = '$cat_id'";
$result_sc = mysqli_query ($link, $query_sc) or die('Catastrophic failure [Super secret code 746]');
$num_sc=mysqli_num_rows($result_sc);
Thanks for your time!
