[pLog-general] about getRecentArticles

[Su Baochen]

below is from php manual:

<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
   die('Could not connect: ' . mysql_error());
}
printf("MySQL server version: %s\n", mysql_get_server_info());
?>


The above example would produce the following output: 

MySQL server version: 4.0.1-alpha

So, detect mysql version is possible.

ÔÚ 2005-03-05ÁùµÄ 18:20 +0200£¬Oscar RenaliasдµÀ£º
> unfortunately mysql 4.1 is too much... I think we shouldn't target 
> anything higher than mysql 3.x anyway, at least for the time being.
> 
> Is there any way to detect whether we're using mysql 4.x or 3.x via 
> adodb or some php code? Perhaps we could add some extra condition: if 
> mysql 4.1 is available, then use your suggestion otherwise use the 
> current way. Or something like that :)
> 
> Oscar
> 
> On 5 Mar 2005, at 15:06, Su Baochen wrote:
> 
> >> From mysql manual:
> >
> > SELECT is used to retrieve rows selected from one or more tables.
> > Support for UNION statements and subqueries is available as of MySQL 
> > 4.0
> > and 4.1, respectively.
> >
> > It seemed that from mysql 4.1, subquery is supported.
> >
> > ÔÚ 2005-03-05ÁùµÄ 13:07 +0200£¬Oscar RenaliasдµÀ£º
> >> I already knew about that but there is not much we can do...
> >>
> >> In which version did mysql introduce nested queries?
> >>
> >> Oscar
> >>
> >> On 5 Mar 2005, at 12:58, Su Baochen wrote:
> >>
> >>> hi oscar,
> >>>
> >>> Function getRecentArticles of summarystatics.class.php may have
> >>> problems?
> >>>
> >>> For example, we can get 15 recent articles, but these 15 articles 
> >>> only
> >>> belongs to one blog user, so the result of getRecentArticles is only
> >>> one
> >>> article.
> >>>
> >>> My friend(baojg) have write a sql to achive:
> >>> 1 one article per blog
> >>> 2 order by date desc
> >>> 3 return 15($maxPosts) articles
> >>>
> >>> The SQL is:
> >>>
> >>>   SELECT id,
> >>>          user_id,
> >>>          blog_id,
> >>>          status,
> >>>          properties,
> >>>          num_reads,
> >>>          slug,
> >>>          date,
> >>>          topic,
> >>>          text
> >>>    FROM
> >>>          (SELECT a.id,
> >>>                  a.user_id,
> >>>                  a.blog_id,
> >>>                  a.status,
> >>>                  a.properties,
> >>>                  a.num_reads,
> >>>                  a.slug,
> >>>                  a.date,
> >>>                  t.topic,
> >>>                  t.text
> >>>            FROM plog_articles a,
> >>>                 plog_articles_categories c,
> >>>                 plog_article_categories_link l,
> >>> plog_articles_text t
> >>>            WHERE t.article_id = a.id
> >>>                  AND TO_DAYS(NOW()) - TO_DAYS(a.date) < 7
> >>>                  AND l.article_id = a.id
> >>>                  AND l.category_id = c.id
> >>>                  AND c.in_main_page = 1
> >>>                  AND a.status = 1 AND t.topic NOT LIKE
> >>> 'Congratulations'
> >>> ORDER BY a.date DESC) AS sorted_articles
> >>>     GROUP BY blog_id ORDER BY date DESC  LIMIT 15
> >>>
> >>> I'd like to explain it:
> >>> The nested SELECT clause get all useful information order by date, 
> >>> then
> >>> group by blog_id can get only one article per blog.
> >>>
> >>> This sql seems not effective, but works on my machine, and get 15
> >>> articles within 0.07s from 501 articles. You can test it on your
> >>> machine
> >>> with bigger database.
> >>>
> >>> I have modified my own summarystatics.class.php, but not commited,
> >>> please double check above sql.... hope helpful.
> >>>
> >>> Best regards,
> >>>
> >>> Su Baochen
> >>>
> >>>
> >>> _______________________________________________
> >>> pLog-general mailing list
> >>> pLog-general at devel.plogworld.net
> >>> http://devel.plogworld.net/mailman/listinfo/plog-general
> >>>
> >>
> >> _______________________________________________
> >> pLog-general mailing list
> >> pLog-general at devel.plogworld.net
> >> http://devel.plogworld.net/mailman/listinfo/plog-general
> >>
> >
> >
> > _______________________________________________
> > pLog-general mailing list
> > pLog-general at devel.plogworld.net
> > http://devel.plogworld.net/mailman/listinfo/plog-general
> >
> 
> _______________________________________________
> pLog-general mailing list
> pLog-general at devel.plogworld.net
> http://devel.plogworld.net/mailman/listinfo/plog-general
>