SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 37
  1. #1
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    563
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    How to get unique data ?

    Hi,

    I have a database in which I have stored url's. For eg:

    http://www.yahoo.com
    http://www.google.com
    http://www.yahoo.com/somepage.htm
    http://www.google.com/mypage.htm
    http://www.msn.com
    http://www.sitepoint.com
    http://www.sitepoint.com/forum

    Now currently i have this simple query:

    "select * from links ORDER BY RAND() LIMIT 0, 12"

    Now this gets me url's randomly.

    What I want to achieve is that I should get random url's but the domain shoudl not be same.

    For eg. using the above data the result should be:

    http://www.yahoo.com
    http://www.google.com
    http://www.msn.com
    http://www.sitepoint.com

    I should only get unique links. Domain should not repeat. How to achieve this ?

    Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    easiest solution: store the domain separately when you add a new row

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    563
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi

    Any other way ? Using php regex or something ?

    Thanks.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    well, sure, that's a suggestion, but you'll have to pull way more than 12 links in order to be sure that you find 12 unique domains within them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    563
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Can i have an example with regex ?

    Thanks.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    moving thread to php forum

    if you do figure out a good regex to do this, you might consider using it when you add the row (in an extra column as i suggested), rather than when you retrieve the row
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    563
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    So you're suggesting that I do the following ?

    id, domain, url

    and data will be like:

    1, http://www.yahoo.com,
    2, http://www.google.com,
    3, http://www.yahoo.com, /somepage.htm
    4, http://www.google.com, /mypage.htm
    5, http://www.msn.com,
    6, http://www.sitepoint.com
    7, http://www.sitepoint.com, /forum


    Is that correct ?

    Thanks.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    more like this --
    Code:
    id domain        url
     1 yahoo.com     http://www.yahoo.com
     2 google.com    http://www.google.com
     3 yahoo.com     http://www.yahoo.com/somepage.htm
     4 google.com    http://www.google.com/mypage.htm
     5 msn.com       http://www.msn.com,
     6 sitepoint.com http://www.sitepoint.com
     7 sitepoint.com http://www.sitepoint.com/forum
     8 foo.co.uk     http://www.foo.co.uk/summat
    notice the last one, and the problem you may have with certain "domains"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    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)
    That would be the preferred option, otherwise you would have to pull all entries back and process them in PHP....Quite daunting.

    It would be much easier to control each entry as it was added.
    @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.

  10. #10
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    563
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    And how will i get unique url's by using that structure ?

    Thanks.

  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)
    Code:
    SELECT DISTINCT(domain) FROM table ORDER BY RAND() LIMIT 12
    @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
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Tapan View Post
    And how will i get unique url's by using that structure ?
    SELECT DISTINCT domain FROM links ORDER BY RAND() LIMIT 0, 12
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    <?php while(!sleep()){code();} G.Schuster's Avatar
    Join Date
    Mar 2007
    Location
    Germany
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT *, SUBSTR(SUBSTR(`link`, 7), 0, (LOCATE('/', SUBSTR(`link`, 7)) - 1)) AS `domain` FROM `links` GROUP BY `domain` ORDER BY RAND() LIMIT 0, 12

    May not be perfect but should work as it groups the links by domain.
    Not sure if it still randomly orders the links within the same domain - just try it.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    aarrggghhh, sniped by a couple of seconds

    SilverB, please do not use parentheses like that

    DISTINCT is not a function !!1!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  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)


    Who am I to argue with the infamous r937?

    From now on, no more parentheses from this man.
    @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
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i would have preferred famous to infamous, but oh well...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    563
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    And how would i seperate the domain from the full url whcih is entered by the user ? I want to keep this domain field completely transparent to the user.

    Thanks.

  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)
    You would have to parse the supplied domain upon submission.
    @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
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    563
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Ofcouse, but how ? I mean should i look for the 1st "/" in the url provided ?

    Thanks.

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no, actually the second, then go backwards from there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    563
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    HI,

    This seems to be working fine, any suggestions ?

    Code:
    <?php
    $urls[] = "http://www.yahoo.com";
    $urls[] = "http://www.google.com";
    $urls[] = "http://www.yahoo.com/somepage.htm";
    $urls[] = "http://www.google.com/mypage.htm";
    $urls[] = "http://www.msn.com";
    $urls[] = "http://www.sitepoint.com";
    $urls[] = "http://www.sitepoint.com/forum";
    $urls[] = "http://www.foo.co.uk/summat";
    
    for ($i = 0; $i <= count($urls) - 1; $i++)
    {
            $url = str_replace ("http://", "", $urls[$i]);
            $url = str_replace ("www.", "", $url);
        
            //lets do it now
            $pos = strpos($url, "/");
    
            if ($pos === false)
            {
                    echo $url . "<br />\n";
            }
            else
            {
                    $url = substr ($url, 0, $pos);
                    echo $url . "<br />\n";
            }
    }
    ?>
    Thanks.

  22. #22
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can use php's parse_url()

    Be aware you don't really have an easy way to seperate a domain from a subdomain. r937 was hinting at this. Hopefully its acceptable to you for these to be considered unique, and potentially have each in the result list.
    Code:
    example.com
    www.example.com
    foo.example.com
    You may think you can extract the xxx.xxx part on the right, but foo.example.co.uk throws a wrench in that approach. You would need to maintain a list of all TLD's and keep it updated if you want to properly parse out the domain from the subdomain.

    You may also want to consider what you should do with the user, pass, and port components.

  23. #23
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    563
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    This also achieve's the same:

    Code:
    for ($i = 0; $i <= count($urls) - 1; $i++)
    {
            $url = parse_url($urls[$i]);
            echo str_replace ("www.", "", $url["host"]) . "<br />\n";
    }
    I guess its better to use this one. Also my links won't be that complicated with port etc. just normal links.

    Thanks for all the help and stuff.

  24. #24
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    563
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I am using this query:
    SELECT DISTINCT domain FROM links ORDER BY RAND() LIMIT 0, 12

    but now i am getting only the domain field and id, and url field is not coming. How to get ?

    Thanks.

  25. #25
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    563
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Okay fixed by doing this:

    SELECT DISTINCT domain, url, linktext FROM links ORDER BY RAND() LIMIT 0, 12

    Thanks.


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
  •