michelle1404

Yes, This is the query SELECT eemp_id, AVG(DATEDIFF( IF(leaving_date is null or leaving_date = '' or leaving_date='0000-00-00', CURRENT_DATE(), leaving_date), joining_date )) AS Days FROM employee_deatils group by eemp_id

I am trying to select all the employees who have Minimum average years of experience of 3 years. If an employee worked for 24 years in 3 companies the average years of experience is 8 years. For this i got result by doing like this SELECT AVG(DATEDIFF( IF(leaving_date is null or leaving_date = '' or leaving_date='0000-00-00', CURRENT_DATE(), leaving_date), joining_date )) AS Days FROM employee_deatils WHERE eemp_id=? Now while searching of those employees who have worked minimum 3 years (avg) in a company, i am not able to get the results. When i run this query in phpmyadmin, SELECT e.fname, e.lname, ROUND(SUM(DATEDIFF( IF(ed.leaving_date is null or ed.leaving_date = '' or ed.leaving_date='0000-00-00', CURRENT_DATE(), ed.leaving_date), ed.joining_date ))/365,0) AS Diff from employees e INNER JOIN employee_deatils ed ON e.id=ed.eemp_id group by ed.eemp_id it shows 2 records one with 0 and 3 years. But how to compare this with entered minimum years i am not getting. Not getting where to put Where condition with =3 years

I could't get the solution for this. How can it get this value into a variable?

