Jump to content

Adamhumbug

Members
 View Profile  See their activity

  • Posts

    585

  • Joined

  • Last visited

 Content Type 

Posts posted by Adamhumbug

    Replace Into blanking some fields

    in PHP Coding Help

    Posted · Edited by Adamhumbug

    17 minutes ago, Barand said:

    Try setting the cpId value to either 0 or NULL for new items.

    It would appear that you are not setting the EMULATE_PREPARES option to "false" in your connection code on leaving it as "true" (default). Set it to false so prepared queries are truly prepared.

    Note that with true prepares you will not be able to use execute array elements multiple times (eg ":minQty" appears twice in the query but only once in the array). Rewrite the query as 

    INSERT INTO consumable_price (id, min_qty, max_qty, GBP, USD, CAD, EUR) 
VALUES (:cpId, :minQty, :maxQty, :GBP, :USD, :CAD, :EUR)
ON DUPLICATE KEY 
UPDATE min_qty = VALUES(minQty)
     , max_qty = VALUES(maxQty)
     , GBP = VALUES(GBP)
     , USD = VALUES(USD)
     , CAD = VALUES(CAD)
     , EUR = VALUES(EUR);

    to re-use the values.

    i have set the emulate in the connection file to 

    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

    and have used the query 

    if (isset($_POST['updateConsumablePrice'])) {
		$sql = "INSERT INTO consumable_price (id, min_qty, max_qty, GBP, USD, CAD, EUR) 
		VALUES (:cpId, :minQty, :maxQty, :GBP, :USD, :CAD, :EUR)
		ON DUPLICATE KEY 
		UPDATE min_qty = VALUES(minQty)
			 , max_qty = VALUES(maxQty)
			 , GBP = VALUES(GBP)
			 , USD = VALUES(USD)
			 , CAD = VALUES(CAD)
			 , EUR = VALUES(EUR)";
            $stmt = $pdo->prepare($sql);
        for ($i = 0; $i < count($_POST['minQty']); $i++) {
			$cpId = $_POST['cpId'][$i];
            $minQty = $_POST['minQty'][$i];
            $maxQty = $_POST['maxQty'][$i];
            $GBP = $_POST['GBP'][$i];
            $USD = $_POST['USD'][$i];
            $CAD = $_POST['CAD'][$i];
            $EUR = $_POST['EUR'][$i];

            
            $stmt->execute([
				':cpId' => $cpId,
                ':minQty' => $minQty,
                ':maxQty' => $maxQty,
                ':GBP' => $GBP,
                ':USD' => $USD,
                ':CAD' => $CAD,
                ':EUR' => $EUR  
            ]);
        }
    }

    I am now getting the error

    Unknown column 'minQty' in 'field list' in...

     

    Screenshot2023-09-14at14_45_48.thumb.png.141b98c5e736c8b5295f7f2b94fcef40.png

    Replace Into blanking some fields

    in PHP Coding Help

    Posted

    This is the $_POST when i add a new line 

    array(11) {
  ["itemName"]=>
  string(23) "Item 1"
  ["itemCategory"]=>
  string(1) "4"
  ["chargeBy"]=>
  string(1) "1"
  ["minQty"]=>
  array(8) {
    [0]=>
    string(1) "1"
    [1]=>
    string(3) "500"
    [2]=>
    string(4) "1000"
    [3]=>
    string(4) "2000"
    [4]=>
    string(4) "3000"
    [5]=>
    string(4) "6000"
    [6]=>
    string(5) "10000"
    [7]=>
    string(2) "11"
  }
  ["maxQty"]=>
  array(8) {
    [0]=>
    string(3) "499"
    [1]=>
    string(3) "999"
    [2]=>
    string(4) "1999"
    [3]=>
    string(4) "2999"
    [4]=>
    string(4) "5999"
    [5]=>
    string(4) "9999"
    [6]=>
    string(8) "10000000"
    [7]=>
    string(2) "11"
  }
  ["GBP"]=>
  array(8) {
    [0]=>
    string(3) "500"
    [1]=>
    string(1) "1"
    [2]=>
    string(3) "0.9"
    [3]=>
    string(3) "0.8"
    [4]=>
    string(3) "0.7"
    [5]=>
    string(3) "0.6"
    [6]=>
    string(3) "0.5"
    [7]=>
    string(2) "11"
  }
  ["USD"]=>
  array(8) {
    [0]=>
    string(3) "750"
    [1]=>
    string(3) "1.5"
    [2]=>
    string(4) "1.35"
    [3]=>
    string(3) "1.2"
    [4]=>
    string(4) "1.05"
    [5]=>
    string(3) "0.9"
    [6]=>
    string(4) "0.75"
    [7]=>
    string(2) "11"
  }
  ["CAD"]=>
  array(8) {
    [0]=>
    string(3) "875"
    [1]=>
    string(4) "1.75"
    [2]=>
    string(4) "1.58"
    [3]=>
    string(3) "1.4"
    [4]=>
    string(4) "1.23"
    [5]=>
    string(4) "1.05"
    [6]=>
    string(3) "0.9"
    [7]=>
    string(2) "11"
  }
  ["EUR"]=>
  array(8) {
    [0]=>
    string(1) "0"
    [1]=>
    string(1) "0"
    [2]=>
    string(1) "0"
    [3]=>
    string(1) "0"
    [4]=>
    string(1) "0"
    [5]=>
    string(1) "0"
    [6]=>
    string(1) "0"
    [7]=>
    string(2) "11"
  }
  ["cpId"]=>
  array(8) {
    [0]=>
    string(1) "1"
    [1]=>
    string(1) "2"
    [2]=>
    string(1) "3"
    [3]=>
    string(1) "4"
    [4]=>
    string(1) "5"
    [5]=>
    string(1) "6"
    [6]=>
    string(1) "7"
    [7]=>
    string(0) ""
  }
  ["updateConsumablePrice"]=>
  string(0) ""
}

     

    Replace Into blanking some fields

    in PHP Coding Help

    Posted

    10 minutes ago, requinix said:

    As the term would hopefully suggest, ON DUPLICATE KEY requires that you have a unique (or primary) key in your field list. That's how it knows there's a duplicate: you tried to insert something that exists. If you don't include any keys then you're simply going to insert new data.

    I'm inclined to think that your min_qty, max_qty, and currency fields are not part of a unique key.

    Put the ID back.

    None of this helps me to understand why your form, which is clearly designed around editing an existing ID, will ever have to deal with a situation where the data to be edited does not exist yet.

    So this 

    if (isset($_POST['updateConsumablePrice'])) {
		$sql = "INSERT INTO consumable_price (id, min_qty, max_qty, GBP, USD, CAD, EUR) 
                    VALUES (:cpId, :minQty, :maxQty, :GBP, :USD, :CAD, :EUR)
                    ON DUPLICATE KEY 
                    UPDATE min_qty = :minQty, max_qty = :maxQty, GBP = :GBP, USD = :USD, CAD = :CAD, EUR = :EUR";
            $stmt = $pdo->prepare($sql);
        for ($i = 0; $i < count($_POST['minQty']); $i++) {
			$cpId = $_POST['cpId'][$i];
            $minQty = $_POST['minQty'][$i];
            $maxQty = $_POST['maxQty'][$i];
            $GBP = $_POST['GBP'][$i];
            $USD = $_POST['USD'][$i];
            $CAD = $_POST['CAD'][$i];
            $EUR = $_POST['EUR'][$i];

            
            $stmt->execute([
				':cpId' => $cpId,
                ':minQty' => $minQty,
                ':maxQty' => $maxQty,
                ':GBP' => $GBP,
                ':USD' => $USD,
                ':CAD' => $CAD,
                ':EUR' => $EUR  
            ]);
        }
    }

    is now working for the updates.

    But it is not adding a new item.

    Again, the screen reloads but nothing happens in the database.

     

    For reference of what i am doing i have attached a screen shot.

    You can add a new line to add another breakpoint

     

    Screenshot2023-09-14at13_50_17.thumb.png.ee03f94286c3bb3d42d79c127595fcd6.png

     

    Replace Into blanking some fields

    in PHP Coding Help

    Posted · Edited by Adamhumbug

    I am not sure why my code here is not working - but its not.

     

    This is what happens when i echo $_POST 

    array(11) {
  ["itemName"]=>
  string(23) "Large Format RFID Cards"
  ["itemCategory"]=>
  string(1) "4"
  ["chargeBy"]=>
  string(1) "1"
  ["minQty"]=>
  array(7) {
    [0]=>
    string(1) "5"
    [1]=>
    string(3) "500"
    [2]=>
    string(4) "1000"
    [3]=>
    string(4) "2000"
    [4]=>
    string(4) "3000"
    [5]=>
    string(4) "6000"
    [6]=>
    string(5) "10000"
  }
  ["maxQty"]=>
  array(7) {
    [0]=>
    string(3) "499"
    [1]=>
    string(3) "999"
    [2]=>
    string(4) "1999"
    [3]=>
    string(4) "2999"
    [4]=>
    string(4) "5999"
    [5]=>
    string(4) "9999"
    [6]=>
    string(8) "10000000"
  }
  ["GBP"]=>
  array(7) {
    [0]=>
    string(3) "500"
    [1]=>
    string(1) "1"
    [2]=>
    string(3) "0.9"
    [3]=>
    string(3) "0.8"
    [4]=>
    string(3) "0.7"
    [5]=>
    string(3) "0.6"
    [6]=>
    string(3) "0.5"
  }
  ["USD"]=>
  array(7) {
    [0]=>
    string(3) "750"
    [1]=>
    string(3) "1.5"
    [2]=>
    string(4) "1.35"
    [3]=>
    string(3) "1.2"
    [4]=>
    string(4) "1.05"
    [5]=>
    string(3) "0.9"
    [6]=>
    string(4) "0.75"
  }
  ["CAD"]=>
  array(7) {
    [0]=>
    string(3) "875"
    [1]=>
    string(4) "1.75"
    [2]=>
    string(4) "1.58"
    [3]=>
    string(3) "1.4"
    [4]=>
    string(4) "1.23"
    [5]=>
    string(4) "1.05"
    [6]=>
    string(3) "0.9"
  }
  ["EUR"]=>
  array(7) {
    [0]=>
    string(1) "0"
    [1]=>
    string(1) "0"
    [2]=>
    string(1) "0"
    [3]=>
    string(1) "0"
    [4]=>
    string(1) "0"
    [5]=>
    string(1) "0"
    [6]=>
    string(1) "0"
  }
  ["cpId"]=>
  array(7) {
    [0]=>
    string(1) "1"
    [1]=>
    string(1) "2"
    [2]=>
    string(1) "3"
    [3]=>
    string(1) "4"
    [4]=>
    string(1) "5"
    [5]=>
    string(1) "6"
    [6]=>
    string(1) "7"
  }
  ["updateConsumablePrice"]=>
  string(0) ""
}

    And this is the php 

    if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    if (isset($_POST['updateConsumablePrice'])) {
		$sql = "INSERT INTO consumable_price (min_qty, max_qty, GBP, USD, CAD, EUR) 
                    VALUES (:minQty, :maxQty, :GBP, :USD, :CAD, :EUR)
                    ON DUPLICATE KEY 
                    UPDATE min_qty = :minQty, max_qty = :maxQty, GBP = :GBP, USD = :USD, CAD = :CAD, EUR = :EUR";
            $stmt = $pdo->prepare($sql);
        for ($i = 0; $i < count($_POST['minQty']); $i++) {
            $minQty = $_POST['minQty'][$i];
            $maxQty = $_POST['maxQty'][$i];
            $GBP = $_POST['GBP'][$i];
            $USD = $_POST['USD'][$i];
            $CAD = $_POST['CAD'][$i];
            $EUR = $_POST['EUR'][$i];

            
            $stmt->execute([
                ':minQty' => $minQty,
                ':maxQty' => $maxQty,
                ':GBP' => $GBP,
                ':USD' => $USD,
                ':CAD' => $CAD,
                ':EUR' => $EUR  
            ]);
        }
    }
}

    The post is happening but no new data is going in and changes are not being made.

    There are no errors shown.

    Replace Into blanking some fields

    in PHP Coding Help

    Posted

    2 hours ago, mac_gyver said:

    From the documentation -

     

    it seems like you should be using an UPDATE query for this operation?

    Thank you for this.

    I am updating but there is also the option on the page to add new data.

    I was torn between ON DUPLICATE KEY or REPLACE INTO.

    I dont know if one of these is better than the other.

    Replace Into blanking some fields

    in PHP Coding Help

    Posted

    I have an insert function that is updating the records that i change fine - id is the key. 

    if (isset($_POST['updateConsumablePrice'])) {
        for ($i = 0; $i < count($_POST['minQty']); $i++) {
            $cpId = $_POST['cpId'][$i];
            $minQty = $_POST['minQty'][$i];
            $maxQty = $_POST['maxQty'][$i];
            $GBP = $_POST['GBP'][$i];
            $USD = $_POST['USD'][$i];
            $CAD = $_POST['CAD'][$i];
            $EUR = $_POST['EUR'][$i];

            $sql = "REPLACE INTO consumable_price (id, min_qty, max_qty, GBP, USD, CAD, EUR) 
                    VALUES (:cpId, :minQty, :maxQty, :GBP, :USD, :CAD, :EUR)";
            $stmt = $pdo->prepare($sql);
            $stmt->execute([
                ':cpId' => $cpId,
                ':minQty' => $minQty,
                ':maxQty' => $maxQty,
                ':GBP' => $GBP,
                ':USD' => $USD,
                ':CAD' => $CAD,
                ':EUR' => $EUR  
            ]);
        }
    }

    The issue that i have is that one of my columns which should not be being touched by this insert is being blanked to zero.

    Is there something obvious that i am doing wrong or a better way of doing this?

    Get data out of array without for each

    in PHP Coding Help

    Posted · Edited by Adamhumbug

    I have an array. 

    Array
