Tips on sending multiple query with postgresql

Question

I have this code for pgsql database i am able to send the first query but i need to send both query at once they are different tables.

<?php 

    include_once('db/db.php'); 
    $description = pg_escape_string($_POST['description']);
    $startdate = pg_escape_string($_POST['startdate']);
    $schedulestarttime = pg_escape_string($_POST['schedulestarttime']);
    $scheduleendtime = pg_escape_string($_POST['scheduleendtime']);
            //those 4 required for second query
            $cycleid = ..; //this must be returning the cycleid of first query
            $eventtypecode = 5;
    $duration = 120;
    $position = 6;
    $query = "INSERT INTO cycles (description, startdate, schedulestarttime, scheduleendtime) VALUES('" . $description . "',  '" . $startdate . "', '" . $schedulestarttime . "', '" . $scheduleendtime . "') RETURNING cycleid";
    $result = pg_query($query);
    if (!$result) {
        $errormessage = pg_last_error();
        echo "Error with query: " . $errormessage;
        exit();
    }
    else {
     $query = "INSERT INTO cycleelements (cycleid, eventtypecode, duration, position) VALUES ($cycleid,  '" . $eventtypecode . "', '" . $duration . "', '" . $position . "')";
     $result = pg_query($query);
    if (!$result) {
        $errormessage = pg_last_error();
        echo "Error with query: " . $errormessage;
        exit();
        }
     printf ("Successfully added | %s | %s | %s | %s | to the database", $cycleid, $eventtypecode, $duration, $position);
     pg_close();
    }
    printf ("Successfully added | %s | %s | %s | %s | to the database", $description, $startdate, $schedulestarttime, $scheduleendtime);
    pg_close();

?>

If someone has any tips for me what i need to change in the code so i can send both query and also there i commented that for the second query the cycleid must be returned from the first query.

Thank you, Regards

Create Procedure in which pass all parameters that you want to use in all Queries and write your queries in procedure instead of PHP file. Call this procedure from your PHP file with all its Parameter. — Ilesh Patel, Apr 16 '14 at 09:29
i can't create procedures i have access to the database only read not execute. — user3458763, Apr 16 '14 at 09:50

score 2 · Accepted Answer · answered Apr 16 '14 at 09:33

2

You can use multiple statements in a single pg_query. These statements are launched in a single transaction, so you can rollback if something went wrong.

See http://www.php.net/manual/en/function.pg-query.php

answered Apr 16 '14 at 09:33

Romain

301
2
7

1

Thanks, seems that it works with the example #2 on the link. But i still need a way after the first query is sent to return first the 'cycleid' that will be inserted on the second query also. – user3458763 Apr 16 '14 at 09:47
This post may help you then : http://stackoverflow.com/questions/19167349/postgresql-insert-from-select-returning-id – Romain Apr 16 '14 at 09:59

Tips on sending multiple query with postgresql

1 Answers1

Linked