Adamhumbug

OK, will give that a try. Is this how i should be structuring all of my queries to interact with the database? I have never used try / catch / throw

Quotes around VALUES 🤦‍♂️

Hi All, I have a very simple function: function createNewUser($pdo, $fname, $lname, $email, $password) { $sql = "SELECT email from user where email = :email"; $stmt = $pdo->prepare($sql); $stmt->execute([ ':email' => $email ]); $emailExists = $stmt->rowCount(); if ($emailExists > 0) { return "This email address is already in use"; } $sql2 = "INSERT INTO user (fname, lname, email, password) VALUES (':fname', ':lname', ':email', ':password')"; $hashedPwd = password_hash($password, PASSWORD_BCRYPT); $stmt = $pdo->prepare($sql2); $stmt->execute([ ':fname' => $fname, ':lname' => $lname, ':email' => $email, ':password' => $hashedPwd ]); return 'User Created'; } The first query is running fine, but the second is giving me Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number (on the SQL2 line) Can anyone shed any light on this?

HI All, I am using bootstrap and have been using its validation on some of my forms. Mainly for "required" fields. I have run into a bit of a tricky situation when trying to use custom validation with bootstraps validation tools. I have a user form where i collect name, email, password and a confirm password field. The validation works great for name and email out of the box but i have created custom functions for password validation. I have been adding and removing the is-valid class. What i see on the screen is what i want to see, however, when i click the submit button, the password fields validate to "all good" when my validation is suggesting that they fail. I got this from bootstraps documentation which works for the basic stuff // Example starter JavaScript for disabling form submissions if there are invalid fields (function() { 'use strict' // Fetch all the forms we want to apply custom Bootstrap validation styles to var forms = document.querySelectorAll('.needs-validation') // Loop over them and prevent submission Array.prototype.slice.call(forms) .forEach(function(form) { form.addEventListener('submit', function(event) { if (!form.checkValidity()) { event.preventDefault() event.stopPropagation() } form.classList.add('was-validated') }, false) }) })() I have a function like this that sets on of the fileds to fail validation if the passwords do not match $('#password2').keyup(function() { $p1 = $('#password1').val() $p2 = $('#password2').val() if ($p1 != $p2) { $('#password2').addClass("is-invalid") $('#password2').removeClass("is-valid") $('.passwordMatchCheck').text("Passwords do not match!") } else { $('#password2').removeClass("is-invalid") $('#password2').addClass("is-valid") } }) I am running into a situation where my validation and bootstraps are fighting and we end up having both valid and invalid messages. I am either looking for a way of omitting these fileds from bootstraps validation method or pointers on the "proper" way to do this. There is nothing i can find online that suggests how this would be done. I want to try and avoid having to write validation for all states on every form when i need something custom on one filed if can help it.

This is really handy, thanks for this.

HI All, I am starting to write a new project and i wanted some advice on error handling in functions. In my personal projects i have shamefully not really worried too much about error handling. I am wanting to progress how i do things so i am looking at how best to handle errors. I have written functions like this: function getUserList($pdo) { $sql = "SELECT fname from user"; $data = $pdo->query($sql)->fetch(); $out = ''; if(!$data){ return; } foreach ($data as $row) { $out .= $row['fname']; } return $out; } Basically starting the function, checking if there is data and if there is do something - i do tell the application what to display if there is no data rather than just return. If there was an error here for ehatever reason there is nothing in place at all to handle this. I have seen threads about try and catch but i have not used these before. In modern programming with PDO what would be the best "catch all" suggestion that i could be implementing?

Thanks all, i will for sure do some reading and be back if i need further help. Thanks again.

