Hello.
First of all thanks in advance for any help. The last time I've asked
here I got a very useful help about my database structure.
I'm developing a very simple forum with PHP5.3 and SQLite.
I have a message table where I store messages along with their flags and
a column referencing the author's id. I also have a topic table where I
store topics. I finally have a categories table. Any topic has a
category and any message has a topic.
Now, the following query lists all topics for the given category. For
each topic it retreives how many messages there are, when the last one
have been saved (AND accepted by admin) and what is the id of last one.
I use these informations to create a link to the page containing it.
I'm using two sub queries to retreive those informations. One of them
use concatenation to give me two results (that I split by PHP). I did so
to avoid using multiple subqueries performing the same operation on the
same table. Is this a good way to go or SQLite can optimize subqueries
and reduce them to one?
Would an index on messaggi.topic (ie. messages, in italian) be useful?
It would have to be regerated each time a message is added or deleted,
so I don't know if it's good.
So said, this is the query. Again thanks in advance for any help and
sorry for my bad english.
SELECT
otopic.id AS idtopic, otopic.titolo,
strftime('%d/%m/%Y', otopic.inserito) AS inserito,
(SELECT strftime('%d/%m/%Y %H:%M', max(inserito)) FROM topic WHERE pub=1
AND id=otopic.id) AS ultimo,
(SELECT count(id)||' '||max(id) FROM messaggi WHERE pub=1 AND
topic=otopic.id) AS topicpostinfo,
otopic.sticky, utenti.nick, utenti.altnick, utenti.pub AS upub
FROM topic AS otopic
INNER JOIN utenti ON otopic.idnick=utenti.id
INNER JOIN categorie ON otopic.categoria=?
WHERE otopic.pub=1
ORDER BY otopic.sticky,otopic.inserito DESC
Sorry for table names in italian
utenti = users
messaggi = messages
--
Saluti da Gabriele Favrin
http://www.favrin.net
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
