SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    returning one result

    Can anyone help me I wonder..

    ..I'm trying to return a group of results - in fact I just want to return one result in what is returned from the query but not necessarily the first. So to return a group of results I would normally put:

    Code PHP:
    while($row = mysql_fetch_array( $sql )) { **actions of the condition**}

    but what if I wanted to return the 4th result of this query. I hope everyone understands and someone is able to help.

    _thanks

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use LIMIT 3, 1 in the sql query.

    If you can't, see mysql_data_seek()

  3. #3
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks.. this seems like good advice >> actually the real reason I'm trying to find this out is to prevent returning the same result twice - do you think that this would be the best way? or is there something easier?

    _thanks

  4. #4
    Web Professional
    Join Date
    Oct 2008
    Location
    London
    Posts
    862
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please elaborate. It seems to me like there is a better way to achieve whatever it is you want to achieve.

  5. #5
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure how to elaborate on that but I'll try...

    ..say you have a simple table like this:

    Id_____Model_____Price (&#163

    1_______Volvo_______9999
    2_____Volkswagon____8499
    3______Cortina_______8499

    If I wanted to return all different prices for this table, i.e. £9999, £8499 - these would be the result >> the Volkswagon and Cortina prices are the same so they would only return once - how could I do that?

    _thanks

  6. #6
    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)
    Code Oracle8:
    SELECT DISTINCT price FROM cars
    @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.

  7. #7
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry what would that be with a mysql database?

  8. #8
    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)
    It would, possibly other SQL databases too.
    @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.

  9. #9
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks - quick & helpful information

    I have noticed that it only works when I use the 'GROUP BY' feature. This is proving to be a little more difficult than hoped because I am actually using another table within the WHILE loop to get even more information and unfortunately this cannot be avoided. Here's another example:

    We'll call this table CATS

    catId_____category

    __1_______Brand New
    __2_______Second-Hand

    ..now remembering the first table (modified) which we'll call CARS

    carId_____model__________category_____________catId___parentId___warranty

    __1_____Vauxhall__________More than 10 years____12_____2_________________Yes
    __2_____Volkswagon_______Less than 5 years______8______2_________________No
    __3_____Porsche__________Less than 2 years______5_______2_________________Yes

    ..so we have two main categories - 'Brand New' and 'Second-Hand'. But these have sub-categories - here, these models displayed (in CARS) show only 'Second-Hand' sub categories. To clarify the sub-categories of 'Second-Hand': they are 'More than 10 years', 'Less than 5 years' and 'Less than 2 years'. So in attempting to return the models with warranty displaying firstly the sub-categories and then their categories - I have something like this:

    PHP Code:
        $first mysql_query("select DISTINCT model, carId, parentId
                  from  CARS
                  where  warranty = 'Yes' GROUP BY model"
    );
        
        while(
    $row mysql_fetch_array$first )) {
        
        
    $first_model $row['model'];
        
    $first_cat_id $row['catId'];
        
    $first_parent_id $row['parentId'];

    // The 2nd query to get the main category names of the models returned    

        
    $second mysql_query("select DISTINCT category, catId
                  from  CATS
                  where  catId = '" 
    $first_parent_id "' GROUP BY category");
        
        while(
    $row mysql_fetch_array$second )) {

        
    $second_cat $row['cat'];

    echo 
    $first_model $first_cat_id $second_cat;

            }


    This is the sort of situation that I'm facing - again I hope everyone understands and someone can help once more - so far you have helped me immensely.

    _thanks

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you have two problems which are needlessly complicating matters for you

    first, you seem to be doing a query within a loop -- that is almost always a very bad idea

    second, you have an unnormalized category relationship inside your product table

    the product (car) should be linked only to its (sub)category, and if these are further categorized into larger categories, this should be done in a separate table

    have a look at Categories and Subcategories for some ideas
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Off Topic:

    A Cortina ??? you stuck in the 70's


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
  •