(
    [0] => Array
        (
            [id] => 19
            [section_id] => 4
            [sectionName] => x
            [itemName] => Item 1
            [GBP] => 0
            [USD] => 0
            [CAD] => 0
            [EUR] => 0
            [charge_by_id] => 1
            [consumable] => 1
            [cMIN] => 1
            [cMAX] => 499
            [cGBP] => 500
            [cUSD] => 750
            [cCAD] => 875
        )

    [1] => Array
        (
            [id] => 19
            [section_id] => 4
            [sectionName] => x
            [itemName] => Item 1
            [GBP] => 0
            [USD] => 0
            [CAD] => 0
            [EUR] => 0
            [charge_by_id] => 1
            [consumable] => 2
            [cMIN] => 500
            [cMAX] => 999
            [cGBP] => 1
            [cUSD] => 1.5
            [cCAD] => 1.75
        )

    [2] => Array
        (
            [id] => 19
            [section_id] => 4
            [sectionName] => x
            [itemName] => Item 1
            [GBP] => 0
            [USD] => 0
            [CAD] => 0
            [EUR] => 0
            [charge_by_id] => 1
            [consumable] => 3
            [cMIN] => 1000
            [cMAX] => 1999
            [cGBP] => 0.9
            [cUSD] => 1.35
            [cCAD] => 1.58
        )

    [3] => Array

    i will foreach through most of it to get the data such as cMIN and cMAX on their own lines.

    I need to get the item name out of this once, rather than once per line - the query means that the data will only be for one item so there is no chance that the item name will be different for any items in the array.

    I have been trying to access the array data with 

    value='$rows[0][itemName]'>

    but i am getting array to string conversion issue.

    How do i get this out of the array without for eaching it?

    Calling a php function from JQuery and wait for the response.

    in Javascript Help

    Posted

    Hi,

    I have a quote system where you can select and item and give a quantity.

    This will then go to the database and work out how much the item is per unit.

    I have the database structure and the code to get the info back.

    My issue is i need the javascript function that is handling the page being updated to wait for the response from Ajax before updating the page.

    It all starts here: 

    $(document).ready(function() {
                updateFormNewQuoteItemChange()
.....
}

    This is called 

    function updateFormNewQuoteItemChange() {

    $isConsumable = $('#itemId').find(':selected ').data('isconsumable')
    $quantity = $('#quantity').val()
    $chargeBy = $('#itemId').find(':selected').data('chargeby')

    if($isConsumable == 1 && $quantity.length !== 0){
        $iid = $('#itemId').find(':selected').val()
      
      
        getConsumablePrices($iid, $quantity);
        
    }
  
   if ($('#currency').length) {
        $ccode = $('#currency').find(':selected').data('currency-code').toLowerCase();
        $price = $('#itemId').find(':selected').data($ccode)
        $('#pricePerItem').val($price)
        $('#pricePerItemDisplay').val($price)
    }
....
}

     

    If the item is consumable and has break points this is called 

    function getConsumablePrices($itemId, $qty) {
                $.ajax({
                    type: 'post',
                    data: {
                        "ajax": 'getConsumablePriceByQty',
                        'itemId': $itemId,
                        'quantity': $qty
                    },
                    dataType: 'json',
                    success: function(resp) {
                        


                        console.log(resp[0]['CAD'])
                    }
                })
            }

    This function returns pricing information which comes from a different table than other pricing.

    I need to feed the result of this AJAX back into the function that it was called from and i think it need it to run synchronously.

    Any advice, help on this would be really appreciated.

    Any further information needed, please do ask.

    Access single array element

    in Javascript Help

    Posted

    I have an ajax function 

    function getConsumablePrices($itemId, $qty) {
  $.ajax({
    type: 'post',
    data: {
      "ajax": 'getConsumablePriceByQty',
      'itemId': $itemId,
      'quantity': $qty
    },
    dataType: 'json',
    success: function(resp) {
      console.log(resp)
    }
  })
}

    This triggers a php function which after doing a fetch all returns an array that looks like this 

    {
  "id": 1,
  "item_id": 19,
  "min_qty": 1,
  "max_qty": 499,
  "GBP": 500,
  "USD": 750,
  "CAD": 875
}

    I am trying to access individual elements in this object.

    I have tried console.log(resp['CAD'] and resp.CAD but i just see undefined.

    How would one go about pulling a single element out of this array?

    (Advice) - Storing price break points in database

    in PHP Coding Help

    Posted

    1 minute ago, Barand said:

    Something like this... 

    +---------------+ 
| invoice_item  |
+---------------+ 
| id            |
| prod_id       |------+
| quantity      |      |      +----------+
|---------------+      |      | price    |
                       |      +----------+
                       +-----<| prod_id  |
                              | minqty   |
                              | maxqty   |
                              | usd      |
                              | cad      |
                              | eur      |
                              +----------+

SELECT i.prod_id
     , p.product_name
     , pr.usd
     , pr.cadd
     , pr.eur
FROM invoice_item i
     JOIN product p ON i.prod_id = p.id
     JOIN price pr ON i.prod_id = pr.prod_id 
                      AND i.quantity BETWEEN pr.minqty AND pr.maxqty

     

    That could be a winner - let me have a play with that and i will report back

     

    (Advice) - Storing price break points in database

    in PHP Coding Help

    Posted

    I have a system that creates quotes.

    Most items are simple, order 2 its 2 times the price, order 10, 10 times the price ect.

    I have some items that break at certain intervals (the intervals are all different.)

    Item A - price is each appart from under 500 where it is a flat £500.

    1 - 499 £500
    500 -999 £1.00
    1,000 - 1,999 £0.90
    2,000 - 2,999 £0.80
    3,000 - 5,999 £0.70
    6,000 - 9,999 £0.60
    10,000 +£0.50

     

    Item B has a similar plan but the breaks are different.

    I have no idea where to start with this and am struggling to come up with a reasonable database structure that doesnt seem overkill.

    Also, i have shown the price in GBP but we also have a price for USD, CAD and EUR which follows the same break points.

    Database and Application Timezone

    in PHP Coding Help

    Posted

    I have an application that records the time that someone last logs in using CURRENT_TIMESTAMP

    The time set in the database is 1 hour ahead of the actual time suggesting that the time zones are out.  I am in the UK.

    I have a function that works out the time  since their last log on and this is reporting 2 hours ago.

    Any advice on time zones for the data base and application?

    Ajax log on handler wrapping redirected to page in response content

    in Javascript Help

    Posted

    I have a log on page that is being handled with ajax and going to a php function.

    The php function returns some text if the log on is not successful - this is being put into an alert that shows on the page.

    If the log in is successful there is a header to redirect to the logged in page.

    The issue is that when logging in successfully, the html that wrapped the error messages is now wrapping the whole logged on page that i am rediurected to.

    This is the script 

    <script>
		$('#login').click(function() {
			$em = $('#email').val();
			$pw = $('#password').val();
			$.ajax({

				type: 'post',
				data: {
					'ajax': 'login',
					'email': $em,
					'pass': $pw

				},
				success: function(resp) {
					if(resp != 'success'){
						$('.alert-container').append("<div class='row alert alert-warning text-center'><span class='text-center'>"+resp+"</span></div>")
						
					}
					

				}
			})
		})
	</script>

    and this is the php - i added the return success to try and avoid this but it has been unsuccesful in remedying my issue. 

    function login($email, $pass){
    include 'includes/dbconn.php';
    $sql = "SELECT pass, id, banned from user where email = :email";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        ':email' => $email
    ]);
    if ($stmt->rowCount() != 1) {
        return "This is a problem with your account, please email the administrator.";
    } else {
        $row = $stmt->fetch();
        if (password_verify($pass, $row['pass'])) {
            $_SESSION['userId'] = $row['id'];
            header('Location: home.php');
            return "success";
        } else {
            return "Your username and password combination do not match.";
        }
    }
}

     

    Calculating "time since" is giving me a negative (CLOSEME)

    in PHP Coding Help

    Posted · Edited by Adamhumbug

    I have a database field that has a datetime in it of 2023-09-04 18:01:50.000000.

    When i try and work out how long ago that was - get a negative number.

    I have tried several "borrowed" functions to work this out and none of them are giving me what i expect.

     

    This is what i am using now: 

    function get_time_ago($time_stamp)
{
    $time_difference = strtotime('now') - $time_stamp;

    if ($time_difference >= 60 * 60 * 24 * 365.242199) {
        /*
         * 60 seconds/minute * 60 minutes/hour * 24 hours/day * 365.242199 days/year
         * This means that the time difference is 1 year or more
         */
        return get_time_ago_string($time_stamp, 60 * 60 * 24 * 365.242199, 'year');
    } elseif ($time_difference >= 60 * 60 * 24 * 30.4368499) {
        /*
         * 60 seconds/minute * 60 minutes/hour * 24 hours/day * 30.4368499 days/month
         * This means that the time difference is 1 month or more
         */
        return get_time_ago_string($time_stamp, 60 * 60 * 24 * 30.4368499, 'month');
    } elseif ($time_difference >= 60 * 60 * 24 * 7) {
        /*
         * 60 seconds/minute * 60 minutes/hour * 24 hours/day * 7 days/week
         * This means that the time difference is 1 week or more
         */
        return get_time_ago_string($time_stamp, 60 * 60 * 24 * 7, 'week');
    } elseif ($time_difference >= 60 * 60 * 24) {
        /*
         * 60 seconds/minute * 60 minutes/hour * 24 hours/day
         * This means that the time difference is 1 day or more
         */
        return get_time_ago_string($time_stamp, 60 * 60 * 24, 'day');
    } elseif ($time_difference >= 60 * 60) {
        /*
         * 60 seconds/minute * 60 minutes/hour
         * This means that the time difference is 1 hour or more
         */
        return get_time_ago_string($time_stamp, 60 * 60, 'hour');
    } else {
        /*
         * 60 seconds/minute
         * This means that the time difference is a matter of minutes
         */
        return get_time_ago_string($time_stamp, 60, 'minute');
    }
}

function get_time_ago_string($time_stamp, $divisor, $time_unit)
{
    $time_difference = strtotime("now") - $time_stamp;
    $time_units      = floor($time_difference / $divisor);

    settype($time_units, 'string');

    if ($time_units === '0') {
        return 'less than 1 ' . $time_unit . ' ago';
    } elseif ($time_units === '1') {
        return '1 ' . $time_unit . ' ago';
    } else {
        /*
         * More than "1" $time_unit. This is the "plural" message.
         */
        // TODO: This pluralizes the time unit, which is done by adding "s" at the end; this will not work for i18n!
        return $time_units . ' ' . $time_unit . 's ago';
    }
}

    when i echo what is being fed to this function i see 2023-09-04 18:01:04 and when strtotime i get 1693850464

    the function is using now to work out the current time so i dont understand why it thinks my date is in the future.

    Current password hashing standard

    in PHP Coding Help

    Posted

    Hi All,

    I have a user table where people will be creating a password.

    There is so much out there on what is the best way to do this but i wanted to get your feelings on what is the most up to date way to hash passwords that i am going to store.

    Is it salt or is that out dated now?

    Duplicating Mysql Table rows but also updating some content

    in PHP Coding Help

    Posted

    1 hour ago, Barand said:

    example... 

    $client = 15;

$res = $pdo->prepare("SELECT   q.id
                             , q.version
                        FROM quote q
                             JOIN (
                                    SELECT client_id
                                         , max(version) as version
                                    FROM quote
                                    WHERE client_id = ?
                                  ) latest USING (client_id, version)
                        ");
$res->execute([ $client ]);
list($quote, $version) = $res->fetch(PDO::FETCH_NUM);
if ($quote)  {
    
    try {
        $pdo->beginTransaction();
        $stmt = $pdo->prepare("INSERT INTO quote (client_id, total_value, job_id, version, name, currency, kit_delivery, kit_return, quote_status_id)
                               SELECT client_id, total_value, job_id, ?, name, currency, kit_delivery, kit_return, quote_status_id
                               FROM quote
                               WHERE id = ?
                              ");
        $stmt->execute([ ++$version, $quote ]);
        $newquote = $pdo->lastInsertId();
    
        $stmt = $pdo->exec("INSERT INTO quote_items (quote_id, item_id, quantity, start_date, end_date, notes, amount_charged_each, original_price_each, chargable_units)
                            SELECT  $newquote, item_id, quantity, start_date, end_date, notes, amount_charged_each, original_price_each, chargable_units
                            FROM quote_items
                            WHERE quote_id = $quote
                           ");
        $pdo->commit();
    }
    catch (PDOException $e)  {
        $pdo->rollBack();
        throw $e;
    }
}
else {
    echo "Client quotes not found";
}

     

    Love this - thank you so much

    Duplicating Mysql Table rows but also updating some content

    in PHP Coding Help

    Posted 

    -- phpMyAdmin SQL Dump
-- version 4.9.11
-- https://www.phpmyadmin.net/
--
-- Host: db5014142045.hosting-data.io
-- Generation Time: Sep 03, 2023 at 08:51 PM
-- Server version: 10.6.12-MariaDB-1:10.6.12+maria~deb11-log
-- PHP Version: 7.0.33-0+deb9u12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `dbs11785372`
--

-- --------------------------------------------------------

--
-- Table structure for table `quote_items`
--

CREATE TABLE `quote_items` (
  `id` int(11) NOT NULL,
  `quote_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `start_date` varchar(30) DEFAULT NULL,
  `end_date` varchar(30) DEFAULT NULL,
  `notes` varchar(250) NOT NULL,
  `amount_charged_each` float(10,2) NOT NULL,
  `original_price_each` float(10,2) NOT NULL,
  `chargable_units` float(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

--
-- Dumping data for table `quote_items`
--

INSERT INTO `quote_items` (`id`, `quote_id`, `item_id`, `quantity`, `start_date`, `end_date`, `notes`, `amount_charged_each`, `original_price_each`, `chargable_units`) VALUES
(168, 64, 1, 1, '', '', '12121212', 10000.00, 0.00, 1.00),
(169, 64, 9, 1, '', '', '121212', 1250.00, 0.00, 1.00),
(170, 64, 7, 1, '', '', '212', 3000.00, 0.00, 1.00),
(171, 65, 1, 1, '', '', '', 10000.00, 0.00, 1.00),
(172, 65, 9, 100, '', '', 'notwe', 1250.00, 0.00, 1.00);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `quote_items`
--
ALTER TABLE `quote_items`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `quote_items`
--
ALTER TABLE `quote_items`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=173;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    -- phpMyAdmin SQL Dump
-- version 4.9.11
-- https://www.phpmyadmin.net/
--
-- Host: db5014142045.hosting-data.io
-- Generation Time: Sep 03, 2023 at 08:51 PM
-- Server version: 10.6.12-MariaDB-1:10.6.12+maria~deb11-log
-- PHP Version: 7.0.33-0+deb9u12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `dbs11785372`
--

-- --------------------------------------------------------

--
-- Table structure for table `quote`
--

CREATE TABLE `quote` (
  `id` int(11) NOT NULL,
  `client_id` int(11) NOT NULL,
  `total_value` float(10,2) NOT NULL,
  `date_created` timestamp NOT NULL DEFAULT current_timestamp(),
  `job_id` int(11) NOT NULL,
  `version` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `currency` varchar(3) NOT NULL,
  `kit_delivery` varchar(30) NOT NULL,
  `kit_return` varchar(30) NOT NULL,
  `quote_status_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

--
-- Dumping data for table `quote`
--

INSERT INTO `quote` (`id`, `client_id`, `total_value`, `date_created`, `job_id`, `version`, `name`, `currency`, `kit_delivery`, `kit_return`, `quote_status_id`) VALUES
(65, 15, 135000.00, '2023-09-03 20:17:52', 7, 5, 'first', '1', '2023-09-01', '2023-09-03', 1),
(71, 15, 135000.00, '2023-09-03 20:40:42', 7, 6, 'first', '1', '2023-09-01', '2023-09-03', 2);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `quote`
--
ALTER TABLE `quote`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `quote`
--
ALTER TABLE `quote`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=72;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

     

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.