Noob blog

[awsomejoe23]

Hey guys I just started out with mysql. My main purpose to learn it now is to make my own blog(and because it looks cool lol). But I have a few questions, and I was wondering if any of you guys could show me a good place to get some tips on making blogs, or the coding used for it. A few of my problems are probably very simple, but here they are.


How do I make a page for when the poster makes a new topic?
How can I improve my security?

I was thinking something like this

<form action="something.php" method="get">
<input name="post" type="text" >
<input type="submit" value="Send">
</form>

<?php
$post = $_GET['post'];
$dbh=mysql_connect ("localhost", "something_joe", "not telling you") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("awsomejo_mydb");

mysql_query("CREATE TABLE post(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(200))")
or die(mysql_error());

echo "Table Created!";

// Insert a row of information into the table "example"
mysql_query("INSERT INTO example
(post) VALUES($post ) ")
or die(mysql_error());
?>

Thanks guys,


Joe

[Fumigator]

Why would you create a table within the context of the user interacting with your blog? You should create the table yourself, once. Surely you're not planning to have a separate table for every blogger?

[awsomejoe23]

Didn't think about that. I was thinking a different row for each blogger. But I guess one table would dot the trick. A new column for each new topic, and a new row for each new post. Bu I'm confused in how I should go about doing this. Is there a create new column function that I can link to a button when someone want to create a new topic? and what about the reply s, and new pages?

Can you please lead me in a direction as to how I could figure these things out?

Thanks

[PappaJohn]

I would use two tables:

1. table for the topics - include a field (column) for 'id' as an auto_increment(integer).

2. table for the posts/replies - include a field to store the 'id' of the related topic.

[Fumigator]

The structure of your table should not change with new data. You mentioned adding a column-- don't do that. Why would you want to? Just add a new row for each blog entry.

[awsomejoe23]

K, but how do I organize them.
The table row would be a mixture of replies from different topics. How would I sort them out?
Then how would I get it to make a page?
ex topic1 be index.php?topicid=1

[Fumigator]

As Pappa John suggested.

How would I sort them out?

By using unique and foreign keys.

Do some reading on how relational databases work.

[awsomejoe23]

So it would be like
------------------
Table post
code:1 post:text
code:2 post:text
code:2 post:text
code:1 post:text
code:3 post:text
------------------
Table topic
code:1 post:text
code:2 post:text
code:3 post:text
------------------
also I have learned about the view function, so for the displayed ones it would be something like
SELECT topic, post from subscriber_view;
but how would I make it specify which code for each different topic?

[Fumigator]

There are only a couple of reasons to use views. 1) For security reasons you need to restrict which columns can be seen (if salary were stored in a column, for example). 2) You have a complex query that is commonly used so rather than code it several times, make a view from the complex query and then use the view.

What reason do you have to use a view?

[awsomejoe23]

O, I thought that it would just be simpler and safer. Thats what I got out of the tutorial at least.
So I guess something more along the lines of

SELECT post, post FROM topic, post
WHERE topic.post_code=post.code AND topic.post='Once';

Is that somewhat right?

[PappaJohn]

An overly simplified description for the tables:

topics_table:
id - defined as: integer, auto_increment, primary key
topic_title - defined as varchar of an appropriate size
topic_text - defined as text

posts_table:
postid - defined as: integer, auto_increment, primary key
topicid - defined as integer - stores the id of the related topic
post_text - defined as text

topic1 be index.php?topicid=1

The sql to return all posts related to topic 1:

SELECT * FROM posts_table WHERE topicid = 1

This returns all fields from all posts where the topicid field (in the posts table) is set to one.