CODEWITHSUNDEEP
phpmysql
Jen
Jen

Reputation: 43

Multiple Parameters in one column Value--One Query

I have a table of Footwer Styles that fall into different, corresponding footwear Categories. What I'm trying to do is list styles under different (separate) categories.

Styles available for SANDALS are: Flip Flops Slide

Styles available for SHOES are: Athletic Lace-up Loafers Moccasins

As you can see below, right now, I'm just reiterating my queries with the different TYPEIDs.

Is there a more code-efficient way to do this with just one query?

Just as the title of my question states: Can I do Multiple Parameters in one column Value--One Query?

Thanks in advance, Jen

+--------+---------+
| typeid | type    |
+--------+---------+
|      1 | Shoes   |
|      2 | Boots   |
|      3 | Sandals |
+--------+---------+

+---------+--------+------------+
| styleid | typeid | style      |
+---------+--------+------------+
|       1 |      1 | Athletic   |
|       2 |      1 | Lace-up    |
|       3 |      1 | Loafers    |
|       4 |      1 | Moccasins  |
|       5 |      2 | Combat     |
|       6 |      2 | Hiking     |
|       7 |      2 | Riding     |
|       8 |      3 | Flip Flops |
|       9 |      3 | Slide      |
+---------+--------+------------+

Styles_in_Cat.php

echo " Styles available for SANDALS are: <br/>";

$query="SELECT DISTINCT style, style.typeid, type 
FROM style, type
WHERE style.typeid=3
AND style.typeid=type.typeid";
if ($result = mysqli_query($con,$query))
 {
    while ($row = mysqli_fetch_assoc($result))    
      {        
          $style = $row['style'];
          $typeid = $row['typeid'];
          $type = $row['type'];

          echo " $style <br/>" ;
      }
 }


echo " Styles available for SHOES are: <br/>";

$query="SELECT DISTINCT style, style.typeid, type
FROM style, type
WHERE style.typeid=1
AND style.typeid=type.typeid";
if ($result = mysqli_query($con,$query))
 {
    while ($row = mysqli_fetch_assoc($result))    
      {        
          $style = $row['style'];
          $typeid = $row['typeid'];
          $type = $row['type'];

          echo " $style <br/>" ;
      }
 }

UPDATE: I think I'm getting there. I put them in a foreach loop and got:

Boots - Combat Boots - Hiking Boots - Riding Sandals - Flip Flops Sandals - Slide Shoes - Athletic Shoes - Lace-up Shoes - Loafers Shoes - Moccasins

Now just have to try and separate them and show under the different headers like "Styles available for SHOES are:"

UPDATE 2:

I've been trying to place the results in arrays for the past 4 hours but have so far been unsuccessful :(.

I had this result from (as suggested by xpy):

SELECT DISTINCT type, style.typeid, style FROM style, type
WHERE style.typeid IN ( 1,3)
AND style.typeid=type.typeid
+---------+--------+------------+
| type    | typeid | style      |
+---------+--------+------------+
| Shoes   |      1 | Athletic   |
| Shoes   |      1 | Lace-up    |
| Shoes   |      1 | Loafers    |
| Shoes   |      1 | Moccasins  |
| Sandals |      3 | Flip Flops |
| Sandals |      3 | Slide      |
+---------+--------+------------+

but what I'd like to do is control which TYPE APPEARS (even if WHERE CLAUSE states 1,3,5 (typeid) --maybe I want to show just 1 and 3).

I've read a few threads but they have the same results as I do.

stackoverflow.com/questions/3936073/mysql-php-selecting-only-unique-values-from-multiple-columns-and-put-them-into-s?rq=1

stackoverflow.com/questions/5345011/fetch-all-ids-and-put-them-into-an-array-in-php

I hope I'm asking the questions correctly. I know I may sound lost, because I AM.

Basically I'm am trying to get to what xpy said: "you can separate them, you can identify them by their by typeid." "You'll have to put your results into separate arrays one for every type or into a multidimensional array with one index for every type, of course you'll have to check your result, then put it in the specific array."

So my browser view would look like:

OUR AVAILABLE STYLES IN SHOES: Athletic Lace-up Loafers

OUR AVAILABLE STYLES IN BOOTS: Combat Hiking Riding

I can just stick with what I have originally (in first question) but being a programming newbie, I really want to learn.

Thanks again. Jen

Upvotes: 1

Views: 152

Answers (2)

Azathoth
Azathoth

Reputation: 582

You have to use GROUP BY

something like

SELECT style.style, type FROM style, type where type.typeid=style.typeid
GROUP BY type, style

you should have a resultset like

Shoes   Athletic
Shoes   Lace-up
Shoes   Loafers
Shoes   Moccasins
Boots   Combat

..

simply cycle 'till type changes to read all different style. This way you can read all styles and types with one query

Upvotes: 2

xpy
xpy

Reputation: 5621

You Could try:

SELECT DISTINCT style, style.typeid, type 
FROM style, type
WHERE style.typeid IN (1,3)
AND style.typeid=type.typeid

Which will give you all the rows for both shoes and sandals BUT due to DISTINCT you won't be able to identify if a style is common style for both of them ( if it will ever be one ).

Upvotes: 0

Related Questions