Hello everyone, I would like to give my contribution on the use of parameters in Bubble when connecting to a MySQL database.
Problem: In Bubble, when using MySQL, unlike Postgre and SQL Server, we can only use “?” (question mark) as a parameter, which prevents us from using the same parameter in more than one place in the code. However, I managed to find a solution that efficiently solves this need.
After “talking” to ChatGPT and testing, I found an efficient way to use parameters in Bubble.
I have a “people” table in which I need to search for the data that the user enters in several fields.
To avoid having to repeat the parameter (in MySQL and Bubble) as many times as I want to search for fields, I found this way to use a single parameter, save it in a variable and then reuse it as many times as necessary.
Below is an example of how it looks in Bubble:
SELECT
cad_pessoas.id,
cad_pessoas.nome,
cad_pessoas.fantasia,
formatar_cpf_cnpj(cad_pessoas.cpf_cnpj) AS cpf_cnpj,
cad_pessoas.dt_inclusao,
cad_pessoas.id_status_pessoa,
cad_pessoas_status.status_pessoa AS STATUS,
cad_pessoas.uf,
cad_pessoas.cidade,
cad_pessoas.telefone1,
cad_pessoas.telefone2,
cad_pessoas.celular,
cad_pessoas.site,
cad_pessoas.email
FROM
cad_pessoas INNER JOIN
cad_pessoas_status ON cad_pessoas.id_status_pessoa = cad_pessoas_status.id
,(SELECT @busca_texto := ? COLLATE utf8mb3_unicode_ci) AS temp
,(SELECT @busca_cnpj := OnlyNumber(?) COLLATE utf8mb3_unicode_ci) AS temp2
,(SELECT @busca_telefone := OnlyNumber(?) COLLATE utf8mb3_unicode_ci) AS temp3
WHERE CPF_CNPJ LIKE IF(@busca_cnpj <> '', CONCAT(@busca_cnpj, '%'), '-1')
OR cad_pessoas.nome LIKE CONCAT('%', @busca_texto, '%')
OR cad_pessoas.fantasia LIKE CONCAT('%', @busca_texto, '%')
OR cad_pessoas.email LIKE CONCAT('%', @busca_texto, '%')
OR cad_pessoas.contato_principal LIKE CONCAT('%', @busca_texto, '%')
OR telefone1 LIKE IF(@busca_telefone <> '', CONCAT('%', @busca_telefone, '%'), '-1')
OR telefone2 LIKE IF(@busca_telefone <> '', CONCAT('%', @busca_telefone, '%'), '-1')
OR celular LIKE IF(@busca_telefone <> '', CONCAT('%', @busca_telefone, '%'), '-1')
ORDER BY ID DESC
LIMIT 20
Parameters in Bubble:
Once done, in the desired object (example: RepeatGroup) in the Data Source option, simply select the “Get data from external API” option and pass the desired parameters.