Create a Database connection class and delete function

iseriouslyneedhelp · July 13, 2012

Okay, so I'm learning OOP and want to get some help/advice from all you pros out there as I have done quite a bit of reading and watching tutorials but haven't quite grasped the concept. Please direct me where I'm wrong and show me in code how to fix anything I've done wrong. Thanks!

I have a database of leads and I want to write a query to delete, but first I'm writing a database connection class. So here I go:

config.php // file name with db connection class

<?php

class DbConn {

	public function __construct($dbhost, $dbuname, $dbpword, $dbname) {

		$this->dbhost = $dbhost;
		$this->dbusername = $dbuname;
		$this->dbpword = $dbpword;
		$this->dbname = $dbname;	

	}

	public function openConnection() {

		$this->connection = mysqli_connect($this->dbhost, $this->dbuname, $this->dbpword, $this->dbname) or die(mysqli_error());
	}

}

$conn = new mysqli('dbhost', 'dbuname', 'dbpword', 'dbname')

?>

delete.php // file with delete function which is called from my index.php (below)

<?php
require("/config.php");
$db = new DbConn($dbhost, $dbuname, $dbpword, $dbname);

// DELETE ROW


function delcontact ($db) {

$ID = $_GET['ID']; // not sure if this should go before or after if(isset...

    if(isset($_GET['ID']) && $_GET['ID'] == "$ID") {

    $db->query("DELETE FROM contacts WHERE ID = '$ID'");
	header('Location: ' . $_SERVER['HTTP_REFERER']);
}
}
?>

index.php // html file

<?php  session_start(); require_once("/config.php"); ?>
<!DOCTYPE html>
<html>
<head>
<script type="text/javascript" src="/jquery-1.7.2.js"></script>
</head>
<body>
      <table class="results" id="results">
        <tbody>
<?php // this is actually in another file I call in but for this question I'm including it here

function showresults($conn) {

// DEFAULT RESULTS

    $status = 'New';

// STATUS RESULTS ('New', 'Hot', 'Warm', 'Cold', 'Rejected', 'Closed')

if (isset($_GET['status']) && !empty($_GET['status'])) {
$status = $conn->escape_string($_GET['status']);
}

$query = "SELECT ID,date,firstname,lastname,spousefirst,spouselast,primarybday,spousebday,phonecell,phonehome,phoneoffice,spousecell,phoneother,email,emailspouse,emailother,emailspouseother,address,suite,city,state,zipcode,addressother,suiteother,cityother,stateother,zipcodeother,agentassigned,contacttype,status,contactsource,timing,password,subscribesearches,subscribedrips FROM contacts WHERE contacttype IN ('Buyer','Seller','Buyer / Seller','Investor') AND status = '$status' ORDER BY date DESC";

$stmt = $conn->prepare($query);

// BIND AND EXECUTE

	$stmt->execute();
	$stmt->bind_result($ID,$date,$firstname,$lastname,$spousefirst,$spouselast,$primarybday,$spousebday,$phonecell,$phonehome,$phoneoffice,$spousecell,$phoneother,$email,$emailspouse,$emailother,$emailspouseother,$address,$suite,$city,$state,$zipcode,$addressother,$suiteother,$cityother,$stateother,$zipcodeother,$agentassigned,$contacttype,$status,$contactsource,$timing,$password,$subscribesearches,$subscribedrips);

    while ($stmt->fetch()) {

    echo'
             <tr>
				<td><input type="checkbox" name="checked[]" value="'.$ID.'"></td>
			<td><a href="/cms/view/?ID='.$ID.'"><strong>'.$firstname.' '.$lastname.'</strong></a></td>
			<td>'.$phonecell.'</td>
			<td><a href="mailto:'. $email.'">'.$email.'</a></td>
			<td>'.date("M jS, g:i A", strtotime($date)).'</td>
			<td>'.$contactsource.'</td>
			<td>'.$status.'</td>
			<td>'.$contacttype.'</td>
			<td>'.$agentassigned.'</td>
			<td><a href="/cms/contacts/notes.php?ID='.$ID.'">V </a>+</td>
			<td><a href="/cms/contacts/todo.php?ID='.$ID.'">V </a>+</td>
			<td><a href="javascript: removelead('.$ID.')">D</a></td>
           </tr>';
}
}
?>
        </tbody>
      </table>
    </div> 
</body>
</html>

java.js // this is my javascript file that asks the user if they are sure they want to delete the contact/lead

function removelead(ID) {
if (confirm("Are you sure you want to delete this lead?")) {
window.location = '/delete.php?ID='+ID;
  }
return false;
}

Currently, it's not deleting any leads and instead goes to my delete.php page, which is blank and stays there instead of following the header location. Any help understanding what I've done wrong in my query would be much appreciated. If you can show me in code, it would be even more appreciated as I learn by visual examples.

FYI - I shortened my index.php file for this question, I actually haven't put it in the browser to check it, just trying to keep the question as short as possible.

Thanks in advance for your help and advice!

ignace · July 13, 2012

You never call the function delcontact in delete.php.

scootstah · July 13, 2012

Once again, that class is doing nothing. You need to be returning the mysqli object, and you are not even calling the openConnection() method.

At the very least, the class should look something like this:

<?php

class DbConn {
private $dbhost;
private $dbusername;
private $dbpword;
private $dbname;
private $conn;

public function __construct($dbhost, $dbuname, $dbpword, $dbname)
{
	$this->dbhost     = $dbhost;
	$this->dbusername = $dbuname;
	$this->dbpword    = $dbpword;
	$this->dbname     = $dbname;
}

public function openConnection()
{
	$this->conn = @new mysqli($this->dbhost, $this->dbusername, $this->dbpword, $this->dbname);

	if ($this->conn->connect_error) {
		// ... handle the connection error
	}

	return $this->conn;
}
}

Also, remove the new mysqli(...) from underneath the class definition, it isn't needed.

Here's the thing, though. Even with the updated class above, the class still doesn't do anything. It has no methods, and it can't interact with the mysqli class. All you are doing is instantiating a class to return a mysqli object. A wrapper class offering no additional functionality, is pointless. mysqli already has an OOP interface, you don't need to wrap it in a class.

You could create a Singleton wrapper if you wanted, which would return a mysqli object, but the object can only ever exist once. This is a common thing, but I have to warn you not to let Singletons get to your head - they might seem useful and the best thing since sliced bread, but they really aren't. They open up a whole bunch of problems for unit testing and such, so use them wisely.

On to your delete function. It is a bit of a mess. It is not really reusable and is pretty limited in its use. To make it a little more dynamic, you ought to pass the ID as a second parameter. That way, the ID doesn't always have to come from a querystring. Something like this:

function delete_contact($db, $id)
{
// make sure $id is an int
$id = (int) $id;

// run the query
$result = $db->query("DELETE FROM contacts WHERE id=$id");

return $result;
}

You can call it like this: delete_contact($db, $_GET['ID']);

Or like this: delete_contact($db, 17);

Or like this: delete_contact($db, someFunctionToGetAnID());

So you see, now it is more flexible. You might also note that I didn't put the redirect in there. The redirect shouldn't really be part of the function, that makes it too rigid. Put the redirect underneath the function call if/when you want a redirect. It now also returns a mysqli result, in case you need to work with it.

I hope some of my rambling helped out.

Sign In

Create a Database connection class and delete function

Recommended Posts

iseriouslyneedhelp

Link to comment

Share on other sites

ignace

Link to comment

Share on other sites

scootstah

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information