MYSQL- Stuck

[Gorsy]

Hi guys, Sorry to trouble you. Just been at this for the past day and really struggling

SO here is what im trying to do.

I have script that searches a mysql database details for players details that are stored in the database. What it does is it checks to see have they certain items that they should not have, (means they are hacking)

Atm it searches for Night Vision goggles in someones inventory table
Brings back a list of who has them, there name id number and some other tables of information.

This part works fine


What I am trying to do is to add a delete button underneath the returned information for each group of information pulled back... to remove that player from the database using the 'id' number from the survivor table.

I know you cant do it via onclick so ive been trying alot of if(isset($_POST['button1']) type commands, but im still learning the ropes here and cant seem to get it to work.

Any advice would be helpful. Here is the working code to return the information

 
//*******(database details go here but left out for obvious reasons)*****
// Connect to the db
  $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname, $dbport);
  
  // Set the variabless up
$db_goggles  = "%NVGoggles%";


  

// Scan Database for NVG and store in sql_goggles
  $sql_goggles = "SELECT * 
FROM `survivor` 
WHERE `inventory` LIKE '$db_goggles'
;";

//run query and put results into result var
  $result = $mysqli->query($sql_goggles);
  
  while ($row = mysqli_fetch_array($result)) {

//set up sql for finding player name once found
  $sqlnamefind = "SELECT *
FROM `profile`
WHERE `unique_id` LIKE '{$row['unique_id']}'
;";

//run name find sql
$nameresult = $mysqli->query($sqlnamefind);

while ($namerow = mysqli_fetch_array($nameresult)) {
echo '<br><font color=red><b>Name:</b></font><font color=blue> ';
echo $namerow['name'];
}

echo '</font><br><font color=red><b>ID:</b></font><font color=blue> ';
echo $row['id'];
echo '</font><br><font color=red><b>Unique ID:</b></font><font color=blue> ';
echo $row['unique_id'];
echo '</font><br><font color=red><b>Inventory:</b></font><font color=blue> ';
echo $row['inventory'];
echo '</font><br><font color=red><b>Backpack Inventory:</b></font><font color=blue> ';
echo $row['backpack'];
echo '</font><br>';

} 
} 

And it displays like

Name: The King of Derp
ID: 130867
Unique ID: 250304
Inventory: [["ItemCompass","ItemMap","ItemGPS","ItemWatch","Binocular_Vector","NVGoggles","ItemToolbox","MakarovSD","MP5A5"],["Skin_Survivor2_DZ","ItemBandage","30Rnd_9x19_MP5","30Rnd_9x19_MP5","30Rnd_9x19_MP5","30Rnd_9x19_MP5","SmokeShellPurple","8Rnd_9x18_MakarovSD","5Rnd_86x70_L115A1","20Rnd_762x51_SB_SCAR"]]
Backpack Inventory: ["DZ_Backpack_EP1",[["BAF_LRR_scoped","Pecheneg"],[1,1]],[["100Rnd_762x54_PK"],[1]]]

Name: The King of Derp2
ID: 130869
Unique ID: 250308
Inventory: [["ItemCompass","ItemMap","ItemGPS","ItemWatch","Binocular_Vector","NVGoggles","ItemToolbox","MakarovSD","MP5A5"],["Skin_Survivor2_DZ","ItemBandage","30Rnd_9x19_MP5","30Rnd_9x19_MP5","30Rnd_9x19_MP5","30Rnd_9x19_MP5","SmokeShellPurple","8Rnd_9x18_MakarovSD","5Rnd_86x70_L115A1","20Rnd_762x51_SB_SCAR"]]
Backpack Inventory: ["DZ_Backpack_EP1",[["BAF_LRR_scoped","Pecheneg"],[1,1]],[["100Rnd_762x54_PK"],[1]]]

Your time is very much appreciated. Thanks Gorsy...

[Gorsy]

Sorry forgot to say I want a Delete button to appear after each set listed if this possible. I have been trying but really struggling with it

[swa66]

You make a second script that does the deleting in the table(s) as appropriate.
Let's call it deleteplayer.php and it takes the id of the player as a get request.
so you call is as

http://www.example.com/path/goes/here/deleteplayer.php?id=130869

You then output a link to the second script from the first ... and you can style it as a button if you like just as well.

[Gorsy]

Sorry to be a pain, I get that, and I think I can get that to work pretty easily. The one thing im not sure on, is how I tell the query to use the ID in the web address. Do I write a varible for this? $id= 'id' or something like that?

[swa66]

$_GET['id'] gives you whatever the user sent after the ?id= part of the url.
Now take care: hackers WILL send nasty stuff (they'll try to inject SQL in there), so be extra careful.

Since your code uses mysqli, you can use prepared statements, they're the more secure choice.

Just to give you a head start, an example:

<?php
function niceerror($str) {
  if(stristr($_SERVER["HTTP_ACCEPT"],"application/xhtml+xml")){
    header('Content-Type: application/xhtml+xml;charset=UTF-8');
  }
  print('<!DOCTYPE html>'."\n");
  print('<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">'."\n");
  print(' <head>'."\n");
  print('  <meta charset="UTF-8" />'."\n");
  print('  <title>delete failed</title>'."\n");
  print(' </head>'."\n");
  print(' <body>'."\n");
  print('  <h1>Oops</h1>'."\n");
  print('  <p>An error occured: <b>'.$str.'</b>.</p>'."\n");
  print(' </body>'."\n");
  print('</html>'."\n");
  exit();
}

#request parsing
if ( ( isset($_GET['id']) ) && ( StrLen($_GET['id']) > 0 ) ) {
  // only digits are kept
  $id= preg_replace('/[^0-9]/m', '', $_GET['id'] );
} else {
  niceerror("bad query");
}
if ( StrLen($id) < 1 || Strlen($id) > 10 ) {
  niceerror('bad query');
}

// server info
$server = '127.0.0.1';
$user = 'user';
$pass = 'password';
$db = 'database';

// connect to the database
$mysqli = new mysqli($server, $user, $pass, $db);
if ($mysqli->connect_errno) {
  niceerror('Error connecting to the database'); 
}

// communicate in utf-8 with the database
$mysqli->set_charset("utf8"); 

//delete entry
$sql = "DELETE FROM table WHERE id = ?";
if($stmt = $mysqli->prepare($sql)) {
  $stmt->bind_param("i", $id);
  if(!$stmt->execute()) {
    niceerror('Delete failed: '.$stmt->error);
  }
  $stmt->close();
} else {
  niceerror('Failed to prepare query: '.$mysqli->error);
}

$mysqli->close();

header('Location: http://www.example.com/'); /* Redirect browser */
exit();
?>

niceerror outputs polyglot html5 .. no need to copy that verbatim.

If you need to delete in more than one table, you can do so by repeating the part between the $mysqli->set_charset() and the $mysqli->close(); as needed.