@Barand Thanks, the logic worked but i have used this code. Now i want to get the data to variable in php thru javascript instead of displaying it based on id. as i do this <script> var res = "helloo"; </script> <?php $variable = "<script>document.write(res)</script>"; echo $variable; ?> it works. so as the same if it inside function onMessageArrived(r_message){ out_msg="Message received "+r_message.payloadString+"<br>"; out_msg=out_msg+"Message received Topic "+r_message.destinationName; //console.log("Message received ",r_message.payloadString); console.log(out_msg); document.getElementById("messages").innerHTML =out_msg; var topic=r_message.destinationName; if(topic=="house/outside-light") { document.getElementById("outside-light").innerHTML =r_message.payloadString; } if(topic=="house/outside-temperature") { var ot = r_message.payloadString; //document.getElementById("outside-temp").innerHTML =r_message.payloadString; } } <?php $test="<script>document.write(ot)</script>"; echo $test; ?> it won't work. Basically i want to get the data into a php variable. where i am going wrong, please suggest.

I am storing a json data which come from server as a message for every 10 seconds. While displaying the data i have to separate it by its type. For single message i have done it but for multiple messages i am not getting how to separate each json message. i have attached the sql dump messages.txt Here is my code to print for single message include('includes/Connection.php'); $l1 = $con->prepare("SELECT msg, title FROM messages ORDER BY id DESC limit 1"); $l1->execute(); $cntmsg = $l1->rowCount(); foreach($l1 as $r1) { $m1 = $r1['msg']; $someJSON = $m1; $someArray = json_decode($someJSON, true); $pr1 = $someArray["data"]["commsettings"]; foreach($pr1 as $MyIndex => $MyValue){ echo $MyValue["name"]; } } It is working. But i should display for all the messages like this $l1 = $con->prepare("SELECT msg, title FROM messages ORDER BY id DESC limit 4"); $l1->execute(); It wont work with my logic. How can i separate the array , i am not getting.

@Barand. Thanks. Atleast i found something to try on now. I will try this to integrate with my php code.

Can you please any kind of tutorial or example for this to achieve? I couldn't get anything on this.

I am developing a script for my client where i should be displaying data continuously coming from MQTT and my script is in PHP. Now i can publish and subscribe to topics but only after refreshing i can see the published data in my page. Do i need to use any script for this or how is it? Here is my subscribe page <?php require('phpMQTT.php'); $server = 'localhost'; // change if necessary $port = 1883; // change if necessary $username = '*****'; // set your username $password = '******'; // set your password $client_id = 'MyClient'; // make sure this is unique for connecting to sever - you could use uniqid() $mqtt = new Bluerhinos\phpMQTT($server, $port, $client_id); if(!$mqtt->connect(true, NULL, $username, $password)) { exit(1); } $mqtt->debug = true; $topics['xf/printservice/registration'] = array("qos"=>0, "function"=>"procmsg"); $mqtt->subscribe($topics,0); $start_time = time(); $done = 0; while (!$done && !hasTimedout() && $mqtt->proc()) { } $mqtt->close(); function procmsg($topic,$msg) { global $done; $done = 1; echo "Msg Recieved: ".date("r")."\nTopic:{$topic}\n$msg\n"; } function hasTimedout() { global $start_time; return (time() - $start_time > 10); } I am running this in localhost. How to keep displaying data without refreshing the page . is there any tutorial/script available ? please suggest.

i am using dropzone to upload files to folder as well as database. It is working properly but after uploading page is not getting refreshed . I want to refresh the page. Not getting where to add the option. this is my form <form action="file_upload.php" class="dropzone"> <div> <strong>Drop files here or click to upload.</strong> </div> </form> And here are the options in dropzon.js Dropzone.prototype.defaultOptions = { url: null, method: "post", withCredentials: false, parallelUploads: 2, uploadMultiple: false, maxFilesize: 256, paramName: "file", createImageThumbnails: true, maxThumbnailFilesize: 10, thumbnailWidth: 120, thumbnailHeight: 120, filesizeBase: 1000, maxFiles: null, params: {}, clickable: true, ignoreHiddenFiles: true, acceptedFiles: null, acceptedMimeTypes: null, autoProcessQueue: true, autoQueue: true, addRemoveLinks: false, previewsContainer: null, hiddenInputContainer: "body", capture: null, renameFilename: null, dictDefaultMessage: "Drop files here to upload", dictFallbackMessage: "Your browser does not support drag'n'drop file uploads.", dictFallbackText: "Please use the fallback form below to upload your files like in the olden days.", dictFileTooBig: "File is too big ({{filesize}}MiB). Max filesize: {{maxFilesize}}MiB.", dictInvalidFileType: "You can't upload files of this type.", dictResponseError: "Server responded with {{statusCode}} code.", dictCancelUpload: "Cancel upload", dictCancelUploadConfirmation: "Are you sure you want to cancel this upload?", dictRemoveFile: "Remove file", dictRemoveFileConfirmation: null, dictMaxFilesExceeded: "You can not upload any more files.", accept: function(file, done) { return done(); }, init: function() { return noop; }, forceFallback: false, not getting where can i put the function location.reload()

Yes, I got the answer. I modified query like this SELECT mid, mod_name, parent_id, link FROM modules m JOIN module_access ma ON (m.mid=ma.page_id OR ma.page_id=m.parent_id) WHERE ma.user=1 ORDER BY m.mid Thanks a lot @Barandfor your valuable time and patience.

My Query return This is how it displays <!DOCTYPE html> <html lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta name="generator" content="PhpED 19.5 (Build 19523, 64bit)"> <meta name="author" content="Barand"> <meta name="creation-date" content="08/11/2021"> <title>Example</title> <style type='text/css'> li { padding: 8px; } .li0 { margin-left: 8px; } .li1 { margin-left: 28px; } .li2 { margin-left: 48px; } </style> </head> <body> <ul> <li class='li0'><a href='#'> Sales </a> </li> <li class='li0'><a href='#'> Finance </a> </li> </ul> </body> </html> As it joins with the mid page_id from modules and module_access respectively, it wont display those ids which is not there in module_access table . So it is not getting displayed, as module_access table has only 1,3 ids which matches with mid from modules table.

@Barand I tried the same <?php //$items = array(); $res = $con->prepare("SELECT mid, mod_name, parent_id, link FROM modules m JOIN module_access ma ON m.mid=ma.page_id WHERE ma.user=? ORDER BY m.mid"); $res->execute([$uID]); $mods = []; foreach ($res as $row) { $mods[$row['parent_id']][] = $row; } function showMenu(&$arr, $parent = 0, $level = 0) { if (!isset($arr[$parent])) return; echo "<ul>\n"; foreach($arr[$parent] as $rec) { echo "<li class='li$level'><a href='{$rec['link']}'> {$rec['mod_name']} </a>\n"; if (isset($arr[$rec['mid']])) showMenu($arr, $rec['mid'], $level+1); echo "</li>\n"; } echo "</ul>\n"; } showMenu($mods, 0); ?> But it shows only module names, not the sub-items under that.

@BarandSorry for my mistake, I couldn't edit above post. in my module_access table, i have only module ids like this id| page_id | user 1 | 1 | 1 2 | 3 | 1 So, when i join , it shows only Sales and Finance as below. I want to display the sub items also. Sales Finance

I have a small php script for crm. i have designed it by module wise. Admin can grant access to each module and sub-items under that modules. 1st step: admin will select modules(Sales, Purchase, Finance) for which he has to give access to a particular user and save the module ids in module_access table. 2nd step : only selected module's sub-items will display to admin , in which he can select to give access 1st step is done. but in 2nd step i am not able to display only selected modules with the sub-items. Here is the table where i stored my data. module table mid | mod_name | parent_id | link 1 | Sales | 0 | # 2 | Purchase | 0 | # 3 | Finance | 0 | # 4 | Clients | 1 | clients.php 5 | Quotes | 1 | quotes.php 6 | Vendors | 2 | vendors.php 7 | POs | 2 | pos.php 8 | Invoices | 3 | invoices.php 9 | Taxes | 3 | taxes.php 10 | Expense | 3 | expenses.php module_access table id| page_id | user 1 | 1 | 1 2 | 3 | 1 3 | 4 | 1 4 | 9 | 1 5 | 10 | 1 user is from another table users where i store user info In this example i want to display like this Sales Clients (with a checkbox) Finance Taxes (with a checkbox) Expense (with a checkbox) I tried like this <?php $items = array(); $res = $con->prepare("SELECT m.mid, m.mod_name, m.parent_id, ma.id, ma.page_id, ma.user FROM modules m INNER JOIN module_access ma ON m.mid=ma.page_id WHERE ma.user=? ORDER BY m.mid"); $res->execute([$uID]); foreach($res as $row) { $items[$row['parent_id']][] = $row; } function showMenu($items, $parent = null) { $index = $parent == null ? '0' : $parent; if (empty($items[$index])) { return; } echo '<ul', $parent == null ? ' id="subitems" ... ' : '', '>'; foreach ($items[$index] as $child) { echo '<li rel="', $child['mid'], '">', htmlentities($child['mod_name']); showMenu($items, $child['mid']); echo '</li>'; } echo '</ul>'; } showMenu($items); ?> But it displays like this Sales Finance I am not getting how to query this from database.

I am adding line items in my invoice script, while adding i validate the data. if validation fails, it will display error message with entered data filled in the form. If it clears the validation, data gets submitted to database and displays the same . Here i can add many line items, so this process should keep repeating. Everything is working fine. But when the form submits it should display error in one place and display the submitted data in other place. Here is my form <form action="" method="post"> <div class="form-row"> <div class="col-md-4 mb-30"> <label for="validationDefault01">Select Customer</label> <select name="customer" class="form-control" id="validationDefault01" required> <option value=""></option> <?php $c1 = mysqli_query($con, "SELECT * FROM customers WHERE status='Active'") or die (mysqli_error($con)); while($c2 = mysqli_fetch_array($c1)) { ?> <option value="<?php echo $c2["cid"]; ?>" <?php if($c2["cid"] == $_POST['customer'] ) { echo "selected"; } ?> ><?php echo $c2["name"]; ?></option> <?php } ?> </select> </div> <div class="col-md-4 mb-30"> <label for="validationDefault02">Date</label> <input type="text" class="form-control" name="edate" id="datepicker" value="<?php echo isset($_POST["edate"]) ? $_POST["edate"] : $today; ?>" required /> </div> </div> <!-- line item --> <div class="table-responsive"> <table class="table table-active table-bordered table-sm"> <thead class="thead-active"> <tr> <th>Name</th> <th>Description</th> <th>UOM</th> <th>Price</th> <th>Stock</th> <th>Qty</th> </tr> </thead> <tr> <td><input type="text" id="productname" name="productname" value="<?php echo isset($_POST["productname"]) ? $_POST["productname"] : ''; ?>" required ></td> <input type="hidden" id="productcode" name="productcode" value="<?php echo isset($_POST["productcode"]) ? $_POST["productcode"] : ''; ?>" /> <td><textarea id="description" name="description"><?php echo isset($_POST["description"]) ? $_POST["description"] : ''; ?></textarea></td> <td><select name="uom" id="uom"> <?php $su1 = mysqli_query($con, "select * from uom"); while($su2 = mysqli_fetch_array($su1)) { ?> <option value="<?php echo $su2["uom_name"]; ?>" <?php if($su2["uom_name"] == $_POST['uom'] ) { echo "selected"; } ?> ><?php echo $su2["uom_name"]; ?></option> <?php } ?> </select> </td> <td><input type="text" required id="price" name="price" value="<?php echo isset($_POST["price"]) ? $_POST["price"] : ''; ?>" /></td> <td><input type="text" readonly id="stock" name="stock" value="<?php echo isset($_POST["stock"]) ? $_POST["stock"] : ''; ?>" /></td> <td><input type="text" required id="quantity" name="quantity" value="<?php echo isset($_POST["quantity"]) ? $_POST["quantity"] : ''; ?>" /></td> </tr> </table> <!-- line item ends---> <div class="form-row"> <div class="col-md-4 mb-30"> <input name="add" class="btn btn-success" type="submit" value="Add" /> </div> </div> </form> form submission <?php if(isset($_POST['add'])) { $customer = $_POST['customer']; $edate1 = $_POST['edate']; $edate = date('Y-m-d', strtotime((str_replace('/','-',$edate1)))); $pname = $_POST['productname']; $pcode = $_POST['productcode']; $uom = $_POST['uom']; $price = $_POST['price']; $quantity = $_POST['quantity']; $pc = mysqli_query($con, "SELECT min_price FROM items WHERE item_id=".$pcode."") or die (mysqli_error($con)); $prow = mysqli_fetch_array($pc); // This error part should be displayed inside <div id="error"></div> which is above the form if($price<$prow['min_price']) { echo '<div class="alert alert-inv alert-inv-danger alert-wth-icon alert-dismissible fade show" role="alert"> <span class="alert-icon-wrap"><i class="zmdi zmdi-bug"></i></span> Price should not be lesser than minimum price defined. <button type="button" class="close" data-dismiss="alert" aria-label="Close"> <span aria-hidden="true">×</span> </button> </div>'; } else { //data gets inserted into invoice table and displays submited data in table format // this part should be displayed below the form inside <div id="success"></div> } } Here i am not getting how to define that displays.