Adamhumbug

Hi All, I have an insert query where sometimes all of the fields will have values to insert and sometimes they will not. I have allowed null on those fields in the database but i still get a warning (i have them turned on but ideally i would like to remedy the warning rather than turn it off). $stmt->execute([ ':quote_id' => $_GET['quoteid'], ':item_id' => $_POST['itemId'], ':quantity' => $_POST['quantity'], ':start_date' => $_POST['startDate'], ':end_date' => $_POST['endDate'] ]); Start and End date may not have values so i would like to pass in something intentional like 0 or NULL but im not sure how to do that or what doing so is called.

Hi All, I have a rental system - if you rent for 3 or more days over a week you pay for 2 weeks, 3 or more days over 2 weeks and you pay for 3 and so on. As i need to be able to see how many weeks someone is renting equipment for i have built a js function but wondered if there was a more effience way of getting the weeks value than the following. if ($days == 0){ $weeks = 0 }else if ($days < 10) { $weeks = 1 } else if ($days >= 10 && $days < 16) { $weeks = 2 } else if ($days >= 17 && $days < 23) { $weeks = 3 } else if ($days >= 24 && $days < 30) { $weeks = 4 } Thanks as always

Thank you all - issue resolved.

Thats my issue right there - i was relying on the primary key (id) - i didnt realise that it would automatically increment when using this method.

The value in question that is not working correctly is the small grey value - the others are set using queries from other functions called at the top of this one. I take your point on the prepared not being required and in terms of the bind column, i am brand new to PDO - actually made the switch due to @Barand suggestions over the years. I am still finding my feet.

I will take your word for this as i am here for help - however, when i have logged out what this sql produces, it seems that i am getting the data that i expect and only this. When i change the data in the database and re run the query i am stil getting the data that i expect. if it helps here is the complete function. function showClientList(){ include 'includes/dbconn.php'; $grandTotal = sumAllQuoteValues(); $sumOpenAndAcceptedValueByClient = sumOpenAndAcceptedValueByClient(); echo "<pre>"; var_dump($sumOpenAndAcceptedValueByClient); echo "</pre>"; $sql = " SELECT client.id as clientid, company_name, country, DATE_FORMAT(client.date_created, '%d %M %Y') as date_created, sum(total_value) as tval from client left join quote on client.id = quote.client_id and 1 = quote.open group by client.id "; $stmt = $pdo -> prepare($sql); $stmt -> execute(); $stmt -> bindColumn('company_name', $coName); $stmt -> bindColumn('country', $country); $stmt -> bindColumn('date_created', $dCreated); $stmt -> bindColumn('tval', $tval); $stmt -> bindColumn('clientid', $cId); $out = ""; while ($stmt -> fetch()){ foreach ($sumOpenAndAcceptedValueByClient as $row){ if($row['client_id'] == $cId){ $valByClient = $row['sum(total_value)']; } } if($cId != null){ $created = new DateTime($dCreated); $now = new DateTime(date("Y-m-d H:i:s")); if($created->modify('+3 year') < $now){ $age = "Long Standing Client"; }else if ($created->modify('+2 year') < $now) { $age = "Established Client"; }else if ($created->modify('+1 year') < $now) { $age = "Second Year"; }else{ $age = "New Client"; } $percentageOfGrandTotal = ($valByClient/$grandTotal)*100; $roundedPercentage = round($percentageOfGrandTotal,2); $out .=<<<EOD <tr class='align-middle'> <td> <div>$coName</div> <div class='small text-medium-emphasis'><span>$age</span> | Created: $dCreated</div> </td> <td class='text-center'> <svg class='icon icon-xl'> <use xlink:href='vendors/@coreui/icons/svg/flag.svg#cif-$country'></use> </svg> </td> <td> <div class='clearfix'> <div class='float-start'> <div type="button" tabindex="0" data-coreui-toggle="popover" data-coreui-placement="top" data-coreui-custom-class="custom-popover" data-coreui-title="Potential New Revenue Value Of Client" data-coreui-trigger="focus" data-coreui-content="Total open quotes come to £$tval." class='fw-semibold'>£$tval</div> </div> <div type="button" tabindex="0" data-coreui-toggle="popover" data-coreui-placement="top" data-coreui-custom-class="custom-popover" data-coreui-title="Total Value Of Client" data-coreui-trigger="focus" data-coreui-content="Total open and accepted quotes come to £$valByClient." class='float-end'><small class='text-medium-emphasis'>£$valByClient</small></div> </div> <div class='progress progress-thin'> <div type="button" tabindex="0" data-coreui-toggle="popover" data-coreui-placement="top" data-coreui-custom-class="custom-popover" data-coreui-title="Total Value Of Client" data-coreui-trigger="focus" data-coreui-content="This client makes up $roundedPercentage% of total revenue." class='progress-bar bg-success' role='progressbar' style='width: $percentageOfGrandTotal%'></div> </div> </td> <td class='text-center'> <svg class='icon icon-xl'> <use xlink:href='vendors/@coreui/icons/svg/brand.svg#cib-cc-mastercard'></use> </svg> </td> <td> <div class='small text-medium-emphasis'>Active/Completed</div> <div class='fw-semibold'>2/8</div> </td> <td> <div class='dropdown'> <button class='btn btn-transparent p-0' type='button' data-coreui-toggle='dropdown' aria-haspopup='true' aria-expanded='false'> <svg class='icon'> <use xlink:href='vendors/@coreui/icons/svg/free.svg#cil-options'></use> </svg> </button> <div class='dropdown-menu dropdown-menu-end' style=''><a class='dropdown-item' href='#'>Info</a><a class='dropdown-item' href='#'>Edit</a><a class='dropdown-item text-danger' href='#'>Delete</a></div> </div> </td> </tr> EOD; } } return $out; }

