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

$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.

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

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

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 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'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...

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.

hey. anybody. how to disconnect or close connection?

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 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,

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.

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 ?

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.

When use LIKE with a :params not working

my mysql version is 5.1.48 and it doesn't work ,
the error is "SQLSTATE[HY000]: General error: 1047 Prepare unsupported!"

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;
}

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!

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.

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.

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

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.

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/

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

Lots of tightly coupled code. Consider using DI principles to make your code more testable.

Hi there,

How can I use lastInsertId(); just for get the last id of row inserted?

Thanks in advance

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"));

Insert not work with utf8.
e.g.: ö -> ö

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.

$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"));

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.

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));
    }

Bind parameters

if(!empty($this->parameters)) {
foreach($this->parameters as $param)
{
$parameters = explode("\x7F",$param);
$this->sQuery->bindParam($parameters[0],$parameters[1]);
}
}

what is "\x7f"?

Hello,

Your website http://www.indieteq.com/ seems down ! Is it going back ?

Thank you

How can I get rowcount from this class?

PSR-1 Coding Standards would be nice too. :)

www.php-fig.org/psr/psr-1

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;

persons and Persons

It is persons in the DB and Persons in code do not match..

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

Would be nice if you followed PSR autoloading conventions

http://www.php-fig.org/psr/psr-0

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() );

don't insert Special characters (ş, ğ etc..). and don't display. help me please

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);

Execute SQL

$this->succes = $this->sQuery->execute();

$this->succes

no succes variable

When use LIKE with a :params not working

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.