How exactly would a blog-type application work with a MySQL database?
Like... I need to make a... thing that will...
So I log in, and go to page X.
there is a textarea on that page.
I type something in and push submit.
It makes what I typed in on the textarea appear on another page... sort of like a CMS.
How would that work in a database?
SELECT * FROM `lolkitty` WHERE (how do I know where? ahh >.<)
:/ Really, how do I know what row to select? How do you select the last row... Come to think of it, how are rows stored?
Are they like...
or are they
or are the rows not organized by entries? @.@
It sounds like you need a really good lesson in relational databases.
Typically a record will have a PRIMARY KEY, a unique value that represents that specific record. It can be an integer or other unique field, but since MySQL has become so popular, it's usually an auto-incremented integer value that retains it's uniqueness in the table.
To the matter of blogs, they're often times related on the date field, obviously because they're meant to be a representation of your life or viewpoint on a specific day (or day / time). You'd probably perform a SELECT statement on those columns you want to display (blog_title, blog_content, post_date, post_time, etc) based on the date and order by the last 5 or 10 records entered (again based on date).
Read up some on relational databases and SQL, then select an RDBMS like MySQL or SQLite (or Postgresql, another excellent free database system) and tinker with it.
whats the difference between MySQL and PostgreSQL?
Do a Google search on PostgreSQL vs. MySQL. You'll no doubt get many returns expressing one view over another, usually with PostgreSQL coming out as a 'more powerful' or more 'feature rich' system. I've used both and find MySQL works well for what I need, and in most cases SQLite works fine.
MySQL in version 5 has come close to PostrgreSQL with many advanced feature, but when MySQL was v3, v4 PostgreSQL was perfect solution for enterprize sized solutions.
As blogs go SQLite would work perfectly for your needs.
Also you could go MySQL because every host has MySQL but the situation with PostgreSQL isn't that great.
Well, always you can get a VPS/Dedicated server and put which db you want to have there - but this is a bit long stretch for this situation I think.
abiko couldn't have said it any better. Thank you.
You're welcome. *blushes*
As for Aarons answer :)
For a simple app like a blog - you have a users table and a blog article table.
Now user logging in/out is another story, but how to select..
First - the simple way.
The table -
So you insert the data to your table, and selecting out is simple.
Now - for pagation - you want to select news from the id 6 and 10 of them - the LIMIT statement would look like this - LIMIT 6, 10.
Now let's say you want to select the author for your articles.
There are several ways to do this - one of them is LEFT JOIN ..
Now we have touched the interesting topic here - the relations between two tables. To connect two tables they need to have an ID that joines them. In this case blog_articles has the author field - in which is the id of an user from blog_users table.
This could be extended to the part where you want to have categories for your articles.
When you select data you select as in the query above and add another "operator" the category id - that is the blog_categories table.
Also you want to get a one especially great article from the database.
All you need is it's id.
I have a feeling that you are a beginner that wants to learn how to work with databases and my suggestions are -
- read some tutorials on that subject - creating your own blog or simple shoutbox example - that is a great foundation to start on.
- read the MySQL (SQLite) docs about certain operators - SELECT, JOIN, LIMIT, ORDER etc. Get to know your database - it will do you good.
- select how you want to access your database - mysql, mysqli [MySQL related] or PDO.
Also when submitting data from the form, and a textarea don't forget to "filter" out your input (HTMLPurifier is a good start ) - removes any suspicious elements and protects you from XSS
I haven't touched the php part of connecting to a database etc presuming you know that.
Please let me know if I can help you with something :)
abiko> Another well thought out, informative post, thank you. Some constructive criticism follows, however. :-)
I strongly disagree with the use of a VARCHAR type column for the date. IMHO you should only ever store a date value in a column of one of the predefined DATE types (unless of course you're storing the UNIX TIMESTAMP value, which has it's own pros and cons, in an INT type column). You'll only have heartache trying to make sense of date formatting and date calculations in a non-standard field.
You may have other experience setting a date in a field such as this, if so, please let me know.
Although I'm sure it would work fine for a very small limited blog as a testbed, TINYINT is also too small to be used, make sure you use at least SMALLINT for the PK field. INT won't hurt, either.
It's also not clear to me why you'd want a LEFT OUTER JOIN rather than an INNER JOIN to join the `blog_articles` and `blog_users` table - you'll wind up returning records with missing author data. Since you've defined your `author` FK field as NOT NULL, this seems highly unlikely.
Really nice post about relations between tables and how to set things up, though. I also like the use of UTF8 and utf8_bin collation, something we should all be looking into.
I agree with the usage of INT for date, and I'm using UNIX TIMESTAMP.
Further I think that tinyint would work fine for smaller blogs however for larger project (that are projected for high input and output trough the database) it is essential to use INT.
Now the date storage has been an issue for me, but I've decided for the UNIX TIMESTAMP but here is also 3 Date and Time types for that usage: DATETIME, TIME, TIMESTAMP.
Here is an interesting article how to use phps date function or MySQL Date/Time types for your date storage.
SOCK, thank you on your criticism 8-)
|All times are GMT. The time now is 02:27 PM.|
Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0