Jump to content

Update SQL Server DB in php with stored procedure


dragonfaster

Recommended Posts

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

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

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

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

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

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.

ex.png

Edited by dragonfaster
Link to comment
Share on other sites

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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...