 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I'm been slowly learning a bit of PHP and the first real world script that I want to write is simply to display the first few active topics in a Phpbb forum database on my index page.
Now I'm very much the beginner and only know a little php but I'm not going to cheat and ask people to write my code for me, but some pointers would help.
I've looked at the MySQL [b]select[/b] command and can see how it works and I shouldn't have a problem getting data out of the database. The problem I have is that I want to display x number of the most recent topics (based on the most recent posts). I can't simply pick the right number of posts and reference the relavent topics from these as several of the posts are likely to occur in the same topic and I don't want the same topic appearing twice in the list. What I need to do is take the first post then move on the the next post, discounting it if its topic number matches the topic number of a post already picked. I think I can see how to do this with logical php code once the posts have been selected but it would seem less effort to simply pick the correct posts using the [b]select[/b] command and print them out.
I see that I can use the [b]where[/b] clause to place conditions for the [b]select[/b] command but I'm unsure what conditions to use. I need to select a post whose topic value is unique from the post value of records have been already placed in the selection by the [b]select[/b] command (not unique to the database from which the select command is picking out the records). Can I use the [b]where[/b] clause to compare fields in records against records already selected or should I crawl through the database selecting records using php logic?
Sorry if things don't make sense but I know what I mean.
so you've got a topic table and post table, where the posts are linked to the topics by a topic id which i'd assume is the primary key of the topic table?
so you'd need to join the two tables, and use max (date) on the post table, so something like
select topic.name from topic, post
where topic.id in
(
select max(date) topic_id from post
)
now that would only bring you back one post, and therefore topic, so to bring back many i'd sort the posts by date, then select a number of these posts say three, then select topic name where topic id is in any of the three you've bought back...ish
Never got properly into SQL, but from my limited knowledge, so long as you ordered your dataset by most recent posts or whatever, then did your SELECT followed by LIMIT 5 or whatever number you wanted, is that what you're after? (Sorry used to be into Access, but well out of touch these days)
A bit more info on your database (tables) structure would help.
This works on a phpbb forum to give the topic titles of the 5 most recently posted to topics.
select distinct tt.topic_title from ( select topic_title from phpbb_topics t, phpbb_posts p where p.topic_id = t.topic_id order by post_id desc ) tt limit 5
Edit: Not very elegant but I just knocked it together in a stream of consciousness SQL styleee moment 🙂
You would need to do something like
SELECT LIMIT 5 fld1, fdl2, fld3, fld4 FROM tblPost WHERE tblPostForiegnKey IN (SELECT DISTINCT(ID) from tblTopic Orber by ID DESC)
Could do with some more info on the schema to write it properly.
I think I may be getting a little ahead of myself (not to mention out of my depth). Maybe I need to write the script in a way I understand (even if it's not elegant of efficient) rather than skipping all over the place trying to learn scraps of information. I can always refine it as I learn more.
I'm clearly going to have to explore the sql side of things in a bit more detail but it does look like I can do what I want to do with the select command, even if I don't understand it at the moment!
Thanks for the input.
currently working my way through sams sql in ten minutes, and on the whole seems to be making sense.
[url= http://www.wrox.com/WileyCDA/Section/PHP-MySQL.id-131383.html ]Wrox books[/url]
Might help... Always found them to be very good.
Mr Mo
I'm glad it's largely making sense to you, for me it's largely doing my head in - big time, but actually trying to do something for real is a good way to learn and hopefully I'll get there.
Now for the really embarassing question that show just how little I do know.
I've been playing around with ideas for the script and have successfully connected to the database and extracted records from it. However, if I do a query [b]ORDERD BY post_time[/b] I retrieve records from the start of the database (the earliest) and I want to pick the latest posts. Can I order records a query in descending order, or is there a mysql function I can use that will tell me the size of the table so I can use a LIMIT with and offset from the end of the table and work back? There are over 23000 posts in the database and it seems needlessly wasteful to select them all.
order by posttime desc 😉
PJay, i had started trying to get my head round SQL whilst trying to write VBA, it didn't work to well. Hence gone back to basics to try and get my head round SQL and then go back to VBA.
Thank you Retro83 that seems to have done the trick although I'm rather embarassed that it was that simple 😳 clearly I've much to learn!
Here's a sql forum I use quite often (amongst others)
[url= http://www.sqlteam.com/forums/default.asp?CAT_ID=3 ]sql forum[/url]
I recommend using the following SQL:
Select book from library where title = 'Beginners guide to MySql'
http://www.w3schools.com/sql/default.asp
I'd recommend this [url= http://oreilly.com/catalog/9780596006303/ ]book[/url]
[img] 
[/img]
I've not used this particular one but have used and recommended several other books in the same series.