Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Update SQL Query with populated variables from AJAX functions over multiple PHP Pages

i try to get help with that question.

All in all Q: It doesnt Update my DB entry like this Step by Step Order how i think it could be done.

its a bit difficult to explain, but i try to explain it step by step with minimal and readable Code. I use the original code, its hard to convert it in reproducible Examples.

A.1 Page ma_aktuelle_ReadOut.php There is a php Part

 <?php echo "<a href='ma_Testende.php?TestergebnisID=&TestaufstellungID=". $row['TestaufstellungID']."&TesterID=".$row['TesterID']."' title='Test stoppen' data-toggle='tooltip' class='stoppen'>   <span class='glyphicon glyphicon-stop'></span></a>";
?>

When i click this link the following javascript function is called and ask me "really stop?"

<script language="JavaScript" type="text/javascript">
$(document).ready(function(){
  $("a.stoppen").click(function(e){
   if(!confirm('Wirklich stoppen?')){
    e.preventDefault();
    $('.alert').show()
    return false;
    }
    return true;
            });
        });
</script>
<style>
 .alert {
  display: none;
    }
</style>

When i cklick "yes" it opens the second Page.

A 2 Page ma_Testende.php On this Page are 2 AJAX JS Functions. The first Ajax is asking for "Datum" via type:get from the following next page and wait till success (see Page B 3):

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js">

/* function to get Stoptime for Cycle from DB.TesterCycleCount zu erhalten  */ 
$(document).ready(async function(){
var Datum;
var TesterID = "<?php echo $_GET['TesterID']; ?>"; /* value from TesterID */ 
await $.ajax({ /* First Ajax function */
            url: 'ma_get-TesterID_Testende.php',
            type: 'get', 
            data: {TesterID:TesterID}, 
            dataType: 'json',
            success:function(response){ 
                var CID = response['CID'];
                Datum = response['Datum'];
                console.log(response);
            },
             error: function(jqxhtt, status, exception) {
                 console.log(exception);
         alert('Exception:', exception)
            }
        });
console.log();
        var TestaufstellungID = "<?php echo $_GET['TestaufstellungID']; ?>";
         $.ajax({ /* Second Ajax function */
            url: 'ma_TestendeSQL.php',
            type: 'get', 
            data: {TestaufstellungID:TestaufstellungID, Datum: Datum}, 
            dataType: 'json',
            success:function(data){ 
            alert('Successfully called');
     },
     error: function(jqxhr, status, exception) {
         console.log(exception);
         alert('Exception:', exception)
            }
        });
        });
</script>

B 3 Page ma_get-TesterID_Testende.php

<?php
$cinfo = array(
    "Database" => $database,
    "UID" => $username,
    "PWD" => $password
);
$conn = sqlsrv_connect($server, $cinfo);
                $sqlreadZeit = "Select TOP 1 CID,Datum from DB.dbo.TesterCycleCount where TesterID = '".$_GET['TesterID']."' order by Datum DESC";
                $result1 = sqlsrv_query($conn, $sqlreadZeit);
                $zeiten_arr = array();
                while ($row = sqlsrv_fetch_array($result1, SQLSRV_FETCH_ASSOC)) {
                $CID = $row['CID'];
                $Datum = $row['Datum']->format('d.m.Y h:m:s');
                $zeiten_arr[] = array("CID" => $CID, "Datum" => $Datum);
                                }
    header('Content-type: application/json');
  echo json_encode($zeiten_arr); 
?>

Back with the "Datum" the second AJAX is called (see Page A 2) With the "Datum" and "TestaufstellungID" as variable it should be call the next Page and Update the DB entry with the populated variablles.

B. 4 Page ma_TestendeSQL.php

<?php
$cinfo = array(
    "Database" => $database,
    "UID" => $username,
    "PWD" => $password
);
$conn = sqlsrv_connect($server, $cinfo);

$TestaufstellungID = $_GET['TestaufstellungID'];
$Testende= $_GET['Datum'];
$Testdatum = date('Y-d-m');

$stop = $connection->prepare("WITH UpdateTestende AS (
  SELECT TOP 1  * from DB.dbo.Testergebnisse 
  WHERE TestaufstellungID = :TestaufstellungID
  ORDER BY TestergebnisID DESC 
)
update UpdateTestende 
set Testende = :Testende,
Datum = :Testdatum");
$stop->execute(array(':TestaufstellungID' => $TestaufstellungID, ':Testdatum' => $Testdatum, ':Testende' => $Testende));

    header('Content-type: application/json');
?>

The php variable $Testende get the populated "Datum" from the Ajax functions. All in all at the end it should be Update, when i click on the link the ( Page A 1) my DB entry with the populated "Datum" which i get from the first Ajax call ( Page A 2 ) from the SQL Query ( Page B 3) back to the second AJAX Call ( Page A 2 ) than with the data: {TestaufstellungID:TestaufstellungID, Datum: Datum} to the last page ( Page B 4)

