This lesson is part of an ongoing Foruml tutorial. The first part is here: Build your own Forum, along with all the files you need. The previous lesson is here.

At the top of the code for the resultsP.php page, there's two functions. We'll take a look at these later. But bear in mind what this page does – inserts the new post into the database table. (The security issues discussed in the previous section apply here, as well)

To get the data posted from the FORM, we have this:

$secID = $_POST['h1'];
$memID = $_POST['h2'];
$posTopic = $_POST['tp'];
$posText = $_POST['post'];

This is the same technique we used in the previous section: just put the POST data into variables. The $secID variable will hold something like secWP, the $memID variable will hold the member id, the $posTopic variable will hold the heading for the topic, and the $posText variable will hold the text of the post itself.

After we get the connection to the database, we run in to our first problem. The problem is that the Primary Keys in the post tables are not auto-incrementing numbers. With an auto-incrementing number, you can usually leave the database to update this field –all it needs to do is to add 1 to the previous value. Like this:

ID
0
1
2
3

If you update the table, the database would automatically add 1 to the ID field, and the next row would be 4. You don't have to do anything.

But for the Primary Key in the post tables, we have a field called threadID. The threadID field looks like this:

threadID
pos1
pos2
pos3

So you can't just add 1 to this field, if you insert a new row. You have to make sure that the new row is pos4, and the next new row will be pos5, etc.

There's another problem as well. How can you be sure that the database hasn't inserted your rows like this (and it will!):

threadID
pos1
pos3
pos2

So the last row in the table is pos2. If you try to update this with pos3 as the new row, you'll get an error. Because there already is a pos3. A Primary Key field is one that has unique values. And that why you'd get an error.

This is a common problem when you have your own format as the Primary Key in a database table – adding a new unique value when that value is not an auto-incrementing number.

In the example above, we need make sure that the new value in the threadID field is pos4. This is a run-down on how we'll do it:

• Get all the posts from the table
• Set up an array to hold the threadID data
• Strip the "pos" part, and just leave the number
• Sort the array with the lowest number first and the highest last
• Get the last value in the array (which will be the highest number)
• Add 1 to this number
• Put the "pos" part back
• Update the threadID array

If you open up the code for the resultsP.php page, you'll see comments that tackle the items in list above. The first part of the code gets all the posts from the table:

$SQL = getPostTable($secID);

This is just a call to one of the functions at the top of the page. When the function is run, you'll have SQL like this:

"SELECT * FROM wpposts";

This selects all the records from a table called wpposts. The next two lines are these:

$result = mysql_query($SQL);
$numRows = mysql_num_rows($result);

The first line executes the SQL. The second line returns how many rows we have returned from table. We'll need this for the for loop. We then set up an array to hold the threadID values:

$posNums = array();

We now need to loop round the rows in the table, and get the threadID field:

for ($i = 0; $i < $numRows; $i++) {

$row = mysql_fetch_row($result);
$pID = $row[0];

$posNums[$i] = ltrim($pID, 'pos');

}

The loop goes from zero, to less than $numRows. The first line in the for loop is this:

$row = mysql_fetch_row($result);

The inbuilt function mysql_fetch_row, as its name suggest, fetches a row from a table. It will fetch it back with row[0] as the first table column, row[1] as the second column, row[2] as the third, etc. Because we know that row[0] is our threadID field, we can grab this value:

$pID = $row[0];

The treadID , then, is placed into a variable called $pID. To strip off the "pos" part of the threadID, we have this:

$posNums[$i] = ltrim($pID, 'pos');

We're using the unbuilt function ltrim( ) to trim the "pos" part. In between the round brackets, you type the text you want examine. After a comma, you type the text you want trimming.

After the loop finishes, we might end up with something like this:

$posNums[ ]
1
3
2

So we have all the numbers in an array, but they are not sorted from lowest to highest. The next line does that:

sort($posNums);

The inbuilt function sort is used to sort the array. The name of the array you want to sort goes between the round brackets of the function. But note that the square brackets of the array go missing.

Once the array is sorted, we can get the last item in the array:

$lastID = end($posNums);

Again, we're using an unbuilt function. The end function is used to move to the last element of an array. Here, we're putting the value returned by the function into a variable called $lastID.

Once we have the last number, increment it:

$lastID++;

Finally, once we have the last number, we can add the "pos" back on:

$threadid = 'pos' . $lastID;

All that coding gets us the highest pos number from our threadID field. We can then go ahead an get the SQL for our INSERT statement:

$tableSQL = getPostSQL($secID);

This calls the function at the top of the page. The function will return something like this:

$sql = "INSERT INTO wpposts(threadID, memberID, threadTopic, postText, datePosted) VALUES "

We want to INSERT INTO the wpposts table a set of VALUES. The names of the table columns go between the round brackets. But we still need to add the VALUES. This is done with the rather long next line:

$tableSQL = $tableSQL . "(" . $threadid . "," . $memID . "," . $posTopic . "," .$posText . "," . $date_today . ")";

We're just building up a string. Something like this:

INSERT INTO wpposts(threadID, memberID, threadTopic, postText, datePosted) VALUES (pos1, mem1, "New Post", "This is the text", "2006-10-22 13:30:14")

Once we have our SQL, we can try to run it:

$result = mysql_query($tableSQL);

We can test to see if it is run successfully:

if ($result) {

print "Your Post has been added to the Forum." . "<BR>";
print "<A HREF = forumTest.php>Back to the forum</A>" . "<BR>";

}
else {

print "Couldn't add Post to the Forum";

}

If the code executes successfully, a new post will be added to the forum.

And that completes the code for Posting new topics. In fact that completes our walkthrough of the entire forum! There is still work to be done, of course. So there's a final Project for you to try.