SitePoint Sponsor

User Tag List

Page 1 of 3 123 LastLast
Results 1 to 25 of 64
  1. #1
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Appreciate help with PHP MySql Query problem

    Hi

    I have a product database which contains a table "product" with fields "id" "name" and "value". "id" is incremental to each product added.

    My problem is that I need to query the table to produce the result "SELECT "value" WHERE name="foo" but both fields contain 22 entries per product. The name field remains the same for each entry but the value obviously changes for each product along with the id.

    For example id="1" name="Colour" Value="Blue"
    id="1" name="Size" Value="Large"
    id="1" name="Stock" Value="Yes"
    id="2" name="Colour" Value="Red"
    id="2" name="Size" Value="Small"
    id="2" name="Stock" Value="No"

    How do I go about getting results from one field dependent upon the contents of another in the same table to return multiple results?

    I then need to add all the results to an array "id="1" "Colour" ="Blue", "id="2" "Colour" ="Red" etc

    I hope I have provided enough information for somebody to help with this. I have tried to work this one out but it has got to the stage where the solution could be looking me in the face but I am unable to see it. I would appreciate some help with this.

    Thanks in advance

    C

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ColinHughes View Post
    How do I go about getting results from one field dependent upon the contents of another in the same table to return multiple results?
    huh? could you clarify what you mean here?

    by the way, your design is usually called EAV and it is generally regarded as a convenient and simple way to store data, but a nightmare to obtain anything meaningful efficiently
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Dave

    EAV? I am sure I could think of better ways to describe it, I did not create this DB by the way and changing its design its not an option.

    I am trying to create an XML feed from this so basically need to create an array than I can use thus:

    <colour>$colour</colour>
    <size>$size</size> etc.

    My real issue is not having fields called colour, size, stock etc they are all included in their own field (name). So how can I write a query that gives me results that I can put into an array to get the correct info from?

    "How do I go about getting results from one field ("value") dependent upon the contents of another ("name") in the same table ("product") to return multiple results?"

    Does this make more sense

    C
    Last edited by ColinHughes; Sep 30, 2009 at 03:18. Reason: addition

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i don't do php but my understanding is that any query result can be put into an array

    by the way, my name's not dave
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Firstly apologies Rudi I saw Dave's guide to the EAV in your signature and made a wrong assumption.

    Yes the PHP Array part is not a problem it is the actual query that I cannot get my head around and where I need some help.

    C

  6. #6
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am beginning to think this is a no can do!!

    I have attached an image of my DB to make things a little clearer. I HOPE!!

    Regards

    C
    Attached Images Attached Images

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ColinHughes View Post
    "How do I go about getting results from one field ("value") dependent upon the contents of another ("name") in the same table ("product") to return multiple results?"
    Code:
    SELECT name, value
      FROM product
     WHERE id = 42
    this returns all attributes for id=42

    i'm not sure if this is what you want...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem is if I do that how can I associate the value with the name? This query would just return all the contents of the fields for a specific id.

    Had I personally structured this database I would have created it with Colour, Size, Stock etc as field names then Colour for instance could have been given a value relative to the id. Technically this may be incorrect but at least it would be possible to return the results I require.

    I could have then given product1 an id of 1 have it available in 3 colours 3 sizes and 3 stock levels. The same for product2 ad infinitum.

    See attached image file for example of this

    The current structure of the database seems to make that impossible and as I have said it cannot be changed.


    Thanks for your patience with this

    C
    Attached Images Attached Images

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ColinHughes View Post
    This query would just return all the contents of the fields for a specific id.
    can't you make an array out of that?

    i wish a real php programmer would step in here...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Try this.
    PHP Code:
    <?php
    $result 
    mysql_query(
        
    sprintf(
            
    'SELECT * FROM table WHERE id = %d',
            
    3
        
    )
    );
    $products = array();
    while(
    $record mysql_fetch_assoc($result))
    {
        foreach(
    $record as $key => $value)
        {
            
    $products[$record['id']][$key][] = $value;
        }
    }
    /*
        array(
            3    => array(
                'colour'    => array(
                    'blue',
                    'yellow'
                ),
                'size'        => array(
                    'small',
                    'massive'
                )
            )
        )
    */
    ?>
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  11. #11
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Actually, why can't you do the following?
    PHP Code:
    <?php
    $result 
    mysql_query(
        
    sprintf(
            
    'SELECT * FROM table WHERE id = %d',
            
    3
        
    )
    );
    $products = array();
    while(
    $record mysql_fetch_assoc($result))
    {

        
    array_push(
            
    $products,
            
    $record
        
    );
    }
    /*
        array(
            array(
                'id'        => 3,
                'colour'    => 'blue',
                'size'        => 'medium',
                'in_stock'    => 'yes'
            ),
            array(
                'id'        => 3,
                'colour'    => 'red',
                'size'        => 'medium',
                'in_stock'    => 'no'
            )
        )
    */
    ?>
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  12. #12
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply. I know have this:

    PHP Code:
    <?php

    include("includes/conn.php");   

    $result mysql_query(
        
    sprintf(
            
    'SELECT * FROM table WHERE id = %d',
            
    3
        
    )
    );
    $products = array();
    while(
    $record mysql_fetch_assoc($result))
    {

        
    array_push(
            
    $products,
            
    $record
        
    );
    }

    echo 
    "<pre>";
    print_r($products);
    echo 
    "</pre>";
    ?>
    I get the following error message:

    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in feed.php on line 12
    Array
    (
    )


    I am not familiar with modulus!! Gently please

    Regards

    C
    Last edited by ColinHughes; Sep 30, 2009 at 09:50. Reason: Update code

  13. #13
    SitePoint Zealot Kayarc's Avatar
    Join Date
    Sep 2009
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php

    include("includes/conn.php");   

    $result mysql_query(
        
    sprintf(
            
    'SELECT * FROM table WHERE id = %d',
            
    3
        
    )
    );

    var_dump($result); /*WHAT DOES THIS RETURN?*/
    Phoenix Arizona Web Design | info *at* kayarc.com | 602.633.2676

  14. #14
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    bool(false)

  15. #15
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I'm assuming you've renamed table in the SQL string to your table name?

    'product' wasn't it?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  16. #16
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes

    PHP Code:
    <?php

    include("includes/conn.php");   

    $result mysql_query(
        
    sprintf(
            
    'SELECT * FROM products WHERE id = %d',
            
    3
        
    )
    );

    var_dump($result);
    ?>

  17. #17
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok with this

    PHP Code:
    <?php

    include("includes/conn.php");   

    $result mysql_query(
        
    sprintf(
            
    'SELECT * FROM product WHERE id = %d',
            
    3
        
    )
    ) or die(
    mysql_error());
    $products = array();
    while(
    $record mysql_fetch_assoc($result))
    {

        
    array_push(
            
    $products,
            
    $record
        
    );
    }

    echo 
    "<pre>";
    print_r($products);
    echo 
    "</pre>";
    ?>
    I get no error but the array is empty

    Array
    (
    )

  18. #18
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php
    include("includes/conn.php");
    $result mysql_query(
        
    sprintf(
            
    'SELECT * FROM products WHERE id = %d',
            
    3
        
    )
    );
    if(
    false === is_resource($result))
    {
        echo 
    mysql_error();
        exit;
    }
    $products = array();
    while(
    $record mysql_fetch_assoc($result))
    {

        
    array_push(
            
    $products,
            
    $record
        
    );
    }
    echo 
    '<pre>'print_r($productstrue), '</pre>';
    ?>
    ...and this Colin?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  19. #19
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ColinHughes View Post
    Ok with this

    PHP Code:
    <?php

    include("includes/conn.php");   

    $result mysql_query(
        
    sprintf(
            
    'SELECT * FROM product WHERE id = %d',
            
    3
        
    )
    ) or die(
    mysql_error());
    $products = array();
    while(
    $record mysql_fetch_assoc($result))
    {

        
    array_push(
            
    $products,
            
    $record
        
    );
    }

    echo 
    "<pre>";
    print_r($products);
    echo 
    "</pre>";
    ?>
    I get no error but the array is empty

    Array
    (
    )
    Ah, progress!

    So, do you actually have a product in the specified table with an id of 3 ?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  20. #20
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Array
    (
    )

    Empty Array

  21. #21
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SilverBulletUK View Post
    So, do you actually have a product in the specified table with an id of 3 ?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  22. #22
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I now have this:

    Array
    (
    [0] => Array
    (
    [id] => 3
    [name] => Colour
    [value] => Green
    )

    [1] => Array
    (
    [id] => 3
    [name] => Size
    [value] => Large
    )

    [2] => Array
    (
    [id] => 3
    [name] => Stock
    [value] => Yes
    )

    )

    Excellent thank you very much but i am still unsure that the content of this array will do what I want!

    Now I need to put the values of each product into my feed then loop around for the next product and so on. This is the part that is giving me the real dilemma

    Colin

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    change this --
    Code:
    'SELECT * FROM product WHERE id = &#37;d'
    to this --
    Code:
    'SELECT * FROM product ORDER BY id,name'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Can you provide a sample of the XML you wish to create covering 2 products?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  25. #25
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    <?xml version="1.0" encoding="UTF-8"?>
    <root>

    // This part loops through the products
    <id></id>
    <colour></colour>
    <size></size>
    <stock></stock>

    </root>

    Colin


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
  •