Question

id  title   cat lang
6101    AAba    1   1
6102    AAad    4   2
6103    AAaf    4   2
6104    AAa 1   1
6105    AAtar   4   2
6106    AAao    1   1
6107    ABya    4   2
6108    ABar    4   2
6109    ACar    1   2
6110    BCad    3   1
6111    CCas    4   1
6112    DCas    4   2
6113    GCaz    2   2
6114    FCaew   2   2
6115    FCaw3   4   2
6116    FCa3    1   1
6117    FCa4    4   2
6118    FCa5    2   1
6119    FCa6    4   2 --- last id

Table news, id is primary key, title - is title field, cat - there are four types of categories of news, lang - language code 1 or 2.

I want to achieve following: 1) Case Edit news - id is available - for example 6111, lang is 1, cat is 4 I want to collect these id and titles:

which are the opposite language - lang is 2, the same category cat is 4 and within range of 4 (or n) id before and after given id 6111 also ordered desc

2) Case Add news - id is not available, or after insert in php is mysql_insert_id() the same functionality - last 10 id with the same cat = 4 and the opposite lang = 2

至今为止,我尝试过这一点:

复数 1) php 片段 :

   $id = 6111;
    $lang = 1;
    $tip = 4;
    $sql = "SELECT id, title 
            FROM pubs 
            WHERE id BETWEEN ".($id-4)." AND ".($id+4). " AND 
            tip = ".$tip." AND 
            lang <> ".$lang." 
            ORDER BY id DESC ";

< 坚固> BUT < / 坚固 > 这在 id 之前和之后得到 4 id, 而不是从我需要的 id 收集。

案例2)

$sql = "SELECT id, title 
        FROM news 
        WHERE cat = 4 AND 
        lang <> 1 
        ORDER BY id DESC LIMIT 10";

这似乎有用,但也许我错了

Is there any way to achieve Case 1 this with php and mysql? Better way for case 2?

Answer 1

鉴于你只有$id ,我只能找到这个可怕的外观野兽。我敢肯定有更细的办法来做到这一点,但我认为这样行得通(FixED )。

$id = 6111;

$query = "
  SELECT `id`, `title`
  FROM `news`
  WHERE `id` IN (
    SELECT * FROM (
      SELECT `id`
      FROM `news`
      WHERE `cat` = (
        SELECT `cat`
        FROM `news`
        WHERE `id` = ".$id."
      ) AND `lang` != (
        SELECT `lang`
        FROM `news`
        WHERE `id` = ".$id."
      ) AND `id` < ".$id."
      ORDER BY `id` DESC
      LIMIT 4
    ) `lower`
  ) OR `id` IN (
    SELECT * FROM (
      SELECT `id`
      FROM `news`
      WHERE `cat` = (
        SELECT `cat`
        FROM `news`
        WHERE `id` = ".$id."
      ) AND `lang` != (
        SELECT `lang`
        FROM `news`
        WHERE `id` = ".$id."
      ) AND `id` > ".$id."
      ORDER BY `id` ASC
      LIMIT 4
    ) `upper`
  )
  ORDER BY `id` DESC
";

Answer 2

类似这样的东西可能会有效:

$sql = "SELECT id, title FROM pubs 
        WHERE tip = ".$tip." AND lang <> ".$lang." 
        ORDER BY id DESC 
        LIMIT ".($id-4).",".($id+4);

可能有一个更优雅的解决方案, 但这样就能完成任务。

友情链接