PHP Snippets

Command Line Named Arguments:

        
            // To execute: php cla1.php -pvalue1 -gvalue2 -avalue3
            $val = getopt("p:g:a:");
            if ($val !== false) {
            echo var_export($val, true);
            //echo $val["p"];
            }
            else {
            echo "Could not get value of command line option\n";
            }
        
    

MySQL PDO Connection:

        
            $host = "myHost";
            $username = "myUsername";
            $password = "myPassword";
            $dbname = "myDBName";
            $charset = 'utf8mb4';
            $dsn = "mysql:host=$host;dbname=$dbname";
            $options = array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            );
            $dsn = "mysql:host=$host;dbname=$dbname;charset=$charset";
        
    

Stored Procedure with Output parameters:

        

            require "../SQL_Configurations/idsprod.php";
            
            $_POST = json_decode(file_get_contents('php://input'), true);
            
            if (isset($_POST['orderType'])) {
                $orderType = $_POST["orderType"];
            }
            
            if (isset($_POST['requestor'])) {
                $requestor = $_POST["requestor"];
            }
            
            if (isset($_POST['dueDate'])) {
                $dueDate = $_POST["dueDate"];
            }
            
            if (isset($_POST['description'])) {
                $description = trim($_POST["description"]);
            }
            
            if (isset($_POST['customer'])) {
                $customer = $_POST["customer"];
            }
            if (isset($_POST['product'])) {
                $product = $_POST["product"];
            }
            if (isset($_POST['cwo'])) {
                $cwo = $_POST["cwo"];
            }
            
            if (isset($_POST['pe'])) {
                $pe = $_POST["pe"];
            }
            
            if ($orderType ==="-1" or $orderType ===-1) {
                echo "Invalid Order Type!";
                $pdo = null;
                die;
            }
            
            if ($customer ==="-1" or $customer ===-1) {
                echo "Invalid Customer selection!";
                $pdo = null;
                die;
            }
            
            $sql = "EXEC ES.Troy_Redo.usp_Insert_Order_Header
            :Order_Type,:Requestor,:Date_Required,
            :Description,:Customer_ID,:Product_ID,
            :CWO,:Process_Engineer, :RETID";
            
            try {
                
                $stmt = $pdo->prepare($sql);
                $stmt->bindValue(':Order_Type', $orderType, PDO::PARAM_STR);
                $stmt->bindValue(':Requestor', $requestor, PDO::PARAM_STR);
                $stmt->bindValue(':Date_Required', $dueDate, PDO::PARAM_STR);
                $stmt->bindValue(':Description', $description, PDO::PARAM_STR);
                $stmt->bindValue(':Customer_ID', $customer, PDO::PARAM_STR);
                $stmt->bindValue(':Product_ID', $product, PDO::PARAM_STR);
                $stmt->bindValue(':CWO', $cwo, PDO::PARAM_STR);
                $stmt->bindValue(':Process_Engineer', $pe, PDO::PARAM_STR);
                $outputParam = '';
                $stmt->bindParam(':RETID', $outputParam, PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT, 100);
                $stmt->execute();
                
                echo "Order Submitted Successfully";
                echo "Output Parameter: " . $outputParam;
            } catch (PDOException $e) {
               // header("HTTP/1.1 404 Not Found");
                print_r($e->errorInfo);
                echo "Order Not Submitted!";
                //echo e->errorInfo[0];
            } finally {
                $pdo = null;
            }
            
        
    

Execute Stored Procedure:

        

            function getTransactions($conn, $beginDate, $endDate)
            {
            $sql = "EXEC Investments.Vanguard.usp_GetTransactions_By_Date :BeginDate,:EndDate";
            $stmt = $conn->prepare($sql);
            try {
            $stmt->bindValue(':BeginDate', $beginDate, PDO::PARAM_STR);
            $stmt->bindValue(':EndDate', $endDate, PDO::PARAM_STR);
            $stmt->execute();
            $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

            echo json_encode($data);
            } catch (PDOException $e) {
            echo $e->getMessage();
            }
            $conn = null;
            }
        
    

SQL Server PDO Connection:

        

            $host = "myServer";
            $dbname = "myDatabase";
            $username = "myUserName";
            $password = "myPassword";
            $dsn = "sqlsrv:server=$host;database=$dbname";

            $options = array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            );

            try {
            $pdo = new PDO($dsn, $username, $password, $options);
            } catch (PDOException $error) {
            echo $sql . $error->getMessage();
            }

        
    

Use Temporary try catch to find PDO Errors:

        
            $sql = "EXEC IDS.[dbo].[usp_UpdateUser] :id,
            :lastName, :firstName, :gender, :city, :state";
            try {
            $stmt = $pdo->prepare($sql);
            $stmt->bindValue(':id', $_id, PDO::PARAM_INT);
            $stmt->bindValue(':lastName', $_lastName, PDO::PARAM_STR);
            $stmt->bindValue(':firstName', $_firstName, PDO::PARAM_STR);
            $stmt->bindValue(':gender', $_gender, PDO::PARAM_STR);
            $stmt->bindValue(':city', $_city, PDO::PARAM_STR);
            $stmt->bindValue(':state', $_state, PDO::PARAM_STR);
            $stmt->execute();
            } catch (PDOException $e) {
            $existingkey = "Integrity constraint violation: 1062 Duplicate entry";
            if (strpos($e->getMessage(), $existingkey) !== false) {
            echo $e->getMessage();
            // Take some action if there is a key constraint violation, i.e. duplicate name
            } else {
            throw $e;
            }
            }

        
    

Calling Stored Proc Method # 2:

        
            // array for inputs.
            $inputs =
            [
            $_lastName = 'King',
            $_firstName = 'Sherrie',
            $_gender = 'Female',
            $_city = 'Cicero',
            $_state = 'NY',
            ];
            $sql = "dbo.usp_AddUser ?,?,?,?,?";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($inputs);

        
    

Calling Stored Proc Method # 3:

        
            $sql = 'dbo.usp_AddUser :lastName,:firstName, :gender , :city, :state';
            $stmt = $pdo->prepare($sql);
            $stmt->bindParam(':lastName', $_lastName, PDO::PARAM_STR);
            $stmt->bindParam(':firstName', $_firstName, PDO::PARAM_STR);
            $stmt->bindParam(':gender', $_gender, PDO::PARAM_STR);
            $stmt->bindParam(':city', $_city, PDO::PARAM_STR);
            $stmt->bindParam(':state', $_state, PDO::PARAM_STR);
            $stmt->execute();
        
    

Calling Stored Proc Method # 4:

        
            $sql = "dbo.usp_AddUser ?,?,?,?,?";
            $stmt = $pdo->prepare($sql);
            $stmt->bindValue(1, $_lastName, PDO::PARAM_STR);
            $stmt->bindValue(2, $_firstName, PDO::PARAM_STR);
            $stmt->bindValue(3, $_gender, PDO::PARAM_STR);
            $stmt->bindValue(4, $_city, PDO::PARAM_STR);
            $stmt->bindValue(5, $_state, PDO::PARAM_STR);
            $stmt->execute();
        
    

Create x number of ???:

        
            // getQParms(5) = "?,?,?,?,?";
            function getQParms($fieldCount)
            {
            return rtrim(str_repeat("?,", $fieldCount), ",");
            }