I can’t bring all the records from the table

Asked

Viewed 342 times

1

Hello, I’m not able to show all the records of a row of the BD table, I can only bring the first record.

Here is my table:

CREATE TABLE IF NOT EXISTS `tbnoticiasrel` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`id_noticia` bigint(20) NOT NULL,
`id_noticia_relacionada` int(20),
`id_noticia_relacionada1` int(20),
`id_noticia_relacionada2` int(20),
`id_noticia_relacionada3` int(20),
 `id_noticia_relacionada4` int(20),
 PRIMARY KEY (`id`)
)

Here are my records in the table ( in admin mode of the website ) inserir a descrição da imagem aqui

Here is my select in php script:

 $query_noticiasrelacionadas = "SELECT * "
                        . "FROM tbnoticiasrel AS nr "
                        . "INNER JOIN tbnews AS n ON nr.id_noticia_relacionada = n.id "
                        . "WHERE nr.id_noticia = " . $_REQUEST[idNoticia];

here is the query result (above): inserir a descrição da imagem aqui Yes, it’s only bringing one of the five related data. what can be?

php mysql query

  • already tested LEFT JOIN?

  • Yes, I have tested and the result is the same as for RIGHT JOIN

  • That one WHERE nr.id_noticia = " . $_REQUEST[idNoticia] shouldn’t be WHERE n.id_noticia = " . $_REQUEST[idNoticia] ?

  • So let me ask you something. Is this field nr.id_noticia common between the two tables? Because in ON you are calling a different relation. And in case if nr.id_noticia does not have in the two tables, the others will be ignored.

  • No, Where is asking that When the id_noticia (from the main news being related) is equal to the news that is in the request. yes, the id_noticia of table nr is equal to the id field of table n.

  • From what I understand, you want to call the content by another id, but that relate by other values, the problem of this will be in Union, the "Where" will only bring content that has nr.id_noticia = value_searched, he will not bring news that were related by another condition unless both have the same related value... I understand?

  • To Test, take the WHERE condition and run your query in phpmyadmin or Workbench, see if it is bringing all the news with nr.id_noticia that you want to bring in the listing. You have to double or more in nr.id_noticia

Show 2 more comments

1 answer

1

Dude, I think the problem is precisely in Join, since it does not return data if there is no match in the two tables, try using LEFT OUTER JOIN instead of INNER JOIN or LEFT JOIN only.

It would be something like:

$query_noticiasrelacionadas = "SELECT * "
                    . "FROM tbnoticiasrel AS nr "
                    . "LEFT OUTER JOIN tbnews AS n ON nr.id_noticia_relacionada = n.id "
                    . "WHERE nr.id_noticia = " . $_REQUEST[idNoticia];

I hope it helps

Browser other questions tagged php mysql query

You are not signed in. Login or sign up in order to post.