Hi All, I have a php function to insert data into my database which works fine but i want to update the record using the same button and have thus used on duplicate key update. if ($_SERVER['REQUEST_METHOD'] == 'POST') { if (isset($_POST['createNewQuote'])) { $sql = "INSERT into quote (name, internal_ref, currency, kit_delivery, kit_return, job_id) values (:name, :internal_ref, :currency, :kit_delivery, :kit_return, :jobId) ON DUPLICATE KEY UPDATE name=:name "; $stmt = $pdo->prepare($sql); $stmt->execute([ ':name' => $_POST['quoteName'], ':internal_ref' => $_POST['internalReference'], ':currency' => $_POST['currency'], ':kit_delivery' => $_POST['kitDeliveryDate'], ':kit_return' => $_POST['kitCollectionDate'], ':jobId' => $_POST['jobId'] ]); } } The issue that i have is that everytime i click the save button that triggers this function, i am getting a new row in the database created - is there something obvious i am doing wrong? I have attached the structure just in case that helps

i have done that, i am now seeing: array(2) { [0]=> array(2) { ["client_id"]=> int(1) ["sum(total_value)"]=> string(4) "1000" } [1]=> array(2) { ["client_id"]=> int(2) ["sum(total_value)"]=> string(3) "211" } } But still have the extra value.

Which outputs this array(2) { [0]=> array(4) { ["client_id"]=> int(1) [0]=> int(1) ["sum(total_value)"]=> string(4) "1000" [1]=> string(4) "1000" } [1]=> array(4) { ["client_id"]=> int(2) [0]=> int(2) ["sum(total_value)"]=> string(3) "211" [1]=> string(3) "211" } }

it comes from this function function sumOpenAndAcceptedValueByClient(){ include 'includes/dbconn.php'; $sql = "SELECT client_id, sum(total_value) from quote where accepted = 1 or open = 1 group by client_id"; $stmt = $pdo-> prepare($sql); $stmt -> execute(); $out = $stmt -> fetchAll(); return $out; }

