Antonio Ruiz's questions

I have a table where the values ​​can be repeated in each year (or not), I need to collect only the references of the last year with their values ​​in the event that they are repeated in different periods.

I want to get this unique filtering taking into account the closest year:

How could I do it?

Here is my last test, but it keeps thinking for more than 3 minutes and finally I cancel it, I need to do it in a few seconds.

SELECT df.* FROM datosfase df,
(
SELECT MAX(PERIODO) AS PERIODO, REFERENCIA
FROM datosfase 
GROUP BY REFERENCIA
ORDER BY PERIODO DESC
) t1
WHERE df.PERIODO = t1.PERIODO AND df.REFERENCIA= t1.REFERENCIA

I have a table with records of entries and exits by user, I need to obtain the last signing of each user.

This is my query, but it returns repeated USER_IDs and I only want the latest most recent.

SELECT c.ID, c.ID_USUARIO, c.FECHA, c.TIPO
FROM acceso c
INNER JOIN 
(
  SELECT MAX(FECHA) max_time
  FROM acceso
  GROUP BY Date(`FECHA`)
) AS t
ON c.FECHA = t.max_time

My 'access' table:

ID   ID_USUARIO   FECHA                  TIPO
--   ----------   -------------------    ------
1    010          2020-07-05 15:20:01    ENTRADA
2    010          2020-07-05 18:25:00    SALIDA
3    066          2020-07-05 10:00:00    SALIDA
4    066          2020-07-05 12:00:00    ENTRADA
5    066          2020-07-05 18:00:00    SALIDA
6    011          2020-07-05 13:20:01    ENTRADA
7    011          2020-07-06 10:25:00    SALIDA
7    011          2020-07-06 11:25:00    ENTRADA

I want to get:

ID   ID_USUARIO   FECHA                  TIPO
--   ----------   -------------------    ------
2    010          2020-07-05 18:25:00    SALIDA
5    066          2020-07-05 18:00:00    SALIDA
7    011          2020-07-06 11:25:00    ENTRADA

SQLFiddle: http://sqlfiddle.com/#!9/414960/2

I have the following table saved in a variable

var table = '<table>
   <tr><td>Test1</td><td>Test2</td></tr>
   <tr><td>Test3</td><td>Test4</td></tr>
</table>';

Is there a possibility of doing an iteration for every tr and saving the td with JS?

From this foreach I get different lines (I have commented how I tried, but nothing):

respuesta2.forEach( function(valor2){                              
  //resultados_f2[valor2.ronda].push([parseInt(valor2.victorias_1), parseInt(valor2.victorias_2)]);
  console.log("-"+valor2.ronda+"-> "+parseInt(valor2.victorias_1)+" "+parseInt(valor2.victorias_2));
                              
});

The result of the previous console is the following:

-2-> 1 0
-1-> 1 2
-1-> 2 1

The array I want to get is the following:

var resultados_f2 = [ 
    [[2,1], [1,2]],
    [[1,2]] 
];

How could I do it? I want all the lines that have the same valor2.rondato be in an array within the array resultados_f2.

I've only been getting started with Android Studio + kotlin for a short time and I'm still mixing some concepts. I am trying to do the following:

1. I run a function called run_service () where I get an array from a json response. (This I have achieved)

