7 Replies - 431 Views - Last Post: 27 April 2013 - 02:16 PM Rate Topic: -----

#1 adn258  Icon User is offline

  • D.I.C Addict

Reputation: 11
  • View blog
  • Posts: 758
  • Joined: 31-August 11

My Own Complicated Algorithm For Insertion Is This Worth It?

Posted 25 April 2013 - 02:18 PM

So I've been looking at things like CodeIgnitor etc. maybe I should be using them but they seem to have a lot of problems. I feel like there's something to be said for making your own class like I just did which handles all of this for you and if anyone here recommends or has done something like this in the past? I whipped this up this morning and I like my implementation.

It's for inserting into MYSQL database tables using prepared statements and it automatically handles EVERYTHING!! I'm only using the PDO types of string and INT thus far but the others could be easy added. Thus far it works fine on my test table of two columns I'm using it for. Have a look I would love your guys opinions. The $dbh part is a returning of a PDO connection object from another class I created :)/>.

Do other people do things like this on here? I feel this is more professional and in the long run WILL SAVE ME TIME BECAUSE THIS CLASS IS EASY TO IMPLEMENT SEE THE BOTTOM FOR IMPLEMENTATION;


<?php
require 'site_database.php';
interface iUpdateOrInsert
{
	function BindAndExecute();
}

interface iUpdateInsertVars
{
	function SetTable($Table);
	function SetValuesAry($Values);
}

class CUpdateInsertVars implements iUpdateInsertVars
{
	private $cValuesAry;
	private $cNumericAry;
	private $cNumericAryReversed;
	private $cTable;
		
	protected function GetNumericAryReversed()
	{
		return $this->cNumericAryReversed;
	}
	
	protected function GetNumericAry()
	{
		return $this->cNumericAry;
	}
	
	public function SetValuesAry($Values)
	{
		$this->cValuesAry = $Values;
		$this->cNumericAry = array_values($Values);
		$this->cNumericAryReversed = array_values(array_flip($Values));
	}
	
	public function SetTable($Table)
	{
		$this->cTable = $Table;
	}
	
	protected function GetTable()
	{
		return $this->cTable;
	}
	
	protected function ReturnPDOType($Data)
	{
		$Conversion = array(
		'string' => PDO::PARAM_STR,
		'integer' => PDO::PARAM_INT
		);
		
		$PDOTYPESTR = gettype($Data);
		$PDOTYPE = $Conversion[$PDOTYPESTR];
		return $PDOTYPE;
	}
	
}

class CUpdateTable extends CUpdateInsertVars implements iUpdateOrInsert
{
	
	private function CreateQuery()
	{
		$ReversedNumAry = $this->GetNumericAryReversed();
		
		$Query = "INSERT INTO ".$this->GetTable().'(';
		
		for ($i = 0; $i < count($ReversedNumAry); $i++)
		{
			if (!($i == (count($ReversedNumAry) -1)))
			{
			   $Query .= (($ReversedNumAry[$i].','));
			}
			else 
			{
				$Query .= $ReversedNumAry[$i];
			}
		}
		$Query .= ')VALUES(';
		
		for ($i = 0; $i < count($ReversedNumAry); $i++)
		{
			if (!($i == (count($ReversedNumAry) -1)))
			{
			  $Query .= '?,';
			}
			else 
			{
				$Query .= '?';
			}
		}
		$Query .= ')';
		return $Query;
	}
	
	public function BindAndExecute()
	{
		$NumAry = $this->GetNumericAry();
		$dbh = DB();
	    $stmt = $dbh->prepare($this->CreateQuery());
		
		for ($i = 0; $i < count($NumAry); $i++)
		{
		    $stmt->bindParam(($i+1),$NumAry[$i],$this->ReturnPDOType($NumAry[$i]));
		}
		
		if ($stmt->execute())
		{
			echo 'Testing Works Cool';
			die();
		}
	}
	
}
?>



To use this class I created all you have to do is this:

<?php
$test = new CUpdateTable();
$test->SetTable("testing");

$values = array(
"Name" => "Connoco",
"Location" => "Missoula"
);

$test->SetValuesAry($values);
$test->BindAndExecute();
?>



DONE!!

You just create an array of the string column in the SQL table you set in this case testing and the columns are Name And Location

The two corresponding values in the array are added automatically....well this has been fun this morning and I feel this class it BETTER for my projects then code ignitor and it needs a little more work but I have a headache....in any case opinions on whether stuff like this is worth it? I know a lot of you are saying just use the libraries out there but somehow it seems like in the long run I run into less problems if I actually coded something myself.

I'll totally admit to you guys though that some libraries would take forever and be a waste of time coding so you should use another API but for PHP in this case this might be better I don't know.

This post has been edited by adn258: 25 April 2013 - 02:21 PM


Is This A Good Question/Topic? 0
  • +

Replies To: My Own Complicated Algorithm For Insertion Is This Worth It?

#2 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3092
  • View blog
  • Posts: 10,360
  • Joined: 12-December 12

Re: My Own Complicated Algorithm For Insertion Is This Worth It?

Posted 25 April 2013 - 03:16 PM

Personally, I'm in favour - although, I should qualify this by stating that I haven't studied your specific code.

You'll want to keep it (as I'm sure you are already aware) very generic, so that you can re-use it in other projects - and fill it full of comments :)

Of course, the risk is that you might get too involved in the process and end up building something like CI. But, hey, if you eventually work with CI you'll have a better understanding of how such a system works.

This post has been edited by andrewsw: 25 April 2013 - 03:17 PM

Was This Post Helpful? 0
  • +
  • -

#3 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,675
  • Joined: 30-January 09

Re: My Own Complicated Algorithm For Insertion Is This Worth It?

Posted 25 April 2013 - 06:20 PM

There's two other datatypes you'll want to cater for when binding - PDO::PARAM_BOOL and PDO::PARAM_NULL. If you're using PHP 6+ then there's a new datatype PDO::PARAM_LOB as well. I've used the following code for binding types:
private static function GetValueType($value)
{
	if (is_int($value))								return PDO::PARAM_INT;
	if (is_string($value) or is_numeric($value))	return PDO::PARAM_STR;
	if (is_bool($value))							return PDO::PARAM_BOOL;
	if (is_null($value))							return PDO::PARAM_NULL;

	// if (is_binary($value)) return PDO::PARAM_LOB; // PHP 6
		
	$emsg = 'Datatype of parameters (' . gettype($value) . ') is currently not supported';
	throw new TransactionException($emsg, 1);
}


MySQL Probably doesn't care about boolean types, as it's 'bit' datatype is actually 'tinyint', but if you're connecting to DBs that handle booleans properly (like PostgreSQL) then you'll want to use PDO::PARAM_BOOL. The null datatype should definitely be in there regardless of your DB.
Was This Post Helpful? 0
  • +
  • -

#4 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3709
  • View blog
  • Posts: 5,957
  • Joined: 08-June 10

Re: My Own Complicated Algorithm For Insertion Is This Worth It?

Posted 26 April 2013 - 05:11 AM

If you feel this saves you time, then by all means go for it. Just as long as you know there are existing libraries out there that can also take care of things like that for you. - I'm always a fan of the Doctrine DBAL for stuff like this.


I would have to protest the use of "Ary" in place of "Array" though. It bugs me to no end when people unnecessarily abbreviate things. It rarely improves anything, but often makes the code less understandable.

In the beginning you may just be innocently shortening "Array" to "Ary", or "User" to "Usr", but before long you may find yourself writing all you code like this:
$inst = UsrAryMgrFctry::crn($usrLst);
$pts = $inst->splt2maxPcsSz(10);
foreach ($pts as $pt) {
    pntUsrTblWoBds($pt);
}


And then it's just a matter of time before somebody in the office loses it and starts throwing punches! :)
Was This Post Helpful? 2
  • +
  • -

#5 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3092
  • View blog
  • Posts: 10,360
  • Joined: 12-December 12

Re: My Own Complicated Algorithm For Insertion Is This Worth It?

Posted 26 April 2013 - 05:34 AM

That Doctrine looks very interesting. There are also a number of minimal (or slim) PHP frameworks that might be worth looking into, as compared to CodeIgniter or Cake (and others). I'm not in a position to recommend one, but a search of "PHP minimal framework" yields a number of results.
Was This Post Helpful? 0
  • +
  • -

#6 adn258  Icon User is offline

  • D.I.C Addict

Reputation: 11
  • View blog
  • Posts: 758
  • Joined: 31-August 11

Re: My Own Complicated Algorithm For Insertion Is This Worth It?

Posted 27 April 2013 - 02:01 AM

View Postandrewsw, on 26 April 2013 - 05:34 AM, said:

That Doctrine looks very interesting. There are also a number of minimal (or slim) PHP frameworks that might be worth looking into, as compared to CodeIgniter or Cake (and others). I'm not in a position to recommend one, but a search of "PHP minimal framework" yields a number of results.


Now correct me if I'm wrong but obviously you take for example my class I created for this it uses some for loops and what not so it WILL SLOW DOWN THE OPERATION TO SOME DEGREE but for the small loops and what not is this an amount event detectable or noticeable using classes like these?
Was This Post Helpful? 0
  • +
  • -

#7 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3709
  • View blog
  • Posts: 5,957
  • Joined: 08-June 10

Re: My Own Complicated Algorithm For Insertion Is This Worth It?

Posted 27 April 2013 - 02:25 AM

Depends on how you use them. Deeply nested loops can have a very bad effect on performance, so if a class method uses a loop and that class method is called inside a loop that is perhaps call in yet another loop, then you should start getting worried. But if you are using this method just once or twice per execution, it's nothing to get concerned about.

It's always a good idea to keep optimization in mind, but not go overboard optimizing things unless you have reason to. Make the code, and then if you need performance improvements, profile it and optimize the bottlenecks.

By the way, since we are talking about loops here. In your CUpdateTable::CreateQuery method you use two loops to create your INSERT query. Neither of those loops is actually needed. The implode() and str_repeat() functions can be used to achieve the same results, without any PHP loops.
	private function CreateQuery()
	{
		$ReversedNumAry = $this->GetNumericAryReversed();
		
		$Query = "INSERT INTO ".$this->GetTable().'(';
		$Query .= implode(",", $ReversedNumAry);
		$Query .= ')VALUES(';
		$Query .= rtrim(str_repeat("?,", count($ReversedNumAry)), ",");
		$Query .= ')';
		
		return $Query;
	}


Was This Post Helpful? 0
  • +
  • -

#8 adn258  Icon User is offline

  • D.I.C Addict

Reputation: 11
  • View blog
  • Posts: 758
  • Joined: 31-August 11

Re: My Own Complicated Algorithm For Insertion Is This Worth It?

Posted 27 April 2013 - 02:16 PM

Atli thank you I didn't know you could do that. So what do you do? When you are creating a professional web site what's the RIGHT WAY to do things? Do you use another library/API. What's your API? Do you code everything by hand? What's the right way?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1