JDevOnline

Source Table Structure

Hi Barand, FYI I've changed the names and structure of both the tables, now I am using the following modified version of your query and also added the id column in the target table "monthly_fee", as mentioned earlier. CREATE TABLE 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)

Hi Barand, I am soooo grateful for your kind assistance 😀😀😀 , your query has completed the tasks table creation and loading data from source table. Now, can you please help me with loading future records from the source table into the table already created by your query. I've added id (primary, unique, auto increment) column at the beginning of the table.

Yes Sir, that is exactly what I want to do.

Hello Barand, I have one similar scenario. I have a table that stores form data into rows instead of columns. In the attached image, the column "cf7_id "is the form id and "data_id" is the for data submitted. I tried many variations but haven't been able to devise a query to fetch the data of each submission as a record set to be inserted into another table. I only want to extract comma separated values from the "value" column as a record set. Following is the query I had written: SELECT * FROM wp_cf7_vdata_entry WHERE data_id in (SELECT data_id FROM wp_cf7_vdata_entry GROUP BY data_id HAVING COUNT(data_id) > 1)

Dear Barand this perfectly solves my problem. I will consider adopting the suggested alternative model which will allow multiple siblings. Thank you very much!

Hi, I am seeking help with the following scenario: I have a mysql db with a table to store students info with following fields: id, sname, ctclass, shift, siblingof, elderof, ctstudent (sname=Student's name, ctstudent=Y/N, siblingof/elder of=id of sibling or elder in the same table.) Every child who has a brother or sister either has a sibling or elder therefore siblingof or elderof store ids of either sibling or elder in the same table. What I want to do is to run a query to select only current children with elders AND also want to find out the status of elders, whether they are current student of have left. This info is stored in "Y or N" in the column name called "ctstudent". I've created the following code but is not showing any result. SELECT id, sname, ctclass, shift ctstudent FROM nroll WHERE siblingof <> '' and ctstudent IN (SELECT ctstudent as ct FROM nroll where id=siblingof)

Hi Barand, I tried used the array declaration, as you mentioned but it is rendering a blank page.

Thanks for the info, I will either upgrade if possible or change my web hosting company, to be able to use the latest version of php, thanks.

Since i can not upgrade the php version to higher than 5.4, please suggest change in code, thanks.

Hi Barand, PHP version on my local server is 5.6 whereas on the online server it is 5.4, this might be the reason that the script has thrown the following error on then taken online: PHP Local: 5.6 PHP Online: 5.4 "Parse error: syntax error, unexpected '[' in /home/content/87/html/nroll-classwise.php on line 37" When I inspected the code on the line number mentioned above it is: $data = []; Please tell me how to handle this situation, thank you.

Barand and taquitosensei thank you very much for you kind assistance. You guys have pointed to the right direction, working on which helped me find the way, thanks.

I've tried the left join but hasn't worked well. I read about outer join but don't have the skill to code it.

Hi, I want to devise a query to join 2 mysql data tables. What i want to achieve is to join both the tables in such a way that the result would display all the rows of TableOne with the single row of TableTwo appended to every single row of the result. TableOne has fields id, a, b, c and multiple rows. Data sample: 1, a, b, c 2, d, e, f TableTow has fields id, d, e, f and single row. Data sample: 1, g, h, i, j, k Desired Output: a, b, c, g, h, i, j, k d, e, f, g, h, i, j, k

Barand, this has perfectly concluded the current project. I am sooooooo very happy for your great help, thanks once again.

Hi Barand, the table structure you've created is lacking one column, which is mimp, which marks important mothers by occupation like in the case of fathers fimp is used. Following is the mockup of the table i am trying to generate.

I want to add student's name and mother's name in occupation tables as rows and not as columns in HTML tables. However, the data is stored in the same table in columns.

Barand, one more thing. I have 2 more columns mother's name (mname) and mother's occupation (moccup), how can I display mother's info in same occupation tables, where moccup = 'Y'.

Dear Barand you are a Hero! You've given me the simplest and most efficient code that I wanted and been trying very hard to product the same output. Once again, thanks a lot!

Hi Barand, thanks for the assistance. I tried running your code but it is resulting in the error: ( ! ) Fatal error: Call to a member function query() on resource in F:\Ampps\www\imp\imp3.php on line 13 I am using following connection string, is it OK? $db = mysql_connect("localhost","root","mysql"); if (!$db){ die('Could not connect: ' . mysql_error());} mysql_select_db("nominalroll", $db); $sql = "select sname , fname , foccup from nroll where fimp = 'Y' order by foccup, sname"; $res = $db->query($sql);

Thanks for your reply. How is it possible to draw individual html table for people belonging to same occupation using only ORDER BY?

I placed that within the table just to cross check, the column will be removed, when not required.

Hi, thank for your replies. Please review my code and suggest necessary amendments to produce the tables like in the image "table that i want.png" with multiple rows, thanks. $query = "select sname, fname, foccup from nroll where fimp = 'Y' group by foccup"; $result = mysql_query($query); $row = mysql_num_rows($result); $index = 0; while($row = mysql_fetch_assoc($result)){ // loop to store the data in an associative array. //$yourArray[$index] = $row; echo "<table border=1>"; echo '<tr><th colspan="3">' . $row[foccup] . '</th></tr>'; echo "<tr><th>Student Name</th><th>Fathers Name</th><th>Occupation</th></tr>"; while($row = mysql_fetch_assoc($result)){ echo '<tr><td>' . $row[sname] . '</td><td>' . $row[fname] . '</td><td>' . $row[foccup] . '</td></tr>'; } $index++; echo "</table><br>"; $index++; }

I am a newbie, please post the real code, I am having difficulties with the mentioned example, thank.

Hi, I in fact want to output multiple html tables each belonging to a different occupation and each with multiple rows of people belonging to the occupation of that table.