Related tables. MySQLi. Pharmacy example

Juan_Antonio · July 20, 2023, 8:44am

1.- MySQLi related tables. Many to many.

p55H_MySQL_Farmacia_v2.aia (10.2 KB)

In this topic we saw an example of "many-to-many" related tables using Sqlite:
https://community.appinventor.mit.edu/t/related-tables-simplesqlite-extension-pharmacy-example/88326

Now let's see that same example with MySQLi.

We have a database with three tables:

mysql60

In the tables pacientes and vademecun we have registered patients and medicines.

A patient can have several medicines:.

One type of medicine can be assigned to several patients:

We have another table called pacientes_vademecum that will relate patients with medicines.

paciente_id: 4 (Pedro) vademecum_id (4 , 5, 3, 2) (Dolorin, Estomac, Ansiolitic, Sleepic) quantity (1, 12, 12, 9)

Juan_Antonio · July 20, 2023, 8:44am

2.- Creation of the tables by importing a file.

farmacia_crea_tablas.sql

-- Create table pacientes
CREATE TABLE IF NOT EXISTS pacientes (
  id INT PRIMARY KEY,
  nombre VARCHAR(20),
  edad INT
);

-- Create table vademecum
CREATE TABLE IF NOT EXISTS vademecum (
  id INT PRIMARY KEY,
  medicina VARCHAR(20),
  precio INT
);

-- Create table pacientes_vademecum
CREATE TABLE IF NOT EXISTS pacientes_vademecum (
  pacientes_id INT,
  vademecum_id INT,
  cantidad INT,
  FOREIGN KEY (pacientes_id) REFERENCES pacientes(id),
  FOREIGN KEY (vademecum_id) REFERENCES vademecum(id),
  UNIQUE (pacientes_id, vademecum_id)
);

-- Insert users into pacientes
INSERT INTO pacientes (id, nombre, edad)
VALUES (1, 'Antonio', 22),
       (2, 'Luis', 33),
       (3, 'Carlos', 55),
	   (4, 'Pedro', 66),
	   (5, 'Eva', 11),
	   (6, 'Miguel', 44);
	   
-- Insert medicina into vademecum
INSERT INTO vademecum (id, medicina, precio)
VALUES (1, 'Aspirina', 251),
       (2, 'Sleepic', 234),
       (3, 'Ansiolitic', 152),
	   (4, 'Dolorin', 139),
	   (5, 'Estomac', 125);
	   
-- Insert users into pacientes_vademecum
INSERT INTO pacientes_vademecum (id, pacientes_id, vademecum_id, cantidad)
VALUES (1, 1, 1, 4),
       (2, 1, 3, 2),
       (3, 1, 5, 6),
	   (4, 2, 1, 3),
	   (5, 4, 4, 1),
	   (6, 4, 5, 12),
	   (7, 4, 3, 12),
	   (8, 4, 2, 9);

Juan_Antonio · July 20, 2023, 8:45am

3.- App Screen1.

In Screen1 of the app we can insert new patients and vademecum. Update age, update price, delete by name and delete by medicine.

- App Screen2.

On Screen2, we select a patient, we select a vademecum.

"Show medicines for this nombre", in this example: Antonio
Aspirina, 4, Ansiolitic 2, Estomac 6
"Show nombres for this medicine", in this example: Aspirina
Antonio, Luis
"Add medicina selected to this nombre", in this example:
Antonio ---> Aspirina, 4

Juan_Antonio · July 20, 2023, 8:45am

4.- PHP Code.

farmacia.php

<?php
 // 1.- IDENTIFICACION nombre de la base, del usuario, clave y servidor
$db_host="localhost";
$db_name="id19781186_my_base_data"; // fake
$db_login="id19781186_juan"; // fake
$db_pswd="Contraseña"; // fake

// 2.- CONEXION A LA BASE DE DATOS
$link = new mysqli($db_host, $db_login, $db_pswd, $db_name);

if ($link->connect_error) {
    die("Error de conexión: " . $link->connect_error);
} else {
	
$boton = $_POST['boton'];


/////////////   INSERTAR PACIENTE - INSERT PACIENTE IF NOT EXIST /////////////////////
if ($boton == "btnInsertarPaciente"){
    $nombre = $_POST['nombre'];
    $edad = $_POST['edad'];
$queryExistencia = "SELECT COUNT(*) FROM pacientes WHERE nombre = '$nombre'";
$resultExistencia = mysqli_query($link, $queryExistencia);
$row = mysqli_fetch_array($resultExistencia);

if ($row[0] == 0) {
    // Insertar datos solo si el nombre no existe previamente
    $query = "INSERT INTO pacientes (nombre, edad) VALUES ('$nombre', '$edad')";
    $result = mysqli_query($link, $query);

    if ($result) {
        echo "Datos agregados a la base.";
    } else {
        echo "Error: ".mysqli_error($link);
    }
} else {
    echo "El nombre ya existe en la base de datos.";
}
mysqli_close($link);
}

/////////////   INSERTAR VADEMECUM - INSERT VADEMECUM IF NOT EXIST /////////////////////
if ($boton == "btnInsertarVademecum"){
    $medicina = $_POST['medicina'];
    $precio = $_POST['precio'];
$queryExistencia = "SELECT COUNT(*) FROM vademecum WHERE medicina = '$medicina'";
$resultExistencia = mysqli_query($link, $queryExistencia);
$row = mysqli_fetch_array($resultExistencia);

if ($row[0] == 0) {
    // Insertar datos solo si la medicina no existe previamente
    $query = "INSERT INTO vademecum (medicina, precio) VALUES ('$medicina', '$precio')";
    $result = mysqli_query($link, $query);

    if ($result) {
        echo "Datos agregados a la base.";
    } else {
        echo "Error: ".mysqli_error($link);
    }
} else {
    echo "La medicina ya existe en la base de datos.";
}
mysqli_close($link);
}

// INSERTAR RELACIÓN PACIENTE -> VADEMECUM - INSERT RELATED PACIENTE -> VADEMECUN
if ($boton == "btnInsertarPacienteVademecum"){
	$nombre = $_POST['nombre'];
	$medicina = $_POST['medicina'];
	$cantidad = $_POST['cantidad'];
	$query="INSERT INTO pacientes_vademecum (pacientes_id, vademecum_id, cantidad) VALUES (
	(SELECT id FROM pacientes WHERE nombre = '$nombre'), 
	(SELECT id FROM vademecum WHERE medicina = '$medicina'),
	 '$cantidad')";
	$result = mysqli_query($link, $query);
	
    if ($result) {
		echo"Datos agregados a la base.";
	} else {
		echo "Error: ".mysqli_error($link);}				
    mysqli_close($link);
}

