1
I have 3 tables in the database to control a "news portal", where the tables have the following functions:
- news -> Table with the news
- gallery -> Table with images used in news
- relation -> Table relating the news to the images
Each news item may have more than one image, hence the segmentation. I was able to elaborate a way to select the news and the images belonging to it, but with the use of foreach, thus running 2 SELECT, for example:
$noticia = "SELECT * FROM noticia";
foreach ($noticia as $row) {
$id = $row['id'];
$galeria = "SELECT a.* FROM galeria a, relacao b WHERE b.idNoticia = '$id' AND b.id_galeria = a.id";;
}
There are cases where the news may not have image yet.
I would like to know if you have any method to improve this code and perform the whole query in just one select, it is possible?
There are other areas that I can expand on this same logic, such as selecting tags registered in the news (follows the same logic, only changing the reference tables), but solving this problem, I can solve the other.
Here’s how: "SELECT a.* FROM gallery a left Join relacao b on a.ID = b.ID WHERE b.idNoticia = '$id' "
– Reginaldo Rigo
Try it this way here,
$galeria = "SELECT a.* FROM galeria a, relacao b JOIN noticia n ON b.idNoticia = n.id WHERE b.id_galeria = a.id";– LocalHost
If you do, I’ll put the full answer...
– LocalHost
@Localhost but the idea would be to merge the
galeriaandrelacaowith the news select, turning only into 1. The way you passed it does not solve this issue. In case, first I need to upload the news itself, then load the images (if any).– celsomtrindade
but in case I withdrew the news and foreach turned into a select only... I just could not test here,but I will test here...
– LocalHost
@Localhost but and the selection of data from the news table?
– celsomtrindade
Ai would just add next to select
$galeria = "SELECT a.*,n.* FROM galeria a, relacao b JOIN noticia n ON b.idNoticia = n.id WHERE b.id_galeria = a.id";– LocalHost
@Localhost, yes, perfect. So, in this case I was able to select, however, only news that have images. Those that do not have were not selected. I need to select all (with/without image) plus the images of those that have.
– celsomtrindade
Put like this your bank to better understand... because now I’ve bugged here kk
– LocalHost
@Celsomtrindade if you want all news, even without a photo, just use LEFT JOIN with the news table on the left. More details here: http://answall.com/questions/6441/70 - Note that in this case, you will be doing the repeat news part in all photos, which is usually a waste of server bandwidth. What you want only makes sense if you want to take a photo only for news (cover photo type, for example), and upload the other news photos when opening, in a separate SELECT. (and I have seen solution to two situations already answered here on the site, to speak well the truth)
– Bacco
@Bacco then, although there are other means, for the sake of resources, the ideal would be to continue doing as demonstrated in my question?
– celsomtrindade
It only depends on your specific case. The important thing is that you understand the difference in each of the ways, as I said before. Using Join, repeats the fields that will be used only once, but returns everything. Doing it your way, are several requests. Each option has a "cost", need to calculate in your real case what more compensates.
– Bacco