Organizing a class of SQL queries

PHP
1

I am looking for a good way to organize some files in a way that accords with good organizational practices.

I have a class that contains several SQL queries on a database table. A second table was created that is identical to the first except that now to look for an individual row one more value has to be provided to the WHERE clause of a query.

Some example code:

class Table1
{
	public function getRowByValue($value1) {
		$query = "SELECT name, age, address FROM table1 WHERE value1 = :value1";
		
		$db->prepare($query);
		$db->bindValue(':value1', $value1);
		$db->execute();
		
		return $db->fetch();
	}
}

class Table2
{
	public function getRowByValue($value1, $value2) {
		$query = "SELECT name, age, address FROM table2 WHERE value1 = :value1 AND value2 = :value2";
		
		$db->prepare($query);
		$db->bindValue(':value1', $value1);
		$db->bindValue(':value2', $value2);
		$db->execute();
		
		return $db->fetch();
	}
}

All of the columns are identical in each table (15+), but table2’s primary key is different, so in order to find a unique row, an additional value is needed. (This is by design and cannot be changed.)

I thought of two ways to do this. The first was to make two different classes and just have a lot of duplicate code, as above. (The functions are more complicated and longer than the example.) But any change in class1 would have to be replicated in class2, something that can lead to errors overtime since things are not all in one place. The second was to check if value2 was given and execute a different query if it was:

class Table1
{
	public function getRowByValue($value1, $value2 = null) {
		if ($value2 === null) {
			$query = "SELECT name, age, address FROM table1 WHERE value1 = :value1";
			
			$db->prepare($query);
			$db->bindValue(':value1', $value1);
			$db->execute();
		}
		else {
			$query = "SELECT name, age, address FROM table2 WHERE value1 = :value1 AND value2 = :value2";
			
			$db->prepare($query);
			$db->bindValue(':value1', $value1);
			$db->bindValue(':value2', $value2);
			$db->execute();
		}
		
		
		return $db->fetch();
	}
}

Are there any better solutions to this? Or which one of these should I use?

2

Try this:

class Table1
{
public function getRowByValue
(
  string $query  = '',	
  string $value1 = '', 
  string $value2 = ''
)
:bool
{
  $db->prepare($query);
  
  if ( ''===$value2)
  {
	$db->bindValue(':value1', $value1);

  }else{
	$db->bindValue(':value1', $value1);
	$db->bindValue(':value2', $value2);
  }
  $db->execute();

  return true;
}  

}///
1 Like
3

Wouldn’t that require passing in the query string itself? The functions are meant to encapsulate the query, so that wouldn’t work in my case. (Sorry that wasn’t clear.)

4

Create abstract class with method fetch($sql, array $params = ) that provides automatically binding of associative array $params to $sql.

All your classes should to extend this abstract class and use its method.

2 Likes
5

Never is this the best way.

Better, but you could still remove some repetiton.
Where is the object $db coming from in that code?

…Why you don’t duplicate code.

This is where abstract or extended classes can help.

6

Thanks for the help. After some work, I implemented the abstract class idea.

7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.