SELECT INNER JOIN using PHP foreach in Datatable

Question

I’m working on a CRUD using Datatable where it shows me data from 3 related tables where these three tables two are related to one.

In case the PHP code is executed to fetch and display the data in the table in question. It happens that when I enter only a SELECT INNER JOIN in the code the field appears the name instead of the ID. So far so good, but when I add another INNER JOIN the following error occurs:

Datatables Warning: table id=user_data - Invalid JSON Response. For more information about this error, Please see http://datatables.net/tn/1

The 3 tables mentioned are structured as follows:

Table type_ps

tipoID    tipo

Table categories

categoriaID    categoria

And table users

id    tipo_fk    categoria_fk    nome

In case the users table has 2 Foreing Key fields (type_fk and categoria_fk) and to perform a php foreach fetch in the Datatable I use the code below:

<?php
include('db.php');
$query = '';
$output = array();
$query .= "SELECT users.*, tipo_ps.tipo, categorias.categoria
FROM users
INNER JOIN tipo_ps ON users.tipo_fk = tipo_ps.tipoID (Obs.: Se eu deixar apenas este INNER JOIN tipo_ps o mesmo aparece como nome ao invés do número ID porém se eu acrescento o INNER JOIN abaixo ocorre o erro)
INNER JOIN categorias ON users.categoria_fk = categorias.categoriaID

 ";


if(isset($_POST["search"]["value"]))
{
	$query .= 'WHERE first_name LIKE "%'.$_POST["search"]["value"].'%" ';
	$query .= 'OR last_name LIKE "%'.$_POST["search"]["value"].'%" ';
}
if(isset($_POST["order"]))
{
	$query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}
else
{
	$query .= 'ORDER BY id DESC ';
}
if($_POST["length"] != -1)
{
	$query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$statement = $connection->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
$data = array();
$filtered_rows = $statement->rowCount();
foreach($result as $row)
{
	
	$sub_array = array();
	$sub_array[] = $row["tipo"];
	$sub_array[] = $row["categoria"];
	$sub_array[] = $row["nome"];
	$sub_array[] = '<button type="button" name="update" id="'.$row["id"].'" class="btn btn-warning btn-xs update">Update</button>';
	$sub_array[] = '<button type="button" name="delete" id="'.$row["id"].'" class="btn btn-danger btn-xs delete">Delete</button>';
	$data[] = $sub_array;
}
$output = array(
	"draw"				=>	intval($_POST["draw"]),
	"recordsTotal"		=> 	$filtered_rows,
	"recordsFiltered"	=>	get_total_all_records(),
	"data"				=>	$data
);
echo json_encode($output);
?>

And on the index page is executed with the following HTML code:

<div class="container box">
			<h1 align="center">Usuários cadastrados</h1>
			<br />
			<div class="table-responsive">
				<br />
				<div align="right">
					<button type="button" id="add_button" data-toggle="modal" data-target="#userModal" class="btn btn-info btn-lg">Add</button>
				</div>
				<br /><br />
				<table id="user_data" class="table table-bordered table-striped">
					<thead>
						<tr>
							<th width="20%">Tipo</th>
							<th width="20%">Categoria</th>
							<th width="20%">Nome</th>
							<th width="10%">Editar</th>
							<th width="10%">Deletar</th>
						</tr>
					</thead>
				</table>
				
			</div>
		</div>
	</body>
</html>

As mentioned when I enter only the SELECT INNER JOIN of the field type_fk it appears in the table with the name instead of the ID:

$query .= "SELECT users.*, tipo_ps.tipo, categorias.categoria
FROM users
INNER JOIN tipo_ps ON users.tipo_fk = tipo_ps.tipoID
INNER JOIN categorias ON users.categoria_fk = categorias.categoriaID

but when I insert another INNER JOIN relative to the categoria_fk field and give a refresh on the index page occurs the Json error:

Datatables Warning: table id=user_data - Invalid JSON Response. For more information about this error, Please see http://datatables.net/tn/1 ";

How can I proceed? Thank you.

Answer 1

This is because you are bringing the type name when you declare in select: type_ps.type

$query .= "SELECT users.*, tipo_ps.tipo, categorias.categoria
FROM users
INNER JOIN tipo_ps ON users.tipo_fk = tipo_ps.tipoID
INNER JOIN categorias ON users.categoria_fk = categorias.categoriaID

 ";

instead of type_ps.typeID:

$query .= "SELECT users.*, tipo_ps.tipoID, categorias.categoria
FROM users
INNER JOIN tipo_ps ON users.tipo_fk = tipo_ps.tipoID
INNER JOIN categorias ON users.categoria_fk = categorias.categoriaID

 ";