How we can assign a value in mysql query

Question

Hi
How we can assign a value in the query
what i need is assume that i write a query for output result using match against i am getting a bunch of result like

http://www.flipkart.com/account.php 
http://www.thomas.com/account.php
http://www.flipkart.com/account22.php
http://www.flipkart.com/account45.php
http://www.thomas.com/account22.php
http://www.thomas.com/account45.php 

etc..... What i am asking. i need only 2 result from each domain.Can i use like this

Step 1:Set DomainName=http://www.flipkart.com/ and Let Rank =0
Step 2:DomainNameofResult= DomainNameofR(http://www.flipkart.com/account.php)
(finding the domain name of result using substring or regular expression)
Step 3:if DomainName=DomainNameofResult then 
Add the http://www.flipkart.com/account.php to result and set rank =1
else
avoid the current domain

And Re start from step 1,in restarting if http://www.flipkart.com/account22.php comes,On step 3 Add the http://www.flipkart.com/account22.php to result and set rank = 2 after rank is set to 2 if any other result came with domain name http://www.flipkart.com/ it should be avoid.

can i write query for this??how??can you guys gave me an example?

Answer 1

I would split the URL into a few fields for this. Likely "domain" and "path"

This would give you the following

domains_table

domain           | path
-----------------+-------------
www.flipkart.com | /account.php 
www.thomas.com   | /account.php
www.flipkart.com | /account22.php
www.flipkart.com | /account45.php
www.thomas.com   | /account22.php
www.thomas.com   | /account45.php

Now, you can do things like:

SELECT DISTINCT domainname FROM domain_tables

Followed by a loop of (if PHP is your end language):

foreach($domain as $d){
    mysql_query("SELECT CONCAT("http://",domain,path) as url FROM domains_table WHERE domain=? LIMIT 2", $d);
     //Then store the result somewhere
}

You can also probably use some joins to do the same, but I'm not sure how to do that off the top of my head.

Answer 2

The task of extracting the domain name out of the URL is the biggest challenge, and would be more performant if you could add a new column to the table - domain or similar. This would make querying a lot easier in the future. You could even use the following function to UPDATE on that new column if possible.

Barring that, implement a user defined function in MySQL, similar to this one from "How to Parse a Domain Name from a URL using SQL".

CREATE FUNCTION `extract_domain_from_url`(url VARCHAR(5000)) RETURNS varchar(500)
BEGIN

   DECLARE output_domain VARCHAR(500);

   --implementation
   RETURN output_domain;

END

Once you do that, then (assuming you have an ID or something else that judges the order):

SELECT *
FROM     MyTable AS o
WHERE 
        (SELECT  COUNT(*) 
        FROM     MyTable 
        WHERE    extract_domain_from_url(url) =extract_domain_from_url(o.url)
        AND      id >= o.id) <= 2
ORDER BY url