Jump to content

Adamhumbug

Members
  • Posts

    581
  • Joined

  • Last visited

Everything posted by Adamhumbug

  1. on duplicate key seems to be better for what i am trying to do so will go with that.
  2. 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.
  3. 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?
  4. 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?
  5. I cant edit for some reason. The first code block should be $('#currency, #itemId, #startDate, #endDate, #quantity').change(updateFormNewQuoteItemChange)
  6. 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.
  7. The data that i pasted came from the inspector window as the response.
  8. console.log(resp[0]['CAD']) - gives me the data i was expecting console.log(resp.CAD) - gives me undefined
  9. 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?
  10. That could be a winner - let me have a play with that and i will report back
  11. 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.
  12. 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?
  13. 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."; } } }
  14. Hi All, I am creating a session with session_start() How do i also set the samesite atribute and set a time limit on how long the session is valid for. When a user visits any page on the site the time limit should be reset to max. After 15 mins of inactivity the session should end.
  15. Hi, Can this please be closed. I dont know what i have done but it is now working - i cannot really give any more info. Could an admin please close this as it is not a helpful post.
  16. 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.
  17. 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?
  18. -- 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 */;
  19. I have quotes and quotes have items. When i duplicate a quote i need to also duplicate the items. I have achieved this in one instance with the following. $sql2 = "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, (version+1), name, currency, kit_delivery, kit_return, 2 from quote where quote.id = :quoteId"; $stmt2 = $pdo ->prepare($sql2); $stmt2 -> execute([ ":quoteId" => $quoteId ]); $iid = $pdo -> lastInsertId(); This works but the version + 1 that i have in there is not going to work as i first need to check for the highest version that has been used. I may have gone down a rabbit hole here however: function createNewQuoteFromThisVersion($quoteId){ include 'includes/dbconn.php'; $sql ="UPDATE quote SET quote_status_id = 1"; $stmt = $pdo ->query($sql) -> execute(); $sql4 = "SELECT client_id, job_id from quote where id = :quoteId"; $stmt4 = $pdo -> prepare($sql4); $stmt4 -> execute([ ':quoteId' => $quoteId ]); if($row = $stmt4 -> fetch()){ $clientId = $row['client_id']; $jobId = $row['job_id']; } $sql5 = "SELECT max(version) as v from quote where client_id = :clientId and job_id = :jobId"; $stmt5 = $pdo -> prepare($sql5); $stmt5 -> execute([ ':clientId' => $clientId, ':jobId' => $jobId ]); if($row = $stmt5 -> fetch()){ $versionNumber = $row['v']; } $sql2 = "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, :version, name, currency, kit_delivery, kit_return, 2 from quote where quote.id = :quoteId"; $stmt2 = $pdo ->prepare($sql2); $stmt2 -> execute([ ":quoteId" => $quoteId, ':version' => $versionNumber ]); $iid = $pdo -> lastInsertId(); $sql3 = "INSERT INTO quote_items (quote_id, item_id, quantity, start_date, end_date, notes, amount_charged_each, original_price, chargable_units) SELECT :iid, item_id, quantity, start_date, end_date, notes, amount_charged_each, original_price, chargable_units from quote_items where quote_id = :quoteId"; $stmt3 = $pdo -> prepare($sql3); $stmt3 -> execute([ ':iid' => $iid, ':quoteId' => $quoteId ]); } This was my effort at doing what i needed to. The sql2 section is where i am going wrong i think. What is the correct way to select what is in the database but also amend the some of the data you are putting in. I can give data here if needed. Please forgive the naming convention here it will be tidied as i go.
  20. fixed with ".selectAllItemsBySection()." Thanks Barry for that pointer.
  21. <select type="text" class="form-control" id="itemId" name="itemId"> <option value="">Please Select...</option> <!--?= selectAllItemsBySection() ?--> </select>
×
×
  • 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.