convert mysql_insert_id and mysqli_multi_query to Postgresql

Question

I found an exercise in mysql and I'm passing it to postgresql, but I get to a part where this "mysql_insert_id" I understand that is to get the last record and also found "mysqli_multi_query" I have no idea how to change it to Postgresql.

<?php
        if(isset($_POST["place_order"]))
        {
            $insert_order = "
            INSERT INTO tbl_order(customer_id, creation_date, order_status)
            VALUES('1', '".date('Y-m-d')."', 'pending')
            ";
            $order_id = "";
            if(pg_query($connect, $insert_order))
            {
                $order_id = mysql_insert_id($connect);
            }
            $_SESSION["order_id"] = $order_id;
            $order_details = "";
            foreach($_SESSION["shopping_cart"] as $keys => $values)
            {
                $order_details .= "
                INSERT INTO tbl_order_details(order_id, product_name, product_price, product_quantity)
                VALUES('".$order_id."', '".$values["product_name"]."', '".$values["product_price"]."', '".$values["product_quantity"]."');
                ";
            }
            if(mysqli_multi_query($connect, $order_details))
            {
                unset($_SESSION["shopping_cart"]);
                echo '<script>alert("You have successfully place an order...Thank you")</script>';
                echo '<script>window.location.href="cart.php"</script>';
            }
        }

http://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id — McNets, Dec 05 '16 at 21:31

score 0 · Accepted Answer · answered Dec 06 '16 at 00:32

Switch to PDO

Run like mad away from that tutorial you are following. Either it was written many years ago or it was written by someone who is not knowledgeable on the subject. This is a horrible tutorial. This is why:

           $order_details .= "
            INSERT INTO tbl_order_details(order_id, product_name, product_price, product_quantity)
            VALUES('".$order_id."', '".$values["product_name"]."', '".$values["product_price"]."', '".$values["product_quantity"]."');
            ";

The query is generated using string concatenation, which is a very unsafe practice. The preferred and the safe way is to use prepared statements. Secondly you are better off leanring PDO rather than mysqli because PDO works with both mysql and PostgreSQL. Thirdly mysqli_mutli_query is hardly ever used.

        $stmt = $db->prepare("INSERT INTO tbl_order_details(order_id,product_name, product_price, price_quantity) VALUES(:order_id, :product_name, :product_price, :price_quantity)";

        foreach($_SESSION["shopping_cart"] as $keys => $values)
        {
            $stmt->bindParam('order_id'], $order_id);
            $stmt->bindParam('product_name', $values['product_name']);
            $stmt->bindParam('product_price',$values['product_price']);
            $stmt->bindParam('product_quantity'], $values['product_quantity']);
            $stmt->execute()
        }

The above does the same thing much more safely and more importantly works on both postgresql and mysql

Last insert id

Getting the last insert id with PDO is easy.

$db->lastInsertId('sequence_name');

Where sequence_name is the name of the sequence for your serial column in postgresql. (Serial is the equivalent for auto_increment)

convert mysql_insert_id and mysqli_multi_query to Postgresql

1 Answers1

Switch to PDO

Last insert id