Modifying MySQL table on different pages with scor

问题:

In regard to this question How to put MySQL table into session variable and using the table on next page?, I would like to explain what I'm trying to do here.

I have a website that contains a form, through which users can answer questions. At the end of the form, the goal is to advice users about which product is best for them.

This advice is achieved by asking users several different questions. Depending on what answers they give, a list of products in a MySQL table will be assigned points. At the end of the form, the Product ID with the most points will be the winner and thus will be the product that is best for the user.

In this explanation, I will just use 2 questions. The first question is called 'Phase1'. The second question is called 'Phase2'.

Phase 1: a HTML web form with a radio box input gives users the choice between multiple values. Depending on the value that has been inputted through the radio boxes, a certain column from an existing table is inserted in a column named 'Phase1' in a newly created temporary table. This temporary table is named 'Advice'. The column Phase1 contains a list of points for the different products.

So this is a step-by-step explanation of what's happening in Phase 1 of the form:

1. HTML Radio box input gives users the choice between 4 values.
2. A temporary table (named: 'Advice') is created with the following columns: 'ProductID', 'ProductName', 'Phase1', 'Phase2'
3. Columns ProductID and ProductName are filled by data about the products. This data is fetched from an existing table called 'Computers_F1'.

So what happens after the user answers question ''Phase1"?

4. Depending on which radio box value is inputted by the user, column Phase1 is updated by column '1' '2' '3' or '4', whose data comes from an existing table named 'Computers_Phase1'.
5. The temporary table now contains three filled columns. 'ProductID', 'ProductName' and 'Phase1'.

Hopefully it all makes sense up until now. Phase2, which contains the second question, should essentially do the same thing as Phase1. Note that phase 2 will take place on a next HTML page, with a new form being served and then outputted to phase2.php. This, however, means that the temporary table 'Advice' needs to be passed from phase1.php on to phase2.php. So this is actually my problem up until now. Because apparently, PHP doesn't allow MySQL SELECT queries to be stored into a $_SESSION variable. I have considered saving the content of the 'Advice' table in a php array. However, the downside of this would be that I cannot use MySQL queries anymore to modify the table in phase2.

I need to be able to modify the table 'Advice' in the page2.php script. This is necessary because I want to further add columns after several different questions. At the end of all the questions, I want to create a column named 'Total'. The values in this column will be the sum of the values in 'Phase1', 'Phase2' and so on. So in the end the row with the highest score in 'Total' will be the product that is best for the user.

Hopefully you guys can help me with this one. Maybe there is something wrong with the design of the system. Suggestions on how to improve the system would be highly appreciated.

回答1:

Short answer would be: store in the session not the data but only user's choice.
On a final page do all your calculations.

Proper answer: temporary table should be action of the last hope. Have to be used only if no regular RDBMS mechanisms can be used.

Most likely your logic can be mapped to standard joins. But it's impossible to tell more as logic is still unknown.

回答2:

Although I have answered this previously to your main question, here is the answer again.

Responding to your question one by one:

Error you are Getting
The error that you are getting normally is the result of incorrect spelling or reference of table name, field name or any other variable in the MySQL query. In your case, it may be due to incorrect calling/storing your Session Variable. For example,

//Instead of "table", you typed "tabel". This is just an example.
$result = mysqli_query($mysqli,"SELECT * FROM table");

Share your code so that I can try picking up this error. Above is just an example.

Storing values in Session Variable is not Recommended
Suppose your user fills in the form and moves on to the next phase. The data from the first phase is transferred to the second phase via Session Variable. What if the user simply closes the tab and restarts the process? Session Variable will still be set and the previous data may interfere with the new one and can produce unexpected results.

Ideal Solution
It is better to store the values in JavaScript Array and then transfer to the next page by Hidden Input field. Some of the benefits of using this logic are:

1. Fast Performance
2. More Secure
3. Easily Manageable

Reference Code
If you are taking the values from HTML Forms, then it is very simple to have the value in POST. Using the JQuery UI selection, you can add the selected values in a JavaScript Array.

//Declare Global JavaScript Variable on Page Load. This will be at the end of <head>
$(document).ready(function() {
    window.fase1result = [];
} );

After this, on each click event where you want to add the data to be taken to the next page, use the following code to add the value to this array.

fase1result.splice(indexOf_to_add, 1, "SelectedValue");

To understand .splice better, click here.

One selection, e.g. clicking on a Div or link, add the value to a fase1result and on submit add the array value to Input Hidden by using the following:

Add a Javascript Function on form's onsubmit.

<form id="myForm" method="POST" action="fase2.php" onsubmit="return fase1Values()">

Add <input type="hideen" name="fase1values_input" id="fase1values_id"> in the form.

Below is the JavaScript onsubmit function just before </body>.

function fase1Values() {
    $( '#fase1values_id' ).val( JSON.stringify(fase1result) );
}

Note that JSON.stringify is required in order to set the Array as an input value.

$decode_fase1result = json_decode( $_POST['fase1values_input'] );

Now you have transferred the fase 1 selection data using an Array from Page 1 to Page 2 without storing data in any temporary table.

Hope this answers your question and solves your problem as well.