wickyaswal / php-my-sql-pdo-database-class Goto Github PK
View Code? Open in Web Editor NEWA database class for PHP MySQL which utilizes PDO.
License: Other
A database class for PHP MySQL which utilizes PDO.
License: Other
hey again :) , I think you should add this line to the begining of 'settings.ini.php' :
;
this will make it inaccessible. you know this file contains Database informations.
$array['time_update'] = strtotime("now");
while ($temp = $db->row('SELECT id, idYt FROM Watch WHERE time_update < :time_update', $array)) { //ONLY FOR TEST
$array['id'] = $id = $temp['id'];
$array['idYt'] = $idYt = $temp['idYt'];
$temp = null;
$G_API_KEY = 'G_API_KEY';
$JSON = file_get_contents("https://www.googleapis.com/youtube/v3/videos?part=snippet%2Cstatistics%2CcontentDetails%2Cstatus%2CliveStreamingDetails&id={$idYt}&fields=items%2CvisitorId&key={$G_API_KEY}");
$JSON_Data = json_decode($JSON, true);
$JSON = null;
if (isset($JSON_Data['items'][0]['statistics']['viewCount'])) {
$array['views'] = $JSON_Data['items'][0]['statistics']['viewCount'];
$array['likes'] = $JSON_Data['items'][0]['statistics']['likeCount'];
$array['dislike'] = $JSON_Data['items'][0]['statistics']['dislikeCount'];
$array['favorite'] = $JSON_Data['items'][0]['statistics']['favoriteCount'];
$array['comments'] = $JSON_Data['items'][0]['statistics']['commentCount'];
//$array['tags'] = gzcompress(base64_encode(serialize($JSON_Data['items'][0]['snippet']['tags'])));
$array['title'] = $JSON_Data['items'][0]['snippet']['title'];
$array['description'] = $JSON_Data['items'][0]['snippet']['description'];
//$array['thumbnails'] = https://i.ytimg.com/vi/pJr2NUlQ6Es/maxresdefault.jpg",
$array['categoryId'] = $JSON_Data['items'][0]['snippet']['categoryId'];
$array['publicStatsViewable'] = $JSON_Data['items'][0]['status']['publicStatsViewable'];
$array['uploadStatus'] = $JSON_Data['items'][0]['status']['uploadStatus'];
$array['embeddable'] = $JSON_Data['items'][0]['status']['embeddable'];
$array['privacyStatus'] = $JSON_Data['items'][0]['status']['privacyStatus'];
print_r($array);
$db->query('UPDATE Watch SET view=:views, `like`=:likes, dislike=:dislike, favorit=:favorite,`commen`=:comments, title=:title,description=:description, categoryId=:categoryId,publicStatsViewable=:publicStatsViewable, uploadStatus=:uploadStatus, embeddable=:embeddable,privacyStatus=:privacyStatus, time_update=:time_update WHERE id = :id', $array);
} else {
echo "\nYT error";
} }
SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
Raw SQL : UPDATE Watch SET view=:views, like
=:likes, dislike=:dislike, favorit=:favorite, commen
=:comments, title=:title,
description=:description, categoryId=:categoryId, publicStatsViewable=:publicStatsViewable, uploadStatus=:uploadStatus, embeddable=:embeddable,
privacyStatus=:privacyStatus, time_update=:time_update WHERE id = :id
Unhandled Exception.
SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
You can find the error back in the log.
is there any specific method show results with pagination.? Or any third party class can be used?
in the file ./easyCRUD/easyCRUD.class.php , in the line 8 : "include "../Db.class.php", there is an issue when we want to use the class "Crud", I suggest to make it like that "include "DIR.'/../Db.Class.php'; ". after that we can juste include the file containing the "crud" class, and everything will be ok/
$person = new person(array("Firstname"=>"John","age"=>"20","sex"=>"F","Id"=>"4")); $saved = $person->Save();
it gonna be
"UPDATE persons SET Firstname = 'John',Age = 20, Sex = 'F' WHERE Id= 4"
how about
"UPDATE persons SET Age = 20, WHERE Sex = 'F'"
and the primarary still id
i have try
$person = new person(array("age"=>"20","sex"=>"F"));
Would be nice if you followed PSR autoloading conventions
on my web hosting service it appears that the mysql database wasn't set all the way to utf-8 which led to encoding problems with special chars.
in order to avoid this you can / should modify line 60 of the Db.class.php to
$this->pdo = new PDO($dsn, $this->settings["user"], $this->settings["password"], array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
PSR-1 Coding Standards would be nice too. :)
hey. anybody. how to disconnect or close connection?
persons and Persons
It is persons in the DB and Persons in code do not match..
Using the EasyCRUD - If I am going to update something, the find method can work fine if I am using as pk and id ... but if I am going to try to find for example an email account or a nickname to avoid a Duplicate entry there is nothing in the class that i can use for that request.
Should be nice to have some method that can handle it
my mysql version is 5.1.48 and it doesn't work ,
the error is "SQLSTATE[HY000]: General error: 1047 Prepare unsupported!"
We're able to get number of rows when statement is DELETE, INSERT or UPDATE, but we're unable to get information on how much rows were affected by SELECT. It would be great to have such instrument.
Another thing is there is no function to get lastInsertId value, which I found useful using another classes.
Sorry if I understood something in wrong way.
Hi,
I wanted to test some stuff with this class, but
whenever I use a JOIN, I get 0 results.
What am I doing wrong?
// PHP -----------------------------------
$sql = "
SELECT
p.Firstname fname,
p.Lastname lname
FROM
persons p
INNER JOIN
bloodtype b ON b.id = p.type_id
WHERE
b.type = :bloodtype
GROUP BY
p.id
ORDER BY
p.Lastname
";
$vars = array("bloodtype" => "A+");
$persons = $db->query($sql, $vars);
// SQL -----------------------------------
/*
Navicat MySQL Data Transfer
Source Server : MSQ
Source Server Version : 50137
Source Host : 10.52.1.110
Source Database : PDO_test
Target Server Version : 50137
File Encoding : utf-8
Date: 03/06/2015 10:37:53 AM
*/
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- Table structure for bloodtype
DROP TABLE IF EXISTS bloodtype
;
CREATE TABLE bloodtype
(
id
int(11) NOT NULL AUTO_INCREMENT,
type
varchar(5) NOT NULL,
PRIMARY KEY (id
)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
-- Records of bloodtype
BEGIN;
INSERT INTO bloodtype
VALUES ('1', 'O+'), ('2', 'O-'), ('3', 'A+'), ('4', 'A-'), ('5', 'B+'), ('6', 'B-'), ('7', 'AB+'), ('8', 'AB-');
COMMIT;
-- Table structure for persons
DROP TABLE IF EXISTS persons
;
CREATE TABLE persons
(
Id
int(11) NOT NULL AUTO_INCREMENT,
Firstname
varchar(32) NOT NULL,
Lastname
varchar(32) NOT NULL,
Sex
char(1) NOT NULL,
Age
tinyint(3) NOT NULL,
Type_id
int(11) NOT NULL,
PRIMARY KEY (Id
)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
-- Records of persons
BEGIN;
INSERT INTO persons
VALUES ('1', 'Johny', 'Doe', 'M', '19', '2'), ('2', 'Bob', 'Black', 'M', '40', '2'), ('3', 'Zoe', 'Chan', 'F', '21', '8'), ('4', 'Sekito', 'Khan', 'M', '19', '4'), ('5', 'Kader', 'Khan', 'M', '56', '3');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
Hi,
it'd be nice if you transfer your class into a namespace.
Db.class.php
<?php
namespace indieteq\pdo;
use \PDO;
/**
* DB - A simple database class ...
Log.class.php
<?php
namespace indieteq\pdo;
easyCRUD.class.php
<?php
/**
* Easy Crud - This class kinda works like ORM. Just created for fun :)
*
* @author Author: Vivek Wicky Aswal. (https://twitter.com/#!/VivekWickyAswal)
* @version 0.1a
*/
require_once(__DIR__ . '/../Db.class.php');
class Crud {
private $db;
public $variables;
public function __construct($data = array()) {
$this->db = new indieteq\pdo\DB();
...
index.php (root)
<?php
require("Db.class.php");
// Creates the instance
$db = new indieteq\pdo\Db();
...
P.S.: Thanks for the release under the Beerware License.
I'm using a simple update query like:
$db->query("UPDATE " . TBL_NAME . " SET title = :title, content = :content WHERE id = :id",array("title"=>$title,"content"=>$content,"id"=>$id));
Only my 'content' field/textarea is an WYSIWYG Editor, so it put HTML chars in the post, but the query does the update without the HTML tags (strong,p,li,etc.)
I saw on a forum that I probably need the following line:
$db->bindValue(':$content', $content, PDO::PARAM_STR);
instead of:
$db->bindValue(':$content', $content);
How do I get the 'PDO::PARAM_STR' connected to the 'content' field with this class? Or is there another way to do this
Can you please help me out??
Kind regards JK
Hi there,
First of all, I asked this question on stack overflow.
I want to fetch values printing content from two different tables. The main table (tblCorredor) has a field called "team", which is the same number as the id of the other table (tblEquipo). Got this :
$fetch = $db->query("SELECT
cor.strApe as strApe,
cor.str as str,
cor.id as id,
cor.valor as valor,
equi.str as equi_str,
equi.archivo as equi_archivo
FROM tblCorredor as cor
LEFT JOIN tblEquipo as equi
ON cor.team = equi.id
");
I get a NULL result while dumping $fetch. On phpMyAdmin the query is working fine, but on the PHP doesn't.
in the rules section, what about if one of the fields is a custom name or custom error? because sometimes the field needs to have to names and when the error is returned it has the name of the input field and sometimes it looks horrible
don't insert Special characters (ş, ğ etc..). and don't display. help me please
I set header of file to utf-8
and table collation is utf8_general_ci
and column is utf8_general_ci too
but output result is words like : یاشاسن
what can i do ?
Hi I have written a function to see if an id in a given table exists but it doesn't work any ideas why?
function doesExist($table,$where,$whereArray,$db)
{
$chk = $db->query("SELECT id FROM $table WHERE $where",$whereArray);
if(count($chk)>0)
{
echo $chk['id']; exit();
return $chk['id'];
}
else
{
return false;
}
}
It always returns false :( I pass the values to it like:
$table = "tablename";
$where = "field = :field";
$chkArray = ["field"=>"value"];
$chk = doesExist($table,$where,$chkArray,$db);
Hi!
First, really nice DB-class! My (really small but annoying) problem, when trying to run this:
$insert = $db->query("INSERT INTO projects(ID) VALUES(:id) ON DUPLICATE KEY UPDATE id = :id",array("id" => $key));
I get the following error message:
"Invalid parameter number"
Do I really have to create two parameters with the same value if they ought to be used twice in the same query? Doing so works but I would rather only have one parameter.
Hey guy, firstly, congrats for the great little project, it help me a lot. Thank you very much.
Depends on the syntax you use for the sql your code presents an error because you explode the query getting the first statement to see if it's a SELECT or a SHOW query. But if I try to make as the code below I got an error.
SELECT
p.`name`,
r.fk_prize,
r.options_module,
r.options_points,
r.created
FROM app1_ranking r
INNER JOIN app1_player p ON p.pk_player = r.fk_player
ORDER BY r.options_points
DESC LIMIT 7
It happen because the first char after SELECT key is a \n (new line char) and not a space, so, because you make an explode by space char it will not work correctly. If you replace your code in the line 186 by the code below it will work as well.
$rawStatement = explode(" ", preg_replace("/\s+|\t+|\n+/", " ", $query));
Thank you guy, good work!
How can I get rowcount from this class?
Hi
I want to call an sp with parameters. My code is:
$sql = "CALL getStatusSalaEsperaByMedicoFecha(:desde, :hasta);
$values = array('desde' => $desde, 'hasta' => $hasta);
$rows = db->query($sql, $values);
I am positive my SP is running fine but $rows is returned null...
Any hint ?
TIA,
Yamil
Hi,
any chance you could tell me where to set the charset to UTF-8 so I can output Chinese, Russian and other non-English characters from a db?
Thanks.
$this->succes = $this->sQuery->execute();
Lots of tightly coupled code. Consider using DI principles to make your code more testable.
I want to do something like this:
SELECT id, SUM(amount)
FROM Data
Group By id
Where "id" is the parameter I want to filter and amount is the quantity to SUM. Thanks in advance
Hi my database structure table:
table: ArtistSongs:
ID(int(10)) autoincrement
ArtistName (varchar(255))
SongTitle (varchar(255))
how to insert multiple rows from this array in one query with bind parameters
array(20) {
[0]=>
array(2) {
["artist"]=>
string(6) "Sandra"
["songtitle"]=>
string(19) "Celebrate Your Life"
}
[1]=>
array(2) {
["artist"]=>
string(7) "Maxthor"
["songtitle"]=>
string(9) "Flamingos"
}
.....
[20]=>
array(2) {
["artist"]=>
string(14) "Modern Talking"
["songtitle"]=>
string(39) "Brother Louie (Bassflow 3.0 Single Mix)"
}
PS Sorry for my bad english.
I had some problems with some of my queries and so I analyzed it and found out that the problem was a numerical zero value, which would cause the query not to run, also there was not error-log created or an error message, the query simply did not run.
Is this intended? Because every other numerical value works fine.
Please have a look at the following sample-code which may explain my problem a bit better:
// Does NOT work - query not executed
$db->bind("status", 0);
$stmt = $db->query("INSERT INTO user (status) VALUES (:status);");
// Works - query executed
$db->bind("status", "0");
$stmt = $db->query("INSERT INTO user (status) VALUES (:status);");
// Works - query executed
$db->bind("status", 1);
$stmt = $db->query("INSERT INTO user (status) VALUES (:status);");
// Works - query executed
$db->bind("status", "1");
$stmt = $db->query("INSERT INTO user (status) VALUES (:status);");
// Does NOT work - query not executed
$db->bindMore(array("status"=>0));
$stmt = $db->query("INSERT INTO user (status) VALUES (:status);");
// Works - query executed
$db->bindMore(array("status"=>"0"));
$stmt = $db->query("INSERT INTO user (status) VALUES (:status);");
// Works - query executed
$db->bindMore(array("status"=>1));
$stmt = $db->query("INSERT INTO user (status) VALUES (:status);");
// Works - query executed
$db->bindMore(array("status"=>"1"));
$stmt = $db->query("INSERT INTO user (status) VALUES (:status);");
// Works - query executed
$db->bind("name", '');
$stmt = $db->query("INSERT INTO user (name) VALUES (:name);");
// Works - query executed
$db->bindMore(array("name"=>''));
$stmt = $db->query("INSERT INTO user (name) VALUES (:name);");
Best Regards
Everything is handled as a parameter. Values should be supported to allow for null binds. Class should check if value and handle appropriately. e.g. bindValue(':param', null, PDO::PARAM_INT);
Hi.
My query not working
if($keyword){
$param['keyword'] = '%$keyword%';
if($keyType === "subject") $query .= " AND A.$keyType LIKE :keyword";
if($keyType === "title") $query .= " AND B.$keyType LIKE :keyword";
if($keyType === "name") $query .= " AND C.$keyType LIKE :keyword"
if(!$keyType) $query .= " AND (A.subject LIKE :keyword OR B.title LIKE :keyword OR C.name LIKE :keyword)";
}
$list = $Db->query("SELECT A.*, B.*, C.* FROM TABLE A, TABLE B, TABLE C WHERE A.no = B.no AND A.no = C.no $query", $param);
$keyType not value
" AND (A.subject LIKE :keyword OR B.title LIKE :keyword OR C.name LIKE :keyword)"
query not working(no error, no data)
how i used to temporarily
if($keyword){
$param['keyword'] = '%$keyword%';
$param['keyword_temp'] = '%$keyword%';
if($keyType === "subject") $query .= " AND A.$keyType LIKE :keyword";
if($keyType === "title") $query .= " AND B.$keyType LIKE :keyword";
if($keyType === "name") $query .= " AND C.$keyType LIKE :keyword"
if(!$keyType) $query .= " AND (A.subject LIKE :keyword OR B.title LIKE :keyword OR C.name LIKE :keyword_temp)";
}
$list = $Db->query("SELECT A.*, B.*, C.* FROM TABLE A, TABLE B, TABLE C WHERE A.no = B.no AND A.no = C.no $query", $param);
this code working
Db.class.php, line 177 instead of:
elseif (stripos($query, 'insert') === 0 || stripos($query, 'update') === 0 || stripos($query, 'delete') === 0) {
it would be better:
elseif (stripos(strtolower($query), 'insert') === 0 || stripos(strtolower($query), 'update') === 0 || stripos(strtolower($query), 'delete') === 0) {
because if I will write "INSERT" query instead of "insert" I will get NULL instead of rowCount.
Example:
$person->Firstname = "Kona";
$person->Age = "20";
$person->Sex = "F";
$creation = $person->Create();
$lastID = $db->lastInsertId();
// $lastID always returns 0
Am I doing something wrong, or is the code broken?
I can return the lastInsertID if I append it to the return results of query(); like so:
return array ('rowCount' => $this->sQuery->rowCount(),
'lastInsert' => $this->pdo->lastInsertId() );
Hi
I've just used the easyCrud files after modifying database information but when running index.php I get this error:
Fatal error: Cannot redeclare class Log in Log.class.php on line 8
after commenting the class for testing I got this error
Fatal error: Cannot redeclare class DB in Db.class.php on line 12
I didn't modify anything but the database information.
Hello there,
I have a problem with special characters. They are inserted to db like this äöäöä
.
Values come from POST
request, and they are tested with mb_detect_encoding
that they are UTF-8 encoded. The document has <meta charset="UTF-8">
, and database table has DEFAULT CHARSET=utf8;
Any suggestions what I can try? Thanks.
When use LIKE with a :params not working
I'm trying to do a query inserting and binding a null value in a nullable field but i'm not able to insert a "real" null value.
My code:
$db->query("INSERT INTO table (...,nullableField) VALUES (..., :nullVal)", array('nullVal'=>null));
in the DB null becomes an empty string or a zero.
I've also tried with the $db->bind() method but the result is the same...
Insert not work with utf8.
e.g.: ö -> ö
try
{
$db->query("INSERT INTO Users(Username,Password,Email,Age,Birthday) VALUES(:user,:pass,:email,:age,:birth)", array("user"=>"$user","pass"=>"$hash","email"=>"$email","age"=>"$age","birth"=>"$birthday"));
}
catch(Exception $e)
{
if (strpos($e,'Integrity constraint violation: 1062') !== false)
{
echo '{result:"Already a user",timestamp:"' . $timestamp . '"}';
}
}
Does nothing, leaving the error still visible.
Firts of all, I want to state that I'm not a super programer, I was having problems with spanish accents because there was a double UTF encoding,
So I fixed this line and now I can use accents:
public function bind($para, $value){
$this->parameters[sizeof($this->parameters)] = ":" . $para . "\x7F" . $value;
}
I think, in DB class, Init method it would be better to use:
echo $this->ExceptionLog($e->getMessage(),$query);
instead of
echo $this->ExceptionLog($e->getMessage(),$this->sQuery->queryString);
with UPDATE query.
Looks like there is no inherent support for procedures? By adding this to the query method, I'm able to at least deal with stored procs that return a dataset..
elseif ( $statement === 'call')
{
return $this->sQuery->fetchAll($fetchmode); // GEF - modified
}
Hi there,
How can I use lastInsertId(); just for get the last id of row inserted?
Thanks in advance
When use LIKE with a :params not working
Sorry if this sounds dumb but how do you know if a record exists? for instance
$query = $db->single("SELECT id FROM tablename WHERE field = :field AND field2 = :field2)",array("field"=>$field,"field2"=>$field2));
if($query)
{
// record exists pull the id
$id = $query['id'];
}
else
{
// add the record
$sql = $db->query("INSERT INTO tablename (field,field2) VALUES(:field,:field2)",array("field"=>$field,"field2"=>$field2));
}
if(!empty($this->parameters)) {
foreach($this->parameters as $param)
{
$parameters = explode("\x7F",$param);
$this->sQuery->bindParam($parameters[0],$parameters[1]);
}
}
what is "\x7f"?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.