# michelle1404

Members

41

1. ## Searching and Calculating Minimum Years of experience

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
2. ## Searching and Calculating Minimum Years of experience

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
3. ## Display MQTT data on php page without refreshing the page.

I could't get the solution for this. How can it get this value into a variable?
4. ## Display MQTT data on php page without refreshing the page.

@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.
5. ## fetch and display json array data stored in database

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.
6. ## Display MQTT data on php page without refreshing the page.

@Barand. Thanks. Atleast i found something to try on now. I will try this to integrate with my php code.
7. ## Display MQTT data on php page without refreshing the page.

Can you please any kind of tutorial or example for this to achieve? I couldn't get anything on this.
8. ## Display MQTT data on php page without refreshing the page.

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.

10. ## Access Level with user and page level

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.
11. ## Access Level with user and page level

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.
12. ## Access Level with user and page level

@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.
13. ## Access Level with user and page level

@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

16. ## Display records on week format

Thanks a ton!
17. ## Display records on week format

it is not considering Week-0,
18. ## Display records on week format

Thanks for the detailed code. For 1st week, data is not displayig properly ... from 2nd week, it works perfectly
19. ## Display records on week format

This is what i did, <?php ob_start(); include('inc/sessions.php'); include('inc/config.php'); include('inc/top.php'); \$msql = "SELECT WEEK(dtime) AS week, DAYNAME(dtime) AS dow, no_one, no_two, no_three FROM mytable ORDER BY dtime ASC"; \$mquery = mysqli_query(\$con, \$msql); ?> <!-- Page content --> <div id="page-content" class="block"> <table class="table"> <tr><td>Days</td> <?php while(\$row = mysqli_fetch_array(\$mquery)) { echo "<tr><td>".\$row['dow']."</td>"; echo "<td>".\$row['no_one']."</td>"; echo "<td>".\$row['no_two']."</td>"; echo "<td>".\$row['no_three']."</td></tr>"; } ?> </tr> </table> </div>
20. ## Display records on week format

I have data stored in datetime format, like this I want to display it Week-wise like this in php Week1 Week2 Monday | 1 | 5 | 8 Tuesday | 3 | 0 | 6 Wednesday | 2 | 3 | 5 Thursday | 7 | 0 | 9 Friday | 0 | 3 | 1 Saturday | 3 | 6 | 8 Now , in my query i am getting like this Can somebody suggest me how can i display it in php
21. ## managing session file separately to include in all file

After logging, session will start. So i have to manage sessions.php in all my other files to manage session . Here is my login file <?php if(isset(\$_POST['submit'])) { include("connect.php"); \$user=mysqli_real_escape_string(\$con, \$_POST['email']); \$pass=mysqli_real_escape_string(\$con, \$_POST['password']); \$sql="SELECT * FROM users WHERE email='".\$user."' AND password='".\$pass."' "; \$query=mysqli_query(\$con, \$sql) or die(mysqli_error(\$con)); \$count=mysqli_num_rows(\$query); if(\$count==1) { \$row=mysqli_fetch_array(\$query); session_start(); \$_SESSION['user_id']=\$row['uid']; } else { header("location:../index.php?error=1"); } if(isset(\$_SESSION["user_id"])) { header("location:../home.php"); } } ?> And in sessions.php <?php session_start(); session_regenerate_id(); if(\$_SESSION["user_id"]) { include("connect.php"); \$m1 = "select * from users where uid='".\$_SESSION['user_id']."'"; \$m2 = mysqli_query(\$con, \$m1); \$m3 = mysqli_fetch_array(\$m2); \$_SESSION['username'] = \$m3['fname'].' '.\$m3['lname']; } else if(!isset(\$_SESSION['user_id'])) { header("location:index.php"); } ?> As the session is started in login.php itself, i get error in sessions.php 'Session is already started'. But if i remove session_start();, it redirects to index.php (login form). I am confused. can somebody help me in this? i have other files like dashboard.php, home.php... in that how do i manage session?
22. ## getting data from multiple tables with sum in a single result set

@requinix Thank you very much.
23. ## getting data from multiple tables with sum in a single result set

@requinix Thanks. In this i get the result, but i want each months data to be added and show it as expense in that month... Now am getting like this But i want to display like this
24. ## getting data from multiple tables with sum in a single result set

i tried doing it like this, but not getting it, income is only from 1 table but expense should fetch add sum 2 tables data. \$sqlm = "(SELECT r.paid_amount, MONTHNAME(r.paid_date) AS 'Month', YEAR(r.paid_date) AS Yr, 'Income' AS 'Income' FROM receipt r INNER JOIN leads l ON r.customer=l.id GROUP BY MONTH(r.paid_date)) UNION ALL (SELECT SUM(pamt) AS paid_amount, 'Income' AS 'Expense' FROM ( Select sum(paid_amount) as pamt FROM expense_pay WHERE MONTHNAME(paid_date) AS 'Month', YEAR(paid_date) AS Yr UNION ALL Select sum(paid_amount) as pamt FROM payments WHERE MONTHNAME(paid_date) AS 'Month', YEAR(paid_date) AS Yr ) GROUP BY MONTH(paid_date) ORDER BY Yr DESC)";
25. ## getting data from multiple tables with sum in a single result set

Hi, I am trying to get the income-expense report on month basis. Actually for income, i am getting data from 'receipt' table structure is like this But for expense, i have to get the data from 2 tables, 'payments' and 'expense' , like this My display should be like this Actually for only 2 tables i got the result in the same format. Here is the working query \$sqlm = "SELECT r.invoice_id, r.customer, r.paid_amount, r.mode, r.ref_no, MONTHNAME(r.paid_date) AS 'Month', YEAR(r.paid_date) AS Yr, r.comments, l.company, 'Income' AS 'Income' FROM receipt r INNER JOIN leads l ON r.customer=l.id GROUP BY MONTH(r.paid_date) UNION ALL SELECT p.invoice_id, p.vendor, p.paid_amount, p.mode, p.ref_no, MONTHNAME(p.paid_date) AS 'Month', YEAR(p.paid_date) AS Yr, p.comments, v.name, 'Expense' AS 'Income' FROM payments p INNER JOIN vendor v ON p.vendor=v.id GROUP BY MONTH(p.paid_date) ORDER BY Yr DESC "; \$query = mysqli_query(\$con, \$sqlm) ?> <table> <tr><th>Paid Amount</th> <th>Details</th> <th>Month</th> </tr> <?php while(\$row = mysqli_fetch_array(\$query)) { ?> <tr> <td><?php echo \$row['paid_amount']; ?></td> <td><?php echo \$row['Income']; ?></td> <td><?php echo \$row['Month']; ?></td> </tr> <?php } ?> </table> But only for expense i should add 2 tables data, which i am not getting. Can somebody guide me on this?
×
×
• Create New...