How to sort and display database results with MySQL/PHP

Question

I am using PHP to sort items on my website into different locations/different divs. I have a script in each div where I want the results to be displayed So if there are 4 divs there are 4 scripts. (4 categories) Each script has a query in the head of my page.

MySQL query for category/div 1

$sql_category1 = <<<SQL
  SELECT *
  FROM `list_items`
  WHERE `category` = 1 
  ORDER BY `rating` ASC;
SQL;

if(!$category1 = $db->query($sql_category1 )){
    die('There was an error running the query [' . $db->error . ']');
}

My PHP for category/div 1:

<?php 
    while($row = $category1->fetch_assoc()) {

    '<div class="list-item-container">'
    . '<a href="' . $row['url'] . '"><img title="' . $row['title'] . '" class="favicon" src="http://www.google.com/s2/favicons?domain='. $row['fav_url'] . '" /></a>'
    . '</div>';

}
?>

My goal: When i am entering data into the table and there are two items that can be classified as two different things, so two different categories, I have to make two copies of it. This way a item will appear in both categories. Same item two different categories. I give one of the items a category value of 1 and the other item a category value of 2.

Rather than making two separate entries of the same item is there a way that I can give that item two separate category values? This way the item will appear in both categories when my scripts run and sort them.

I tried 1,2 1:2 and 1;2 as the value of the item. Tho im sure it's not even close to that.

Thanks in advance!

smnbbrv · Accepted Answer · 2013-12-08 19:25:06Z

1

make 3 tables:

category (id, name, ...)

item (id, name, ...)

item_category (id_item, id_category)

them make join of them in your queries, e.g. your first query will become

SELECT *
FROM `list_items` t,`item-category`
WHERE `id_category` = 1
  and `id_item` = t.`id`
ORDER BY `rating` ASC;

After you have connection table you can put in it whatever connections you want

answered Dec 8, 2013 at 19:25

smnbbrv

24.7k9 gold badges85 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2526699 Over a year ago

EDIT: Not sure what you're getting at. sorry newish to this. . Well, the example i list is pretty much how I'm using it in production, however, there are far more than 3 columns i am using there are 30 columns. I just removed the rest of the code from the above example to include only 3 of the columns. Would your method still be valid giving that? Rather than me having to make 30 new tables? All i really need to do is give the item category two values or 3 values or 4. depending on the item.

smnbbrv Over a year ago

this is a valid method. See starting from en.wikipedia.org/wiki/First_normal_form to all normal forms to learn how the tables should be made. This is an article exactly about your case

smnbbrv Over a year ago

Right now I am sitting in front of the Oracle database with 500 tables :)

user2526699 Over a year ago

Hmm i might have to redo my entire database Or atleast the way I create the content to put in it. I use a spreadsheet. I give a row in the category column 1-4 values rather than making 4 entries and then just upload it to the database. Any ideas?

smnbbrv Over a year ago

you simply can't do that in a relational database. At least, it will be a really weird way

Collectives™ on Stack Overflow

How to sort and display database results with MySQL/PHP

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related