JDevOnline

Hi Barand, hope you are well, Can you please suggest the edit to this sql query to fetch only the rows in which any month (from January to May) is not paid but at the same time keep showing all the columns of months. Doing it by using the quoted query, it removes the columns in which the fee is paid, thanks.

Dear Barand, thank you sooo very much! 👌

Hi Barand, I want to insert one more value to the above table after "Name", like email or phone number. Please tell me how can that value be put inside your loops?

Hello Barand, thank you very much, that resolved the issue. ✔️ 👌

Hi Barand, thanks for the help. How can we show just one row per student's name and only two columns, name and "Y", those who paid all 3 months.

😇

Hi Barand, I now want to extract the details of students who have paid all 2 months, i.e. march, april and may. I have written the following query but it is not bringing any records, however there are many students who have paid all 3 months. SELECT nroll.id as id, nroll.sname, nroll.ctclass, parents.memail, parents.femail FROM nroll INNER JOIN parents on nroll.id=parents.id INNER JOIN mfee on nroll.id=mfee.sid WHERE nroll.ctstudent = 'Y' and nroll.ctclass='1 Reception-1' and nroll.shift='1' and (mfee.month='2020-01-01' and mfee.month='2020-02-01') ORDER BY nroll.sname

Excellent! Thanks a lot! 😇

Hello Barand, I've tried to change the code you mentioned as below but it is still now working. Please help me modify the code to achieve the desired results: if($paid_arr == 'Y'){$cls='class="hilite"';}else{$cls='class="centex"';} echo "<tr><td class='centex'>$index</td><td>$name</td><td $cls>" . join("</td><td $cls>", $paid_arr) . "</td></tr>\n";

Hi Barand, Using CSS I want to highlight the cells where paid is equal to "N". I can't figure out where to insert this code due to arrays. Please help: <td <?php if($row['paid']=='N'){echo 'class="highlight"';} ?> </td>

Hello Barand, Thank you very much! That perfectly resolved my issue, I am once again very grateful to you.

I want to complete the task today, I would therefore be extremely grateful if you could help me remove the error asap, thanks.

Barand thank you very much for the prompt reply. After make 2 changes to your code I saved the code into a php file and called it into the browser, but it is showing few errors, kindly let me know how to remove these errors. Changes: Table name changed to "mfee" at both the occurrences. In both the queries "WHERE month >= ?" is changed to "WHERE month >= '$start'" Output:

Hi Barand, I am seeking you help to make an appropriate query to fetch data from monthly fee table of mysql db, whereby data is stored in such a way that each row stores the data of a student for one month, there are several rows per student. I want to fetch the data such that the result set should have the one row per student with multiple columns for months, each column showing the status of fee, paid or not. I have created the following query but the problem is that it is showing only one month which is '2020-03-01'. SELECT DISTINCT name, month, paid from mfee WHERE month >= '2020-03-01' GROUP BY name order by name

Hi Barand, In simple term, I want to fetch the details of payments, grouped by date, that is, on particular dates, as mentioned in my first post, thanks.

Hi Barand, I've tried the following query but it is showing only one row of data per paydate: Select distinct paydate, name, class, shift, vno from mfee where month='2020-04-01' and paid 'Y'

Hi Barand, I hope you don't mind posting my requests in the same thread: I need help with mysql query to fetch records in from a single database table grouped by paydate. The structure of table is as follows: id int(11) - P,U,AI sid int(10) - Student ID from another table shift int(1) - values: 1 or 2 name varchar(50) vno int(10) - voucher No. typically a 6 digit number class varchar(20) mfee varchar(6) - Monthly fee month date issuedate date lastdate date paydate date Each row in the table keeps the record of fee paid by a student for one month and every students has multiple rows. What I want to achieve is to fetch records of single month grouped by paydate, example: PayDate: 2020-04-01 & Month: 2020-04-01: Name1 | Class1 | Shift1 | VNo 420112 Name2 | Class1 | Shift2 | VNo 42080 Name3 | Class3 | Shift2 | VNo 42063 ..... PayDate: 2020-04-02 & Month: 2020-04-01: Name4 | Class1 | Shift1 | VNo 42066 Name5 | Class1 | Shift2 | VNo 42034 Name6 | Class3 | Shift2 | VNo 42099 .....

Hi Barand, That last query worked perfectly and successfully concluded my issue. I am soooooo thankful to you for you kind assistance. 😁😁😁 Kind regards.

Hi Barand, once again your query has worked flawlessly and as desired! If not very time consuming, can you please alter the following query to get the timestamp from the table "wp_vxcf_leads", the field name is "created". INSERT IGNORE INTO monthly_fee SELECT DISTINCT lead_id, cname.value as cname, chno.value as chno, mode.value as mode, email.value as email, mobile.value as mobile, amount.value as amount, omode.value as omode, tid.value as tid, date.value as date, comments.value as comments FROM wp_vxcf_leads_detail LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='cname') cname USING (lead_id) LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name = 'chno') chno USING (lead_id) LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='mode') mode USING(lead_id) LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='email') email USING (lead_id) LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='mobile') mobile USING(lead_id) LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='amount') amount USING(lead_id) LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='omode') omode USING(lead_id) LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='tid') tid USING(lead_id) LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='date') date USING(lead_id) LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='comments') comments USING(lead_id) Thanks a lot! 😃

We are now dealing with following 2 tables: 1. Source Table: wp_vxcf_leads_detail 3. Additional Table: wp_vxcf_leads These tables don't have a declared relationship however the "id" field of "Additional Table" is being used as the "lead_id" in source table.

Target table, created and populated by the modified query.

Structure and data of table "wp_vxcf_leads_detail", the source table.

Barand I thought lead_id could be used to determine the order of entries, however, it would be great if we could get the timestamp which is stored in another table called "wp_vxcf_leads". Following is the structures and data stored into this the second table.

I would like to use the second one, the modified version of your query. What I want to is to load only the new records from the source table without overwriting the ones already loaded in the target table, thanks.

Target Table Structure