php mysql deleting a record

[PNewCode]

Hello everyone! I hope your day is awesome.

I have a puzzling issue. I have made plenty of files that delete records from databases, however those all call for such as "?id=blablabla". This time I'm calling it from a button that grabs the session and is supposed to get the id from that. But it's not deleting the record.
Here is the full script. If you want to know where it's coming from, it's just a button on a previous page that links to this page below

No errors are displaying on screen nor in the log

NOTE: "mybandid" is equal to the user id in the database (but different table called "users"). This is to identify that entry for that specific user from a different table. Which works for displaying the page.
so if the user id is 100, then the mybandid is also 100 in this database

 

<?php

error_reporting(E_ALL);
ini_set('display_errors', '1');

// Start the session
session_start();


$servername = "Deleted For Posting";
$username = "Deleted For Posting";
$password = "Deleted For Posting";
$dbname = "Deleted For Posting";


//Connect DB
//Create query based on the ID passed from your table
//query : delete where id = $mybandid
// on success delete : redirect the page to original page using header() method

$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// sql to delete a record
    $mybandid=$_SESSION['id'];

$sql = "DELETE FROM bandstage WHERE id = $mybandid"; 

if (mysqli_query($conn, $sql)) {
    mysqli_close($conn);
    header('Location: delete-epk3.php'); 
    exit;
} else {
    echo "Error deleting record";
}?>

 

Edited May 9, 2023 by PNewCode

[ginerjm]

Try either of these:

- put the id value in quotes for the query if the table column is NOT defined as a numeric value

- echo out the query statement before running the query runs to be sure the query looks like you think it should look

[Barand]

Add

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

before your call to "new mysqli"

[PNewCode]

@ginerjm Thank you. I can't put the id value in because that could be different for each user. So it has to be from whoever is logged in on that page (session id)

@Barand I removed the header and added your code, though no error is displaying

Any thoughts?

[ginerjm]

Of Course you can put an echo in there.  What's stopping you from debugging your code?  When you are testing the script out you already Have logged in!

[Barand]

2 hours ago, PNewCode said:

$sql = "DELETE FROM bandstage WHERE id = $mybandid"; 

ginerjm is asking you to echo the query string that you are trying to execute That is...

$sql = "DELETE FROM bandstage WHERE id = $mybandid"; 

echo $sql;                                              // ADD THIS TO SEE IF IT LOOKS AS EXPECTED

 

[mac_gyver]

what result or output are you getting on this page? what does adding the following, after the session_start(), show -

var_dump($_SESSION['id']);

a bunch of points about the posted code -

1. the php error related settings should be in the php.ini on your system.
2. if you put the database connection code in a separate .php file, you can just require it when needed. this will save you from having to redact the connection credentials when posting the code.
3. the line that barand posted, which enables exceptions for errors, should always be part of your code. this is also the default setting in php8+
4. don't output raw database connection onto a web page or tell the visitor that a database error occurred. this only helps hackers. when using exceptions for database statement errors, the error handling logic you have now won't ever be executed upon and error and should be removed, simplifying the code.
5. you should set the character set when you make the database connection to match your database tables, so that no character conversion occurs over the connection.
6. you should be using a post method form when performing an action on the server, such as deleting data. if this a get method button/link, when a search engine indexes your site, you could end up with a bunch of your data getting deleted.
7. $_SESSION['id'] is an 'input' to your code. you must validate it before using it. technically, you should trim() it before validating it if it could have accidentally gotten modified when putting the value into the session variables.
8. don't copy variables to other variables for nothing. just use the original variables.
9. you should also be using a prepared query when supplying external, unknown, dynamic value to a query when it gets executed.
10. php automatically destroys all resources on a page when your script ends. there's no need to close database connections in your code.
11. upon successful completion of post method form processing code, you should perform a redirect to the exact same url of the page to cause a get request for that page. this will prevent the browser from trying to resubmit the form data should the user reload or navigate away from and back to that page.

[PNewCode]

@Barand and @ginerjm this is the echo I'm getting. And that is the correct id
DELETE FROM bandstage WHERE id = 281

Yes testing it after logged in too

So since it's getting the correct ID, then why in the world isn't it deleting the record for it? 

@mac_gyver thank you for that education. my amatuer mind understood about half of that but that also gives me some more to look up to learn from.

Edited May 9, 2023 by PNewCode

[mac_gyver]

using var_dump() on the session variable (see my reply above) will help identify if it contains any non-printing/white-space characters.

1 hour ago, PNewCode said:

I removed the header and added your code, though no error is displaying

if by that you mean the header() statement. no, he told you to put that line of code -

 

1 hour ago, Barand said:

before your call to "new mysqli"

