SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Huge problem for me! Please help if you can!

    I need to:

    Code MySQL:
    1. SELECT column_name FROM columns WHERE product_id='5'
     
    2. SELECT value FROM atributes WHERE product_id=5
    3. SELECT value FROM atributes WHERE product_id=8
    1+2+3 = image posted.

    I need to merge values from two different products with same column for both in one table.

    HTML TABLE:

    HTML Code:
    <table>
    [mysql select]
    <tr>
    <td>Column</td>
    <td>1st product value</td>
    <td>2nd product value</td>
    </tr>
    [/mysql select]
    </table>


    I cannot solve this for days... Please help me if you can!!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    column name? atributes? articles? values?

    i think maybe you should try to explain in english what the database is supposed to do for you

    if you're storing column names in a user table (this is called meta-data) there's a very good chance that you have a poor design
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    column name? atributes? articles? values?

    i think maybe you should try to explain in english what the database is supposed to do for you

    if you're storing column names in a user table (this is called meta-data) there's a very good chance that you have a poor design
    Yes i think i have poor design (not by me) but i need next:

    Code MySQL:
    SELECT column_name FROM table_columns(etc..) UNION SELECT value FROM atributes WHERE product_id='5'  UNION SELECT value FROM atributes WHERE product_id='8'

    HTML Code:
    <table>
    [mysql results loop row by row]
    <tr>
    <td><?= $row['column_name']; ?></td>
    <td><?= $row['value1']; ?></td>
    <td><?= $row['value2']; ?></td>
    </tr>
    [/mysql results loop row by row]
    </table>

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i have no idea what you're doing and why
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i have no idea what you're doing and why
    I need to compare two products by their atributes (values)

    RAM : 1 GB (for selected product) : 2 GB (for another product)
    VGA : 1024 MB (for selected product) : 512 MB (for another product)


    RAM & VGA are columns that are stored in one table: product_columns

    1 GB, 2 GB, 1024 MB and 512 MB are values for columns that are stored in product_atributes

    I need to select IN ONE QUERY columns (name) and atributes for 2 different products...

    Simply: i need to compare two products by their atributes and show column name for every row of product atribute


    ...........PRODUCT #1........PRODUCT #2
    [ram]....1 GB....................2 GB
    [vga]....1024 MB...............512 MB


    Now?!

  6. #6
    SitePoint Enthusiast NathanaelB's Avatar
    Join Date
    Dec 2006
    Location
    Canberra, Australia
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't claim that I understand your question - but I suspect that perhaps you're having trouble accessing the query values because the column name is the same. You could access the array of the SQL result in PHP, or you could use the AS keyword in SQL to map an alias to each of the values.

    That's probably not what you were looking for, but I'll throw it out there ...
    Nathanael Boehm
    Web user interaction and UX designer, social media consultant
    Links: bio, linkedin, twitter, flickr

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by iDeaStuff View Post
    Now?!
    nope

    i suspect you've over-engineered a simple problem into a monstrous problem

    why are you storing column names?

    could you do a SHOW CREATE TABLE for both tables please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    THREE tables IN TOTAL because third 'attributs_columns' are some kind of linkage between columns and values..


    Code MySQL:
    CREATE TABLE IF NOT EXISTS `attributs` (
      `id` int(11) NOT NULL auto_increment,
      `sid` int(11) NOT NULL default '0',
     
      `parent__atribut_id` int(11) default NULL,
      `atribut_type_id` int(11) default NULL,
     
      `title` varchar(200) default NULL,
     
     
      `parent__atribut__sid` int(11) default NULL,
     
     
      PRIMARY KEY  (`id`,`sid`),
     
      KEY `IX_Relationship458` (`parent__atribut_id`,`parent__atribut__sid`),
      KEY `IX_Relationship356` (`atribut_type_id`,`atribut_type__sid`),
      KEY `IX_Relationship762` (`sid`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1058 ;


    Code MySQL:
    CREATE TABLE IF NOT EXISTS `attributs_columns` (
      `id` int(11) NOT NULL auto_increment,
      `sid` int(11) NOT NULL default '0',
     
      `attribut_id` int(11) NOT NULL default '0',
      `parent__attribut_id` int(11) default NULL,
     
      PRIMARY KEY  (`id`),
     
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2664 ;

    Code MySQL:
    CREATE TABLE IF NOT EXISTS `attributs_values` (
      `id` int(11) NOT NULL auto_increment,
      `
      `atribut_column_id` int(11) NOT NULL default '0',
      `product_id` int(11) NOT NULL default '0',
     
      `txt_val` varchar(200) default NULL,
     
      `atribut_column__sid` int(11) NOT NULL default '0',
     
     
      PRIMARY KEY  (`id`),
      UNIQUE KEY `ix_atribval_location` (`atribut_column_id`,`atribut_column__sid`,`doc_id`,`doc__sid`),
      KEY `IX_Relationship1638` (`firma_id`,`firma__sid`),
      KEY `IX_Relationship1639` (`sid`),
      KEY `IX_Relationship1647` (`atribut_column_id`,`atribut_column__sid`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=121620 ;

    Needed result:

    I need to compare two products by their values and show column name.

    .........................PRODUCT 1..........PRODUCT 2
    Column name:..........Value..................Value

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm afraid i can't help you, that's ~way~ too complicated for me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •