trjhtr

I am very new to classes, and not sure how efficient this class is, would appreciate improvement feedback and info on efficiency including speed.

The class includes a construct that creates a connection to the database and two other functions that get data from the database and return some values concated with some HTML.

This is made from code that I've found around the web mostly SO, so would be good if suggestions were easy to understand for a novice.

Can improvements be made to this so that it is efficient and best practices are incorporated?

<?php
class MyClass 
protected $PDO;

public function __construct(PDO $PDO) 
 $this->PDO = $PDO;


public function getCount($sq,$bkid,$bkname) 
 $sth = $this->PDO->prepare("SELECT COUNT(sentence) AS b FROM sentence, chapters, books WHERE MATCH(sentence) AGAINST (:q IN BOOLEAN MODE) AND chapters.book_Id = books.book_Id AND sentence.chapter_Id = chapters.chapter_Id AND books.book_Id = :bk");
 $sth->execute(array(':q' => $sq, ':bk' => $bkid));
 $row = $sth->fetch(PDO::FETCH_ASSOC);
 $total = $row['b'];
 if($total!=0)
 
 $ensq = urlencode($sq);
 $count = "<a href='filter.php?id=$bkid&amp;q=$ensq'>$bkname</a><sup>$total</sup> ";
 return $count;
 
 else
 
 return;
 


public function getwriter($sqn) 
 $nsth = $this->PDO->prepare("SELECT COUNT(DISTINCT(writer)) AS n FROM sentence WHERE MATCH(writer) AGAINST (:q IN BOOLEAN MODE)");
 $nsth->execute(array(':q' => $sqn));
 $nrow = $nsth->fetch(PDO::FETCH_ASSOC);
 $ntotal = $nrow['n'];
 if($ntotal!=0)
 
 $nensq = urlencode($sqn);
 $ncount = "<a href='nsearch.php?q=$nensq'>writers</a><sup>$ntotal</sup> ";
 return $ncount;
 
 else
 
 return;
 


public function getChapter($cqn) 
 $csth = $this->PDO->prepare("SELECT COUNT(chapter_Title) AS c FROM chapters WHERE MATCH (chapter_Title) AGAINST (:q IN BOOLEAN MODE)");
 $csth->execute(array(':q' => $cqn));
 $crow = $csth->fetch(PDO::FETCH_ASSOC);
 $ctotal = $crow['c'];
 if($ctotal!=0)
 
 $censq = urlencode($cqn);
 $ccount = "<a href='csearch.php?q=$censq'>Chapters</a><sup>$ctotal</sup> ";
 return $ccount;
 
 else
 
 return;
 


$dbh = new PDO('mysql:host=localhost;dbname=dbname;charset=utf8', 'db', 'pass');
?>

And also is it ok to call the function multiple times next to each other like so:

$t = new MyClass($dbh);
echo $t->getCount($search,11,"Book 1");
echo $t->getCount($search,4,"Book 2");
echo $t->getCount($search,39,"Book 3");
echo $t->getCount($search,56,"Book 4");
echo $t->getWriter($search);
echo $t->getChapter($search);
$dbh = null;

The most important thing you have to learn about classes: every method should be responsible for one task only. Means a method that gets you a count should return you a count, not some HTML.

Two other minor improvements would be a better SQL formatting, so anyone could actually read it and a nice helper function that exists in PDO, fetchColumn():

So your methods should be like

public function getCount($sq,$bkid) 
 $sql = "SELECT COUNT(sentence)
 FROM sentence, chapters, books 
 WHERE MATCH(sentence) AGAINST (:q IN BOOLEAN MODE) 
 AND chapters.book_Id = books.book_Id 
 AND sentence.chapter_Id = chapters.chapter_Id 
 AND books.book_Id = :bk"
 $sth = $this->PDO->prepare($sql);
 $sth->execute(array(':q' => $sq, ':bk' => $bkid));
 return $sth->fetchColumn();

Whereas for the HTML formatting there should be another method/function. Let me suggest to you something like this:

function getLink($title, $count, $parameters)

 if ($count)
 
 $qs = http_build_query($parameters);
 $title = htmlspecialchars($title);
 return "<a href='filter.php?$qs'>$title</a><sup>$total</sup>n";
 

it could be used like this:

$count = $t->getCount($search, 11);
echo getLink("Book 1", $count, ['id' => 11, 'q'=> $search]);

Here, as you can see, a handy http_build_query() would take care of encoding your keys and values.