How can I make a query to a DB according to what I want?

Navigation
0

Greetings,

I'm doing a program and I want the same to have different types of queries to the database, because they asked me to do the following types of questions:

  • Search by cedula
  • Search by name
  • Search for diseases
  • Search by date range (Between two dates or only one)

I already have the model of how to make the query but only for a single type of parameter, in this case by the name of the person, but I would like to know how I can make different types of queries and most importantly by date range.

What I have in my mind is to put a SELECT where I ask for the method I want to search for and that parameter takes it to do the search but I have no idea how to do it.

Could someone throw me a cable?

I leave the code I have.

NOTE : Also when making these queries I need to pull data from two different tables, one called basic data and another one called medical data 

<?php 

	function ejecuta_consulta($labusqueda){
	include("conexiond.php");

	$conexion= mysqli_connect($db_host,$db_usuario,$db_contra);

	if(mysqli_connect_errno()){

		echo "Fallo al conectar con la base de datos";
		exit();
	}

	mysqli_select_db($conexion,$db_nombre) or die ("No se encuentra la base de datos.");


	$consulta = "SELECT * FROM DATOSBASICOS WHERE NOM_PAC LIKE '%$labusqueda%'";

	$resultados = mysqli_query($conexion,$consulta);

	while($fila=mysqli_fetch_array($resultados, MYSQLI_ASSOC)){

	echo $fila ['CED_PAC']. " ";
	echo $fila ['NOM_PAC']. " ";
	echo $fila ['APE_PAC']. " ";
	echo $fila ['TEL_PAC']. " ";
	echo $fila ['CEL_PAC']. " ";

	echo "<br> ";
	echo "<br> ";
	}

	mysqli_close($conexion);

	}
?>
<!DOCTYPE html>
<html>
<head>
	<title>Sistema de historias médicas - Dr. Darling Davila</title>
	<meta charset="utf-8">
	<link rel="stylesheet" href="css/bootstrap.css">
	<link rel="stylesheet" type="text/css" href="css/estilo.css">
	<link href="https://fonts.googleapis.com/css?family=Lato|Roboto" rel="stylesheet">
</head>
<body>
	<?php
		$mibusqueda=$_GET["buscar"];

		$mipag=$_SERVER["PHP_SELF"];

		if($mibusqueda!=NULL){
			ejecuta_consulta($mibusqueda);
		}else{
			echo ("<form action='". $mipag . "' method='GET'>
				<img src='imagenes/header.png'>
					<h2>Busqueda de paciente</h2>
					<div class='contenedor'>
					<input type='text' name='buscar' class='input-100 text-center inline-block col-md-6 btn-enviar espacio-arriba'></label>

					<input type='submit' name='enviando' value='Consulta' class='text-center inline-block col-md-12 espacio-arriba btn-enviar'>
				</div>
				</form>");
		}
	?>


</body>
</html>

ANNEX:

I leave photos of my two tables

    
asked by Pablo Pernia 19.08.2017 в 08:30
source

2 answers

0

What is usually used for the kind of queries you want is JOIN , joining the tables by primary key / foreign key.

If you have two tables:

  • datosbasicos (id_persona, nombre, cedula, fecha_nacimiento...)
  • datosmedicos (id, id_persona, id_enfermedad, datomedico, fecha_consulta...)

And these tables are related by the column id_persona

You can bring data from both tables by JOIN .

- Using LIKE : 

SELECT db.id_persona, db.nombre, dm.datomedico, dm.fecha_consulta 
FROM datosbasicos db
INNER JOIN datosmedicos dm ON db.id_persona=dm.id_persona 
WHERE db.nombre LIKE '%Juan%';

There are some things to keep in mind:

  • db and dm are used as ALIAS of the tables datosbasicos and datosmedicos
  • If you want the LIKE to work for you both for juan and for Juan the column nombre of the table must be declared case insensitive (ci)

- By ID: 

SELECT db.id_persona, db.nombre, dm.datomedico, dm.fecha_consulta 
FROM datosbasicos db
INNER JOIN datosmedicos dm ON db.id_persona=dm.id_persona 
WHERE db.cedula = 'numero-cedula';

Keep in mind that if cedula is of type INT the criteria is written without single quotes: WHERE db.cedula = 10783643

- Between two dates:

There are several possibilities, the most common is to use BETWEEN : 

SELECT db.id_persona, db.nombre, dm.datomedico, dm.fecha_consulta 
FROM datosbasicos db
INNER JOIN datosmedicos dm ON db.id_persona=dm.id_persona 
WHERE dm.fecha_consulta BETWEEN '2017-07-31' AND '2017-08-31';

- Due to illness: 

SELECT db.id_persona, db.nombre, dm.datomedico, dm.fecha_consulta, e.enfermedad 
FROM datosbasicos db
INNER JOIN datosmedicos dm ON db.id_persona=dm.id_persona 
INNER JOIN enfermedades e  ON dm.id_enfermedad=e.id_enfermedad
WHERE dm.id_enfermedad = 4;

Here it is assumed that you also have a separate table called enfermedades , that way you unnecessarily avoid having the name of the disease in datosmedicos and related by a key ( id_enfermedad ). As you can see, this last query brings you data from three different tables using JOIN .

  

IMPORTANT NOTES:

     

  • I suggest you also read about SQL Injection. For the type of   application that you are creating you will need to give a higher level of   security to your code.

    •   

  • If you use the InnoDB engine when creating the tables you must set the constraints so that the related columns in   the tables are handled properly and no orphan data remains.   For example, if a person is deleted from the table datosbasicos   do not keep the data of that person orphaned in the table    datosmedicos .

    •   

  • You must also indicate which are the primary / foreign keys of each table.

    •   

  • So that the queries are optimized and there are no repeated data you will have to establish the indexes of each table properly.

    •   
    
answered by 19.08.2017 / 15:25
source
0

Good morning

For your SELECT you must have two basic conditions:

  • User with permissions (at least from SELECT, of course) to the BDs in which you need to do the search (s).
  • In each SELECT either in the FROM and / or JOIN, UNION ... etc place the name of the BD followed "." tablla name; below example (with the illustrations you provided):
    •   

    / * I will place "table [1 | 2]" because in the illustrations not
      manage to identify the respective name of each one * / 

    SELECT CED_PAC FROM DATOSBASICOS.tabla1    
UNION ALL    
SELECT CED_PAC FROM DATOSMEDICOS.tabla2

    Each SELECT above should be accompanied by their respective WHERE as appropriate.

    In the previous way, queries in both tables of different BD without making two connections; keep in mind that for the UNION performed both results must be identical in number of columns and data type; consideration that you should have when applying.

    Of course; that the above is to obtain the "necessary" data in a single query; also in case you need other data completely different (whether it is the same number of columns but different types of data), two query each with its SELECT [CAMPOS] FROM BasedeDatos.Tabla [...] ; no need (required) to have to make two connections to the database.

        
    answered by 19.08.2017 в 10:29
    Audio Manager's setSpeakerphoneOn () method generates interference. Problems with c ++ projects in visual studio 2013