Mysql query to group by field and print grouped fields

Question

I need help to make the best query posible here. I have the following Database:

+----+--------------+-----------------+------------------------------+
| id | reference_id | reference_field |            value             |
+----+--------------+-----------------+------------------------------+
| 1  |     6215     |      title      |  Best recipe                 |
| 2  |     6215     |      introText  |  Intro for best recipe       |
| 3  |     6215     |      fullText   |  Full text for best recipe   |
| 4  |     6216     |      title      |  Play Football               |
| 5  |     6216     |      introText  |  Intro for play football     |  
| 6  |     6216     |      fullText   |  Full text for play football |
+----+--------------+-----------------+------------------------------+

I need to make a query where I group by reference_id and I should print the value by the reference_field, example of the output info:

Best recipe
Intro for best recipe
Full text for best recipe

Play Football
Intro for play football 
Full text for play football

UPDATE To accomplish this, I will print the query on the following way with PHP:

$result = $config->mysqli->query("SELECT * FROM table_name ORBER BY reference_id");
while($row = $result->fetch_array()) {
    echo ('<h1>'.$row["title"].'</h1>');
    echo ('<h1>'.$row["introText"].'</h1>');
    echo ('<h1>'.$row["fullText"].'</h1>');
}

With the query above, I get all the records one by one (not grouped by the reference_id), in other hand if I do the query

SELECT * FROM table_name GROUP BY reference_id

How do I get the 3 values (title, introText, fullText) to print on the loop interaction in PHP?

As you can see with a normal "order by" or "group by" does not produce the proper result to print the values on the loop. What I see here is that on the result I should print the values of 3 records by each loop interaction in PHP instead print 3 fields of each records, does it make sense?

score 0 · Answer 1 · answered Dec 12 '15 at 14:59

0

I think this is what you need:

SELECT value from TABLE_NAME
ORDER BY reference_id

answered Dec 12 '15 at 14:59

Waqas Shahid

1,051
1
7
22

Doing this will print all the records one by one just that ordered by reference_ID, if you see the data estructure by each record on the PHP loop I need to print the value from 3 different records instead 3 fields of each record. Your query assumes the 3 values to print are in the same record, but that's not the case here. Please see my update on the description where I put more info. – Saymon Dec 12 '15 at 15:44

score 0 · Answer 2 · answered Dec 12 '15 at 15:10

0

You may want this:

select *
from your_table
order by reference_id, reference_field desc;

answered Dec 12 '15 at 15:10

Jodevan

732
1
6
20

Doing this will print all the records one by one just that ordered. In one loop interaction in php I should get 3 values from 3 different records, please see my update on the description where I put more info, thanks @Jodevan – Saymon Dec 12 '15 at 15:42
Now your question is clear. Refer to this post: http://stackoverflow.com/questions/16568228/how-to-transpose-mysql-table-rows-into-columns – Jodevan Dec 12 '15 at 16:10

score 0 · Answer 3 · answered Dec 12 '15 at 15:17

0

Create connection, do query and show the results:

<?php
$dbo = //Create db connection
$statement = $dbo->prepare("SELECT * FROM table_name ORBER BY reference_id");
$statement->execute();
$result = $statement->fetchAll(PDO::FETCH_ASSOC);

echo "<pre>";
print_r ($result);
echo "</pre>";

?>

answered Dec 12 '15 at 15:17

EJW

338
3
6
18

Please see the update on my description where I put more info about my request, your proposed query return me all the records just ordered by reference_id, if you see the output data I need, by each record that should come from the query result I should print 3 values of 3 different record according the Database estructure. Your query assume the 3 values to print are in the same record, but that's not the case here – Saymon Dec 12 '15 at 15:48

Mysql query to group by field and print grouped fields

3 Answers3