at the header() statement, it is too late to put in that line of code.

3 hours ago, PNewCode said:

in this database

does that mean you have multiple databases or does that mean you actually have only one database, but it has multiple tables in it? if you do have multiple databases, are you selecting the correct one and is that the same one you are looking at to see if the records are being deleted?

[PNewCode]

8 minutes ago, mac_gyver said:

if by that you mean the header() statement. no, he told you to put that line of code -

I know, but I removed it so it wouldn't switch to that page in the header statement before I could see the results of the code he had me put in (so it stayed on that page)

 

9 minutes ago, mac_gyver said:

does that mean you have multiple databases or does that mean you actually have only one database, but it has multiple tables in it? if you do have multiple databases, are you selecting the correct one and is that the same one you are looking at to see if the records are being deleted?

Same database, different tables. One table is "users" that has all the users that login and their user id's. The other table is "bandstage" that has their EPK's they create

[mac_gyver]

so, how about the var_dump output?

if you actually use all the points made, including switching to the much simpler and more modern PDO database extension, you will get code that looks like this (untested) -

<?php

// initialization

session_start();

$errors = []; // array to hold user/validation errors

// post method form processing
if($_SERVER['REQUEST_METHOD'] === 'POST')
{
	// validate input data
	if(!isset($_SESSION['id']))
	{
		$errors['logged_in'] = 'You must be logged in to delete your bandstage data.';
	}
	
	// if no errors, use the input data
	if(empty($errors))
	{
		require 'pdo_connection.php';
		
		$sql = "DELETE FROM bandstage WHERE id = ?";
		$stmt = $pdo->prepare($sql);
		$stmt->execute([ $_SESSION['id'] ]);
	}
	
	// if no errors, success
	if(empty($errors))
	{
		// note: if you want to display a one-time success message, store it in a session variable, then test, display, and clear that variable in the html document
		$_SESSION['success_message'] = 'You have successfully deleted your bandstage data.';
		// redirect to the exact same url of the current page to cause a get request for the page - PRG Post, Redirect, Get.
		die(header("Refresh:0"));
	}
}

// get method business logic - get/produce data needed to display the page

// html document
// note: this in incomplete and only shows the parts corresponding to this example code
?>

<?php
// display and clear any success message
if(isset($_SESSION['success_message']))
{
	echo $_SESSION['success_message'];
	unset($_SESSION['success_message']);
}
?>

<?php
// display any errors
if(!empty($errors))
{
	echo implode('<br>',$errors);
}
?>

 

[Barand]

Does your bandstage table have a record with 281 in the id column?

[PNewCode]

@mac_gyver thank you much for that effort. I appreciate it.

First I tried to translate what you have in comparison with what I did, and also with the other comments to understand it. Then I realized I could not make heads or tails of it because it's more advanced than my understanding. Even a lot of the verbage thats said I don't understand haha. 
So THEN I thought I would copy and paste it, and if it worked then I could go in and learn to see WHY it worked and then learn and understand it (which is how I learn best unfortunately), but alas it didn't work. Not even adding the error scripting and there was no error displaying. Just a white page.

But I thank you for taking the time to write that out to help me

[PNewCode]

@Barand yes it does. It's the logged in users id, that is also in the database's table column "mybandid"

The user that is logged in, is 281. When that user made the EPK, his user id is put in this table under the column "mybandid".
So this way both are with the same id number... in the table "users" and "bandstage"

Edited May 9, 2023 by PNewCode

[mac_gyver]

the column in question in the bandstage table is apparently mybandid

[PNewCode]

3 hours ago, mac_gyver said:

the column in question in the bandstage table is apparently mybandid

Exactly

[Barand]

So, problem solved then.

[Drummin]

As I understand it in the `users` table the users ID is in the field `id`, which is pretty common but the user may have records in the `bandstage` table and the `id` field represents the bandstage record ID and user ID is held in the `mybandid` field. This seemed to be what you were saying with your table image.  So to delete the user records you query against the field that represents the user ID, which in this table is `mybandid`. 

$sql = "DELETE FROM `bandstage` WHERE `mybandid` = '$mybandid'";

 

 

[ginerjm]

You are querying for an id value yet you are showing us your data having that value in mybandid.   Problem?

[PNewCode]

Sorry everyone. I had a crash from a diabetic incident and was gone. @AKADRUMMIN nailed it. The fix was `mybandid` = '$mybandid. That resolved it. I'm in shock that I missed something so simple to fix and a little disappointed in myself for not catching that but, hey... this is how we learn right? Thank you so much everyone. Now I know how to make that work and why. I appreciate everyones input sooooo very much!

Edited May 12, 2023 by PNewCode