sorting with category, need idea

[mraza]

hi i hv built a small posting system, i hv built few categories like this

 

in mysql i hv these tables

 

Categories table:

id catname

 

Post Table:

id postbody posttile date  catid

 

so i am getting posts with catid for related posts like

 

http://localhost/site/index.php?action=category&cat=1

 

so where $_GET['cat'] is 1 it display posts from that category 1, now what i need is when i post a new post i hv choice to assign multiple categories to one post, so a post could be in cat1 and in cat2 as well . plz any idea how can i do that? i was thinking to make a new rows in posts table for each category and set it to 0 or 1 , and when  i submit new post it will set 1 to those categories which i select upon writing post. but if i have like 20 categories i need to add more 20 rows in posts table.

 

is there any other better way i can accomplish this?

Thanks for help

[Psycho]

If you want to assign multiple categories then you need to remove the 'catid' field from the Post table and instead use an intermediary table to associate posts to categories:

 

Categories table:

id, catname

 

Post Table:

id, postbody, posttile, date

 

post_category table

postid, catid

 

You would have one record for each post to category assignment. For example a post that is associated with three categories would require three records in the post_category table.

[mraza]

Thank you for help

 

but what will be my query to insert and retrieve data from those tables

 

[Psycho]

If you don't know how to do that then you should read up on database queries. But for a generic answer you would need to do two queries to insert records and do a join when retrieving your records.

 

Example script to add a new record. This assumes the form will have the following input fields: post_title, post_body and an array of checkboxes named "categories[]" where each checkbox has the id of the different categories (note I am not including any code to validate/cleanse teh input for brevity):

//insert the post record
$query = "INSERT INTO `Post` (`postbody`, `posttile')
              VALUES ($_POST['post_title'], $_POST['post_body'])";
mysql_query($query);
    
//Get the inserted record id
$postID = mysql_insert_id();
    
//Insert the array of categories as individual records
$values = array();
foreach($_POST['categories'] as $catID)
{
    $values[] = "($postID, $catID)";
}
$query = "INSERT INTO `post_category` (`postid`, `catid`)
              VALUES " . implode(', ', $values[);
mysql_query($query);

 

Sample query to select all the posts associated with a particular category

SELECT * FROM `Post` as p
JOIN 'post_category` as pc ON p.id = pc.postid
WHERE pc.catid = $searchCategoryId

[mraza]

Thank you sir now i understand this join stuff, just one last thing, now i can get that post but how can i get categories names associated with that post, i tried this one

						$id = $row['id'];
						$sql = "SELECT * FROM categories as p
						JOIN post_category as pc ON p.id = pc.catid
						WHERE pc.postid = '$id'" ;
						$query= $db->query("$sql");
						$rows = $db->fetchArray($query);
					//	print_r($rows);
					        echo $rows['name']; 

and with that i only get one name of category, how can i get all categories names.

thanks again.

[Psycho]

To get a post record alonw with the associated category names, your query would look something like this:

SELECT p.posttitle, p.postbody, c.catname

FROM `Post` as p
JOIN `post_category` as pc
  ON p.id = pc.postid
JOIN `Categories` as c
  ON c.id = pc.catid

WHERE p.id = '$postID'

 

When getting multiple associated records like this, you will need to process the results. If the post was associated with three categories, the result set will include three records with the post data repeated three times. Something like this:

posttitle     | postbody            | catname
================================================
My First Post   This is a test post | Gaming
My First Post   This is a test post | Discussion
My First Post   This is a test post | Community

[mraza]

Thanks again for taking time to look in it, as u can see the same post has been repeated three time above in ur example:

 

posttitle     | postbody            | catname
================================================
My First Post   This is a test post | Gaming
My First Post   This is a test post | Discussion
My First Post   This is a test post | Community

 

what i wants is like this:

posttitle     | postbody            | catname
================================================
My First Post   This is a test post | Gaming, Discussion, Community

 

so it display catnames rather then repeating post itself.

Regards

 

[Psycho]

No, that is what you think you want. But, that is not how you are going to get records returned from the database. You just need to process the db results to display the records accordingly.

 

So, let's assume you are getting the results for a single post and getting back three records because the post is associated with three categories as in the above example. Then let's assume you want to display the post something like this:

 

My First Post

This is a test post

 

Categories: Gaming, Discussion, Community

 

Then your PHP code for processing the db results might look something like this:

//Run the query
$query = "SELECT p.posttitle, p.postbody, c.catid, c.catname
          FROM `Post` as p
          JOIN `post_category` as pc ON p.id = pc.postid
          JOIN `Categories` as c ON c.id = pc.catid
          WHERE p.id = '$postID'";
$result = mysql_query($query) or die(mysql_error());

//Generate the output
$output = '';
if(mysql_num_rows($result)<1)
{
    $output .= "No post found with that ID.";
}
else
{
    //Parse the db results
    $category_links = array();
    while($row = mysql_fetch_assoc($result))
    {
        $category_links[] = "<a href=\"showcategory.php?catid={$row['catid']}\">{$row['catname']}</a>";
    }

    //Create the HTML output
    $output .= "<b>{$row['posttitle']}</b><br />\n";
    $output .= "<p>{$row['postbody']}</p><br />\n";
    $output .= "<p>Categories: </p>" . implode(', ', $category_links);
}