But it doesnt Update my DB entry like this Step by Step Order how i think it could be done.

Encapsulated is the SQL-Code working fine. With the Code header('Content-type: application/json'); the browser tell me the following when i click on the link from ( Page A 1 )

SyntaxError: JSON.parse: unexpected character at line 1 column 1 of the JSON data

Thats why i posted all the Step i think on one point the variables are not passed right to the next page or they are empty becasue the code is not executed in the right order Server/Client PHP/JS or Asynchronous problem... The console.log tell me nothing. At the moment i have no idea where to start with the debugging?

Hope someone can help me. thx

Edit: iam pretty sure the ajax call is empty, but i dont see it in which step the values getting empty

Edit2: AJAX Call is empty or is not starting. Further invstigation: The Ajax alert me the error part with empty exception and dont alert me the success part. So it doesnt go to the page ma_get-TesterID_Testende.php or it doesnt return back the Datum . Could be not enabled Cross-Site-Scripting be the Problem?

But in another Page is a similiar Ajax Call working fine.

$(document).ready(function(){

var TesterID = "<?php echo $_GET['TesterID']; ?>"; /* value der Tester erhalten */ 

        $.ajax({ /* AJAX aufrufen */
            url: 'ma_get-TesterID.php',
            type: 'get', /* Methode zum übertragen der Daten */
            data: {TesterID:TesterID}, /* Daten zu übermitteln */
            dataType: 'json',
            success:function(response){ /* Die zurückgegebenene Daten erhalten */

                var len = response.length;

                $("#Teststart").empty(); /* Die erhaltenden Daten werden bei der ID angezeigt */
                for( var i = 0; i<len; i++){
                    var CID = response[i]['CID'];
                    var Datum = response[i]['Datum'];

                    $("#Teststart").append("<option value='"+Datum+"'>"+Datum+"</option>");

                }
            }
        });


    $("#TesterID").change(function(){ /* Wenn du änderst und vom Select Feld auswählst */
        var TesterID = $(this).val(); /* value der Tester erhalten */ 

        $.ajax({ /* AJAX aufrufen */
            url: 'ma_get-TesterID.php',
            type: 'get', /* Methode zum übertragen der Daten */
            data: {TesterID:TesterID}, /* Daten zu übermitteln */
            dataType: 'json',
            success:function(response){ /* Die zurückgegebenene Daten erhalten */

                var len = response.length;

                $("#Teststart").empty(); /* Die erhaltenden Daten werden bei der ID angezeigt */
                for( var i = 0; i<len; i++){
                    var CID = response[i]['CID'];
                    var Datum = response[i]['Datum'];

                    $("#Teststart").append("<option value='"+Datum+"'>"+Datum+"</option>");

                }
            }
        });
    });

});

In this example the Ajax Call starts when i change the value from a Dropdown selection Form. Is there a difference?

How this Ajax should work i try to explain in my other question step by step, how it my application should be execute.

Update SQL Query with populated variables from AJAX functions over multiple PHP Pages

Edit 3: JQuery Version: https://code.jquery.com/jquery-3.4.1.js

like image 600
Daniel Avatar asked May 21 '19 13:05

Daniel


1 Answers

One alternative is use $_SESSION superglobal; I offer it as this question is tagged as PHP rather than AJAX/JSON, and to avoid encode-decode common issues with JSON values or arrays. If you're able to, change your strategy and use client-side to broadcast data and stop normal flow in an error response event.

With a started session_start() you can use an inner temporary array in $_SESSION to attach/detach step by step data, in this example "step" array, like this:

<?php
// step 1
$_SESSION['step'][1]['TesterID']            = 64;
$_SESSION['step'][1]['var2']                = 'bar';
// step 2
$_SESSION['step'][2]['TestaufstellungID']   = 65;
$_SESSION['step'][2]['var4']                = 100;
$_SESSION['step'][2]['var5']                = 256.04;
?>

When you send data, over AJAX, is not necesarry to convert it to JSON, because you can work with $_POST object. Meanwhile, response can give to you every $_SESSION['step'] values in JSON format.

<?php
header("Content-type:application/json")
// conditions goes here
if ( $conditions == true ) {
    echo json_encode($_SESSION['step']);
}
?>

This way, and considering is a "step by step" process, you can chose to save an json_encode() object updating in every step, or use session superglobal to save it only when process finish, considering all "step" array or just relevant values, users id in this case

https://www.php.net/manual/es/reserved.variables.session.php

https://www.php.net/manual/es/function.session-id.php

https://www.php.net/manual/es/function.json-encode.php

like image 186
Benjamin Avatar answered Nov 15 '22 02:11

Benjamin