Query with many fields and records in MySQL

Question

Query with many fields and records in MySQL

Navigation

#1 by (0 votes)

0

Good afternoon forum STACKOVERFLOW , I hope you can support me with a MySQL query

I have a table of 72 columns and 64 records. The table so requires my boss for questions of standardization.

The base has only in the registers the number 1 to identify if the field is registered (the base is of services and each column with the "1" shows if it has the permission of the column mentioned), some fields are in < em> NULL to identify that they do not have permission from the registry.

I will create a web interface that helps me, using CHECKBOX of the columns and thus return the records that have an approximate as selected in the web interface.

The intention of my question is to be able to make a query that returns me several results of the same query similar or similar. Help me please.

Next I place a screen print of the database:

Each column is a permission, if the permission has registered a 1 is because that record has access to the service. That is, the query should find what records have the services with 1 in the columns.

An example is that my base has these characteristics:

id nombre servicio1 servicio2 servicio3 servicio4 servicio5

1  super     1         1          1         1        1 

2  admin     null      1          null      1        1

3  admin2    null      null       1         null     null

In the interface are the services with a checkbox and depending on which you select the query should return the most similar to the services you have selected and some extra record that is similar.

mysql jquery select

asked by Harrison Olvera 20.06.2018 в 19:10

source

1 answer

Range of dates between another range of dates Validate the record in bdd

score 0 · Accepted Answer

what you are looking for is a query code to mysql in php, very common in the community I will put it below for your case

 <?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "basedatos";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT * FROM tutabla";
$result = $conn->query($sql);
$posicion=0;
if ($result->num_rows > 0) { //si existen datos
    // recorres fila a fila con el while y la funcion fetch_assoc
    while($row = $result->fetch_assoc()) {
        //con el foreach recorres todos los campos almacenados en esa fila
        foreach ($row as $valor) {
             if($valor==1)
             {echo $valor; //aqui estas recorriendo todos los valores de tu tabla y mostrandolos si son iguales a uno, otra pregunta seria si son iguales a null o algo parecido, ya depende de tu desarrollo como programador.
              }
        }
    }
} else {
    echo "0 results";
}
$conn->close();
?>

is the way you should do it, it's a question that you read it, analyze it and adapt it to your needs