Generate sql from a js return

Asked

Viewed 40 times

0

Good morning, stack overflow. It’s been a while since I’ve been hammering to look for a solution to a problem that came up, that nothing comes to mind.

I have a form, where this form will make a filter in the js datatable. This datatable I use it serverside, so I need sql querys. So far so good, the problem starts with the return I get in php.

It is a return that can vary according to what I have in the form. There begins my problem, that varying return.

You can get it like this in php: Desistência de Cadastro,Carta Negada,Carta Cancelada,,,,

How can you arrive that way too.

Cadastro Iniciado,Aguardando aprovação de cadastro,Cadastro Aprovado,Carta Solicitada,Desistência de Cadastro,Carta Negada,Carta Cancelada,,,MS,MG,PA,PB,PR,PE,PI,RJ,RN,RS,RO,RR,SC,SP, The question is: how to generate an sql from these values.

The query will look like this:

   SELECT id_parceiro, nome_empresa, cidade, estado, status_atual, ultima_data, data_anterior, status_anterior, dias_entre_status
        FROM  report_partner_view WHERE ativo = 1
        AND (status_atual = 'Cadastro Iniciado' OR status_atual = 'Carta Negada')
        ORDER BY  id_parceiro
                    asc
        LIMIT 0, 10

Being this AND, OR, by state, status, being formed by the return shown above.

Thanks in advance.

php javascript jquery mysql

  • the return of the status is always 1 of those quoted right ?

  • Yes... are always these: Registration Started, Awaiting registration approval, Registration Approved, Letter Requested, Registration Withdrawal, Letter Denied, Letter Cancelled

  • then with your problem is with the return of status you need to make a select based on it and the state is this ?

  • Yes, in fact, all that can come, are the states, these status, 2 dates and a city field... I will have to assemble a select based on them

  • I imagine if I compare it one by one, whether it’s gambiarra, there’s a way to do it dynamically?

  • I couldn’t quite understand your difficulty, but that wouldn’t solve it ? status_current = '. $return . ' OR state= '. $return .'

  • I’ll start a chat

  • Let’s go continue this discussion in chat.

Show 3 more comments

1 answer

0

PHP can look like this:

$filtro = "Cadastro Iniciado,Aguardando aprovação de cadastro,Cadastro Aprovado,Carta Solicitada,Desistência de Cadastro,Carta Negada,Carta Cancelada,,,MS,MG,PA,PB,PR,PE,PI,RJ,RN,RS,RO,RR,SC,SP,"

$status = explode(',', $filtro);
$status_where = [];
foreach($status as $s) {
   $status_where[] = "'{$s}'";
}
$status_where = implode(',', $status_where);

This will generate an array of items ['Cadastro Iniciado','Aguardando aprovação de cadastro','MS']

And with this array mount your query using a IN

SELECT .. FROM report_partner_view WHERE ativo = 1 AND status_atual IN ($status)

  • ai que tá @Marcos, the "MS" in this case is not a status but a state.

  • @gabrielfalieri One thing you can do is consider that the positions of this array will always be the same and build your query based on this.

Browser other questions tagged php javascript jquery mysql

You are not signed in. Login or sign up in order to post.