PDO Crud – Advanced PHP CRUD application

https://websitebeaver.com/php-pdo-prepared-statements-to-prevent-sql-injection

//select data
include 'pdo-connect.php';
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

//single query
        $pdo = Database::connect();
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $Ssql = $pdo->prepare($Ssql);
        $Ssql->bindParam(":pid", $pid,PDO::PARAM_INT) ;
        $Ssql->bindParam(":CurrentDate", $CurrentDate,PDO::PARAM_STR) ;
        $Ssql->execute();
        $olddata = $Ssql->fetch(PDO::FETCH_ASSOC);
//total records
        $stmt = $pdo->prepare($Tsql);
        $stmt->bindParam(":pid", $pid,PDO::PARAM_INT);
        $stmt->bindParam(":CurrentDate", $CurrentDate,PDO::PARAM_INT);
        $stmt->execute();
        $user_dup_record=$stmt->rowCount();
        $data=$stmt->fetch(PDO::FETCH_ASSOC);

//update
    $Usql = $pdo->prepare($Uquery);
    $Usql->bindParam(":pid", $pid,PDO::PARAM_INT) ;
    $Usql->bindParam(":CurrentDate", $CurrentDate,PDO::PARAM_STR) ;
    $Usql->execute();


//read sql protected
        $email=$_POST[loginid];
        $ps=md5($_POST[password]);
        //echo $email . '--' .$ps;
        $rowCount = 0;
        include 'pdo-connect.php';
        $pdo = Database::connect();
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $sql="SELECT pwd,emailid,isroot FROM usermaster WHERE emailid=:email AND pwd=:ps";
        //echo $sql;
        $stmt = $pdo->prepare($sql);
        $stmt->bindParam(":email", $email,PDO::PARAM_STR);
        $stmt->bindParam(":ps", $ps,PDO::PARAM_INT);
        $stmt->execute();
        $count=$stmt->rowCount();
        $data=$stmt->fetch(PDO::FETCH_ASSOC);

        OR
        --
        $pdo = Database::connect();
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $sql = "SELECT * FROM customers where id = ?";
$q = $pdo->prepare($sql);
        $q->execute(array($id));
        $row = $q->fetch(PDO::FETCH_ASSOC);
echo $row[id];
        Database::disconnect();

//loop
$WorldSql = "SELECT id,woeid,collection_dt,trend,country,city FROM trends WHERE woeid = :WorldID AND trim(trend)!='' AND DATE(collection_dt)='$CurrentDate' ORDER BY trendid ASC";
//echo '<br> Sql Display '.$WorldSql;
$WorldSql = $pdo->prepare($WorldSql);
        $WorldSql->bindParam(':WorldID', $WorldID,PDO::PARAM_INT);
$WorldSql -> execute();
$result = $WorldSql->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $key => $value) {
//$trendURL=trim($value->trendUrl);
$tweetTrend.= trim($value->trend) . ', ';
}

        // with array shift
        $rows = $page->fetchAll(PDO::FETCH_ASSOC);
        // use array_shift to free up the memory associated with the record as we deal with it
        while($row = array_shift($rows)){
           // do something awesome with row
        }


// insert data
if ($valid) {
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO customers (name,email,mobile) values(?, ?, ?)";
$q = $pdo->prepare($sql);
$q->execute(array($name,$email,$mobile));
    $emplastId = $pdo->lastInsertId();
    Database::disconnect();
header("Location: index.php");
}

//insert an array into a single MySQL Prepared statement w/ PHP and PDO
// or multiple sql statement or more query or php pdo insert array values

    $mname = $_POST['mname'];
    $tcode = $_POST['tcode'];
    $pdo = Database::connect();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    for($i=0;$i<count($_POST['categories']);$i++){
        $data[] = $_POST['categories'][$i];
      }

    $sql = 'INSERT INTO modules (modulename, modulecode, occid) VALUES ';
    $insertQuery = array();
    $insertData = array();
    foreach ($data as $row) {
        $insertQuery[] = '(?, ?, ?)';
        $insertData[] = $_POST['mname'];
        $insertData[] = $_POST['tcode'];
        $insertData[] = $row;
    }

    if (!empty($insertQuery)) {
        $sql .= implode(', ', $insertQuery);
        $stmt = $pdo->prepare($sql);
        $stmt->execute($insertData);
    }