echo $output;

[mraza]

Thank you Sir i got it

[mraza]

one more issue, as i also have one search function associated with it, so now for example if i have one post in three categories, it shows me three results after search how can i prevent it to show only one post when search something, here is  code i used from here:

      $searchSQL = "SELECT * FROM posts as p
						JOIN post_category as pc ON p.id = pc.postid
						WHERE ";
      
      // grab the search types.
      $types = array();
      $types[] = isset($_GET['title'])?"`title` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['body'])?"`body` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['year'])?"`year` LIKE '%{$searchTermDB}%'":'';
      
      $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)
      
      if (count($types) < 1)
         $types[] = "`title` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked
      
          $andOr = isset($_GET['matchall'])?'AND':'OR';
      $searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `title`"; // order by title.

      $searchResult = $db->query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
      
      if ($db->Numrows($searchResult) < 1) {
         $error[] = "The search term provided {$searchTerms} yielded no results.";
      }else {
         $results = array(); // the result array
         $i = 1;
         while ($row = $db->fetchArray($searchResult)) {
            $results[] = "{$i} : <a href=index.php?action=test&cat={$row['catid']}&post={$row['id']}>{$row['title']}</a> ({$row['year']}) Body: ss{$row['body']}<br />";
            $i++;
         }
      }

 

 

so with above i get multiple results like if i search "My first Post" it will return me three results as it is associated with three categories, how can i fix it, i checked array_unique but did not figured it out.

 

Regards

 

[Psycho]

The simple answer is don't use a JOIN on the category table when doing a search. But, that seems problematic because of the dependency on the category ID you are using in the link. Why do you need the category ID included in the link? That really doesn't make sense to me. If it IS required then what logic are you going to use to determine which category ID to use for a post when it is associated to multiple categories?

 

I think you should remove the dependency on including the category ID in the URL and remove the JOIN on the search query.

[mraza]

Actually sir if u note this line in above code

$results[] = "{$i} : <a href=index.php?action=test&cat={$row['catid']}&post={$row['id']}>{$row['title']}</a> ({$row['year']}) Body: ss{$row['body']}<br />";

 

i am using a related post feature so when after search i click on any return result it will take me to that post and as i have category id in my url so there i am displaying related topics tile with $row['catid'] from $_GET['cat']

[Psycho]

i am using a related post feature so when after search i click on any return result it will take me to that post and as i have category id in my url so there i am displaying related topics tile with $row['catid'] from $_GET['cat']

 

Huh? I'm not following. If you are clicking a link to access a post, why do you need a category ID associated with that link? On the page that displays the post you can determine what category or categories the post is associated with and display related posts accordingly. There is no need to pass it on the query string to access the post.

 

Besides, as I stated above, how would you determine what category ID to use on the query string when a post is associated with more than one category.

[mraza]

I am using include function to include that related posts page , thing is when after search i go to

index.php?action=test&cat=2&post=5

 

there i have code to display post like this

include('leftbar.php');
// Here comes the post data from above url of post id 5
include('rightbar.php'); // in this page i hv code to use $_GET['cat'] to display related posts

so to display a category id i need to hv $_GET['cat'] in my url or else rightbar.php would not work,

 

also i found a link here which explain to prevent this behavior but that's in virtuemart http://forum.virtuemart.net/index.php?topic=45182.msg189142 but dont understand what exactly need todo

 

Regards

 

[Psycho]

So, again, my question is "which" category ID do you want used when the post is associated with multiple categories? It seems like you are trying to reverse engineer some 3rd party application.

 

I will say it again - you do NOT need to pass the category ID on the query string. It is even more irrelevant when a post can be associated with multiple categories. On the page that displays the post you would just do a query to get the associated categories:

include('leftbar.php');// Here comes the post data from above url of post id 5

//Run query to get the associated categories
$query = "SELECT catid FROM `post_category` WHERE postid = '{$_GET['post']}'";
$result = mysql_query($query);
$catIDs = array();
while($row = mysql_fetch_assoc($result))
{
    $catIDs[] = $row['catid'];
}
//You now have an array of all the associated category IDs.
//You can then use those values in the rightbar.php page

include('rightbar.php'); // in this page i hv code to use $_GET['cat'] to display related posts

 

 

[mraza]

Thanks again sir now i get the point, and to answer regarding multiple categories i just wants to display any of one category posts but this your code solved it i guess i will test it now and to reverse engineer not really as i stated above i am building my own posting system, i hv already built registration, user comments etc, so if i was upto like that i will not goto build a new system for me there are several available like wordpress,joomla, i am learning php from several months and trying to do whatever comes in my mind as it is the only way i can learn, and thankfully there are people like you who helps people like me. i really appreciate for your kind support.

 

Regards

[mraza]

solved thank you very much