Adamhumbug

If more information is needed for this i will be happy to supply it, i didnt want to overload with too much info in one place.

Hi All, I think this may be a bit of a slog to get to the answer here as there are a fair few function calls and quite long functions. I have a function that is called by ajax to create some form items. This works fine. The form can be filled out and all of the data goes to the DB. Now, when wanting to recreate the form, filled with the data, so that users can edit i have part of the function that should be setting the selected item in an option list that is outputting the value where no match has been found rather than the value when a match has been found. here is the big function function getItemsForQuote($itemType, $currency, $qty = null, $startDate = null, $endDate = null, $itemId = null, $notes = null) { require 'includes/dbconn.php'; $sql = "SELECT id, name, price_uk, price_us, price_ca, price_au, price_eu, billing_freq from item where type = :item_type"; $stmt = $pdo->prepare($sql); $stmt->bindParam(':item_type', $itemType); $stmt->execute(); $items = $stmt->fetchAll(PDO::FETCH_ASSOC); if ($items) { $qtyArray = array( 1 => '1', 2 => '2', 3 => '3', 4 => '4', 5 => '5', 6 => '6', 7 => '7', 8 => '8', 9 => '9', 10 => '10', 11 => '11', 12 => '12', 13 => '13', 14 => '14', 15 => '15', 16 => '16', 17 => '17', 18 => '18', 19 => '19', 20 => '20' ); $out = "<div class='row mb-3 completeLine'><div class='col-1'> <select name='itemQty[]' class='itemQty form-select'>"; foreach ($qtyArray as $key => $value) { if ($qty == $key) { $selected = 'selected'; $out .= "<option selected='{$selected}' value='{$key}'>{$value}</option>"; } $out .= "<option value='{$key}'>{$value}</option>"; } $out .= "</select> </div> <div class='col-4'> <select name='itemSelected[]' class='itemSelected form-select'><option selected disabled>Please Select</option>"; foreach ($items as $item) { if ($itemId == $item['id']) { $selected = 'selected'; $out .= "<option selected='{$selected}' value='{$item['id']}'>{$item['name']}</option>"; } $out .= "<option data-billingfreq='{$item['billing_freq']}' data-priceuk='{$item['price_uk']}' data-priceus='{$item['price_us']}' data-priceca='{$item['price_ca']}' data-priceau='{$item['price_au']}' data-priceeu='{$item['price_eu']}' value='{$item['id']}'>{$item['name']}</option>"; } $out .= "</select> </div> <div class='col-2'> <input name='fromDate[]' type='date' class='form-control from-date'> </div> <div class='col-2'> <input name='toDate[]' type='date' class='form-control to-date'> </div> <div class='col-2'> <input name='lineTotal[]' type='text' disbaled placeholder='Total' class='line-total form-control'> </div> <div class='col-1'> <div class='btn btn-primary w-100'>Opt</div> </div> <div class='col-7 mt-3'> <div class='input-group mb-3'> <span class='input-group-text'>Notes</span> <input name='lineNotes[]' type='text' class='line-notes form-control' placeholder='Add notes for line item'> </div> </div> <div class='col-3 mt-3'> <div class='input-group mb-3'> <span class='input-group-text pricePerWeek'>PPW</span> <input type='text' class='pricePerWeek form-control' placeholder='PPW'> </div> </div> <div class='col-2 mt-3'> <div class='input-group mb-3'> <span class='input-group-text noOfWeeks'>Weeks</span> <input type='text' class='noOfWeeks form-control' placeholder='Weeks'> </div> </div> </div> </div> "; } return $out; } The section that is not working correctly is foreach ($items as $item) { if ($itemId == $item['id']) { $selected = 'selected'; $out .= "<option selected='{$selected}' value='{$item['id']}'>{$item['name']}</option>"; } $out .= "<option data-billingfreq='{$item['billing_freq']}' data-priceuk='{$item['price_uk']}' data-priceus='{$item['price_us']}' data-priceca='{$item['price_ca']}' data-priceau='{$item['price_au']}' data-priceeu='{$item['price_eu']}' value='{$item['id']}'>{$item['name']}</option>"; } itemId is being passed into this function correctly i believe which has been checked with the function that calls this one below. The $itemId which is passed in = 2 and the $item['id'] that is being pulled in from the database in this query should match. The above function is called from the following function function rebuildQuote($quoteId, $version) { include 'includes/dbconn.php'; $sql = "SELECT item_id as itemId,quote.currency as quoteCurrency, item.type as itemType, quantity, from_date, to_date, price_quoted from quote_items inner join item on item.id = quote_items.item_id inner join quote on quote.id = quote_items.quote_id where quote_id = :quoteId and quote_items.version = :version"; $stmt = $pdo->prepare($sql); $bindData = [ 'quoteId' => $quoteId, 'version' => $version ]; $stmt->execute($bindData); $items = $stmt->fetchAll(PDO::FETCH_ASSOC); $out = ""; if ($items) { foreach ($items as $item) { $out .= getItemsForQuote($item['itemType'], $item['quoteCurrency'], $item['quantity'], $item['itemId']); // $out .= $item['itemId']."and"; } } else { $out .= "no items"; } return $out; } You will see that i have been changing the $out at the end to check what variables are being sent into the function call. I have been going around the houses on this but any pointers in the right direction would be appreicted. I am hoping that it is a "cant see the woods for the trees" issue.

This was a mistake that was corrected on my last posting. I origionally did it to check that the issue was not with the params that were passed into the function.

Actually, that did seem to do the trick - i had been playing with the version definition in the $bindData - when i set that back to $version it worked. Thanks for that, i had a feeling it would be something small. Do you know why that has been causing the issue, i have tons of other queries with quotes around them that seem to work ok?

i updated to this with no change function rebuildQuote($quoteId, $version){ include 'includes/dbconn.php'; $sql = "SELECT item_id as itemid, quantity, from_date, to_date, price_quoted from quote_items where quote_id = :quoteId and version = :version"; $stmt = $pdo->prepare($sql); $bindData = [ 'quoteId' => $quoteId, 'version' => 1 ]; $stmt->execute($bindData); $items = $stmt->fetchAll(PDO::FETCH_ASSOC); $out = ""; if($items){ foreach($items as $item){ $out .= "this"; } }else{ $out .= "no items"; } return $out.$version; }

i have the following function function rebuildQuote($quoteId, $version){ include 'includes/dbconn.php'; $sql = "SELECT item_id as itemid, quantity, from_date, to_date, price_quoted from quote_items where quote_id = ':quoteId' and version = ':version'"; $stmt = $pdo->prepare($sql); $bindData = [ 'quoteId' => $quoteId, 'version' => 1 ]; $stmt->execute($bindData); $items = $stmt->fetchAll(PDO::FETCH_ASSOC); $out = ""; if($items){ foreach($items as $item){ $out .= "this"; } }else{ $out .= "no items"; } return $out; } when i return $quoteId and $version i can see 19,4 so i know that vars are coming into this function. When i run the SQL manually in the db i get 2 rows of data. When i run the function as is i get "no items" I cant see what i am doing wrong here.

I have the below query: SELECT max(quote.id) as quoteId, job_id as jobId, job.name as jobName, job.client_id as clientId, client.name as clientName, currency, version from quote inner join job on job.id = quote.job_id inner join client on client.id = job.client_id where quote.closed != '1' group by job_id i have attached an image of the data structure when i run the qry i am getting quoteId jobId jobName clientId clientName currency version 13 21 Test Job 12 Test Client GBP 1 14 22 JOB JOB 3 Testing LTD USD 1 19 24 Adams Job 13 Adams Co GBP 1 but i would be expecting the version numbers of the quote ids selected to be 8, 1 and 4. Could anyone point me at what i am doing wrong with my sql?

Hi all, I have a function that is returning data from a select statement. I am wanting to change the fields that are returned dynamically based on a variable that is passed into the function. I thought i would be able to do this with a switch and a variable name in the select statement. I have done some reading and it seems that this is not a thing that can be done. Would the best option be to get all fields that i might want to decide which to use after the data has been returned?

Yes that helps very much thank you.

I have numbers stored in a database in pence (or cents). For example i have an item that costs 100 pounds stored as 10000 as this is the number of pence in 100 pounds. When i am working with Jquery, how would i show the value as 100 pounds. I have chosen this route based on some research with maths issues of storing it in pounds should their be decimals to calculate. I was concidering a division by 100 but i am not sure what the recommended method for working with currency like this is for display only purposes.

Thanks all for your feedback. @mac_gyver and @Barand - both of your solutions worked perfectly out of the box so that is very much appreciated.

I am actually getting undefined variable on both qty and item. The console logs indicate that they are set and they are all set in the same function so i am a bit lost. I am not sure that what i am doing is the best way of going about this...

I have got to this stage but i am getting qty is not defined when i try and set the linetotal box function calcLineTotal(){ $('.completeLine').each(function(){ $(this).find($('.itemQty')).each(function(){ var qty = $(this).val(); console.log(qty) }) $(this).find($('.itemSelected option:selected')).each(function(){ var item = $(this).data('priceuk') console.log(item) }) var linetotal = qty * item; $(this).find($('.lineTotal')).val(linetotal); }) }

I think i have made a pretty solid start function calcLineTotal(){ $('.completeLine').each(function(){ $(this).find($('.itemQty')).each(function(){ var qty = $(this).val(); console.log(qty) }) }) }

I have a form that creates new rows on a button click - i am wanting to do maths on these rows. The form when created has the following html and new rows create mostly the same thing (just the data in the drop down differs). <div class="row mb-3 completeLine"> <div class="col-1"> <select name="itemQty[]" class="itemQty form-select"> <option value="1">1</option> <option value="2">2</option> <option value="3">3</option> <option value="4">4</option> <option value="5">5</option> </select> </div> <div class="col-4"> <select name="itemSelected[]" class="itemSelected form-select"> <option data-priceuk="1000000" value="1">Annual AllowMe Licence</option> <option data-priceuk="300000" value="2">Single Event Licence</option></select> </div> <div class="col-2"> <input name="fromDate" type="date" class="form-control from-date"> </div> <div class="col-2"> <input name="toDate" type="date" class="form-control to-date"> </div> <div class="col-2"> <input name="lineTotal" type="text" disbaled="" placeholder="Total" class="line-total form-control"> </div> <div class="col-1"> <div class="btn btn-primary w-100">Opt</div> </div> <div class="col-12 mt-3"> <div class="input-group mb-3"> <span class="input-group-text">Notes</span> <input name="lineNotes" type="text" class="line-notes form-control" placeholder="Add notes for line item"> </div> </div> </div> I am wanting to multiply the quanity on each line by the value of the item. The bit i am struggling to remember is how to write the JQ to select the items from each line to multiply, i will be wanting to add on change events to these also. Each line gets a completeLine class I am thinking it will be a for each with a find but i cant string it together.

Thanks again - all up and running. I am sure i will be back.

I think that was the issue here, i removed the bit you suggested and all seems to be working - looks like i was breaking the variable there. Thanks very much!

So i am calling the function like this: <?=getClientName($pdo, 'select');?> and needed to ammend the function name like this:? function getClientName($pdo, $display) That gives me: Fatal error: Uncaught Error: Call to a member function query() on int in /homepages/20/d832698785/htdocs/platforms/quote-master/includes/functions.php:9 Stack trace: #0 /homepages/20/d832698785/htdocs/platforms/quote-master/new-quote.php(12): getClientName(1, 'select') #1 {main} thrown in /homepages/20/d832698785/htdocs/platforms/quote-master/includes/functions.php on line 9

I am brand new to PDO and i am very sure this is a simple issue but i am struggling to work it out. My connection file: <?php $host_name = '*******.hosting-data.io'; $database = '******'; $user_name = '******'; $password = '******!'; try { $pdo = new PDO("mysql:host={$host_name}; dbname={$database}", $user_name, $password); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); echo "Connected successfully"; } catch(PDOException $e) { echo "Connection failed: " . $e->getMessage(); } ?> and the function that i am trying to run: function getClientName($display) { $pdo = require 'includes/dbconn.php'; $sql = "SELECT id, name from clients"; $stmt = $pdo->query($sql); $clients = $stmt->fetchAll(PDO::FETCH_ASSOC); if ($display == 'select') { if ($clients) { $out = "<div class='input-group mb-3'> <label class='input-group-text' for='inputGroupSelect01'>Client Name</label> <select class='form-select' id='inputGroupSelect01'> <option selected disabled>Please Select</option> <option value='0'>-- New Client --</option>"; foreach ($clients as $client) { $out .= "<option value='{$client['id']}'>{$client['name']}</option>"; } $out .= "</select> </div>"; } } return $out; } I am getting an error on line 9 of the function which is: $stmt = $pdo->query($sql); I am trying to figure out what is causing this error and if my PDO is not correct. I am getting the connection successful message.

Thanks ill take a look

very useful info - thanks for that - i could well be back here for help but ill have a stab.

I am wanting to create a pdf document. Basically a branded document that pulls data from the database and builds a quote that would be sent to a client.

Hi all, I wasnt sure of the best place to put this thread so would appreciate the admins advice on that. I have a web app and i am wanting it to be able to create a downloadable pdf. I am early in the process but i am assuming that i create the view that i want to pdf in html/css. Can anyone suggest the best way for me to go about creating this PDF or any third party add ons that i could be using. @Barand will be happy to know this will be a PDO project, although a simple one to start.

Yes, this seems to be the issue - i was only using 2 of the values - rookie. I need to look more into the error reporting for sure - thanks all for your input as always. PDO will be something that i will look into but wanted to start with it on a new project - it will come.

I have the following function function getBallInOver($gameId){ include 'includes/dbconn.php'; $sql = "SELECT MAX(id), over_number, ball_in_over from deliveries where game_id = ?"; $stmt = $conn->prepare($sql); $stmt->bind_param('i', $gameId); $stmt->execute(); $stmt->bind_result($overno, $ballinover); if($stmt->fetch()){ $out = $overno; } $stmt -> close(); return $out; } which is called from this function function newDelivery($gameId, $runs, $fairDelivery){ include 'includes/dbconn.php'; $batterId = getOnStrikeBatter($gameId); $ballInformation = getBallInOver($gameId); $one = 1; $sql = "INSERT into deliveries (game_id, on_strike_batter_id, runs_scored, fair_delivery, over_number) values (?,?,?,?,?)"; $stmt = $conn->prepare($sql); $stmt -> bind_param('iiiii', $gameId, $batterId, $runs, $fairDelivery, $ballInformation); $stmt -> execute(); if($runs % 2 !== 0){ include 'includes/dbconn.php'; $zero = 0; $one = 1; $sql = "UPDATE batter_in set on_strike=(case when on_strike = ? then ? else ? end)"; $stmt = $conn->prepare($sql); $stmt -> bind_param('iii', $zero, $one, $zero); $stmt -> execute(); } return; } I am getting an error that the over_number cannot be null. I dont understand why the value is null - when i run the sql directly on the server i get the following: MAX(id) = 42, over_number = 1, ball_in_over = 1 Could someone point me in the right direction. For clarity, the function above its call takes the same peramaters and that works fine so i know the function is getting the data it needs.