Php works fine on my computer but not on my webpage

[Pedroski55]

I made a little online survey. Students ask questions and choose answers to 14 questions. First the answers are written to a table surveyU6_3_data. That works fine.

I want to collect the totals for each question and each possible answer.

The code below works fine on my localhost, does exactly what I want, no errors in apache2 error.log

This particular part from the code below echoes the SQL to the success page for my information:

echo 'SQL is ' . $mycount . '<br>';

I can copy and paste these commands in my webpage phpmyadmin and they work fine. For example:

UPDATE surveyU6_3_totals SET A3_total = A3_total + 1 WHERE Qnr = ‘Q1’

But they are not doing what they should do when the form is submitted on my webpage.

Like I said, this works perfectly on my laptop. Any ideas why it won’t work on my webpage?? PHP version problems maybe?? I have the latest version on my laptop, the webpage is not so up to date.

// the possible answers
			$str = "ABCDEFG";
			$length = strlen($str);
			// loop through the possible answers
			for ($index = 0; $index < $length; $index++) {
		    //echo 'the letter is: ' . $str[$index] . '<br>';
		    
		    // loop through all questions, 1 to 14
				for ($i=1; $i <= 14; $i++) {
				 $letter = 	${"q$i"};
				 //echo "Answer letter is " . $letter . '<br>';
				 $questionnr = 'Q' . $i;
				    if ($letter == $str[$index]){
				    	try{ 
						    //echo "Question number is " .  $questionnr . '<br>';
						    $columnnr = intval($index) + 1;
						    $column = 'A' . $columnnr . '_total';
						    //echo '$column is ' . $column . '<br>';
						    //exit();
						    //$mycount = 'UPDATE `surveyU6_3_totals` SET `A1_total`= A1_total+1 WHERE Qnr=' . $questionnr;
						    //$mycount = 'UPDATE `surveyU6_3_totals` SET `A1_total`= A1_total+1 WHERE Qnr = ' . $questionnr;
						    //$mycount = 'UPDATE surveyU6_3_totals SET ? = ? + 1 WHERE Qnr= ?';
						    $mycount = 'UPDATE surveyU6_3_totals SET ' . $column . ' = ' . $column . ' + 1 WHERE Qnr = \'' . $questionnr . '\'';
						    echo 'SQL is ' . $mycount . '<br>';
						    //exit();
						    $mystmt2 = $pdo->prepare($mycount);
						    $mystmt2->execute([$column, $column, $questionnr]);
							}
							catch(PDOException $e){
							$_SESSION['error'] = $e->getMessage();
							//echo $_SESSION['error'];
							//header("Location: ../19BEwW1.html");
							//include '/20BEhw/html/20BEsW21.html.php';
							header('Location: /19BEcw/BE4p88survey3.html.php');
							exit();
							}
						}
					}	
				}

[ErnieAlex]

Is your database set up on both systems the same? Do they use the same config file to connect to the database with the same host, user and password? I suspect it is that issue…

[Pedroski55]

Hi again and thanks!

Yes, the systems are set exactly the same, except for the location of some css files. I always test new stuff at home first, iron out any problems, then upload.

The first part of my php file (not posted) saves the students answers to surveyU6_3_data and that works fine on the webpage. I have hundreds of answers.

That is this:

$mysql = 'INSERT INTO surveyU6_3_data(surveyornr, weeknr, Answer1, Answer2, Answer3, Answer4, Answer5, Answer6, Answer7, Answer8, Answer9, Answer10, Answer11, Answer12, Answer13, Answer14) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
		$mystmt = $pdo->prepare($mysql);
		$mystmt->execute([$surveyornr, $weeknr, $q1, $q2, $q3, $q4, $q5, $q6, $q7, $q8, $q9, $q10, $q11, $q12, $q13, $q14]);

The part I posted above should write to surveyU6_3_totals , immediately after the answers are saved to surveyU6_3.

I do not see any PHP error in the webpage error.log, but I am not getting any data in surveyU6_3_totals and I don’t know why!! It works fine on my laptop.

Like I said above, I echo out the SQL UPDATE commands to the success page, to see what is happening.

I can copy and paste any of them into phpmyadmin on the webhost and they work, no problem!!

That makes it hard to understand why they won’t work in the PHP script!!

Very grateful for any tips!

[phdr]

I have seen cases where if you supply more values in the execute() call, then there are place-holders in the query (which is a programming mistake), that the query doesn’t execute, and there are no php or PDO errors. I don’t know if this is/was a bug and for which versions of php/database it occurred.

Since you are not actually using a prepared query with ANY place-holders in it, don’t use prepare() and execute(). Just use the query() method.

BTW - The reason your attempts a using a prepared query failed, is because you cannot supply column names (identifiers) via prepared query place-holders. You can only supply DATA values via place-holders.

Next, the only time you should have a try/catch block in your code are for errors that the visitor caused and can correct, such as inserting/updating duplicate values. Since this update query cannot fail for that reason, don’t even bother with the try/catch as part of its logic.

[Pedroski55]

Thank you thank you thank you!

I had:

$mycount = 'UPDATE surveyU6_3_totals SET ' . $column . ' = ' . $column . ' + 1 WHERE Qnr = \'' . $questionnr . '\'';
$mystmt2 = $pdo->prepare($mycount);
$mystmt2->execute([$column, $column, $questionnr]);

I changed:

$mystmt2 = $pdo->prepare($mycount);
$mystmt2->execute([$column, $column, $questionnr]);

for

$mystmt2 = $pdo->query($mycount);

First, I tried at home and then on the webhost!! Both accept

$mystmt2 = $pdo->query($mycount);

This UPDATES my totals table!!

Thank you very much!!