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
– Ilesh Patel, Commented Apr 16, 2014 at 9:29
i can't create procedures i have access to the database only read not execute. — user3458763
– user3458763, Commented Apr 16, 2014 at 9:50

Romain · Accepted Answer · 2014-04-16 09:33:19Z

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, 2014 at 9:33

Romain

3213 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3458763 Over a year ago

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.

Romain Over a year ago

This post may help you then : stackoverflow.com/questions/19167349/…

Collectives™ on Stack Overflow

Tips on sending multiple query with postgresql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related