delete from table where NOT IN array

[hyster]

i have a table that is populated by users individually.

as a user leaves i want to clean them out using an array listing only current members.

 <?php
 
 // account details
 $app_id = "????";
 $clan_id = '????';

// get json
$jsonurl1 = "https://api.worldoftanks.eu/wgn/clans/info/?application_id=$app_id&clan_id=$clan_id";
$json1 = file_get_contents($jsonurl1,0,null,null);
$output1 = json_decode($json1, true);

// lists all players in the array
foreach ($output1['data'][$clan_id]['members'] as $item){
    echo $item['account_name'].'</br>';
}
 ?>

the above gets the array from a json source and displays current members.

 

 

im after something like below, its the process i do not know how to do not the code, if i can work out how its done then i can find the code i need.

<?php
// account details
$app_id = "????";
$clan_id = '????';

// get json
$jsonurl1 = "https://api.worldoftanks.eu/wgn/clans/info/?application_id=$app_id&clan_id=$clan_id";
$json1 = file_get_contents($jsonurl1,0,null,null);
$output = json_decode($json1, true); 

//holds the player name array
$player_list_array = $output['data'][$clan_id]['members'] ;
    
//sql     
!!database connect here !!

//THIS PART IM CONFUSED ON,
$sql = "delete from garage_list where $player_list_array NOT IN 'player'" ;
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    echo "old members deleted"
}
?>

//table design
"gargae_list"
      country - type - "player" - account_id - tank_id - master - tier - name - garage

thanks for any help!!

Edited May 19, 2017 by hyster

[requinix]

You really shouldn't delete data. It's more likely to do harm not having it than having too much of it. Mark the member as inactive or unjoined or something.

 

To the question, though. About how many members are there going to be at a time, and what is the structure of $player_list_array - like, is it an array of ID numbers, or an array of more data, or what?

[hyster]

its a clan page for keeping track of what members have what tanks in game (world of tanks).

http://www.t-s-k.co.uk/garage_list.php

max members is 100 but each member could have over 400 entries (typically maybe 20-40).

 

the reason i want to delete rather than "disable" is once a member leaves the data is worthless to me.

<?php
 
 // application_id
 $app_id = "????";
 $clan_id = '????';

// get json
$jsonurl1 = "https://api.worldoftanks.eu/wgn/clans/info/?application_id=$app_id&clan_id=$clan_id";
$json1 = file_get_contents($jsonurl1,0,null,null);
$output1 = json_decode($json1, true);

$cart = array();

foreach ($output1['data'][$clan_id]['members'] as $item){
    $cart[] = $item['account_name'];
}

print_r($cart);
?>

i decided it is probs easier to work from a php array rather than a json 1.

the above code gives me a list of players (old $player_list_array)

(

working here

http://www.t-s-k.co.uk/includes/array.php

) 

 

so now i need to compare that array $cart to the DB and remove entries not in the array.

 

hope that makes more sense :$

[requinix]

100 isn't too bad for a query...

 

You need to generate something like

DELETE FROM garage_list WHERE account_id NOT IN (1, 2, 3, 4, ...)

1. Use the account_id (assuming that's a unique number) instead of the player name

2. It's the database column "not in" the list of values, not the other way around.

 

I'm guessing you can adjust your demo code to

foreach ($output1['data'][$clan_id]['members'] as $item){
    $cart[] = $item['account_id'];
}

to get an array of IDs. You probably don't need to but it would be good to force everything to a number, just in case,

$cart[] = (int)$item['account_id'];

then implode() it into a list

"... NOT IN (" . implode(", ", $cart) . ")..."

"cart" isn't the best name for that variable though.

[DulongC]

If you really want to delete the records, use this:

 

$query = "DELETE FROM `garage_list` WHERE `player` NOT IN (" . implode(',', $cart) . ")";

Edit:

Sorry double post, requinix beat me to it

Edited May 20, 2017 by DulongC

[requinix]

If you really want to delete the records, use this:

 

$query = "DELETE FROM `garage_list` WHERE `player` NOT IN (" . implode(',', $cart) . ")";

 

Also works, but with a small problem: the player names are strings, so not only do they need quotes

$query = "DELETE FROM `garage_list` WHERE `player` NOT IN ('" . implode("','", $cart) . "')";

but the names would have to be escaped - or use a prepared statement instead. I went with the account_ids instead since they're numbers which makes it a bit easier, but looking at it now it's not actually that much less work.

[DulongC]

The account ID probably makes more sense, less room for errors and forcing an int will prevent injection

[hyster]

huge thanks guys got it working with the below code encase others have a need.

account id was easier, i wanted to use names so i could see who was deleted but as long as it works

<?php
 
 // application_id
 $app_id = "????";

// clan id
 $clan_id = '????';

// get json
$jsonurl1 = "https://api.worldoftanks.eu/wgn/clans/info/?application_id=$app_id&clan_id=$clan_id";
$json1 = file_get_contents($jsonurl1,0,null,null);
$output1 = json_decode($json1, true);


$old_members = array();

foreach ($output1['data'][$clan_id]['members'] as $item){
    $old_members[] = $item['account_id'];
}

include '../includes/config.php';
mysql_connect("localhost", "$username", "$password") or die(mysql_error());
mysql_select_db("$dbname") or die(mysql_error());
$sql = "DELETE FROM `garage_list` WHERE `account_id` NOT IN (" . implode(',', $old_members) . ")";
$result=mysql_query($sql);

?>

Edited May 20, 2017 by hyster

[requinix]

You can still find out if you want: do a SELECT first, then DELETE after. Wrap it in a transaction to be sure, but it doesn't sound like concurrency will be an issue here.

[hyster]

as its for a game, when a player buys or sells a new tank they use a "add garage" page that deletes there old records then gets and inserts there current tanks, that seemed simpler than comparing old - new records.

 

my old way of clearing out old members was to manually delete each member from a list, im trying to get a system in place for not so bright people lol so idiot proof it as much as i can