Hi All, Sorry for the rubbish title, wasnt sure how else to word it. I have a select statement grabbing some info from my DB. $sql = " SELECT client.id as clientid, company_name, country, DATE_FORMAT(client.date_created, '%d %M %Y') as date_created, sum(total_value) as tval from client left join quote on client.id = quote.client_id and 1 = quote.open group by client.id "; $stmt = $pdo -> prepare($sql); $stmt -> execute(); $stmt -> bindColumn('company_name', $coName); $stmt -> bindColumn('country', $country); $stmt -> bindColumn('date_created', $dCreated); $stmt -> bindColumn('tval', $tval); $stmt -> bindColumn('clientid', $cId); $out = ""; i then have a while loop that is comparing the output from another function to match IDs and give a value to the output if they match. while ($stmt -> fetch()){ foreach ($sumOpenAndAcceptedValueByClient as $row){ if($row['client_id'] == $cId){ $valByClient = $row['sum(total_value)']; echo $cId; echo " ".$row['client_id']; echo " ".$row['sum(total_value)']; echo "<br/><br/>"; } } It seems to be working, however i have a client in the list that has no value so its sum(total_value) should be blank, actually it wont even show in the array i am foreaching. The issue is that in the table it is producing, it is giving me the same value of whatever comes before it - you see the £211 in the table image below. I have tried debugging this by echoing the values that are getting passed around which gives me the following output. 1 1 1000 2 2 211 I am therefore not sure why i am getting a value put into the last row. I have tried adding and else to the for each making it while ($stmt -> fetch()){ foreach ($sumOpenAndAcceptedValueByClient as $row){ if($row['client_id'] == $cId){ $valByClient = $row['sum(total_value)']; echo $cId; echo " ".$row['client_id']; echo " ".$row['sum(total_value)']; echo "<br/><br/>"; }else{ $valByClient = 0; } } that gives me the same echo values but now the first row =0 as well as the last. I am a bit stumped on this and would appreciate your help. If you need more info or for me to show more code, i will be happy to do so. As always, thanks in advance.

Amazing, that is absolutely what i was looking for but does lead me on to another question. As i am wanting to show on each row what percentage of the overall total each client makes up i would need to do something different with the rollup as currently it adds another row to the table. Any suggestions on how to deal with that?

Actually my question is the other way around, i have the total amount just not the amount per client - that might help with my googling.

Hi All, I fear this may be very simple but i have been struggling a little with it - brain fog. I am running the following query: SELECT client.id, company_name, country, DATE_FORMAT(date_created, '%d %M %Y') as date_created, sum(total_value) as tval from client inner join quote on client.id = quote.client_id but i also want to do a count of everything that is in the total_value column so i end up with a number per client and a total of everything. Any pointers appreciated.

Sorry to be more clear about what i mean - the where_terms i dont really want to have to write out explicitly if there is a way around it. I dont know if there is a good way of doing this, especially as they include params that have been passed in from the front end. Can you think of another way or do you think i am beat?

This has worked for one of my queries but actually for the one in the example it has not. Your example would require that i re-write all of the output of all of the queries that come back from the other functions that are run. Is there a way for me to do this without having to manually evaluate the functions and then hard code what they produce for the prepared statements?

So the SQL would end up looking something like? "select op_id, ".$arr[0].", op_role from prs_op where ".$arr[1] $arr[2] $arr[3]." limit 40";

My issue with this is that i will have to have several arrays as i am putting the code in several places in the query which i struggled to work out when referencing the stack overflow post i quoted.

I believe that it is php 7.3.

$opsrch['srch'] contains "(CONCAT(TRIM(op_firstname),' ',TRIM(op_middlenames),' ',TRIM(op_lastname)) like '".apos($sterm)."%' or CONCAT(TRIM(op_firstname),' ',TRIM(op_lastname)) like '".apos($sterm)."%' or CONCAT(TRIM(op_middlenames),' ',TRIM(op_lastname)) like '".apos($sterm)."%' or op_lastname like '".apos($sterm)."%')";

If it is numeric i get this. select op_id, CONCAT(TRIM(op_firstname),' ',TRIM(op_lastname)) as op_fullname, op_role from prs_op where (CONCAT(TRIM(op_firstname),' ',TRIM(op_middlenames),' ',TRIM(op_lastname)) like '9999%' or CONCAT(TRIM(op_firstname),' ',TRIM(op_lastname)) like '9999%' or CONCAT(TRIM(op_middlenames),' ',TRIM(op_lastname)) like '9999%' or op_lastname like '9999%') or op_id='9999' or op_id like '9999%' order by op_firstname, op_lastname limit 40

HI All, I am going through some old code and trying to sanitise sql injection problems and need some help on the best method here. The project does come with some caveats, which i cant change. I have to use Mysqli and cannot use PDO. Because the project is so large, i am going to need to deal with all code changes inside the function and not changing or removing other functions. I always appreciate the help on my personal stuff that i am doing and you guys are amazing but please on this one can we move past the fact that the code is rubbish and old and full of problems. I have a sql query that looks like this - as you can see there are variables in here as well as functions "select op_id, ".$opsrch['op_fullname'].", op_role from prs_op where ".$opsrch['srch'].(is_numeric($srch) ? " or op_id='".$srch."' or op_id like '".$srch."%'" : "")." order by ".$opsrch['sort']." limit 40"; I know how to write a prepared statement but i cannot do this simply as some of the variables contain more sql rather than just a simple variable. Once the sql and all of the variables and functions have been evaluated, this is what i get select op_id, CONCAT(TRIM(op_firstname),' ',TRIM(op_lastname)) as op_fullname, op_role from prs_op where (CONCAT(TRIM(op_firstname),' ',TRIM(op_middlenames),' ',TRIM(op_lastname)) like 'adam h%' or CONCAT(TRIM(op_firstname),' ',TRIM(op_lastname)) like 'adam h%' or CONCAT(TRIM(op_middlenames),' ',TRIM(op_lastname)) like 'adam h%' or op_lastname like 'adam h%') order by op_firstname, op_lastname limit 40 All references to adam have been passed in from the front end. I have been looking at this -https://stackoverflow.com/questions/56135017/building-a-dynamic-php-prepared-statement-from-user-entry but that is putting everything in one place and im not sure if it can work here. I would really appreciate any help with info to tidy this mess - thanks in advance for helping me with crappy code.

Thanks both, appreciate the point in the right direction.

Hi All, I have a very simple piece of php that is set when a user logs on - it sets a sessin var. At the top of the page i have the following: if (session_status() == PHP_SESSION_NONE) { session_start(); } if(empty($_SESSION['logged-in-user'])){ header("Location: index.php"); exit; } If i comment out the header line the page loads fine, if i dont comment it i get the error message that the page is not redirecting properly. This seems too simple to be wrong but apparently it is - can anyone offer any light on this. For clarity the page that i am redirecting to is the page that this code is on, i am redirecting to the same page (the log on page) Thanks as always.

Hi all, I hope this is the right place to post this. I am trying to learn node.js and i am really struggling to get going. I have a mysql database hosted with ionos and i am trying to just start with a connection. I have the following which is the connection file and i am running it from the command line. var mysql = require('mysql'); var con = mysql.createConnection({ host: "xxxxxxx.hosting-data.io", user: "xxxxx", password: "xxxxx" }); con.connect(function(err) { if (err) throw err; console.log("Connected!"); }); I am getting the error: ❯ node db_conn.js /Users/xxxxxxx/Documents/xxxx/Sites/Node/first/db_conn.js:11 if (err) throw err; ^ Error: getaddrinfo ENOTFOUND xxxxxx.hosting-data.io at GetAddrInfoReqWrap.onlookupall [as oncomplete] (node:dns:118:26) -------------------- at Protocol._enqueue (/Users/xxxx/Documents/xxxx/Sites/Node/first/node_modules/mysql/lib/protocol/Protocol.js:144:48) at Protocol.handshake (/Users/xxxx/Documents/xxxx/Sites/Node/first/node_modules/mysql/lib/protocol/Protocol.js:51:23) at Connection.connect (/Users/xxxx/Documents/xxxx/Sites/Node/first/node_modules/mysql/lib/Connection.js:116:18) at Object.<anonymous> (/Users/xxxx/Documents/xxxx/Sites/Node/first/db_conn.js:10:5) at Module._compile (node:internal/modules/cjs/loader:1267:14) at Module._extensions..js (node:internal/modules/cjs/loader:1321:10) at Module.load (node:internal/modules/cjs/loader:1125:32) at Module._load (node:internal/modules/cjs/loader:965:12) at Function.executeUserEntryPoint [as runMain] (node:internal/modules/run_main:83:12) at node:internal/main/run_main_module:23:47 { errno: -3008, code: 'ENOTFOUND', syscall: 'getaddrinfo', hostname: 'xxxxx.hosting-data.io', fatal: true } Node.js v20.0.0 I appreciate this may be difficult to work on and i am sure there is info needed that i have not supplied but i am really struggling to get set up to work. If anyone can help or can suggest resources to help me that would be very much apprecitated. Thanks, As always.