MySQL Complex Query

Question

I have a table that contains a keyword_id, site_id, percentage.

Now I use:

SELECT   *
FROM     keyword_relations
WHERE    keyword_id = "game"
ORDER BY percentage DESC,
LIMIT    {$page}, {$limitperpage}

Now lets say on page 26 according to this SQL a website called "example.com" appears.

Now lets say that I want to get the row number where a keyword on example.com, how can I do that.

For example, if example.com is ranked 5th on page 6 for keyword "examples" then how do I get the page number when I have the website "example.com" and the keyword "examples" from a different page.

I am trying to find the page number where the website should appear by dividing the the row number of the ranking of a website for a keyword (sorted by keywords occurrence) by the limit_per_page.

well thanks @Col. Shrapnel. I would appreciate if you could help me further as I know that count(*) usually helps. But in this case it is a bit different. — Speedy Wap
– Speedy Wap, Commented Feb 16, 2011 at 21:05
The question is very confusing. There's page 26, then page 6 then.. please try rephrasing and possibly adding some sample data rows. — RichardTheKiwi
– RichardTheKiwi, Commented Feb 16, 2011 at 21:48
i am sorry @Richard aka cyberwiki. I could not explain the question well enough. — Speedy Wap
– Speedy Wap, Commented Feb 16, 2011 at 21:54

Brad Christie · Accepted Answer · 2011-02-16 21:38:00Z

3

SELECT   *, @row:=@row+1 AS Row_Number
FROM     keyword_relations, (SELECT @row:=0) r
WHERE    keyword_id = "game"
ORDER BY percentage DESC,
LIMIT    {$page}, {$limitperpage}

I think, given what I could find

Note: Change the :=0 assignment to the value of {$page}*{$limitperpage}

EDIT

If you're interested in page, try the following:

SELECT    *, FLOOR((@row/5)+1) AS Page_Number, @row:=@row+1 AS Row_Number
FROM      keyword_relations,
          (SELECT @row:={$page}*{$limitperpage}) r
WHERE     keyword_id = "game"
ORDER BY  percentage DESC
LIMIT     {$page},{$limitperpage}

edited Feb 16, 2011 at 21:38

answered Feb 16, 2011 at 21:05

Brad Christie

102k16 gold badges160 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Speedy Wap Over a year ago

let me try this and I will get back to you in a moment

Speedy Wap Over a year ago

Hi, I am trying to find the page number by dividing the the row number of the ranking of a website for a keyword by the limit_per_page. The query above does not work.

Brad Christie Over a year ago

@user381595: You can use FLOOR(((@row:=@row+1)-1)/5)+1 AS PAGE in your query if you'd like, but note you need to use either or.

Brad Christie Over a year ago

@user381595: Try my new query in my answer, see if that doesn't help.

Speedy Wap Over a year ago

sorry, but if just have the site and the keyword but not the page then how do i get the page number, the query you just submitted also has a $page in limit which is what i am trying to get. thanks

|

Speedy Wap · Accepted Answer · 2011-02-16 23:00:38Z

1

$per_page_limit = 20;
$sql = "SELECT count(k_id) as k_id FROM keywords_relations WHERE k_id = '{$row['k_id']}' AND percent >= '{$row['percent']}'";
$data = mysql_fetch_array(mysql_query($sql));
$k_ids = $data['k_id'] + 1;
//is_int($k_page) ? floor($k_page) : floor($k_page) + 1;
if($k_ids % $per_page_limit ){
    $k_page = floor($k_ids/$per_page_limit) + 1;         
} else {
    $k_page = $k_ids/$per_page_limit;

answered Feb 16, 2011 at 23:00

Speedy Wap

4884 gold badges7 silver badges18 bronze badges

Collectives™ on Stack Overflow

MySQL Complex Query

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related