I have a SQL table called "picturevents". In there are all events stored to which pictures exist.
I would now like to implement a dropdown on my page, where the user can select the year from which he would like to have the pictures displayed. For this I need to receive all years from my database.
I now have the following function in my php script (which works perfectly fine):
function getPictureYears(){
$ret = mysqli_query($this->link, "SELECT `id` FROM `pictureevents` WHERE 1");
$array = null;
while($row = mysqli_fetch_array($ret, MYSQL_ASSOC)){
$array[] = $row;
}
for($i=0; $i < sizeof($array); ++$i){
$id = $array[$i]['id'];
$array[$i]["year"] = mysqli_fetch_row(mysqli_query($this->link, "SELECT EXTRACT(YEAR FROM ( SELECT `date` FROM `pictureevents` WHERE `id` = $id))"))[0];
}
return $array;
}
I would be happy if I could simplify it. I found the following function:
SELECT EXTRACT(YEAR FROM (SELECT `date` FROM `pictureevents` WHERE 1))
This works great as long as the subquery only returns one row, when there is more than one row in the table, SQL reports:
1242 - Subquery returns more than 1 row
How can I get around the error, but still doing everything "in SQL"? Is it even possible?
BTW: The reason why I always try to do as much as possible in SQL is: their code is much more sophisticated than what I could ever write and it simplifies my code very much.
