Hello gentlemen guru. Faced the most unpleasant problem: there are two tables 1. The table contains all sections and categories and has the form uid | category | razdel - where uid is a unique category identifier, category is the Russian name, razdel, respectively, is also the Russian section name. 2. The table is news with a bunch of fields, but the main one in it is the uid (only here it is already catEN)

I need to bring 5 news from each category belonging to one section.

At the moment, everything works but is very clumsy, it turns out an array in the array and the page is generated more than 1.5 seconds

$c = mysql_query("SELECT uid,nameRU FROM category WHERE nameEN='$razdel'"); if(!$c) exit('Ошибка'); while ($cat=mysql_fetch_assoc($c)) { $r = mysql_query("SELECT id,name FROM news WHERE catEN='".$cat['uid']."' order by mktime DESC LIMIT 5"); if(!$r) exit('Ошибка2'); while ($dt=mysql_fetch_assoc($r)) { } } mysql_free_result($c);

    2 answers 2

    Use 1 query with the JOIN construction in the sql query. maybe help?

     SELECT news.* FROM category LEFT JOIN news ON news.catEN=category.uid WHERE category.nameEN='$razdel' ORDER BY mktime DESC LIMIT 5

    UPD: Quarrel, overlooked, then you can choose without a limit, and transfer to php the task of sampling 5 news from the category, sorting the news into categories. php will do it anyway faster than sending several queries to the database:

      <?php $query = "SELECT news.* FROM category LEFT JOIN news ON news.catEN = category.uid WHERE category.nameEN = '$razdel' ORDER BY сategory.uid, mktime DESC"; $res = array();//результат $j=0; //счетчик новостей в категории if ($result = mysqli_query($link,$query)){ $res[0]=$result[0]; for($i=1;$i<count($resut);$i++){ if($result[$i]['catEN']==$res[count($res)-1]['catEN']){ if($j++<5) $res[]=$result[$i]; } else { $j=0 $res[]=$result[$i]; } } }
    • She, so he will only bring 5 news items, but you need 5 from each category, because in Section there are about 5-7 categories. - srazil
    • Thank you, although I didn’t really think about it, but I understood which way to dig! - srazil

    You can try to do it with the help of the stored procedure and the cursor - it will be confused)

    But I would advise you - if there is a lot of news, then this option is no good - it would be terribly slow to slow down - there is no point in dragging 1000 lines of data from the server - if you end up with just 50, for example.

    Here is my suggestion - add table last_news - with trail columns

     id - содержащию ссылку на news.id cat_id- категория category.uid.

    they will contain the finished data - that is, you will simply take the data from this table. Now I will explain how to prepare the data: it is logical that the data will change only with 2 actions - adding news and deleting it. Therefore, when these actions occur, we perform the following actions:

    • we delete all id from the last_news table, provided that cat_id is equal to the category of news that we added or deleted.
    • After inserting / deleting a new news, we make a request to receive a list of the 5 latest news from the news category we added or deleted. You have this request.
    • And the last action - we insert into the table last_news rows with id equal to id obtained with n2 (previous action) and cat_id for all rows is equal to the category of news that we added or deleted.

    Here is the principle and everything, by the way, point 2 and 3 can be done with one request