Jump to content
dragonfaster

Update SQL Server DB in php with stored procedure

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>

 

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
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!";
        
    }
?>

 

Share this post


Link to post
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"

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...