Inner join query to display the name with the id in two PHP tables

First of all, the names of the tables do not have to be the same, that will generate conflict. The idea is to have something like this:

TABLA: articulos
id      nombre                   idCategoria
================================================
1       Soldador de Estaño       1
2       PC                       2
3       Teclado                  2

TABLA: categoria
idCate      nombreCate
================================================
1           Electronica       
2           Computacion

<?php
try {
$conn = new PDO('mysql:host=localhost;dbname=BASEDEDATOS', 'ROOT', 'PASS');
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e){
    echo "ERROR: " . $e->getMessage();
}

$code = $_GET['id']; //teniendo en cuenta que viene desde un link '.php?id=3'

$sql = $conn->query("SELECT * FROM articulos INNER JOIN categoria ON categoria.idCate = articulos.cateId WHERE articulos.id = '".$code."'");
$row = $sql->fetch();
?>
ID de articulo: <?=$row['id'];?>
<p style="font-family: Arial; margin: 0 0 10px;">
Producto: <?=$row['nombre'];?><br>
en categoria <?=$row['nombreCate'];?>
</p>

What I did was also change in the table articulos the name of the same by artNombre to not generate problems. I hope I have helped you, just remember not to create tables with the same names when you use inner join

Because of the keys you put in, it seems that some code is missing there, you should review the question.

If what you put is what you have it, the problem is in the SELECT p.id_articulo... that should be SELECT p.id_producto...

I am very fussy about the names used and I think it is a good practice to use a series of patterns, which in addition to streamlining the analysis and programming, facilitate the exchange of code and group programming.

Some basic rules would be:

  

Table names always in plural. For example: categorias

     

The Primary Key field always exists, always is a autoincremental and always is called id

     

The Foreing Keys always are called id + _ + nombre_de_tabla_en_singular : For example: id_categoria

     

Never repeat the name of the table in a field, for example: nombre_articulo in table articulos . It is redundant information,   because just putting nombre already suggests that it is the name of the   article (the table or the alias will indicate it). If i was   justified using the name of the table to "facilitate" understanding   it should also be used in the other fields: color_articulo ,    familia_articulo , precio_articulo .... and that is not usually done.   It also allows us to use generic methods or properties that will work most of the time, for example: $registro->nombre We know that it will work for most of the queries, be it the table articulos , categorias or clientes , with which we obtain a more reusable code.

     

Use tables / fields names semantically consistent with their   functionality, using complete and separated words by low script in case   of multiple words, for example, the table permRespAlm would be permisos_responsable_almacen with which a more readable code is generated and therefore easier to maintain, even if it is longer.

This way your tables would be:

TABLA: articulos
id      nombre      id_categoria
===================================================
1       Prueba       2


TABLA: categorias
id      nombre
==================================
1       Prueba1       
2       Prueba2     

And the query called correctly (as indicated by @ ivan.depi) iterating over the result:

<?php
$id_del_articulo = $_REQUEST['id'];
$sql = ' SELECT a.id, a.id_categoria, a.nombre, c.nombre as categoria
           FROM articulos AS a
          INNER JOIN categorias AS c ON c.id = a.id_categoria
          WHERE a.id = '.$id_del_articulo;

$query = $con->query($sql);
while ($row = $query->fetch_object()) {
    echo " Artículo: $row->nombre  Categoría: $row->categoria <br>".PHP_EOL ;    
}

?>

The query variable collects all returned rows. You must go through the object first.

$sql = "SELECT p.id_articulo, p.id_categoria, c.id_categoria, c.nombre_categoria as 
categorias
FROM articulos p
INNER JOIN categorias c ON p.id_categoria = c.id_categoria";
$query = $con->query($sql);


while ($row = $query->fetch_assoc()) {
    echo $row['id_categoria'];
    echo "\n";
    echo $row['categorias'];
}

Have you tried the query directly in the database to make sure it works and that everything is correct? Check also the spaces within the query to me happened once I gave an error because I put it in several lines within the php as you have it. Also the "assoc" once did not go well at all, replaced by "fetch_array".

Try this way to see.

<?php 
$sql = "SELECT p.id_articulo, c.id_categoria, c.nombre_categoria as categorias FROM articulos p INNER JOIN categorias c ON p.id_categoria = c.id_categoria";
$query = $con->query($sql);

$total_rows = $query->num_rows;

if($total_rows > 0){
   while($row = $query->fetch_array()){
       echo $row['id_categoria'];
       echo "\n";
       echo $row['categorias'];
   }
}
?>