2. I'm trying to pass this array to my MyCustomAdapter , which generates a listView but I can't pass the data.

   class HomeFragment : Fragment() {
   //Este es un array que he tratado de hacer global y donde guardo la consulta web
   var nombres_establecimientos = arrayListOf<String>()
   
   override fun onCreateView( inflater: LayoutInflater, container: ViewGroup?,  savedInstanceState: Bundle? ): View? {
       //Ejecuto el servicio que hace la llamada json
       ejecutar_servicio()
       val listView = root.findViewById<ListView>(R.id.listView)
       //Aqui he pensando en pasar el array que en la otra función se rellenó, a la clase, pero no lo consigo.
       listView.adapter =
           getActivity()?.let { MyCustomAdapter(it, nombres_establecimientos) } // this needs to be my custom adapter telling my list what to render
   
       // Inflate the layout for this fragment
       return root
   }
   
   
   
   private class MyCustomAdapter(context: Context, arr: ArrayList<String>): BaseAdapter() {
       private val nombres_establecimientos = arr
       ....
       override fun getView(position: Int, convertView: View?, viewGroup: ViewGroup?): View {
   
       val layoutInflater = LayoutInflater.from(mContext)
       val rowMain = layoutInflater.inflate(R.layout.row, viewGroup, false)
       println(">Nombres_establecimientos3: ${nombres_establecimientos}")
   
   
       val nameTextView = rowMain.findViewById<TextView>(R.id.textView1)
       nameTextView.text = nombres_establecimientos.get(position)
   
       val descriptionTextView = rowMain.findViewById<TextView>(R.id.textView2)
       descriptionTextView.text = nombres_establecimientos.get(position)
   
       return rowMain
   }
   
   class Establecimiento( var id: Int, var nombre: String, var direccion: String ) {
       override fun toString(): String {
           //return "Category [id: ${this.id}, nombre: ${this.nombre}, direccion: ${this.direccion}]"
           return this.nombre
       }
   }
   }
   
   //Esta es la función donde recibo los datos de la consulta a la api json, hay varios println donde confirmo que si recibo los datos.
   private fun ejecutar_servicio(): Array<MyCustomAdapter.Establecimiento?> {
   
   val url = "url/api/respuesta_json"
   val request = Request.Builder().url(url).build()
   
   val client = OkHttpClient()
   client.newCall(request).enqueue(object: Callback {
       override fun onResponse(call: Call?, response: Response?) {
           val body = response?.body()?.string()
           println(body)
   
           val gson = Gson()
           val arrayEstablecimientoType = object : TypeToken<Array<MyCustomAdapter.Establecimiento>>() {}.type
   
           var resultados: Array<MyCustomAdapter.Establecimiento> = gson.fromJson(body, arrayEstablecimientoType)
           resultados.forEachIndexed  { idx, item ->
               println("> Item ${idx}: ${item}")
               nombres_establecimientos.add(item.toString())
           }
           println(">Nombres_establecimientos: ${nombres_establecimientos}")
   
   
       }
   
       override fun onFailure(call: Call?, e: IOException?) {
           println("Failed to execute request")
       }
   })
   
   return emptyArray()
   }
   

What am I doing wrong?

I am working with the following function to search for a term entered in a search engine.

I search for a value within an array, at a specific position in the array to check if it exists.

/***** BUSCAR EN EL ARRAY PRODUCTOS POR VALOR Y POSICION ****/
function buscar(mi_array, valor, pos){
    count = 0;
    var results = mi_array.filter(function (mi_array) { return (mi_array[pos].toUpperCase().startsWith(valor.toUpperCase())) && count++ < 36; }); 
    var firstObj = (results.length > 0) ? results[0] : null;
    return results;

}

My problem comes when the user looks for a product with a tilde and in the array where he looks for the function it is without a tilde and vice versa.

How could I deal with this problem?

I want when the user types:

url.com/armchairs/horseshoe-armchair/

The content of:

url.com/productos/armchairs/horseshoe-armchair/

sillon-herradurais an example, it could be any product.

How could I do it?

I have done the following but it doesn't work:

RewriteEngine On
RewriteRule ^productos/sillones/(.*)$ https://url.com/sillones/$1 [R=301,L]

I update if it helps I use CodeIgniter.

I want to validate that at least one checkbox of a group is selected, but I don't know their ids or names since they are dynamic, they change. What if is the name or id of the div that encompasses them. I can do it?

<div class="group-check1">
<input type="checkbox"> Opc 1
<input type="checkbox"> Opc 2
<input type="checkbox"> Opc 3
</div>

I have two groups of checkboxes, independent, in both there must be at least 1 check selected.

I have a function that correctly searches within an array and returns the match, but it is case sensitive. I need the comparison to be done at both ends in lowercase so it doesn't distinguish.

How can I do it?

This is my current code:

function buscar(mi_array, valor, pos){
    var results = mi_array.filter(function (mi_array) { return mi_array[pos].startsWith(valor); });
    var firstObj = (results.length > 0) ? results[0] : null;
    return results;
}

This is the code, I want that when clicking on Do you want to book? the "calendario_reservation" div is displayed, but something is wrong:

activar_reserva();

function activar_reserva(){
    if(document.getElementById('activar_reserva').checked){ 
        document.getElementById('calendario_reserva').style.display = "unset"; 
    }else{ 
        document.getElementById('calendario_reserva').style.display = "none"; 
    }
}

<form action="#" method="post">
  <input type="checkbox" id="activar_reserva" onclick="activar_reserva();">
  <label>¿Quieres reservar?</label>

  <div class="" id="calendario_reserva">
  AQUI UN CALENDARIO PARA SELECCIONAR LA FECHA.
  </div>

  <br/>
  <button type="submit" name="action" value="contestar" class="form-control fondo2 color1" style="margin-top:10px;">Enviar</button>

</form>

https://jsfiddle.net/ogdbt09v/

With this loop I am checking if the destination page returns a 1 or nothing (blank page), but it always enters the two IFs , I don't understand it. It's wrong?

I have tested the destination page (const url) in the browser and if I am logged in it gives me 1, but otherwise it stays blank.

var i = 1;
var response = 0;

function myLoop () { 
   setTimeout(function () { 
                const Http = new XMLHttpRequest();
                const url='url/get_session'; //Devuelve 1 o nada (página en blanco)
                Http.open("GET", url);
                Http.send();

                Http.onreadystatechange = (e) => {
                    response = Http.responseText.replace(' ','');
                    if(response == '1'){ console.log('In: '+response); }
                    if(response != '1'){ console.log('Out: '+response);
                        //location.reload();
                    }
                }
      i++;
      if (i < 10000000) {  myLoop(); } 
   }, 5000)
}

myLoop();

Below I make an explanation of what I do and what I need, now I leave an approach of the target table and the query that I use.

Target table (fact_accounting_lines):

id_producto    | unds   | compra_venta
10517931       | 2      | Compra 
10517929       | 4      | Venta 
10517929       | 2      | Venta
10517939       | 6      | Compra 
10517931       | 3      | Venta 

This is my query:

SELECT 
fact_contabilidad_lineas.id_producto, 
SUM(fact_contabilidad_lineas.unds) as unds,
fact_contabilidad.compra_venta

FROM fact_contabilidad, fact_contabilidad_lineas

WHERE fact_contabilidad_lineas.serie = concat_ws('-', fact_contabilidad.id_serie, fact_contabilidad.serie_num)
AND fact_contabilidad_lineas.iduser = 98
AND fact_contabilidad.iduser = 98
AND fact_contabilidad.compra_venta = 'Venta'
AND fact_contabilidad_lineas.id_producto > 10517927

GROUP BY fact_contabilidad_lineas.id_producto

Result:

id_producto    | unds   | compra_venta
10517929       | 6      | Venta 
10517931       | 3      | Venta 

I have not shown the table (fact_accounting) because it is not necessary, it does not influence my question and in this way the question is somewhat cleaner. The query works fine but it only gets me the lines that are buy_sell = Sell.

What I need to get is:

1 column the sum of the products grouped by product_id and buy_sell = Sale .

1 column the sum of the products grouped by product_id and buy_sell = Buy .

Expected result:

id_producto    | Venta | Compra
10517929       | 6     | 6
10517931       | 3     | 2

Can you guide me? I can't think of how to do it.

The accumulated VAT per line that each product (table_products) has in the table_order (add all 4%, all 10% and all 21% per line) will not be taken out.

• Table_products

  id    | nombre    | iva | precio
  1     | Producto1 | 4   | 20 
  2     | Producto2 | 10  | 50 
  

• order_lines table

  id    | pedido  | id_producto | unidades  | total
  1     | 100     | 1           | 1         | 20
  2     | 100     | 2           | 1         | 50
  3     | 101     | 1           | 1         | 20
  

I want to get the following result broken down by VAT type:

pedido  | iva4 | iva10 | iva21 | total
100     | 0.16 | 5     | 0     | 70
101     | 0.16 | 0     | 0     | 20

The total is the sum of the items in the order_table, this is if you save it. What is not taken is the accumulated VAT per line that each product in the order_table has (add all 4%, all 10% and all 21% per line).

Is there any way to know through PHP the origin of the visit?

I would like to hide the registration button and show a message if the visitor is not from Spain, since the application would not work.

Something like:

if($pais != "ES"){
      echo "<p>No ofrecemos servicio para tu pais.</p>";
else{
      echo "<button type='submit'>Enviar</button>";
}

This code is getting through to me.

<?php   echo '<pre>';
        system('tail -f -n 5 /var/log/syslog');
        echo '</pre>'; 
?>

Other commands I can run fine but this one doesn't work for me.

Is it possible with system to open the syslog file and display it?

INSERT INTO contactos
       (iduser, tipo, codigo, nombre, dni_nif )
VALUES
       ('1','cliente','1','Juan','500000'),
       ('1','cliente','2','Maria','500001'),
       ('2','cliente','1','Sara','500002')
ON DUPLICATE KEY UPDATE 
       codigo = codigo,
       iduser = iduser;

In MySQL : iduser+codigo(unique key)

iduser = code assigned to the client

code = key of the contact of each client

I have the previous query, the first time it is executed it works fine, but if the client decides to re-upload the data ( excel ) to update the existing ones and/or add new clients, I can't get it to update the existing ones.

Where is the problem?

I'm using Codeigniter's profiler function and I'd like to save that information to an array and be able to display it.

$this->output->enable_profiler(TRUE);

Currently it shows me the information without layout and I can't control it, at most I can hide some elements:

$sections = array(
         'config'  => FALSE,
         'benchmarks'  => FALSE,
         'config'  => FALSE ...
}

I am very interested in obtaining the total execution time of all the queries that are executed in each section of my website and displaying it.

Something like this I want to get:

Página cargada en 0.003 segundos.

Is it possible to do this?

I try to do the following:

  SELECT a.id_serie, a.serie_num, IFNULL(d.total,0)

  FROM ( SUBCONSULTA ) d

  LEFT
  JOIN fact_contabilidad a
    ON a.id_serie = d.id_serie
    AND a.serie_num = d.serie_num

• The subquery is a very, very extensive query of several nested selects where there are many tables involved and I do not show it to make it clearer, I leave an extract of the subquery.

id_serie | serie_num | nombre   | total
2508     | 1         | Cliente1 | 20€
2508     | 2         | Cliente1 | 10€
2508     | 4         | Cliente2 | 20€

Now, I want to do a left join between that previous query and a table where all the series are found, like the following:

id_serie | serie_num | nombre  
2508     | 1         | Cliente1         
2508     | 2         | Cliente1      
2508     | 3         | Cliente2
2508     | 4         | Cliente2       

• The series_num = 3 does not appear in the above.

And the result should be the following:

id_serie | serie_num | nombre   | total
2508     | 1         | Cliente1 | 20€
2508     | 2         | Cliente1 | 10€
2508     | 3         | Cliente1 | 0€
2508     | 4         | Cliente2 | 20€

I want to list all the series and if there is no line in the subquery, mark it with €0.

It's possible? I'm trying but I don't know where I went wrong.

I am reading an excel file from PHP, I am collecting the fields that interest me and saving the values ​​in a variable to later build the update.

foreach ($rowData as $key => $value) {
  blah blah...
  $sql2 .="(pcompra='".$pcompra."', margen='".$margen."')  WHERE iduser=45 AND 
  referencia='".$referencia."', ";
}

So far so good, the problem is building the query, because I know that the above is wrong and I don't know how to build it so that the bulk update works correctly.

This is how I have inserted the 6000 records in a single query, but I don't know how to do the UPDATE.

My table has a series of fields, but among them I have:

• ID (unique) <- but I don't know it in the update.
• iduser
• reference <- is unique depending on the iduser
• pbuy2 <- I want to update it
• margin <- I want to update it

Now I have 6000 products to update. How could I update the 'pcompra2' and 'margin' fields in the 6000 products according to iduser and reference in a single sentence ?

I try something like this:

UPDATE stocks 
SET 
      (pcompra='5', margen='0.2')  WHERE iduser=45 AND referencia='00257', 
      (pcompra='6', margen='0.1')  WHERE iduser=45 AND referencia='00258', 
      (pcompra='2', margen='0.2')  WHERE iduser=45 AND referencia='00259', 
      .... hasta 6000.

Something very strange happens to me that I don't see any sense in it.

I have a for loop in javascript that always skips iteration 4. The code is very long, I leave you only the fragment where I detect the error.

I add a bit of info: I have a previous function that generates inputs in this way:

<input type="hidden" name="ref_X" value="2" id="ref_X">
<input type="hidden" name="unidades_X" id="uni2_X" value="1">
<input type="hidden" name="precio_X" value="3" id="pre2_X">
<input type="hidden" name="id_X" id="id_X" value="9299984">
<input type="hidden" name="nombre_X" id="nombre_X" value="Prueba 2">
<input type="hidden" name="iva_X" id="iva_X" value="21">

Where X is a counter (every time I generate the above block, the counter is incremented).

After creating each block I pass to the for below which iterates through all the input blocks to save them and update other data.

The case is that block 4 contains all the info and does not read the input id="iva_4":

<input type="hidden" name="iva_4" id="iva_4" value="21">

but if the rest of the same block 4.

Block for:

    for(var a=1; a<=contador; a++){
            unidades = document.getElementById('uni2_'+a).value; 
            precio = document.getElementById('pre2_'+a).value; 
            total = parseFloat(total) + (parseFloat(unidades)*parseFloat(precio));
            nombre = document.getElementById('nombre_'+a).value; 
            iva = document.getElementById('iva_'+a).value;

            alert(a+"->"+document.getElementById('iva_'+a).value);//Da undefined
            alert(a+"->"+document.getElementById('nombre_'+a).value);//Si lo muestra
}

And what is really there at that moment is:

<input type="hidden" name="ref_4" value="2" id="ref_4">
<input type="hidden" name="unidades_4" id="uni2_4" value="1">
<input type="hidden" name="precio_4" value="3" id="pre2_4">
<input type="hidden" name="id_4" id="id_4" value="9299984">
<input type="hidden" name="nombre_4" id="nombre_4" value="Prueba 2">
<input type="hidden" name="iva_4" id="iva_4" value="21">

Makes sense? The rest of the interactions if you do them correctly.

PS: Solved, I had another one with the id="iva_4"...!!