Jump to content


Photo

SQL query - HELP!


  • Please log in to reply
6 replies to this topic

#1 Freid001

Freid001

    Advanced Member

  • Members
  • PipPipPip
  • 114 posts
  • LocationUK
  • Age:19

Posted 19 June 2013 - 07:19 AM

Hi, I need help with a SQL query. However i'm not sure if its possible to do what I want in one query. 

Basically have two tables one called items and one called user_items. In Items there is a list of items each with its own id. In user items there is the users id, account name and then a column number that represents the id of an item in items. For example.

 

Items Table:

id | Item        |

1  | Cheese  |

2  | Ham       |

3  | Tomato  |

 

User_items Table:

id | account | i1 | i2 | i3 |

1  | freid001| 0  |  1 | 5  |

 

I basically want to run one query which will return the number of items that a account has. So using the example I want this to show: 

 

items.id | User_items.account | {NUMBER OF ITEM}

1           | fried001                   | 0 

2           | fried001                   | 1

3           | fried001                   | 5 

 

Here is what I have done so far, i don't think it is right

SELECT `Items`.id, `user_items.account`, `Items`.Item, `user_items`. {GET THE COLUMN THAT HAS THE SAME NUMBER ID AS THE ITEM.ID}
FROM  `Items`, `user_items`
WHERE `user_items`.account = 'freid001';

Edited by Freid001, 19 June 2013 - 07:19 AM.

Thanks for any help it is much appreciated!

#2 trq

trq

    Advanced Member

  • Administrators
  • 30,723 posts
  • LocationSydney, Australia.

Posted 19 June 2013 - 07:29 AM

Nope, it's not at all right. MSSQL doesn't like `backticks` at all.

http://thorpesystems.com | http://proemframework.org | http://github.com/trq

SmtpCatcher - A very simple mock sendmail useful for testing PHP mail scripts.
OPM - My Linux package manager.


#3 Boxerman

Boxerman

    Advanced Member

  • Members
  • PipPipPip
  • 218 posts

Posted 06 August 2013 - 09:59 PM

Sorry, to add something else to the questions.

 

When you say "I basically want to run one query which will return the number of items that a account has." did you want to output * or did you want to COUNT ? 



#4 mikosiko

mikosiko

    this mat is so comfy!!

  • Members
  • PipPipPip
  • 1,260 posts

Posted 07 August 2013 - 09:06 AM

You should start by normalizing your tables... the fact that you have "i1, i2, i3" in your user_items table means that it is not correctly designed and will bite you sooner or later... I better design could be:

Items Table:   (it is ok)

id | Item        |

1  | Cheese  |

2  | Ham       |

3  | Tomato  |

 

Users table  ( the items that the user has doesn't belong here... hence table is normalized)

id | user_name (?)        

1  | freid001  

 

Users_Items Table : (this must contain as many rows for a user as items he has)

user_id | item_id| item_number

1           | 2         |  1     (user 1 has 1 of item# 2)

1           | 3         |  5     (user 1 has 5 of item# 3)

 

now just make your select's JOINing the tables depending of your objectives


 
ini_set("display_errors", 1);
error_reporting(-1);

#5 dogdaynoon

dogdaynoon

    Newbie

  • New Members
  • Pip
  • 7 posts

Posted 19 August 2013 - 03:13 PM

could you put all the items in an array and then put that in the user_items table in one column? assuming that the user is selecting items from a list...?

$items = $_POST['checked_items_checkboxes'];



#6 Freid001

Freid001

    Advanced Member

  • Members
  • PipPipPip
  • 114 posts
  • LocationUK
  • Age:19

Posted 26 January 2014 - 03:36 PM

Sorry, to add something else to the questions.

 

When you say "I basically want to run one query which will return the number of items that a account has." did you want to output * or did you want to COUNT ? 

Yes I want to output. Not count.


Thanks for any help it is much appreciated!

#7 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 13,885 posts
  • LocationCheshire, UK

Posted 14 February 2014 - 09:24 AM

I agree with Mikosiko. That is one of the worst table designs I've seen in a long time. SQL tables are NOT spreadsheets.

 

With that design you will need to use UNIONS

mysql> use idiot;
Database changed
mysql> SELECT ui.account, i.id, i.item, i1 as numItems
    ->     FROM items i JOIN user_items ui ON i.id = 1
    -> UNION
    -> SELECT ui.account, i.id, i.item, i2 as numItems
    ->     FROM items i JOIN user_items ui ON i.id = 2
    -> UNION
    -> SELECT ui.account, i.id, i.item, i3 as numItems
    ->     FROM items i JOIN user_items ui ON i.id = 3;
+----------+----+--------+----------+
| account  | id | item   | numItems |
+----------+----+--------+----------+
| freid001 |  1 | Cheese |        0 |
| freid001 |  2 | Ham    |        1 |
| freid001 |  3 | Tomato |        5 |
+----------+----+--------+----------+


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com