[Løst] Forbedre en DB klasse med div. bugs

[andrew92]

Hei

 

Jeg er litt fersk innen oop og prøver stadig å forbedre meg.

 

Har lagd en klasse (DBHandler) som benytter PDO til å koble seg mot mysql databaser.

 

Målet med denne klassen er først og fremst å teste ut litt oop samt bli mer vandt til pdo og bruk av prepared satements.

 

Lagde klassen da jeg fant ut at det ikke finnes en metode for å hente ut antall rader for en select spørring. Så om jeg har en tabell som heter "users" og skriver SELECT * FROM users, så er det ingen måte å hente ut antall rader den spørringen gir. Eneste løsningen er å sende en ny spørring som da blir SELECT COUNT(*) as total_rows FROM users. Dette synes jeg virket litt tungvindt og har prøvd å ordne en egen metode som genererer count spørringen for deg.

 

Nå over til problemet. Om man ser på funksjonen countQuery så fungerer den relativt greit, men jeg finner ikke ut hvordan jeg skal få funksjonen til å ta høyde for subqueries til høyre for det ytterste "from" nøkkelordet. Noen som vet om dette kan la seg gjøres på noe vis eller prøver jeg på noe håpløst? Slik den fungerer nå så fjerner den alle subqueries noe som fungerer fint så fremt det er til venstre for "from" nøkkelordet, men om jeg fjerner subqueries til høyre for det ytterste "from" nøkkelordet er denne subquerien et kriterie som er avgjørende for resultatet av spørringen som igjen vil påvirke antall rader. Muligens forvirrende forklart, men håper noen forstår

 

Eksempel på bruk av klassen:

<?php

include 'DBHandler.php';

define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_NAME', 'testdb');

$con = new DBHandler();

$stmt = $con->query(
	"SELECT * FROM users WHERE email = :email AND password = :password",
	array(':email' => $_GET['email'], ':password' => md5($_GET['password']))
);

if ($con->count() <= 0)
	die('Invalid login');

$data = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($data);

$con->close();

?>

DBHandler klassen:

 

PS. Beklager at jeg har forkortet variabler til $a $k $r $v osv. forstår at dette gjør ting mye mer rotete..

<?php

class DBHandler {

	public function __construct($host = DB_HOST, $user = DB_USER, $pass = DB_PASS, $dbname = DB_NAME, $charset = 'utf8'){
		$dsn = 'mysql:host=' . $host . ';dbname=' . $dbname . ';charset=' . $charset;
		
		$options = array(
			PDO::ATTR_PERSISTENT    => true,
			PDO::ATTR_ERRMODE       => PDO::ERRMODE_EXCEPTION
		);

		try {
			$this->dbh = new PDO($dsn, $user, $pass, $options);
		} catch (PDOException $e) {
			$this->error = $e->getMessage();
		}
	}

	/*
		Remove sub queries and create a count query

		NB. Doesn't work properly with subqueries on the right side of the outer "from" keyword.
		However it does work with subqueries on the left side of the outer "from" keyword.
	 */
	private function countQuery($query) {
		$a = explode(' ', $query);

		foreach ($a as $k => $v)
			if (strtoupper(trim($v)) === 'SELECT' || strtoupper(trim($v)) === 'FROM')
				$a[$k] = strtoupper(trim($v));

		while (true) {
			$x = 0;
			for ($i = 0; $i < count($a); $i++) {
				if ($a[$i] === 'SELECT') {
					$x++;

					if ($x > 1)
						unset($a[$i]);
				}
			}

			$a = array_values($a);

			$y = 0;
			for ($i = (count($a)-1); $i > 0; $i--) {
				if ($a[$i] === 'FROM') {
					$y++;

					if ($y > 1)
						unset($a[$i]);
				}
			}

			$a = array_values($a);

			if (($x === 1 && $y === 1) || ($x === 0 && $y === 0)) break;
		}

		$x = array_search('SELECT', $a);
		$y = array_search('FROM', $a);

		if (!$x && !$y) return false;

		for ($i = ($x + 1); $i < ($y); $i++)
			unset($a[$i]);

		$a = array_values($a);

		$x = array_search('SELECT', $a);
		$y = array_search('FROM', $a);

		$f = "";
		for ($i = 0; $i < count($a); $i++)
			if ($i === 0)
				$f.= $a[$i] . " COUNT(*) as num_rows";
			else
				$f.= " " . $a[$i];

		return $f;
	}

