How we can assign a value in mysql query

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 

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?

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.

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