Multiple variables in server update script

Question

Hi I have a table that records details which then after the record is saved I can use the link to update the diary. one field is a simple Job reference, the second is basically all the rest, name address etc inserted into a memo field in the diary, this is what I've come up with can I have some guidance please.

    <?php
    //record identifier date format 0000-00-00 same as server
    $Dt = $_REQUEST['DT'];
    // text output for appontment
    $A = $_REQUEST['A'];
    $B = $_REQUEST['B'];
    $C = $_REQUEST['C']; 
    $D = $_REQUEST['D'];
    $E = $_REQUEST['E'];
    $F = $_REQUEST['F']; 
    $G = $_REQUEST['G']; 
    $H = $_REQUEST['H'];
    $I = $_REQUEST['I'];
    $J = $_REQUEST['J'];
    $K = $_REQUEST['K']; 
    $L = $_REQUEST['L'];
    $M = $_REQUEST['M'];
    $N = $_REQUEST['N']; 
    // field names to reference
    $APP = $_REQUEST['P'];
    $JD = $_REQUEST['Q'];
    // Field content
    $JN = $_REQUEST['JID'];
    $Desc = $"" . $A . "" . $B . " " . $C . ". " . $D . ", " . $E . " " . $F . " " . $G . " TF " . number_format($H,0, $decimal_point,"") . " " . $I . ", " . $J . " Walls, " . number_format($K,0, $decimal_point,"") . "Beds.  " . $J . " " . $K . " boiler, with " . number_format($L,0, $decimal_point,"") . " radiators. notes " . $M . " observations " . $N . "";
    ?>

    <?php
    $servername = "localhost:3306";
    $username = "xxxdjw";
    $password = "xxxxxx";
    $dbname = "xxxxx";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 

    $sql = "UPDATE masterdiary SET $APP ='$JN', $JD = '$Desc'  WHERE date = '$dt'";

    if ($conn->query($sql) === TRUE) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record: " . $conn->error;
    }

    $conn->close();
    ?>

Or would it be easier to try and write a trigger?

This code is brutally vulnerable to SQL injection. Please parameterize your queries. https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Cruncher, Oct 22 '17 at 16:41
You have one update. If you think about using a trigger, then which statement would trigger that? I don't really understand what you are asking. — trincot, Oct 22 '17 at 16:53

Rahul Cv · Answer 1 · 2017-10-22T16:48:10.670

0

It is advisable to use trigger for this

Sample trigger given below

CREATE TRIGGER blog_after_insert AFTER INSERT ON blog FOR EACH ROW BEGIN

    IF NEW.deleted THEN
        SET @changetype = 'DELETE';
    ELSE
        SET @changetype = 'NEW';
    END IF;

    INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype);

END$$

edited Oct 22 '17 at 16:48

answered Oct 22 '17 at 16:41

Rahul Cv

725
5
12

Yes I was wondering about triggers, but the diary has 8 possible job appointments, and 8 job description fields so a trigger could clear an existing appointment, as it would not be able to identify which job ...or have I got that wrong? – Dai13 Oct 22 '17 at 16:55
Could you please share your table desc – Rahul Cv Oct 22 '17 at 16:58
the client table has multiple fields, but has fields that match 2 of the Diary field appointment number no (field name) and appointment description (field name) .. plus day date. the diary has 8 appointment slots and 8 description slots, but the description slot content is generated by multiple fields from the client table – Dai13 Oct 22 '17 at 18:49

Multiple variables in server update script

1 Answers1