dragonfaster Posted April 25, 2017 Share Posted April 25, 2017 Hi,As the title said, i'm trying to update an SQL Server DB with php. From my code below, you can see that i call once a stored proc to get information who will be in a table. One of the row allow me (supposed to be)(the last row who is an input), to change an other one (row name GL). But with the button save, it seems like nothing change at all. I tried to make some log with console and i can see test1 and test2. So it seems like it can go throught but doesnt ajust anything. I really don't know anymore and i really need your help. Any ideas or help will be really really appreciate. More on that, i'm not sure if i do it well about sql injection... <?php if(isset($_POST["test"])) { $NewValue = $_POST["noGL" . $_POST["TLZno"]]; $TLZ = $_POST["TLZ"];?> <script> console.log ("test1"); </script> <?php $sql1 = "UpdateGL @CIE ='RAL', @TLZ='$TLZ', @NOGL='$NewValue'";?> <script> console.log ("test2"); </script> <?php } else { echo "Rien!"; } ?> <button class="btn btn-success btn-sm dropdown-toggle save" name='submit' id='submit' type="submit" value="Enregistrer"> <i class="fa fa-floppy-o fa-lg" aria-hidden="true"> Enregistrer</i> </button> <script> $(document).ready(function(){ $('.save').click(function(){ $('#GL_form').submit(); }); }); </script> </div><!-- Fin class pull-left--> <div class="panel panel-primary filterable"> <div class="panel-heading"> <h1 class="panel-title" style="font-weight:bold;">Liste des numéros de GL</h1> </div><!--Fin class panel-heading--> <div class="table-responsive"> <form id="GL_form" method="post" action="ListeGL.php"> <input type="hidden" id="test" name="test"/> <table id="employees" class="table table-highlight"> <thead> <tr class="filters" id="filters"> <th style="text-align: center;"># TLZ</th> <th style="text-align: left;">Description</th> <th style="text-align: center;">D/C</th> <th style="text-align: center;"># GL</th> <th style="text-align: left;">Nouveau # GL</th> </tr> </thead> <tbody> <?php $sql = "ListeGL"; $result = sqlsrv_query($conn, $sql); $row_count = sqlsrv_num_rows($result); while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) { //print_r( $row ); // debug code $couleur='black'; $font= 'normal'; $input="a"; if( strstr($row['HTML_CODE'], "BOLD()")){ $font= 'bold'; } if( strstr($row['HTML_CODE'], "BG()")){ $font2= '#D8D8D8'; } if( strstr($row['HTML_CODE'], "INPUT()")){ $input= 'input'; $font2= '#transparent'; } ?> <tr> <?php echo "<tr style=\"font-weight:$font; color:$couleur; background-color:$font2;\">"; ?> <td style="text-align: center;"><?php echo ($row['TLZno']); ?></td> <td style="text-align: left;"><?php echo ($row['TlzDescription']); ?></td> <td style="text-align: center;"><?php echo ($row['TlzType']); ?></td> <td style="text-align: center;"><?php echo ($row['noGL']); ?></td> <td style="font-weight: normal; text-align: left; color: #BDBDBD; "> <<?php echo $input . ' name="noGL' . $row['TLZno'] . '"'; ?> type="text" name="new" value="<?php echo ($row['noGL']); ?>" /> </td> </tr> <?php } ?> </tbody> </table> </form> Link to comment Share on other sites More sharing options...
justsomeguy Posted April 25, 2017 Share Posted April 25, 2017 To avoid SQL injection, you should use prepared statements. The first comment on this page shows an example of using it to execute a stored procedure: http://php.net/manual/en/function.sqlsrv-prepare.php That being said, you build that $sql1 variable but you never execute that query. You're just building a string of SQL in PHP, but not doing anything with it. Link to comment Share on other sites More sharing options...
dragonfaster Posted April 25, 2017 Author Share Posted April 25, 2017 so if i want to do something with $sql1, what i must do exactly? sorry, but i'm just a beginner. Link to comment Share on other sites More sharing options...
justsomeguy Posted April 26, 2017 Share Posted April 26, 2017 You need to send the query to the SQL server. Check the examples on that documentation page I linked to, it starts with sqlsrv_connect to connect to the database server, and then you can use sqlsrv_prepare and sqlsrv_execute. Like I mentioned, the first comment on that page shows that process using a stored procedure also. Link to comment Share on other sites More sharing options...
dragonfaster Posted April 26, 2017 Author Share Posted April 26, 2017 My connection is way before that code, that's why i didn't copy it. Connection to the database work well because i can receive the information correctly. So even if i add this part to the code, it still doesn't work. I'm really not sure if i do it well. Sorry, i'm really confuse. <?php if(isset($_POST["test"])) { $NewValue = $_POST["noGL" . $_POST["TLZno"]]; $TLZ = $_POST["TLZ"];?> <script> console.log ("test1"); </script> <?php $sql1 = "UpdateGL @CIE ='RAL', @TLZ='$TLZ', @NOGL='$NewValue'";?> <script> console.log ("test2"); </script> <?php $stmt = sqlsrv_prepare( $conn, $sql1); if( !$stmt ) { die( print_r( sqlsrv_errors(), true)); $result = sqlsrv_execute($stmt); $row = sqlsrv_fetch_array($stmt); } } else { echo "Rien!"; } ?> Link to comment Share on other sites More sharing options...
dsonesuk Posted April 26, 2017 Share Posted April 26, 2017 Is this right? $NewValue = $_POST["noGL" . $_POST["TLZno"]]; If $_POST["TLZno"]] = "test" You are attempting to get value from $_POST array with name of "noGLtest" ($_POST["noGLtest"]) OR are you trying join both values If $_POST["noGL"] = "BillAnd" and $_POST["TLZno"] ="Ben, So $NewValue will equal "BillAndBen" Link to comment Share on other sites More sharing options...
dragonfaster Posted April 26, 2017 Author Share Posted April 26, 2017 (edited) As i show on the result, it explain that TLZno and noGL must be link each others to know exactly with on of GL is change and apply the change. TLZno always stay the same. I must do it because in my request, i used a loop where i don't have any information how many row will be. I don't know if you understand what i means? So if i understand well your question, the second one is what i meant. Edited April 26, 2017 by dragonfaster Link to comment Share on other sites More sharing options...
dsonesuk Posted April 26, 2017 Share Posted April 26, 2017 Then it should be $NewValue = $_POST["noGL"] . $_POST["TLZno"]; Which will mean $NewValue will equal '888822' Link to comment Share on other sites More sharing options...
dragonfaster Posted April 26, 2017 Author Share Posted April 26, 2017 Nope, i don't want to add TLZno and noGL. The input show what is already noGL, i can write a new number and then, it will change noGL. I want to use TLZno and noGL linked to be able to know exactly with row is change. Link to comment Share on other sites More sharing options...
dsonesuk Posted April 26, 2017 Share Posted April 26, 2017 The First is the correct meaning not second, attribute name = "noGL22" with 22 coming from field 'TLZno' from database table row, So $_POST["noGL" . $_POST["TLZno"]]; will equal whatever value is in input with attribute name "noGL22". which at present will be 8888. You have two name attributes (name="new") for single input though which is not allowed. Link to comment Share on other sites More sharing options...
dsonesuk Posted April 26, 2017 Share Posted April 26, 2017 You should check out update here https://www.w3schools.com/php/php_mysql_update.asp 'UpdateGL' should be UPDATE GL, you are not setting the fields to be updated, and don't have where clause, which will cause ALL records to be updated. Link to comment Share on other sites More sharing options...
dragonfaster Posted April 26, 2017 Author Share Posted April 26, 2017 But UpdateGL is the name of the stored procedure that i use 3 parameters (@CIE, @TLZ and @NOGL) <?php $sql1 = "UpdateGL @CIE ='RAL', @TLZ='$TLZ', @NOGL='$NewValue'";?> Link to comment Share on other sites More sharing options...
justsomeguy Posted April 26, 2017 Share Posted April 26, 2017 Are you sure that's the right syntax, you don't need EXEC? Again, this is from the documentation that I linked to: // EXEC the procedure, {call stp_Create_Item (@Item_ID = ?, @Item_Name = ?)} seems to fail with various errors in my experiments $sql = "EXEC stp_Create_Item @Item_ID = ?, @Item_Name = ?"; You should really use those placeholders with prepared statements also instead of injecting the variables into your SQL query, maybe those variables have a value which is breaking your SQL code. That's the point of prepared statements, to avoid situations like that. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now