Store and fetch CSV columns from DB using implode and magic __get()

Question

I have PHP code to select categories from tbl_categorie, Now I have a multiple select in a form where an article can have more than one categorie. While inserting the values in database, I want to store multiple categories values in one column/attribute. Where ID_CAT attribute would only store categories ids of the tbl_categorie separated by comma(,). I have two tables in one DB,

 tbl_blog:                           
  ID_BLOG    ID_CAT               TITLE          ARTICL        DATE
  1          1,3                  title1         article1      2013-03-04
  2          4,10                 title2         article2      2013-03-04 
  3          3,6                  title3         article3      2013-03-04

 tbl_categorie:                           
  ID_CAT    NOM_CAT               
  1          HTML                
  2          CSS                
  3          DESIGN
  4          PHP
  5 ..

I have problem in first place to add tow ID_CAT for one article although I used the implode() statment but she works when i change the type of ID_CAT from int to varchar , and this is the process to add article :

Article::creatArticle(0,$_POST['title'],implode(', ', $_POST['id_categorie']),$_POST['article']);

and this is the function to add article from class_article :

/**
 * function créeArticle
 */ 
public static function creatArticle($id_article,$title,$id_categorie,$article)
{
    global $db; 
    $req = $db->prepare("INSERT INTO blog (ID_BLOG,TITLE,ID_CAT, ARTICLE,DATE) VALUES ('',:title,:id_categorie,:article,'".date('Y-m-d')."')"); 
    $ok = $req->execute(Array('title' =>$title,'id_categorie' => $id_categorie,'article' => $article));
     return $db->lastInsertId();
    //$erreur = $req->errorInfo();
}

now i have probleme to fetch all categories for which in each article shall have , and this is how i fetch in my back-office the table of articles using the magic method get() which she returne only the first value in th column :

 <?php
    foreach(Article::getAllArticle()as $blog ){
        $article= new Article($blog->ID_BLOG);
        $categorie = new Categorie($article->getIDCategorie());

        echo'
                    <tr>';
                            echo '<td><input type="checkbox" /></td>';
                            echo '<td>'.$article->getTitlearticle().'</td>';
                            echo '<td>'.$categorie->getNomCategorie().'</td>';
                            echo '<td>'.$article->getDatearticle().'</td>';
                            echo '<td>35</td>';
                            echo ' <td class="actions">';
                            echo '<a href="javascript:editArticle('.$article->getIDarticle().');" title="Edit this content"><img src="img/icons/actions/edit.png" alt="" /></a>';
                            echo ' <a href="javascript:deleteArticle('.$article->getIDarticle().');" title="Delete this content"><img src="img/icons/actions/delete.png" alt="" /></a></td>';
                            echo '</tr>';

                        } ..

i know that i have to use the explode() statment or making a loop but i can't figure it out how :( ,and i have a doubt about the type of ID_CAT should be varchar make the problem ? thanx !

irishman · Accepted Answer · 2013-03-06 21:23:15Z

It is difficult to understand what you are doing and what you want to do. I'm not sure if you are getting the results from a CSV file or from a database?

If it is from a database, from what I can see you need to normalise your database tables. Create a linking table between the blog and categories, this would make it a lot easier to bring back the results. A simple join sql will get all articles in an category or the get all categories that the article is associated with.

tbl_blog:                           
  ID_BLOG                TITLE          ARTICL        DATE
  1                      title1         article1      2013-03-04
  2                      title2         article2      2013-03-04 
  3                      title3         article3      2013-03-04

 tbl_categorie:                           
  ID_CAT    NOM_CAT               
  1          HTML 

tbl_blogcat
 ID_BLOG    ID_CAT 
 1           1
 1           3
 2           4
 2           10

It might be a little extra work now but it will be worth it in the future. Storing your categories as a comma delimited string makes it difficult to search.

user1978081 · Accepted Answer · 2013-03-06 21:45:31Z

This problem can be solve easy way, You can create a new table which will store the information of tbl_blog and tbl_catagory's primary id. The benefit is when you choose one more categories of a blog it will store your information of every category of a blog. the table is look like.....


tbl_combine:

ID_COMB    ID_Blog    ID_Cat
1          1          2
2          1          1
3          1          3
4          2          3
5          2          2
6          3          1
7          3          2
8          3          3

Above we just let 3 blog and 3 categories. the tbl_combine can store each blog category's primary id of its chosen blog in each row. So we can insert unlimited categories of a blog. there's no need explode or implode function.

Then we can retrieve any blog categories. The query look like.






SELECT
      (SELECT category_name FROM tbl_catagory 
       WHERE id_cat=A.id_cat) 
       AS catagory_name 
FROM tbl_combine 
AS A 
WHERE id_blog=$blog_id

Note we just store id of tbl_blog and tbl_catagory tables is tbl_combine it will not slow

Collectives™ on Stack Overflow

Store and fetch CSV columns from DB using implode and magic __get()

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related