Posts by Roberto de Campos • 5,345 points
235 posts
-
4
votes2
answers66
viewsA: I’m having problems with Mysql, I don’t know how to get media
To calculate the mean no MySQL we use the function AVG. This way you filter the payment method by the code, which in case is 6: SELECT a.`Tipo`, AVG(b.`ValorPagar`) AS media FROM FormaPagamento a…
mysqlanswered Roberto de Campos 5,345 -
2
votes2
answers16
viewsA: How to select most used foreign key?
You have to group by idTema, sort by the quantity decreasing and limit in 1 record: SELECT a.`idTema`, COUNT(*) qtde FROM nome_da_tabela a GROUP BY a.`idTema` ORDER BY qtde DESC LIMIT 1; See more…
-
0
votes2
answers214
viewsA: Knowing when my list is coming with values in sequence
You can use the following function: public bool hasNumberSequence(List<int> numbers) { numbers.Sort(); int numerosSequenciais = 0; for (int i = 1; i < numbers.Count(); i++) { if (numbers[i]…
-
2
votes2
answers46
viewsA: Go to next statement - C#
You can use the return; if it is a return function void, for example: public void NomeDaFuncao() { if (lanceSelecionado.ID_LANCE == null) { await DisplayAlert("Comunicado", "Selecione um lance para…
c#answered Roberto de Campos 5,345 -
1
votes1
answer27
viewsA: Problem to list database data
The ORDER this spelled wrong, change to: $sqlBusca = 'SELECT * FROM usuario ORDER BY pontos;'; You can also improve your function by changing to: function buscar_rank($conexao) { $sqlBusca = 'SELECT…
-
3
votes1
answer2032
viewsA: Delete in 2 tables at the same time in a single query
Use the DELETE with JOIN, in your case would look like this: $sql = " DELETE emails, emailsacompanhamento FROM `emails` LEFT JOIN `emailsacompanhamento ` ON `emails`.`idEmail` =…
-
3
votes2
answers103
viewsA: How can I select a range of items, after some values using SQL IN operator
In that case you have to use the BETWEEN: SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value100; See more about the BETWEEN here.…
-
0
votes2
answers98
viewsA: Select data from three SQL tables
To group the results we use the GROUP BY with the field(s) (s) that will be grouped, in your case, would be the field cod_nota: SELECT cod_nota, cupom, valor_contrato, valor_cadastro, data_emissao…
-
1
votes4
answers1474
viewsA: Remove text within column in MYSQL
Use the function Replace: UPDATE PRODUTOS SET OBS = REPLACE(OBS, "<p><a target=\"_blank\" href=\"/uploads/docs/58e7a4fd05ca6.pdf \"><img alt=\"\" style=\"width: 175px; height: 45px;\"…
mysqlanswered Roberto de Campos 5,345 -
2
votes1
answer107
viewsA: Mysql Process Error
The end of WHILE is determined by the END WHILE; and not only by END; DELIMITER $$ CREATE PROCEDURE SP_INS_EXT_ON_OFF_01() BEGIN DECLARE ramal VARCHAR(30); DECLARE STATUS VARCHAR(30); DECLARE…
-
0
votes2
answers6988
viewsA: Query with SELECT and SUB SELECT in Mysql
In your case it would look something like this: SELECT CLI.id, CLI.nome, CLI.senha, CLI.email, CLI.cpf, CLI.celular, CLI.data_nasc, CLI.genero, CLI.data_cadastro, CLI.status, CLI.id_socket,…
-
2
votes1
answer281
viewsA: SELECT Mysql - Prioritizing results that are not null
You can do it on ORDER BY: SELECT ID, Col-A, Col-B, Col-C, Col-D FROM tabela ORDER BY (IF(Col-A IS NULL, 0, 1) + IF(Col-B IS NULL, 0, 1) + IF(Col-C IS NULL, 0, 1) + IF(Col-D IS NULL, 0, 1)) DESC…
-
0
votes2
answers690
viewsA: SQL - group by, Count
For this, you will need to use a sub-query: SELECT TIPO.NOME_TIPO_MERCADORIA AS TIPO, b1.ID_VEICULO AS IDVeiculo, b1.MATRICULA AS matricula, b1.QTDE_VIAGENS AS Viagens FROM TIPO a INNER JOIN (…
-
0
votes3
answers402
viewsA: How to do an operation on a Trigger? MYSQL
You don’t need to make one UPDATE to change the record itself. That one trigger will be executed every time a new record is inserted, the variable NEW will contain the data of this new record, so to…
-
-1
votes1
answer58
viewsA: Database does not accept single quote ' when sending
You can use the function addslashes to get around this problem: <?php require_once("../config.php"); $nome = $_POST['nome']; $tipo = $_POST['tipo']; $data = $_POST['data']; /*Inserar na tabela */…
-
2
votes1
answer51
viewsA: query using Case when
You don’t have to make a query different, the basic already solves: SELECT a.conta, SUM(IF(a.tipo = 'ENTRADA', a.credito, 0)) AS pos, SUM(IF(a.tipo = 'SAIDA', a.credito, 0)) AS negat FROM credito AS…
mysqlanswered Roberto de Campos 5,345 -
2
votes1
answer20
viewsA: SQL query with OR gets slow
If you make a UNION may improve: (SELECT pt.virtuemart_product_id, pt.product_name as nome, pt.slug as musica, ct.mf_name, cantor.virtuemart_product_id, pc.virtuemart_product_id,…
-
0
votes2
answers402
viewsA: SUBSTRING_INDEX in mysql
I can’t guarantee it’s the best alternative, but it’s still a: SELECT SUBSTRING_INDEX(arrachar, ",", 1) AS `data`, SUBSTR(arrachar, LENGTH(SUBSTRING_INDEX(arrachar, ",", 1)) + 2,…
mysqlanswered Roberto de Campos 5,345 -
7
votes2
answers406
viewsA: Why doesn’t Java add up the numbers in expression?
This is because you have a string in the middle, when there’s string he understands that this concatenating, try to do the operation before concatenating, so put the operation between parentheses:…
-
0
votes2
answers92
viewsA: Delete duplicate records in MYSQL
It turns out that the MySQL does not allow nicknames in instruction DELETE, you have to run like this: DELETE FROM bid_account WHERE id < ( Select max(t2.id) FROM bid_account t2 WHERE…
-
0
votes1
answer146
viewsA: Mysql trigger - Syntax error
The MySql has the ; end of each query, in which case he’s trying to execute two querys rather than one, precisely because the ; is separating. To prevent this from happening, it is necessary to show…
mysqlanswered Roberto de Campos 5,345 -
2
votes3
answers99
viewsA: Take data that is not in the table
Check if the return of LEFT JOIN is void: SELECT cod FROM login LEFT JOIN pode ON pode.cod_usuario = login.cod_usuario WHERE pode.cod_usuario IS NULL; If it is NULL does not exist in the table pode.…
mysqlanswered Roberto de Campos 5,345 -
0
votes0
answers44
viewsQ: Move Image to Jrdesignimage
I have a barcode image being generated by barbecue , this image is stored in an instance of Graphics2D. I wanted to know how I put this image in an instance of JRDesignImage for it to be compiled…
-
0
votes1
answer26
viewsA: Report always prints in A4 format
This problem occurred because the page had less than 1cm, to solve, I had to do the calculation to convert mm for px, the formula is this: (X * 72) / 25,4 OBS: 72 is the resolution (quantity of…
-
3
votes2
answers438
viewsA: How to add event to an inherited form event in Delphi?
In that case you will have to override the class event TFModelo1 in TFClientes, would look something like this: type TFClientes = class(TFModelo1) private FOnCellClick: TDBGridClickEvent; procedure…
-
4
votes1
answer54
viewsA: how do I pull the result from a column inside a string in mysql
To concatenate into the MySQL have to use the function CONCAT, would look like this: SELECT CASE WHEN campo = 1 THEN CONCAT('existe o resultado=', banco.tabela.coluna, 'fim da string') ELSE 0 END…
mysqlanswered Roberto de Campos 5,345 -
5
votes1
answer588
viewsA: How to know which object is focused at runtime
In the form you can return the field that is active with the property ActiveControl, in your case would look more or less like this: if (cbbTipoConsultaEmAberto.ItemIndex = 5) and…
-
0
votes2
answers109
viewsA: How to bring SQL result grouped by year and within months
First problem in your code is that if the date is the same and the time is different, they will not be grouped together, then ideally you group by the year and month of the date, the select would…
-
1
votes1
answer26
viewsQ: Report always prints in A4 format
I am creating the entire report at runtime, however, at the time of printing it is not respecting the size I set. No ReportViewer it displays right, but in the printer or on PrimoPDF does not print…
-
1
votes1
answer423
viewsA: Dynamic printing Jasperreports
I found the problem, it seems that the JasperReports is lost when it does not have the margins set, so I set the margins, another problem is that it also can not generate the report without a…
-
4
votes1
answer1443
viewsA: Delete record by comparing values between two tables
Make a DELETE with a SUB-SELECT: DELETE FROM valores WHERE id_produto NOT IN (SELECT id_produto FROM produtos);
-
1
votes1
answer423
viewsQ: Dynamic printing Jasperreports
I’m trying to make an impression with JasperReports, when compiling the report I get the following error: Exception in thread "main" net.sf.jasperreports.engine.design.Jrvalidationexception: Report…
-
1
votes2
answers56
viewsA: Create OR Update -> Mysql
If you have a primary key or a single index, you can use the event ON DUPLICATE KEY, would look something like this: INSERT INTO `sua_tabela` (`campo1`, `campo2`) VALUES ('informação1',…
mysqlanswered Roberto de Campos 5,345 -
1
votes2
answers32
viewsA: passing class and variable in contruct
In fact it is receiving a variable of type Person. As the constructor is asking for an object of the uncle Pessoa as a parameter, if whoever uses the class passes anything else as a parameter, they…
phpanswered Roberto de Campos 5,345 -
6
votes2
answers611
viewsA: Updating multiple Mysql lines and fields
Just put a comma to each field: UPDATE produtos SET nome = CASE codigo WHEN 5 THEN 'teste 1' WHEN 6 THEN 'teste 2' WHEN 7 THEN 'teste 3' END, campo2 = "valor2", campo3 = "valor3" WHERE codigo IN…
-
1
votes2
answers31
viewsA: Error comparing php array
Better start from the second item and each loop compare with the previous: $rua[] = {1,1,2,2,2,2}; $countRua = count($rua, COUNT_RECURSIVE); for ($i=1; $i < $countRua; $i++) { if ($rua[$i] ==…
phpanswered Roberto de Campos 5,345 -
2
votes1
answer153
viewsA: Problem with Media Query
Summary Let’s assume that the screen width of the device has 667px. Both media queries will be met. How to media querie of major max-width this after the minor max-width it is overlapping, that by…
-
0
votes1
answer138
viewsA: Select JOIN + AVG() - Doubt with feedback
To make the AVG for each supplier you need to use the GROUP BY: SELECT f.codigo, f.nome, AVG(p.preco), COUNT(codfornecedor) FROM produto as p JOIN fornecedor as f on codfornecedor = f.codigo WHERE…
-
1
votes2
answers322
viewsA: Find lines of the last 24 hours that repeat the most (Mysql)
You can do it yes, I’d do it that way: SELECT `from`, `to`, COUNT(*) AS num_clicks FROM my_rank WHERE my_rank_data >= DATE_SUB(NOW(), INTERVAL 24 HOUR) GROUP BY `from`, `to` ORDER BY num_clicks…
-
3
votes2
answers61
viewsA: Query with Internet
You have to use LEFT JOIN , would look like this: SELECT a.NF, a.parcelas, COUNT(b.id) AS qtdeParcelas FROM tabela1 a LEFT JOIN tabela2 b ON a.NF = b.NF GROUP BY a.NF; If you want to bring only the…
mysqlanswered Roberto de Campos 5,345 -
1
votes2
answers71
viewsA: How to control version number on budget?
I believe that cloning the tables is not the best alternative. What you can do is make your main table (which I imagine it is orcamento) be recursive. For this add a field id_orcamento_anterior, so…
-
2
votes1
answer49
viewsA: LEFT JOIN count likes
You need to put a GROUP BY to group the data: SELECT *, count(curtida.id) AS curtidas FROM curso LEFT JOIN curtida ON curtida.id_curso = curso.id GROUP BY curso.id…
mysqlanswered Roberto de Campos 5,345 -
0
votes1
answer43
viewsA: How to create an array the same size as another array without copying it?
With Length works normally, looks an example: private string InversaoString(string Texto) { char[] arrChar = Texto.ToCharArray(); char[] arrChar2 = new char[arrChar.Length]; for (int i =…
-
0
votes1
answer417
viewsA: PHP error while saving using quotes
Use the function addslashes of PHP in that string, example: addslashes("Frantchelle's"); //Frantchelle\'s \(Backslash) in the MySQL is the escape character, so it will not treat this ' as closure of…
-
0
votes2
answers99
viewsA: Handler for undefined index in array
I would do the opposite, instead of checking the next, I would check the previous: for ($i=0; $i < $array.lenght(); $i++){ if(($i == 0) || ($array[$i - 1] != $array[$i])) echo "<p>O Site do…
-
0
votes1
answer48
viewsA: How to use a Join clause by adding a column indicating the existence of a connection
Make a LEFT JOIN and put a if to check whether the b.id is void or not: SELECT a.`emblema_id`, IF(b.`id` IS NOT NULL, "SIM", "NÃO") AS 'possui' FROM `Atividades` a LEFT JOIN `Horas` b ON a.`id` =…
-
0
votes3
answers864
viewsA: Search two Mysql tables and select rows with a common element
Do with INNER JOIN would be the clearest and perhaps performative way to solve this problem, the query would be more or less that: SELECT b.* FROM `tabela_dois` a INNER JOIN `tabela_um` b ON…
-
0
votes0
answers137
viewsQ: Standard Singleton in simultaneous connections
I’m developing a project and I made a class in the pattern Singleton to connect to the database: <? class Conexao extends PDO { private static $instancia; public function __construct($dsn,…
-
1
votes1
answer300
viewsA: Select if it does not exist in another table, but if there is no record in 24h
You can add the check from datahora in his sub-select using the function DATE_SUB: SELECT * FROM links l WHERE NOT EXISTS ( SELECT * FROM works w WHERE w.linkId = l.linkId AND w.datahora <=…
mysqlanswered Roberto de Campos 5,345 -
1
votes1
answer619
viewsA: PHP code error - SQLSTATE[42000]
Remove the simple quotes from your SELECT, being like this: $pegar_categorias = "SELECT * FROM loja_categorias ORDER BY id DESC"; Another alternative is to replace single quotes with crase:…
phpanswered Roberto de Campos 5,345