SQL query with two fields

Question

SQL query with two fields

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have the problem in an SQL query because in a field with auto complete I return the full name of the person I'm looking for in this case are first and last name, that sends it by post to a php file that generates the table Starting from the SQL query, however, it does not find anyone because for obvious reasons when searching for the name field there is no one named as the received parameter is the name and surname query

This is my sql query in PHP

$sql = "SELECT Nombre,Apellidos, ct_asistencias_loc.fecha, hora, ct_asistencias_loc.Status from ct_alumnos INNER JOIN ct_asistencias_loc on ct_alumnos.idAlumno=ct_asistencias_loc.idAlumno where Nombre ='".$_POST['nombre']."'";

An example of the parameter received for the query is the name of " Ramon Guillermo Juarez Carbajal "

In my database I have the fields of first and last name, my question is how to ask the query so that it uses the where from the two tables or that it mixes the name and surname data and the LIKE works?

mysql php

asked by Alejandro Melendez 11.10.2017 в 01:37

source

2 answers

0

You can concatenate and compare the concatenated values.

To concatenate you can use CONCAT_WS :

Exact name and last name, without LIKE :

 $nombre=$_POST['nombre'];

$sql="SELECT 
        Nombre, Apellidos, ct_asistencias_loc.fecha, 
        hora, ct_asistencias_loc.Status, 
        CONCAT_WS(' ', Nombre, Apellidos) nombre_completo 
        FROM ct_alumnos 
        INNER JOIN ct_asistencias_loc on ct_alumnos.idAlumno=ct_asistencias_loc.idAlumno 
        HAVING nombre_completo='".$nombre."'";

First and Last name with LIKE :

 $nombre=$_POST['nombre'];

$sql="SELECT 
        Nombre, Apellidos, ct_asistencias_loc.fecha, 
        hora, ct_asistencias_loc.Status, 
        CONCAT_WS(' ', Nombre, Apellidos) nombre_completo 
        FROM ct_alumnos 
        INNER JOIN ct_asistencias_loc on ct_alumnos.idAlumno=ct_asistencias_loc.idAlumno 
        HAVING nombre_completo LIKE '%".$nombre."'%";

Or CONCAT :

Exact name and surname, without LIKE :

$nombre=$_POST['nombre'];

$sql="SELECT 
        Nombre, Apellidos, ct_asistencias_loc.fecha, 
        hora, ct_asistencias_loc.Status, 
        CONCAT(Nombre, ' ', Apellidos) nombre_completo 
        FROM ct_alumnos 
        INNER JOIN ct_asistencias_loc on ct_alumnos.idAlumno=ct_asistencias_loc.idAlumno 
        HAVING nombre_completo='".$nombre."'";

First and Last name with LIKE :

$nombre=$_POST['nombre'];

$sql="SELECT 
        Nombre, Apellidos, ct_asistencias_loc.fecha, 
        hora, ct_asistencias_loc.Status, 
        CONCAT(Nombre, ' ', Apellidos) nombre_completo 
        FROM ct_alumnos 
        INNER JOIN ct_asistencias_loc on ct_alumnos.idAlumno=ct_asistencias_loc.idAlumno 
        HAVING nombre_completo LIKE '%".$nombre."'%";

Notes:

On the consequences of using one or the other query this question: Use CONCAT or CONCAT_WS in MySQL?
You may need to use prepared queries to protect your SQL Injection code
When two tables are involved, it is convenient to put the name of the table in front of each column, to avoid possible names of ambiguous columns, in which case the code will fail.

answered by 11.10.2017 в 02:05

problem with slash when saving a route in mysql with php postgresql php connection problem with objects

score 0 · Accepted Answer

This way the question is asked both in the name field and in the surname field what comes by $_POST using LIKE

$sql = "SELECT Nombre,Apellidos, ct_asistencias_loc.fecha, hora, ct_asistencias_loc.Status 
    FROM ct_alumnos 
    INNER JOIN ct_asistencias_loc on ct_alumnos.idAlumno=ct_asistencias_loc.idAlumno 
    WHERE Nombre LIKE '%".$_POST['nombre']."%' OR Apellidos LIKE '%" . $_POST['nombre'] . "'%";

You can adjust the query and instead of using the OR use AND , it all depends on how you want the result