Posts by Roberto de Campos • 5,345 points
235 posts
-
0
votes1
answer28
viewsA: Mysql - Select the most inserted records in a given period
You can use a INNER JOIN with that sub-query, something like that: SELECT a.id, a.assunto, a.dtCriacao FROM tabMsgs INNER JOIN ( SELECT assunto, count(*) AS total FROM tabMsgs WHERE dtCriacao >…
-
1
votes1
answer263
viewsA: How to Query between two php+mysql Dates?
To use the BETWEEN correctly it is necessary to have dates for comparison, if there is text the comparison will not work even, in this case. For this we have two options, change the field type to…
-
1
votes1
answer125
viewsA: Hello, I have a database - mysql and would like to decrease a value in an entire column
There are several ways to solve this, one of them is using the function SUBDATE() It serves to decrease any kind of information from a field DATETIME. Example: UPDATE sua_tabela SET hora =…
-
0
votes2
answers78
viewsA: Did I use the if and Else wrong?
You are doing independent check of the amount of residents, need to do this check before doing the income check: if (A == 1){ if(E[0] > S[1]) printf("Sua renda é superior a 3 salarios minimos!");…
canswered Roberto de Campos 5,345 -
0
votes1
answer21
viewsA: Find records between a range of seconds
You reversed the ADDTIME and SUBTIME in the BETWEEN, in the way that you are searching for all the records of 2019-04-04 10:45:10 until 2019-04-04 10:44:50. That’s why your query is not returning…
mysqlanswered Roberto de Campos 5,345 -
1
votes1
answer40
viewsA: How to rollback the transaction using?
You can put a block try catch and within the catch call the method RollBack of SQLTransaction: using (SqlConnection cnn = Geral.conexaoSql()) using (SqlTransaction tran = cnn.BeginTransaction()) {…
c#answered Roberto de Campos 5,345 -
5
votes2
answers602
viewsA: Return Zero in Function Mysql if query is NULL
As your problem lies in the return of your SELECT, you will have to declare a variable in the function to store the result of the SELECT and before returning the variable, check if it is NULL with…
mysqlanswered Roberto de Campos 5,345 -
2
votes1
answer1711
viewsA: FDQUERY at runtime
This problem most likely occurs because your FDInsertForn not being instantiated. Just adding the instantiation your problem will be solved: FDInsertForn := TFDQuery.Create(nil); But in this code…
-
1
votes2
answers105
viewsA: Sum values of the same code
This would be a grouping of records as well as others SGBD Mysql has a clause for just that, which is the GROUP BY. With it you can group the data by column seqproduto, would look something like…
-
2
votes1
answer58
viewsA: Repeat a number of times in an interval of days
In these cases the ideal is to do with while, not that it is not possible to do with for, but the for is usually used with integers. <?php $data = DateTime::createFromFormat('d/m/Y',…
-
2
votes1
answer421
viewsA: Delphi Mdichild flashing form when opening in modal mode
Set the property Visible and FormStyle of the form as False and fsNormal respectively by default and create a constructor method that takes a parameter stating whether the form will be modal or not,…
-
1
votes2
answers683
viewsA: Is it possible to use Inner Join in several columns of the same table in a Select?
In the clause ON of JOIN you can use the operator IN and compare with all columns containing the id employee. As you want to know the total time per sector, it is also necessary to add a GROUP BY by…
-
4
votes1
answer68
viewsA: Adding data from the same column
This is necessary to do in programming, but if you really need to do it in Mysql, an output would be to mount a procedure that calculates and returns to you. I’ve put together a very simple one that…
-
7
votes3
answers123
viewsA: Format value for date
You can use the function createFromFormat of the object DateTime: <?php echo DateTime::createFromFormat('Ymd', '20181107')->format('Y-m-d'); ?> This way you can define the input format, and…
-
2
votes1
answer275
viewsA: How to get total sales by months with mysql
Just group by year and by month: SELECT CONCAT(LPAD(MONTH(data_hora), 2, '0'), '/', YEAR(data_hora)) mes, COUNT(id) qtde FROM nome_da_sua_tabela GROUP BY YEAR(data_hora), MONTH(data_hora) Function…
mysqlanswered Roberto de Campos 5,345 -
0
votes2
answers454
viewsQ: Submit being called twice
I have a form where I need to intercept the event submit, cancel it and via Ajax, perform the required request. However, even using the preventDefault(); and return false; the submit is being…
-
1
votes1
answer1390
viewsA: Delphi Traversing An XML Nodes (DOM)
Apparently the property length of IXMLDOMNodeList not working properly. The solution then was to use the function nextNode until she returns nil. After getting the node just sweep all the child…
-
1
votes1
answer245
viewsA: radio button selected according to BD on foreach
The problem is that the variable $check not being cleaned before checking. That way all items after what should actually be selected will also be with checked="checked". Like property name is equal,…
-
3
votes2
answers64
viewsA: Delphi Two methods for Onmessage
In Delphi it is not possible to assign more than one method to the same event. One way to circumvent this deficiency is to make a MetodoAB() and reference it at the event: procedure MetodoAB();…
-
3
votes3
answers881
viewsA: How to calculate difference of dates that are a previous row with MYSQL?
You can make a SUB-SELECT taking all records with dates less than the current, sort by the date in descending order and limit to 1, if actually all records have the sequential date, you will always…
-
4
votes2
answers50
viewsA: Travel consultation
You can select all packaged products and make one LEFT JOIN with the same table, only this time looking for the line with the same Produto and with the movement despachado. In the WHERE Insert to…
mysqlanswered Roberto de Campos 5,345 -
0
votes1
answer44
viewsA: Bring data from range
You need to list the dates on a sub-select and do the LEFT JOIN to list all: SELECT dados.id, dados.valor, datas.data FROM ( SELECT '2018-10-01' AS data UNION SELECT '2018-10-02' AS data UNION…
-
1
votes2
answers4117
viewsA: SQL how do I enter into a table that has FK?
Like id_endereco is a foreign key, it needs the reference to exist. So to insert a person with the id_endereco = 10, it is necessary that in the table endereco there is a record with the id = 10. To…
-
5
votes1
answer1428
viewsA: How to quote a variable for Mysql query
Whenever we’re riding a string and within that string it is necessary to have another string, we have some exits: As in the SQL so much ' as " begin and end a string, we can interlink these…
-
2
votes2
answers426
viewsA: SQL command in String Mysql
To execute a dynamic command within a function, procedure or in trigger it is necessary to prepare the string and then execute her. For example, to create a precedent that sums two numbers and…
-
1
votes2
answers52
viewsA: SQL Join
You were almost there, the comparison has to be codcid table funcionario with codcid table cidade. select funcionario.matfunc, funcionario.nome, cidade.nome from funcionario, cidade where…
sqlanswered Roberto de Campos 5,345 -
4
votes2
answers62
viewsA: Name not printing
You have two problems with your code: 1) To call a function JavaScript have to put the () and preferably place the ; in the end too. 2) You are taking the value of input id1 out of the function,…
-
2
votes2
answers1538
viewsA: Return 3 months previous from day 1
Use the function GetDate() to return the current date and from the date returned remove 3 months with the function DATEADD: SELECT Z.ID_CONTA, W.ID_TRANSACAO, Y.DS_TIPO_TRANSACAO, W.DT_ORIGEM,…
-
1
votes1
answer111
viewsA: Why the "quotes" in the INT data?
First of all, this is not exclusive to Workbench. I used other tools and the SqlYog for example, does exactly the same thing. This is to optimize the generation of query. Like the MySql does not…
-
0
votes1
answer1791
viewsA: How to remove duplicate records in Mysql with WHERE condition
You can make a INNER JOIN with the same table to find duplicates. In your case it will be considered duplicated if there is another record with the same parent_id, the same post_code and a entity_id…
-
0
votes2
answers919
viewsA: Exchanging column values in Mysql
A solution would be to combine the two values into one and work with the CONCAT, SUBSTR and LENGTH. UPDATE sua_tabela SET X = CONCAT(X, Y), Y = SUBSTR(X, 1, LENGTH(X) - LENGTH(Y)), X = SUBSTR(X,…
-
0
votes1
answer550
viewsA: Store image(jpg) in a variable - Delphi
The class TPicture has the procedure LoadFromFile to load an image, to use it would be more or less like this: function CarregarImagem(caminhoImagem: String): TPicture; var picture: TPicture; begin…
delphianswered Roberto de Campos 5,345 -
1
votes2
answers400
viewsA: Add to MYSQL query in varchar field by deleting Strings
It is necessary to chain the functions, leaving the one that must be executed first inside the one that must be executed last, in your case would be more or less like this: SELECT…
-
3
votes2
answers520
viewsA: How to find all '>' characters of a memo and store in an array using Delphi?
To get the position of all characters > in string and store in a array, would be so: var posicao: Integer; posicoes: Array of Integer; texto: String; begin while (Pos('>', texto) > 0) do //…
-
5
votes2
answers66
viewsA: Search two numbers at once
You making a INNER JOIN searching for the idtransactions and the number sought in idsubstatus will force the SELECT to return only the items that have the two numbers: SELECT a.idtransactions FROM…
mysqlanswered Roberto de Campos 5,345 -
2
votes1
answer517
viewsA: Is there a command to list all foreign keys of a MYSQL table?
For each instance of MySql there is a database called information_schema. This database contains all the information of all the instance databases. To recover all foreign keys, just make a SELECT on…
mysqlanswered Roberto de Campos 5,345 -
2
votes1
answer66
viewsA: PDO does not return query results
The assembly of your WHERE is incorrect, the sign ?(query), will be replaced by the parameter passed, then the WHERE should be assembled as follows: switch($opcao_filtro) { case 'titulo': { $sql .=…
-
2
votes3
answers41
viewsA: Do not show element that has a certain value
You’ll have to do with sub-select: SELECT usuario.id AS usuarioId FROM usuario LEFT JOIN usuario_gerenciamento ON usuario.id = usuario_gerenciamento.usuario_id WHERE NOT EXISTS ( SELECT * FROM…
mysqlanswered Roberto de Campos 5,345 -
3
votes2
answers823
viewsA: How to read comments from Mysql table fields?
There is a database called information_schema, that database is responsible for storing the structures of all databases. To get the comment from a column you can select the table COLUMNS: SELECT…
-
2
votes1
answer173
viewsA: PHP MYSQL Query Bring Results from Today and This Week
You can add a column to your SQL indicating this from some date checks with IF, follows an example: SELECT IF( DATE(datacompromisso) = DATE(NOW()), 'hoje', IF( YEAR(datacompromisso) = YEAR(NOW())…
-
2
votes1
answer418
viewsA: remove repeated data in mysql
You can make a DELETE with INNER JOIN in the same table, however in INNER JOIN you will bring only the records where the cod is less than in the first table. DELETE b FROM cadastro a INNER JOIN…
mysqlanswered Roberto de Campos 5,345 -
3
votes1
answer434
viewsA: Use the values of a variable array of a procedure in the main algorithm routine
When you create a variable within a method/function/procedure, this variable can only be accessed within the scope itself, so you cannot access the function 1 variable by being in function 2 unless…
-
1
votes3
answers1538
viewsA: Count Number of Letters in a String - Delphi
There are many unnecessary variables in your code when you declare a function is required to determine the type of return (in your case is Integer). The Delphi automatically creates a variable in…
delphianswered Roberto de Campos 5,345 -
0
votes2
answers115
viewsA: Replace with variable - mysql
I’ve never worked with variables this way, which I usually do when I need to treat the information on SELECT more than once it is, duplicate the code, in your case it would look like this: SELECT…
-
1
votes1
answer85
viewsA: Valid working hours
You can use the function [IF()][1] so that when the time is less than @HoraEntrada you switch to own @HoraEntrada and if it’s bigger than @HoraSaida also assume the @HoraSaida, would look something…
-
1
votes2
answers426
viewsA: Problem creating table in Mysql
No size set for fields of type double: CREATE TABLE `narguile`.`Aluguel` ( `idAluguel` INT(255) NOT NULL, `produto` VARCHAR(255) NOT NULL, `qntdeProduto` INT(255) NOT NULL, `desconto` DOUBLE NOT…
-
2
votes2
answers47
viewsA: More than one value in the same GET parameter
It is possible yes, just put [] after the parameter name, it will automatically mount the array, the parameters would look like this: ?fields[]=fieldname[]=data&fields[]=address By making a…
-
1
votes3
answers200
viewsA: Select bringing maximum Columns with Writing - MYSQL
Make a IF, if something is written add 1 but add 0. This IF will have to be done for each column, example: SELECT a.`REFERENCIA`, a.`DESCRICAO`, ( IF(a.`POSTO1` <> '', 1, 0) + IF(a.`POSTO2`…
-
1
votes3
answers887
viewsA: Create DELPHI Component at Runtime
To create component at runtime, I do it as follows: var DWResponseTranslator1: TDWResponseTranslator; begin DWResponseTranslator1 := TDWResponseTranslator.Create({Aqui é necessário colocar os…
delphianswered Roberto de Campos 5,345 -
4
votes5
answers1540
viewsA: Check that all $_POST was shipped without using too many ifs
You can do it with a foreach, would look something like this: foreach ($_POST as $key => $value){ if (empty($value)){ echo "<p>Campo ".$key." em branco</p>"; exit; } } $nome =…