Maybe it is impossible, but it would be useful showing a bibliographical database in a php page grouping the books of the same author within his name.
I mean, instead of :
have
I ask the impossible?
You could work with JSON objects to get a class with nested array
SELECT author,
JSON_ARRAYAGG(JSON_OBJECT(‘book’, book, ‘ed’. ed, ‘place’, place’, ‘date’, date)) AS books
FROM TABLE
GROUP BY author
It’s not tested maybe it has to be corrected a bit but I am just on my iPad
I have still to try, but this code doesn’t seems to fit my aim, because I can’t repeat as many ‘book’ as they are for every author (I have many authors with a different quantity of books each one).
Maybe I re-write my example:
autor1, book1, ed, place, date
autor1, book2, ed, place, date
autor1, book3, ed, place, date
autor2, book1, ed, place, date
autor2, book2, ed, place, date
autor3, book1, ed, place, date
autor3, book2, ed, place, date
autor3, book3, ed, place, date
autor3, book4, ed, place, date
and I wish have:
author1
book1, ed, place, date
book2, ed, place, date
book3, ed, place, date
author2
book1, ed, place, date
book2, ed, place, date
author3
book1, ed, place, date
book2, ed, place, date
book3, ed, place, date
book3, ed, place, date
and so on… (with many different authors and different books for each one).
I mean, this is rather more what the requesting language is for, rather than the database engine.
Returning the information, ORDERed by Author, BookName (I dont know which chapter you’re looking at from the post, so…), would allow PHP to walk the results, and say
“If the Author is not the same as the last author, start a new block, and put the author’s name.
Now output the book info.”
As I Said…
My query had a little typos but it works perfectly as expected
You only need to json_decode the books column.
Or more clean, get a complete JSON Object
or you can put all in a JSON string
https://www.db-fiddle.com/f/sqfzppdmTKogFKt4emwcPX/3
then you only need to do a
json_decode($stmt->fetch(PDO::FETCH_COLUMN))
and you have an array of objects containing the authors with a nested array containing the books.
I will try, thank you!
EDIT
Excuse my ignorance, but what php code for the output?
I quite like PDOs fetch modes for this kind of thing. I think in this case I would go for PDO::FETCH_UNIQUE then use author as the first column in the list of columns in the query, that way you should get all books grouped by their unique author.
$data = $pdo->query('SELECT author, book, ed, place, date FROM books')->fetchAll(PDO::FETCH_UNIQUE);
Thank you, Sam, but or you provide me the whole code (with php), or it would be better follow Thallius suggestion (helping me with the php code).
Otherwise I will get very confused.
Thanks!
I can’t provide all the code, as I don’t know the full database or PHP set up you have. But if you try a query like my example you should get the exact result you want.
Of course you may need to adapt the query to fit exactly the set up you have, but the two important parts to remember are that the first column you list is the one you want to group by, and the fetch mode should be PDO::FETCH_UNIQUE.
Uhm… But you could use the db-fiddle example provided above by Thallius.
EDIT
I don’t understand why this query in phpmyadmin works
SELECT JSON_OBJECT('autore', a.autore,
'books', JSON_ARRAYAGG(JSON_OBJECT('titolo', a.titolo, 'data', a.data))) as bibliografie
FROM bibliografie a
GROUP BY a.autore
But in a php page it doesn’t.
That is another option, but I think using the correct fetch mode is the simplest solution.
This code give me only one book per author:
try {
$db_name_specifico = "bibliografia";
require "../PDO_connect.php";
} catch (PDOException $e) {
echo "Errore: " . $e->getMessage();
die();
}
$sql = ('SELECT autore, titolo, data FROM bibliografie group by autore');
$stmt = $pdo->prepare($sql);
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
echo '<p><b>
' . $row['autore'] . '</b> ' . $row['titolo'] . ' ' . $row['data'] . '
</p>';
}
Removing group by autore I get all the books of my database (bibliografie).
This other doesn’t work at all:
try {
$db_name_specifico = "bibliografia";
require "../PDO_connect.php";
} catch (PDOException $e) {
echo "Errore: " . $e->getMessage();
die();
}
$sql = $pdo->query('SELECT autore, titolo, data FROM bibliografie')->fetchAll(PDO::FETCH_UNIQUE);
$stmt = $pdo->prepare($sql);
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
echo '<p><b>
' . $row['autore'] . '</b> ' . $row['titolo'] . ' ' . $row['data'] . '
</p>';
}
While there are no varialbes in your statement, there is no need to prepare() and execute(), you may simply use query().
You run the query() then try to prepare() and execute() the resulting data. That makes no sense at all.
You are also trying to fetch twice with different methods.
The example code I gave, by using query() chained with fetchAll() does all of prepare, execute and fetch in one line.
Assuming $pdo is your connection:-
$result = $pdo->query('SELECT autore, titolo, data FROM bibliografie')->fetchAll(PDO::FETCH_UNIQUE);
var_dump($result) ; exit() ; // For testing only
Once you see the result you want, remove the var_dump() then loop through the data as you wish.
The result is only one book (titolo) for author (autore)…
Can you post the exact code you tried.
And the dumped result.
Here you are:
try {
$db_name_specifico = "bibliografia";
require "../PDO_connect.inc";
} catch (PDOException $e) {
echo "Errore: " . $e->getMessage();
die();
}
$stmt = $pdo->query('SELECT autore, titolo, data FROM bibliografie order by autore')->fetchAll(PDO::FETCH_UNIQUE);
var_dump($stmt) ; exit() ; // For testing only
The output (I have to cut somehow):
["Algazali"]=> array(2) { ["titolo"]=> string(36) "Rinnovazione delle scienze religiose" ["data"]=> NULL } ["Alighieri"]=> array(2) { ["titolo"]=> string(15) "Divina commedia" ["data"]=> string(4) "1314" }
["Alston"]=> array(2) { ["titolo"]=> string(23) "Realism and Antirealism" ["data"]=> string(4) "2002" } ["Alvarez"]=> array(2) { ["titolo"]=> string(16) "Spie in Vaticano" ["data"]=> string(4) "2004" }
["Amartya K. Sen"]=> array(2) { ["titolo"]=> string(26) "Scelta, benessere, equità" ["data"]=> string(4) "1986" }
["Ambrogio da Milano"]=> array(2) { ["titolo"]=> string(173) "Exaemeron; in Lucae Evangelium.; De virginibus; De poenitentia; De sacramenti; la dipartita del fratello; la morte di valentiniano; la morte di Teodosio; Lettere; inni scelt" ["data"]=> NULL }
["Angelini"]=> array(2) { ["titolo"]=> string(39) ""Comunità cristiana e spazio politico"" ["data"]=> string(4) "1994" }
["Anscombe "]=> array(2) { ["titolo"]=> string(43) "An Introduction to Wittgenstein's Tractatus" ["data"]=> string(10) "1959, 1963" }
["Anscombe"]=> array(2) { ["titolo"]=> string(9) "Intention" ["data"]=> string(4) "1957" }
["Anselmo"]=> array(2) { ["titolo"]=> string(15) "De casu diaboli" ["data"]=> string(4) "1085" }
["Antiseri"]=> array(2) { ["titolo"]=> string(39) "Credere dopo la filosofia del secolo XX" ["data"]=> string(4) "2000" }
["Apel"]=> array(2) { ["titolo"]=> string(69) "L'influsso della filosofia analitica sul mio itinerario intellettuale" ["data"]=> string(4) "1997" }
["Applebaum"]=> array(2) { ["titolo"]=> string(5) "Gulag" ["data"]=> string(4) "2004" }
["Arac"]=> array(2) { ["titolo"]=> string(26) "Postmodernism and Politics" ["data"]=> string(4) "1986" } ["Arendt"]=> array(2) { ["titolo"]=> string(55) "Eichmann in Jerusalem: a report on the banality of evil" ["data"]=> string(4) "1963" }
["Ariès Ph.-G.Duby"]=> array(2) { ["titolo"]=> string(29) "La vita privata. Il Novecento" ["data"]=> NULL } ["Ariosto"]=> array(2) { ["titolo"]=> string(15) "Orlando furioso" ["data"]=> string(4) "1532" }
["Aristotele"]=> array(2) { ["titolo"]=> string(27) "Costituzione degli Ateniesi" ["data"]=> NULL } ["Armstrong"]=> array(2) { ["titolo"]=> string(39) "Universals: An Opinionated Introduction" ["data"]=> string(4) "1989" }
I.g. I have many books of Aristotele (and of Anselmo).
That result does not seem right. Can you try without the ORDER BY ?
Author is not a unique field. Why would you expect FETCH_UNIQUE to pull multiple records for a single key?
I guess that either we come back to the above @Thallius suggestion, or, as far I can suppose, it would be simpler use two tables, one new (a view) with only the authors and the other the existing one (with all the books).
EDIT
I have now a view (table), with all authors (and their name). Let’s call it table A. Could I call a query to use this view as main table and call all the books (in the main, original, table: table B) of a given author?
In this way:
table A
table B
And how show it in php code so that I get
author1
Sorry but this makes absolutely no sense at all.
You do not need other tables or views. Either you query all rows as they are and have one row for each book. then you can group them with PHP code as already mentioned in post #4 from hutley
or you take my query and work with PHP objects (Which you might need to learn how they work before)
To be honest, what you are doing is trying to get a result without knowing what you do. That cannot work