Solved

Order by column with multiple values

Posted on 2012-03-22
7
281 Views
Last Modified: 2012-04-30
I have a products table in my db which has a category field which relates to another table.

For example:

prodId   prodName   prodCat
1            test              2
2            test 2           2
3            test 3           1
4            test 4           1
5            test 5           2

The category table has id, name and order:

catID   catName   catOrder
1         cat1           3,4
2         cat2           1,2,5

The catOrder field is just a collection of the product ID's, what I want is to display the products table but in the order which appears in the catOrder column.

I currently have the following in my SQL:

SELECT store.*, cat.* FROM (store LEFT JOIN cat ON store.prodCat = cat.catID) WHERE store.prodCat = '" . $_GET['cat'] . "'

How would I go about splitting the catOrder field so that I can order by this field?

Thanks,

Chris
0
Comment
Question by:djfenom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 40

Expert Comment

by:gurvinder372
ID: 37751852
Or you can update your table structure in this way

prodId   prodName   prodCat   order
1            test              2              1  
2            test 2           2              2
3            test 3           1              1
4            test 4           1              2
5            test 5           2              3

which will make your sql query quite simple, isn't it?
0
 

Author Comment

by:djfenom
ID: 37751884
Unfortunately the tables are already in place and there are multiple categories which need an order for each one, the only way this would work is if there was a column for each category but this isn't feasible as there is an indefinite number.
0
 
LVL 40

Expert Comment

by:gurvinder372
ID: 37751901
Not sure, if there is a way to do it through a query. You will have to get the column value, split the same, and then fetch the products details for an id after iterating through all the id.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 24

Expert Comment

by:johanntagle
ID: 37752051
You need to rearchitecture the database.  The way to properly handle multiple related values is to put it to a seperate table.  Something like a category_orders table the has catID and catOrder as columns, and you have one row each for every CatID-catOrder combination.  Then you can just join this table to your query.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 37753455
Unfortunately the tables are already in place
Not really a problem.  You can change data base tables easily with ALTER TABLE.  In this case you should change the tables.  Going forward, you will never again want to have more than one data element in each cell.
0
 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 37753678
I am not saying this is efficient, just that it should work for what you want. A lot of times reworking a database after an entire system is in place is just not useful or possible especially if it was a purchased system and changing it will affect other purchased software that runs off of it.

If this is a case of a system currently being developed or one that you or your company has permission to modify then you should try to normalize your database to at least 3rd normal form so you don't run into these problems in the future.


To handle your current situation if my hands were completely tied against changing the database, I would do something like the following

 I am going to make a few assumptions in this

1. you are using mysql_query to access your database
2. you don't want to be hacked via url parameters

also, this is off the top of my head and not tested.


$catId = mysql_real_escape_string($_GET['cat']);
$strSQL = "SELECT store.*, cat.* FROM (store LEFT JOIN cat ON store.prodCat = cat.catID) WHERE store.prodCat = '$catId'";

$result = mysql_query($strSQL);

if(mysql_num_rows($result)>0)
{
        $row = mysql_fetch_array($result);
        $ordervals = $row['catOrder'];
        $order = explode(",",$ordervals);
         mysql_data_seek($result,0); 
}

//you now have an array that denotes the order to display your fields.
$index = 0;
while($row = mysql_fetch_array($result))
{
     if($index < mysql_num_rows($result))
    {
         if($row['prodId'] == $order[$index])
         {
               //add code here to display your data
               $index++;  
               mysql_data_seek($result,0);  //set mysql result pointer back to beginning
     }
 }

Open in new window

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 37753741
this should do (untested ...)
SELECT s.*, c.* , p.*
FROM store s
LEFT JOIN cat c
  ON s.prodCat = c.catID
LEFT JOIN Product p
  ON p.prodcat = c.catid
WHERE s.prodCat = '" . $_GET['cat'] . "'
ORDER BY INSTR( CONCAT(',', c.catOrder,',') , CONCAT(',', p.ProdID, ',') ) 

Open in new window

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_instr

but I agree that the design is bad ...
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this tutorial viewers will learn how to code links for mobile sites that, once clicked, send a call or text to a specified number. For a telephone link (once clicked, calls a number), begin with a normal "<a href=" link tag. For the href, specify…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question