Tags down


mysql group by show all rows

By : Gazet
Date : May 04 2020, 05:15 PM

I have a MySQL db with names and medical prescriptions. What I what to do is loop through all prescriptions belonging to a specific name and show them in a table, and then create a new table with the next name and a new set of prescriptions.

When I using GROUP BY, I able to display the name of a patient and the first table row of each group. But I do not know how to populate rows from the sub_fields within the groups. I have tried with GROUP_CONCAT, and it allows me to show all rows of a column from the group as comma-separated, but still I need to populate all the data from the sub rows like this:

NAME1: - START1_1 - END1_1 - DRUGID1_1

  • START1_2
  • END1_2
  • DRUGID1_2

  • START1_3

  • END1_3
  • DRUGID1_3

NAME2: - START2_1 - END2_1 - DRUGID2_1

  • START2_2
  • END2_2
  • DRUGID2_2

  • START2_3

  • END2_3
  • DRUGID2_3

I tried with the following code, using Group by, and I have also tried using GROUP_CONCAT, but I do not know how to handle the output.

$med = "SELECT  *  FROM {$wpdb->prefix}medicin_skema GROUP BY elevnavn";

 foreach ($wpdb->get_results($med, ARRAY_A ) as $row) {
     echo '<br>'.$row['elevnavn'].' ';
     echo '<br>'.$row['start_dato'].' ';
     echo '<br>'.$row['slut_dato'].' ';
     echo '<br><br>'.$row['drugid'].' '; 
Answer :

 you are group by the patient's name - so you will get one record PER PATIENT NAME.

The brute force approach is to select a distinct array of patient names; then select each of their prescriptions ordered by start date or / end date or whatever sensible option you want - then do your creation?

A sub-select in essence (I assume elevnavn is NAME)


SELECT distinct(elevnavn) from {$wpdb->prefix}medicin_skema while($row = mysql_fetch_array($result)) { $name=$row[0]; $newquery="SELECT * from {$wpdb->prefix}medicin_skema where elevnavn='".$name"'"; execute the query while($row = mysql_fetch_array($result)) { retrieve dates, drug id etc new create query } }

You can do a insert (select from) but you will need to get the sub-select correct first and use a left join to get ALL the records for each name.

Share : facebook icon twitter icon
Related Posts Related Posts :
  • clear array after form submit
  • pass url variable to javascript
  • access data using api
  • div position fixed css
  • add horizontal line between two div
  • disable toggle button css
  • css fixed line height
  • background image not working
  • table cell border
  • text line break css
  • overlay div on div
  • text direction rtl css
  • javascript convert string to an object
  • bind inner div click to outer div
  • jquery ajax oncomplete
  • how to use promise in for loop
  • jquery get element using variable
  • javascript send textbox value
  • pass div class content to another class or id
  • css new line after element
  • css calc not working properly
  • link disabled jquery
  • sql join table to subquery
  • MS Access SQL Issue with OR AND Operators
  • set textarea value using php
  • php merge two arrays into one
  • php form with google captcha
  • update current row in php
  • php date format mysql
  • mysql pdo select query
  • php function return an array
  • PHP mysqldump database to sql file
  • display image query
  • php create array using foreach loop
  • associative array php
  • group_concat in mysql laravel
  • php array_push empty
  • mysql on update cascade not working
  • pdo insert prepared statement
  • mysql insert ignore vs on duplicate key
  • mysql insert into existing row
  • mysql multi insert query
  • php change profile image
  • combine multiple arrays into one array php
  • how to sum values in an array
  • how to get sum of the total time
  • How to Create Facebook live stream without notification using API ?
  • on button click make div visible
  • css affect another element on hover
  • insert line after paragraph word
  • shadow
    Privacy Policy - Terms - Contact Us © bighow.org