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

In the previous part of this lesson, you had a look at the structure for the Survey database. We're now going to look at the code for setting a question. The web page itself, remember, looks like this:

The Form to set a question (opens in a new window - 43K)

Using your favourite text editor, open up the file called setQuestion.php again, and take a look at the code. Most of it is code you've already met. In the first two sections we just get the data entered in the textboxes on the form (the questions and three possible options), check for any unwanted script tags, and then open up a connection to the database. The first new code is this:

//============================================
// GET THE LAST QUESTION NUMBER
//============================================

$SQL = "Select * FROM tblQuestions";

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

$boolLastRow = mysql_data_seek($result, ($numRows - 1));

$row = mysql_fetch_row($result);

$qID = $row[0];

$next_Q_Number = ltrim($qID, 'q');
$next_Q_Number++;
$question_Number = 'q' . $next_Q_Number;

As the comments say, we're getting the last question number from the tblQuestions table. We need to do this because the primary key doesn't auto increment.

Because the primary key is a unique field, the auto increment feature of MySQL would have added one to the primary key for us. But we've made our own unique field to be used as a primary key (q1, q2, q3, etc), so we're going to have to increment the values with code. The idea is that we get the last number value from the QID field, then add 1 to it. (For simplicity's sake, there's no checking to see if the value we write back to the database is indeed unique. If it isn't, you'll get an error. You'll see how to code for this in the next section.)

The code, then, starts off by getting all the records from the tblQuestions table:

$SQL = "SELECT * FROM tblQuestions";

Next, we try to run this query:

$result = mysql_query($SQL);

We could check here if the value in the variable called $result is true. If it wasn't, we could display an error. (Another thing for you to do!) But we're keeping things simple, so you can see how all this works.

The next thing to do is to get the number of rows in the table:

$numRows = mysql_num_rows($result);

We need to get the number of rows so that we can move the pointer to the last row in the database. We want to get this last row so that we can check the QID field. Remember: we're trying to add 1 to the value in the QID field - to increment it. The code that moves the internal pointer to the last row in the database is this:

$boolLastRow = mysql_data_seek( $result, ($numRows - 1) );

We're using an inbuilt PHP function called mysql_data_seek( ). This allows you to jump to any row in a table. Between the round brackets of the function, you first type the name of your resource ($result, for us). Then, after a comma, you put the row that you want to jump to: $numRows - 1. The count with mysql_data_seek( ) starts at zero, so we're deducting one from the number of rows in our table. Otherwise, we'd get an error about no such row found. We're returning all this to a boolean variable called $boolLastRow. You can write an error message, if this returns a value of false. But we're leaving out the error checking here.

Once we've moved the pointer to the last row in the table, we can fetch the data back:

$row = mysql_fetch_row($result);

You've met the inbuilt function mysql_fetch_row( ) before. It fetches a row of data, and puts it all in to an array. You can then access the array, and manipulate the data.

$qID = $row[0];

The QID field is at position zero in the array (it's the first field in the database). So we just use $row[0] to return the value. This is then put in to the variable we've called $qID. This variable will then hold the last QID value from the table. The format we're using for the QID field is to type a letter "q" followed by a number. To increment the QID field, we can just add 1 to the number then join that number to the letter "q". The next few lines do exactly that:

$next_Q_Number = ltrim($qID, 'q');
$next_Q_Number++;
$question_Number = 'q' . $next_Q_Number;

We use the ltrim function to strip off the letter "q". This leaves just the number itself. You can then increment this number (next_Q_Number++). The final line joins the new data back together:

$question_Number = 'q' . $next_Q_Number;

This adds the new number to the letter "q", and then stores it in a variable called $question_Number. It's this question number that will get written to the QID fields in both the tblQuestions table and the answers table.

The next two sections use the INSERT INTO command to add the new question to the database. The first thing to do is to add the question to the tblQuestions table. The rather long SQL line that does that is this:

$SQL = "INSERT INTO tblquestions (QID, Question, qA, qB, qC) VALUES ('$question_Number', '$question', '$answerA', '$answerB', '$answerC')";

You've met code like this in a previous section. You should be able to figure out what it does: adds the data into the Field names mentioned in the first round brackets.

To update the table, we run the SQL query:

$result = mysql_query($SQL);

The next SQL command is slightly different:

$SQL = "INSERT INTO answers (QID, A, B, C) VALUES ('$question_Number', 0, 0, 0)";

Again, it's an INSERT INTO command, but note that we're now updating the answers table. The VALUES between the round brackets are:

'$question_Number', 0, 0, 0

The value inside of $question_Number will be the QID number. But the next three values are all zero. This sets up the answers table, and ensures that the default options are all filled in. A, B and C, remember, will hold the number of votes. We start them off at zero because nobody has voted yet!

And that's about it for setting the question. We've covered quite a lot of ground in a short space of time, so let's review what we did.

1. Created two tables in the same database
2. Had a primary key in one table that is joined to a foreign key in the other table
3. Wrote code to move an internal pointer to the last record in a table
4. Returned a specified row, and incremented a value to be used as a unique key
5. Inserted a new record into two tables, using the same field in both

Probably the most important thing to learn in this section is how to join separate tables together using a primary/foreign key combination. We'll use this technique again when we create a forum. For now, let's move on to the survey itself.