CODEWITHSUNDEEP
phpmysql
yusuf__
yusuf__

Reputation: 7

How can I provide conditions in SQL query?

I'm trying to update the database table. How can I prevent the password ("MemberPassword", $ pass) coming from the form from being updated with sql codes by providing a condition if it is empty? Is it possible?

   //database connection
        $SQL = "mysql:host=" . $this->MYSQL_HOST . ";dbname=" . $this->MYSQL_DB;

        try {
            $this->pdo = new \PDO($SQL, $this->MYSQL_USER, $this->MYSQL_PASS);
            $this->pdo->exec("SET NAMES'" . $this->CHARSET . "'COLLATE'" . $this->COLLATION . "'");
            $this->pdo->exec("SET CHARACTER SET'" . $this->CHARSET . "'");
            $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
            $this->pdo->setAttribute(\PDO::ATTR_DEFAULT_FETCH_MODE, \PDO::FETCH_OBJ);

            
        } catch (PDOException $e) {
            die( $e->getMessage());
        }
    }
    //Connect DB END

    private function myQuery($query, $params = null)
        {
            if (is_null($params)) {
                $this->stmt = $this->pdo->query($query);
            } else {
                $this->stmt = $this->pdo->prepare($query);
                $this->stmt->execute($params);
            }
            return $this->stmt;
        }
    
    
        public function Update($query, $params = null)
            {
                try {
                    return $this->myQuery($query, $params)->rowCount();
                } catch (PDOException $e) {
                    die($e->getMessage());
                }
            }
    
    
            
            $update = $db->Update("UPDATE members SET
            MemberUsername=?,
            MemberPassword=?,
            MemberEmail=?,
            MemberName=?,
            MemberLastName=?,
            MemberBirthday=?,
            MemberAge=?,
            MemberGender=?,
            CityID=?
            WHERE MemberID=?
            ", array($username, $pass, $email, $name, $lastname, $birthday, $age, $gender, $city, $memberID));

Upvotes: 0

Views: 100

Answers (3)

Majid Hajibaba
Majid Hajibaba

Reputation: 3260

Use something like this in your php code:

 $param = array($username, $email, $name, $lastname, $birthday, $age, $gender, $city);

 $sqlUpdate = "UPDATE members SET  
    MemberUsername=?,
    MemberEmail=?,
    MemberName=?,
    MemberLastName=?,
    MemberBirthday=?,
    MemberAge=?,
    MemberGender=?,
    CityID=?"
   

 if(!is_null(pass)) {
    $sqlUpdate = $sqlUpdate . ", MemberPassword = ?";
    array_push($param , $pass);
 }

 $sqlUpdate = $sqlUpdate . " WHERE MemberID=?";
 array_push($param , $memberID);

 $update = $db->Update($sqlUpdate, $param);

You can use this pattern for all other fields.

Upvotes: 1

Salman Daryanavard
Salman Daryanavard

Reputation: 26

I didn't understand what your question exactly is but I think this is what you want : User submits a form and if Password that sent from this form was not empty then update everything including password, otherwise update everything except password! For do this you can use if statement in your SQL Query

$update = $db->Update("UPDATE members SET
MemberUsername=?,
MemberPassword=IF(? IS NOT NULL AND LENGTH(?) > 0, ?, MembersPassword),
MemberEmail=?,
MemberName=?,
MemberLastName=?,
MemberBirthday=?,
MemberAge=?,
MemberGender=?,
CityID=?
WHERE MemberID=?
", array($username, $pass, $pass, $pass, $email, $name, $lastname, $birthday, $age, $gender, $city, $memberID));

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can easy use IFNULL(expr1,expr2) like:

IFNULL returns expr1 if they is not null else expr2

$update = $db->Update("UPDATE members SET
            MemberUsername=?,
            MemberPassword=IFNULL(?,MemberPassword),
            MemberEmail=?,
            MemberName=?,
            MemberLastName=?,
            MemberBirthday=?,
            MemberAge=?,
            MemberGender=?,
            CityID=?
            WHERE MemberID=?
            ", array($username, $pass, $email, $name, $lastname, $birthday, $age, $gender, $city, $memberID));

Upvotes: 1

Related Questions