	/**
	 * Fetches all parameters in a query, either in form of
	 * a questionmark or in form of colon followed by a word.
	 * 
	 * @param  [string] $query 	[an sql query]
	 * @return [array]    		[an array containing the found parameters or questionmarks]
	 */
	private function getParameters($query) {
		$a = explode(' ', $query);
		$r = array();

		foreach ($a as $k => $v)
			if (substr($v, 0, 1) === ':' || substr($v, 0, 1) === '?')
				$r[] = $v;

		return $r;
	}

	private function execute($parameters = array()) {
		if (count($parameters) === 0)
			$this->stmt->execute();
		else
			$this->stmt->execute($parameters);
	}

	public function count() {
		$stmt = $this->query(
				$this->countQuery($this->lastquery),
				$this->lastparameters
			);
		return $stmt->fetchColumn();
	}

	public function query($query, $parameters = array()) {
		$this->lastquery = $query;
		$this->lastparameters = $parameters;

		$this->stmt = (count($this->getParameters($query)) === 0 ? $this->dbh->query($query) : $this->dbh->prepare($query));
		
		if (count($parameters !== 0))
			$this->execute($parameters);
		
		return $this->stmt;
	}

	public function close() {
		$this->dbh = null;
		$this->stmt = null;
	}
}

?>

Endret 1. august 2016 av andrew92

[Crowly]

Så om jeg har en tabell som heter "users" og skriver SELECT * FROM users, så er det ingen måte å hente ut antall rader den spørringen gir. Eneste løsningen er å sende en ny spørring som da blir SELECT COUNT(*) as total_rows FROM users.

Hvorfor ikke bare hente ut alle rader først i en array, og så telle opp antall forekomster?

 

Modifisert eksempel fra http://php.net/manual/en/pdostatement.fetchall.php

$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll();

$numRows = count($result ?: []); // antall rader i selecten

[andrew92]

 

Så om jeg har en tabell som heter "users" og skriver SELECT * FROM users, så er det ingen måte å hente ut antall rader den spørringen gir. Eneste løsningen er å sende en ny spørring som da blir SELECT COUNT(*) as total_rows FROM users.

Hvorfor ikke bare hente ut alle rader først i en array, og så telle opp antall forekomster?

 

Modifisert eksempel fra http://php.net/manual/en/pdostatement.fetchall.php

$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll();

$numRows = count($result ?: []); // antall rader i selecten

 

Forslaget du kommer med er absolutt en fungerende løsning, men gir et stort ytelse-problem.

 

Tok en liten test for å se hvor lang tid de forskjellige spørringene ville ta.

 

Har en stor tabell med litt over 800 000 rader bare for å vise hvordan dette vil være et problem for spørringer som gir mange rader tilbake.

 

SELECT COUNT(*) as num_rows FROM `logs`;

 

Første gang: 1.048s

Andre gang: 1.021s

Tredje gang: 0.800s

Fjerde gang: 0.924s

Femte gang: 1.001s

 

Gjennomsnitt: 0,9588s

 

 

SELECT `id` FROM `logs`;

 

Første gang: 16.162s

Andre gang: 16.087s

Tredje gang: 18.681s

Fjerde gang: 16.163s

Femte gang: 16.228s

 

Gjennomsnitt: 16.6642s

 

