MYSQL NOT IN array php

Asked

Viewed 224 times

1

Hello, gentlemen

I am working on a project for a hobby I have and am having a hard time with the MYSQL part. I have an array that comes from the API and is decoded and inserts some data of the connections in DB. So far, everything normal. However, the connections will be closed later - after all customers will disconnect from the network. I need these records to be deleted from DB, and so I thought I’d use Mysql’s NOT IN. Searching the internet, I realized that I would have to use the php implode to be able to adapt to the MYSQL format. So, I created:

$comma_separated = "'" implode("", $csv[$i]) "'" ;
//Comando SQL - Remove do DB aeronaves que não partem do Brasil
$sql = "DELETE FROM connections WHERE callsign not in ('$comma_separated')";

Where, csv is the array with the data - the $i comes from a loop that fetches the API data. The problem is that when executing the code it simply deletes all records, including those that still have Callsign in the array.

How do I proceed in this situation? I have researched several places, including here in Stackoverflow itself and found no reference.

From now on, I thank you and would like to inform and I apologize for any bizarre code. I am beginner in language. For those who want to see some more of the code, it’s uploaded to Github by the url: github.com/Sirgwaihir/parsec

Hug

php
  • 1

    Try $comma_separated = implode("','", $csv[$i]);

2 answers

1


Basically you need simple comma quotes:

$comma_separated = implode("','", $csv[$i]);

You also need to see if the $csv[$i] is really what you want.

A demo code. Use echo to avoid DB testing:

$lista = array( 'PY2UFO', 'PY2VB' );
$comma_separated = implode("','", $lista);
$sql = "DELETE FROM connections WHERE callsign not in ('$comma_separated')";
echo $sql;

See working on IDEONE.

  • It is worth noting that looking at your code on github I have the impression that you already have some previous problem. Make sure to join all the callsigns in a array only before using the code above. A print_r( $lista ); is a good way to check and hit the problem BEFORE testing the above code.

  • Hello, @Bacco. I think that’s really the big problem. The fact that you couldn’t put all the callsigns together in one array. Would you have any suggestions? I’ve tried doing it in the usual way, like array($callsign); but it creates an array for each Callsign which is not nearly what I want. Doing the tests with what you suggested I realized that the solution would really add all the callsigns to the same array, but I don’t know how. Hug

  • make a loop that picks up the callsigns and use it like this $lista[] = $csv[237] (instead of $csv[237] you use the correct field). Once you have finished the loop, you will have all occurrences in $list. [] = at the end of the variable means "add to array".

  • Okay, I will be and as soon as I have any results I will report to you. Thank you very much for the support :D

  • 1

    I was able to solve the problem, Bacco. The problem really was this. I saw by var_dump that the callsigns were in string form. I followed his steps and managed to turn to an array. Now it’s all working ball show. Thanks for the support. Hug :D

0

Looking at the surface, one notices the lack of the delimiter.

implode("", $csv[$i])

swap for 

implode("', '", $csv[$i])

Tip

Before executing the query, check the integrity by breakpoint:

right after that line

$sql = "DELETE FROM connections WHERE callsign not in ('$comma_separated')";

add 

echo $sql; exit;

This is just to see how the query is being mounted.
If you are mounting correctly, disable the test or delete.

//echo $sql; exit;

Note that the tips above may not solve the problem 100%. It is a solution tip based on the most likely hypothesis, according to what you presented in the question.

Browser other questions tagged php

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