Select query in php to my database mysql does not return the record because it does not recognize the "&" character

Question

Select query in php to my database mysql does not return the record because it does not recognize the "&" character

Navigation

#1 by (0 votes)

0

I am making a query select my mysql database to bring the records of the users that I have registered. The query field for the select is the name and the id, in the field name of the table there are records that have characters like "&" or "ë" or "'", reason why you do not return the records that are in the table.

The query is done from an android app.

    <?php
header("Content-type: application/json; charset=utf-8");

//Invocar conexion a host de base de datos
require_once 'mysqlLogin.php';

//Variables de busqueda metodo GET
$id=$_GET["id"];
$empresa=$_GET["empresa"];

// Conectarse a y seleccionar una base de datos de MySQL llamada 
$mysqli = new mysqli($hostname, $username,$password, $database);

// comprobar si existe algún error
if ($mysqli->connect_errno) {
    echo "Error: Fallo al conectarse a MySQL debido a: \n";
    echo "Errno: " . $mysqli->connect_errno . "\n";
    echo "Error: " . $mysqli->connect_error . "\n";
    exit;
}

//enable utf8!
$mysqli -> query("SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'");


// Creamos la consulta SQL
$sql = 'SELECT id_empresa, paquete_empresa, categoria_empresa, nombre_empresa from directorio where id_empresa=? and nombre_empresa=?';


// La preparamos
$stmt = $mysqli->prepare($sql);
// bindeamos los datos
$stmt->bind_param('is', $id,$empresa );
// Ejecutamos la consulta
$stmt->execute();
// Recuperamos los datos
$stmt->bind_result($id, $paquete, $categoria, $nombre);

//Declarar arrelgo
$datos=array();

// comprobar si devolvio registros
if ($stmt){

    while ($stmt->fetch()) {
        //Crear arreglo temporal para agregar valores de las columnas
        $tmp = array();
        $tmp["id_empresa"] = $id;
        $tmp["paquete_empresa"] = $paquete;
        $tmp["categoria_empresa"] = $categoria;
        $tmp["nombre_empresa"] = $nombre;

        //Agregar a arreglo variable temporal
        array_push($datos, $tmp);

    }
    // Liberar resultados
    $stmt->close();

    //Convertir respuesta a Json
    echo json_encode($datos, JSON_UNESCAPED_UNICODE);


}
else{
    echo 'No se encontraron resultados';
    exit;
}


// Cerrar la conexión
$mysqli->close();

?>

Database: Configuration

mysql android php

asked by jvega 03.05.2018 в 17:58

source

1 answer

Mail from PHP arrives as spam Mute MediaPlayer 'ONLY' when a call comes in

score 0 · Answer 1

Vega, your code is not coherent in its logic, nor is it consistent with the headers.

If you indicate a type of header, it means that, whatever happens, you must return that type of data in the code (in this case a json ).

Regarding the charset con que solamente lo indiques en el header 'and in the object of connection to the database, it would be enough. I'm not sure, but I think that the command set you send to the database could be a problem.

In the following code I use a variable called $arrDatos , which will collect all the possible eventualities of the code and at the end it will print only that variable, like a json, with whatever it has.

That way the code is normalized, because you can not put header json and then do echo of a normal string, as you do more than once in your code.

Try this solution, which I hope will help you. I hope I have not made any typo, if so, you can detect it quickly.

I await your comment back when you've tried it :)

<?php
header("Content-type: application/json; charset=utf-8");

//Invocar conexion a host de base de datos
require_once 'mysqlLogin.php';

//Variables de busqueda metodo GET
$id=$_GET["id"];
$empresa=$_GET["empresa"];

// Conectarse a y seleccionar una base de datos de MySQL llamada
$mysqli = new mysqli($hostname, $username,$password, $database);

/*Array para recoger lo que ocurra en el código*/
$arrDatos=array();
// comprobar si existe algún error
if ($mysqli->connect_errno) {
    $arrDatos["error"]= "Error: Fallo al conectarse a MySQL debido a: ".PHP_EOL
        . $mysqli->connect_errno . PHP_EOL
        ." Error: " . $mysqli->connect_error . PHP_EOL;
} else {
    $mysqli->set_charset("utf8");
    $sql = 'SELECT id_empresa, paquete_empresa, categoria_empresa, nombre_empresa from directorio where id_empresa=? and nombre_empresa=?';
    // La preparamos
    $stmt = $mysqli->prepare($sql);

    if ($stmt){
        // bindeamos los datos
        $stmt->bind_param('is', $id,$empresa );
        // Ejecutamos la consulta
        $stmt->execute();
        // Recuperamos los datos
        $stmt->bind_result($id, $paquete, $categoria, $nombre);

        // comprobar si devolvio registros
        while ($stmt->fetch()) {
            $arrDatos["id_empresa"] = $id;
            $arrDatos["paquete_empresa"] = $paquete;
            $arrDatos["categoria_empresa"] = $categoria;
            $arrDatos["nombre_empresa"] = $nombre;
        }
        /*Operador ternario para poner un mensaje en el array si no hay registros, o dejarlo tal cual*/
        (!$arrDatos)?$arrDatos ["error"]= "Error, no se encontraron registros" : "";

        // Liberar resultados
        $stmt->close();

    }else{

        $arrDatos["error"]="Error preparando la consulta: ".$mysqli->error;
    }

    // Cerrar la conexión
    $mysqli->close();
}

//Convertir respuesta a Json
echo json_encode($arrDatos);

?>