Hi All i have a function function showQuoteList() { include 'includes/dbconn.php'; $res = $pdo->query("WITH priceinfo as ( SELECT qi.quote_id, SUM(discounted_price * quantity * chargable_units) as price from quote_items qi group by quote_id ) SELECT client.company_name as companyName , job.name as jobName , quote.id as quoteId , quote.name as quoteName , version , currency , job.internal_ref , kit_delivery , kit_return , quote_status_id , total_value , quote_status.status , quote.date_created , price , client.id as clientId ,client.company_name as clientName from quote inner join client on quote.client_id = client.id inner join job on quote.job_id = job.id inner join quote_status on quote.quote_status_id = quote_status.id inner join priceinfo on quote.id = priceinfo.quote_id order by companyName, version desc "); $data = $res->fetchAll(PDO::FETCH_ASSOC); if (!$data) { $out = "<div class='alert alert-danger'>There are no quotes to show - create one by clicking <a href='clients'>here</a></div>"; } else { $results = []; $out = ''; foreach ($data as $u) { $results[$u['companyName']][$u['jobName']][$u['quoteId']] = array_slice($u, 2); } foreach ($results as $client => $jobs) { $out .= "<div class='card mb-4'>"; foreach ($jobs as $j => $items) { foreach ($items as $i) { $out .= "<div class='card-header'><a href='jobs?clientId=$i[clientId]&cN=$i[clientName]'><strong>$client</strong></a></div>"; } } .......... the issue that i have is that when i put the line; $out .= "<div class='card-header'><a href='jobs?clientId=$i[clientId]&cN=$i[clientName]'><strong>$client</strong></a></div>"; in its current location - it is displayed multiple times as there are multiple data sets in the $items and $i. When i move the line up inside the $jobs as $j loop, which is where it should be to display the correct information - i dont have access to the variables that i need from $i. how do i get access to the variables in $i keeping that line where it should be? If this is not enogh information please let me know and i will provide more context.

Thanks both thats really helpful. Would you suggest then, that when looking at making the API that i try and do this with OOP rather than POP. It seems far more popular to use OOP for this.

Thanks for that explanation - appreciated. My goal here is to build another site, that is seperate from my first one that can call an API in the first to get certain information. Eventually it will be a site that can be logged into and using the apis it will grab the information that it needs from the first site. I am sure that there are other ways of doing this but i wanted to use it as a way of learning apis as well as building something for a reason rather than it being hypothetical. As mentioned i have seen some tutorials that are OOP but i find them hard to follow or they are so specific to that project that i struggle to break out what i need. I am looking to start basic - grab all client names from client table - return json. Also i know that i will need it to be secure, so i want some sort of key exchange in there which i also have no idea about.

Hi All, I am looking to explore the world of API's and i would like to build one. I have had a look around online and most of the examples use classes and are OOP. I have never done OOP before and although i am sure it is something i should be looking into, i wondered if either anyone could point me in the right direction or suggest any good reading around this topic. I am starting by just trying to send a list of Clients in the response. Thanks in advance as always.

So this should only be happening becuase i have inspector open at the same time? I will do some testing around this. I have noted your other comments, which i will come back to.

This seems to do the trick - or is this a horrible way of doing it. if (window.history.replaceState) { window.history.replaceState(null, null, window.location.href); } window.location = window.location.href;

So in one of my function (in functions.php file) that is called with ajax from other page i should put header("Location: functions.php");

The URLs has got quite a lot of params in them. Would header("Refresh:0"); yield the same result? This is currently how i am doing everything: function applyDiscountToAllDiscountableQuoteItems($quoteId, $discountPercentage) { $remainingPercentage = 100 - $discountPercentage $.ajax({ type: 'post', data: { 'ajax': 'applyDiscountToAllDiscountableQuoteItems', 'quoteId': $quoteId, 'discountPercentage': $discountPercentage, 'remainingPercentage': $remainingPercentage }, success: function(resp) { location.reload(); } }) } function setNewItemPrice($newPrice, $itemId) { $.ajax({ type: 'post', data: { 'ajax': 'setNewItemPrice', 'quoteId': $quoteId, 'newPrice': $newPrice, 'itemId': $itemId }, success: function(resp) { // location.reload(); } }) }

My application requires the page to reload alot to trigger all of the functions that calculate price and i am now starting to see the following message alot. To display this page, Firefox Developer Edition must send information that will repeat any action (such as a search or order confirmation) that was performed earlier. I know that i can use AJAX to only reload sections that i have changed, but in effect i am changing so much of the page that i felt it more simple to reload the page. Is there a simple but 'professional' way that i can prevent this message and prevent things being added to the database more than once when a reload is required?

Ahh

The only thing i have changed is how the statement gets its parameter.

Weirdly i have another issue. This works: $sql = "SELECT qs.name as sectionName, i.name as itemName, i.id as itemId, i.GBP, i.USD, i.CAD, cb.charge_by, qs.display_order, COUNT(cp.item_id) > 0 as isConsumable from items i inner join quote_sections qs on i.section_id=qs.id inner join charge_by cb on i.charge_by_id = cb.id left join consumable_price cp ON i.id = cp.item_id group by i.id union SELECT qs.name as sectionName, ci.name as itemName, concat('CI', ci.id) as itemId, ci.price as GBP, ci.price as USD, ci.price as CAD, cb.charge_by, qs.display_order, 0 as isConsumable from custom_item ci inner join quote_sections qs on ci.section_id=qs.id inner join charge_by cb on ci.charge_by = cb.id where ci.job_id = 106 order by display_order, itemName"; $stmt = $pdo->query($sql)->fetchAll(PDO::FETCH_GROUP); and this does not $jobId = 106; $sql = "SELECT qs.name as sectionName, i.name as itemName, i.id as itemId, i.GBP, i.USD, i.CAD, cb.charge_by, qs.display_order, COUNT(cp.item_id) > 0 as isConsumable from items i inner join quote_sections qs on i.section_id=qs.id inner join charge_by cb on i.charge_by_id = cb.id left join consumable_price cp ON i.id = cp.item_id group by i.id union SELECT qs.name as sectionName, ci.name as itemName, concat('CI', ci.id) as itemId, ci.price as GBP, ci.price as USD, ci.price as CAD, cb.charge_by, qs.display_order, 0 as isConsumable from custom_item ci inner join quote_sections qs on ci.section_id=qs.id inner join charge_by cb on ci.charge_by = cb.id where ci.job_id = :jobId order by display_order, itemName"; $stmt = $pdo->prepare($sql); $stmt -> execute([":jobId"=> $jobId]); $stmt ->fetchAll(PDO::FETCH_GROUP); is there something simple i am doing wrong here. Neither has errors but the second does not populate the select box and the first does.

They are not! And in answer to your questions, the first one. All items with an indication of whether they are consumable.

I think this has done what i need select i.name as itemName, qs.name as sectionName, i.id as itemId, i.GBP, i.USD, i.CAD, cb.charge_by, if(EXISTS(Select item_id from consumable_price where item_id = i.id),1,0) as icConsumable from items i inner join quote_sections qs on i.section_id=qs.id inner join charge_by cb on i.charge_by_id = cb.id union select ci.name as itemName, qs.name as sectionName, concat("CI", ci.id) as itemId, ci.price as GBP, ci.price as USD, ci.price as CAD, cb.charge_by, 0 as isConsumable from custom_item ci inner join quote_sections qs on ci.section_id=qs.id inner join charge_by cb on ci.charge_by = cb.id

I have rewritten like this select i.name as itemName, qs.name as sectionName, i.id as itemId, i.GBP, i.USD, i.CAD, cb.charge_by from items i inner join quote_sections qs on i.section_id=qs.id inner join charge_by cb on i.charge_by_id = cb.id union select ci.name as itemName, qs.name as sectionName, concat("CI", ci.id) as itemId, ci.price as GBP, ci.price as USD, ci.price as CAD, cb.charge_by from custom_item ci inner join quote_sections qs on ci.section_id=qs.id inner join charge_by cb on ci.charge_by = cb.id but how do i implement your count check into this?

Hi Kicken, I have tied myself up here. The reason for the consumable table here is to set the isConsumable flag and that is the only reason, so i could look to do your greater than 0 method. The method i used was a hope to just get one of each item id from the consumable table. Sql is not my strong suit so this may take some time for me to figure out.

i do care about what is in the consumable table, it has items that should be in the list - it just doesnt have the same format as the normal items table. The consumables table has several rows per item as it has price breaks. Sorry if that wasnt too clear.

So all i am trying to do here is populate a dropdown with all of the items that appear in the items table. I also include items that are in the consumable_price table but as there are several rows per item, i have used the WITH. I am now letting the user add custom items, they are stored in a different table as they have different properties such as only being available in that "job". The code that i posted above shows how i select them in the same layout as the initial select that works. So the layout of both of the queries is the same and they return the same columns. CREATE TABLE `custom_item` ( `id` int(11) NOT NULL, `job_id` int(11) NOT NULL, `name` varchar(200) NOT NULL, `section_id` int(11) NOT NULL, `price` float NOT NULL, `charge_by` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; INSERT INTO `custom_item` (`id`, `job_id`, `name`, `section_id`, `price`, `charge_by`) VALUES (1, 106, 'name', 3, 100, 0), (2, 22, 'NAME', 2, 9999, 2); ALTER TABLE `custom_item` ADD PRIMARY KEY (`id`); ALTER TABLE `custom_item` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; COMMIT; In a nut shell i just need to also show the items from the custom_items table in the select box that i populate with the first piece of code that has the WITH. This select doesnt care about the different price breaks, it just wants to grab the items and group them by the display_order. The select looks like this currently.