// update data
            $pdo = Database::connect();
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $sql = "UPDATE customers  set name = ?, email = ?, mobile =? WHERE id = ?";
$q = $pdo->prepare($sql);
            $q->execute(array($name,$email,$mobile,$id));
            Database::disconnect();
            header("Location: index.php");

            //mass update
            https://stackoverflow.com/questions/26959784/pdo-php-the-fastest-way-to-update-or-insert-multiple-rows
            $update_array = array(
                1 => 'foo',
                2 => 'bar',
                10 => 'baz'
            ); // key is row id, value is value to be updated

            $stmt = $dbh->prepare("UPDATE table SET column1 = :column1 where id = :id");
            $stmt->bindParam(":column1",$column_value);
            $stmt->bindparam(":id",$id);
            foreach($update_array as $k => $v) {
                $id = $k
                $column_value = $v;
                $stmt->execute();
                // add error handling here
            }

            https://stackoverflow.com/questions/17219992/update-mass-data-using-pdo-for-mysql-database?rq=1
            //===================================================
            //prepare sql
            $sql = "UPDATE TickersList SET field1=?, field2=?, field3=? WHERE field4=?";
            $STH = $dbHandle->prepare($sql);
            //===================================================
            //declare arrays
            $ufield1 = array();
            $ufiled2 = array();
            $ufiled3 = array();
            $ufiled4 = array();
            //===================================================
            /fill arrays with data
            foreach( $this->_data as $qty ){
            $params = array($qty->data1,$qty->data2,$qty->data3,$qty->data4);
            $STH->execute($params);
            }
            //===================================================
            //execute query

$update = 'SET ';
$fields = array_keys($_POST);
$values = array_values($_POST);
foreach ($fields as $field) {
    $update .= $field . '=?,';
}
$update = substr($update, 0, -1);
$db->query("update sub_projects ${update} where id=${_GET['id']}");
$db->execute($values);


// delete data
        $pdo = Database::connect();
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $sql = "DELETE FROM customers  WHERE id = ?";
        $q = $pdo->prepare($sql);
        $q->execute(array($id));
        Database::disconnect();
        header("Location: index.php");


// count records with injection preventation at virtualfollow/feed
$sql="SELECT woeid,trend FROM trends WHERE woeid=:Mywoeid AND trim(trend)=:Mytrend";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(":Mytrend", $trendDB,PDO::PARAM_STR) ;
$stmt->bindParam(":Mywoeid", $WorldID,PDO::PARAM_STR) ;
$stmt->execute();
$count=$stmt->rowCount();
//$data=$stmt->fetch(PDO::FETCH_ASSOC);
//echo '<br> total '. $count . ' ' . $trendDB;
if ($count < 1) {
insert data
}

//bind value example
$user_id = 16;
$query = $SQL->prepare('SELECT tags FROM users WHERE user_id = :uid');
$query->bindValue(':uid', $user_id, PDO::PARAM_INT);
$query->execute();



// count records -- see my-emarketing-home.php
$stmt = $pdo->prepare("SELECT ComposedID,EmailName,AdminID,Subject,default_email_id FROM raj_composed_emails WHERE AdminID = '$_SESSION[MemberID]' and default_email_id='1' ");
$stmt->execute();
$dRow = $stmt->fetch();
echo count($stmt);
//all your results is in $rows array
//$stmt->setFetchMode(PDO::FETCH_ASSOC);
if (count($stmt) > 0) {
    $dmail = $dRow['Subject'];
    $eid=$dRow['ComposedID'];
    foreach ($dRow as $row) {
        //output your rows
    }
}

for count
$Tsql = "select UserId from followers where UserId=$uid";
echo $Tsql;
$stmt = $pdo->prepare($Tsql);
//$stmt->bindParam(":uid", $uid,PDO::PARAM_STR) ;
$stmt->execute();
$total_dup_record=$stmt->rowCount();
$data=$stmt->fetch(PDO::FETCH_ASSOC); //necesarry

error handling
==============
try
{
    $selectQueryResult->execute();
}
catch(PDOException $e)
{
    handle_sql_errors($sql, $e->getMessage());
}

- See more at: http://www.startutorial.com/articles/view/php-crud-tutorial-part-3#sthash.XJpiJUqb.dpuf

http://brady.lucidgene.com/articles/pdo-lost-mysql-connection-error