Spørringene er utført mot en ekstern databaseserver, mulig dette har en innvirkning på resultatet, men man ser fremdeles hvor betydelig forskjell det er snakk om.

 

Jeg har ikke lagt ved noen "where" kriterier bare for å demonstrere hvordan dette vil fungere når man arbeider med tabeller med mange rader.

Endret 1. august 2016 av andrew92

[Crowly]

Hvis du kun skal telle opp antall rader i tabellen så kjørt en SELECT COUNT(*), men hvis du skal hente ut data så er ikke eksemplet ditt over relevant, siden du skal ha du ut X antall rader uansett. Slik jet oppfattet spørsmålet ditt er at du skal gjøre en SELECT for å få ut data, samtidig som du vil vite hvor mange rader som er i data settet du nettopp hentet ut. Skal du sammenligne ytelse så blir det SELECT COUNT(*) mot count($result).

 

class db
{
    private $numRows;

    public function getRows()
    {
        return $this->numRows;
    ]

    // denne kan gjøres langt mer elegant, men viser prinsippet
    public function select($sql)
    {
        $sth = $dbh->prepare($sql);
        $sth->execute();

        $data = $sth->fetchAll();

        $this->numRows = count($result ?: []); 

        $this->numRows = $sth->rowCount (); // alternativt *

        return $result;

        // alternativt
        return [
            'data' => $result,
            'rows' => $this->numRows, 
        ];
    }
}

* http://php.net/manual/en/pdostatement.rowcount.php

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

Endret 2. august 2016 av Crowly

[andrew92]

Hvis du kun skal telle opp antall rader i tabellen så kjørt en SELECT COUNT(*), men hvis du skal hente ut data så er ikke eksemplet ditt over relevant, siden du skal ha du ut X antall rader uansett. Slik jet oppfattet spørsmålet ditt er at du skal gjøre en SELECT for å få ut data, samtidig som du vil vite hvor mange rader som er i data settet du nettopp hentet ut. Skal du sammenligne ytelse så blir det SELECT COUNT(*) mot count($result).

 

class db
{
    private $numRows;

    public function getRows()
    {
        return $this->numRows;
    ]

    // denne kan gjøres langt mer elegant, men viser prinsippet
    public function select($sql)
    {
        $sth = $dbh->prepare($sql);
        $sth->execute();

        $data = $sth->fetchAll();

        $this->numRows = count($result ?: []); 

        $this->numRows = $sth->rowCount (); // alternativt *

        return $result;

        // alternativt
        return [
            'data' => $result,
            'rows' => $this->numRows, 
        ];
    }
}

* http://php.net/manual/en/pdostatement.rowcount.php

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

 

 

Eksempelet jeg skrev i første innlegg var bare for å vise hvordan man benytter seg av klassen. Ønsker ikke å hente ut data og antall rader samtidig.

 

Det jeg ønsker å oppnå er om man kun ønsker å utføre en select spørring med visse kriterier og man kun ønsker ut antall rader den spørringen gir.

 

Og siden det kun er snakk om en telle-funksjon som har som eneste hensikt å hente ut antall rader. Om jeg henter ut all data som du viste til så blir ytelses-problemet som nevnt over et reelt problem ettersom det ikke er nødvendig for å få returnert antall rader.

 

Som du også skriver så sier du at det blir riktig å sammenligne SELECT COUNT(*) mot count($result), men arrayet $result må jo ha fått dataen via en spørring, og det er denne spørringen som er krevende og unødvendig når man kun ønsker antall rader.

 

http://php.net/manual/en/pdostatement.rowcount.php

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned. Your application can then perform the correct action.

 

 

Ønsker altså at man skal kunne generere en SELECT COUNT(*) spørring basert på forrige spørring som ble utført så fremt det er snakk om en select spørring. Var det en update/delete/insert spørring kan man fint benytte rowCount som du viser til.

Endret 2. august 2016 av andrew92