//  ACTUALIZAR EDAD PACIENTE- UPDATE AGE PATIENT
if ($boton == "btnActualizarPaciente"){
    $nombre = $_POST['nombre'];
    $edad = $_POST['edad'];
    $query="update pacientes set edad='$edad' WHERE nombre='$nombre'";
    $result = mysqli_query($link, $query);
	
    if ($result) {
		echo"Edad modificada.";
	} else {
		echo "Error: ".mysqli_error($link);}				
    mysqli_close($link);
}

//   ACTUALIZAR PRECIO VADEMECUM - UPDATE PRICE
if ($boton == "btnActualizarVademecum"){
    $medicina = $_POST['medicina'];
    $precio = $_POST['precio'];
    $query="update vademecum set precio='$precio' WHERE medicina='$medicina'";
    $result = mysqli_query($link, $query);
	
    if ($result) {
		echo"Precio modificado.";
	} else {
		echo "Error: ".mysqli_error($link);}				
    mysqli_close($link);
}

// BORRAR PACIENTE - DELETE  PATIENT 
if ($boton == "btnBorrarPaciente"){
    $nombre = $_POST['nombre'];
    $query="delete from pacientes where nombre='$nombre'";
    $result = mysqli_query($link, $query);
	
    if ($result) {
		echo"Paciente borrado.";
	} else {
		echo "Error: ".mysqli_error($link);}				
    mysqli_close($link);
}

//  BORRAR VADEMECUM - DELETE  VADEMECUM 
if ($boton == "btnBorrarVademecum"){
    $medicina = $_POST['medicina'];
    $query="delete from vademecum where medicina='$medicina'";
    $result = mysqli_query($link, $query);
	
    if ($result) {
		echo"Vademecum borrado.";
	} else {
		echo "Error: ".mysqli_error($link);}				
    mysqli_close($link);
}

/////////////////////// MOSTRAR TABLA PACIENTES - SHOW TABLE PATIENTS //////////////
if ($boton == "btnVerTablaPacientes"){
	$query = "SELECT * FROM pacientes";
	$result = mysqli_query ($link, $query);
	
	if ($result) {
		while ($fila = mysqli_fetch_assoc($result)) {
			echo $fila['id'] . ', '.$fila['nombre'] . ', ' . $fila['edad'] ."\n";
		}
	} else {
		echo "Error: ".mysqli_error($link);
	}
}

/////////////////////// MOSTRAR TABLA VADEMECUM - SHOW TABLE VADEMECUM ///////
if ($boton == "btnVerTablaVademecum"){
	$query = "SELECT * FROM vademecum";
	$result = mysqli_query ($link, $query);
	
	if ($result) {
		while ($fila = mysqli_fetch_assoc($result)) {
			echo $fila['id'] . ', '.$fila['medicina'] . ', ' . $fila['precio'] ."\n";
		}
	} else {
		echo "Error: ".mysqli_error($link);
	}
}

/////////////////////// MOSTRAR MEDICINAS DE UN PACIENTE - SHOW  MEDICALS /////////////////////////////////////
if ($boton == "btnBuscarNombre"){
	$nombre=$_POST['nombre'];
	$query="SELECT v.medicina, pv.cantidad
	FROM pacientes p
	JOIN pacientes_vademecum pv ON p.id = pv.pacientes_id
	JOIN vademecum v ON pv.vademecum_id = v.id
	WHERE p.nombre = '$nombre' ";
	$result = mysqli_query ($link, $query);
	
	if ($result) {
		while ($fila = mysqli_fetch_assoc($result)) {
			echo $fila['medicina'] . ': ' . $fila['cantidad'] . ', ';
		}
	} else {
		echo "Error: ".mysqli_error($link);
	}
}

////// MOSTRAR PACIENTES CON ESTA MEDICINA - SHOW PATIENTS WITH THIS MEDICINE /////
if ($boton == "btnBuscarMedicina"){
	$medicina=$_POST['medicina'];
	$query="SELECT p.nombre
    FROM pacientes p
    JOIN pacientes_vademecum pv ON p.id = pv.pacientes_id
    JOIN vademecum v ON pv.vademecum_id = v.id
    WHERE v.medicina = '$medicina' ";
	$result = mysqli_query ($link, $query);
	
	if ($result) {
		while ($fila = mysqli_fetch_assoc($result)) {
			echo $fila['nombre'] . ', ';
		}
	} else {
		echo "Error: ".mysqli_error($link);
	}
}
}
?>

Juan_Antonio · July 20, 2023, 8:45am

5.- Screen1. Blocks.

- Screen2. Blocks.

Juan_Antonio · July 20, 2023, 9:23am

This is a didactic example that can be improved.

Juan_Antonio · July 20, 2023, 9:43am