Why is insert not working and not outputting any error on postresql?

Question

I tried checking if a table already exists and if it doesn't it should create it and insert the given data to the table but if it already exists it should just insert the data directly. But I am just getting a plain page. The pg_error(); is not outputting nothing. Could someone help please?.. Below is the code;

  <?php   

   $pra = "SELECT * FROM people";

   $decks = pg_query($connection, $pra); 

   if(!$decks){                         
   $sql =  "CREATE TABLE people(
   mom INT PRIMARY KEY     NOT NULL, 
   non        TEXT    NOT NULL,
   ooo        INT   NOT NULL,
   ppp          INT   NOT NULL,
   aqqq   TEXT,
   pq   TEXT

    )";

      $ins = " INSERT INTO people (mom, non, ooo, ppp, aqqq, pq)
             VALUES(
            '$mom', '$non', '$ooo’, '$ppp’, '$aqqq', '$pq')";


    $rcon =pg_query($connection, $ins);

     if(!$rcon){
        pg_last_error($connection);
             }else{
                  echo "Record added to database"; //success 
          confirmation
                  }


       }
     ?>

Some how the table here is created because I can see it in the database via terminal. But apart from that everything is coming blank and no error messages.

You're not doing anything with `pg_last_error` - it returns a string, but you aren't assigning it to anything. Your query is also missing a single quote after `$ppp`, which is why the insert itself isn't working. (I'm assuming all the query variables are actually defined somewhere else) — iainn, Jun 29 '20 at 13:57
You are aware that you are not actually executing any database query in your if branch, right? All you are doing there, is assign some text to a variable. — CBroe, Jun 29 '20 at 13:58
And if you wanted to perform this INSERT in any case, either into the already existing table, or the one you just created (/attempted to create) in the if branch - then why is the insert inside an elseif (resp. else in general), in the first place? — CBroe, Jun 29 '20 at 13:59
@iainn Oh sorry that was a mistake but inside the original code...they are closed. — Vvictory, Jun 29 '20 at 14:47
@CBroe I had tried it inside the if function but the same result...so I had to try it out this way...Sad enough the post associated to this post as already answered are not in anyway similar...let people kindly answer this question. — Vvictory, Jun 29 '20 at 14:50
@Vvictory Please see the comments @CBroe provided. You need to execute the `$sql` to create the table in the first part. The `insert` should not be run in the `else` block since you want to insert in either case. If you have been modifying your code to address these, then please update your question. — Mike Organek, Jun 29 '20 at 15:06
@MikeOrganek I appreciate your time for showing interest too..could you show this on code? — Vvictory, Jun 29 '20 at 15:50

Mike Organek · Accepted Answer · 2020-06-29T20:29:15.753

Please try something like this. Note the use of pg_query_params() instead of pg_query() for the insert to guard against SQL injection.

 <?php   

   $pra = "SELECT * FROM people";

   $decks = pg_query($connection, $pra); 

   if(!$decks){                         
     $sql =  "CREATE TABLE people(
                mom  INT PRIMARY KEY NOT NULL, 
                non  TEXT NOT NULL,
                ooo  INT NOT NULL,
                ppp  INT NOT NULL,
                aqqq TEXT,
                pq   TEXT
              )";
      $rcon = pg_query($connection, $sql);
      if(!$rcon){
        echo pg_last_error($connection);
      } else {
        echo "Database table created"; //success confirmation
      }

   }

   $val_array = array($mom, $non, $ooo, $ppp, $aqqq, $pq);
   var_dump($val_array);
   $ins = " INSERT INTO people (mom, non, ooo, ppp, aqqq, pq)
            VALUES($1, $2, $3, $4, $5, $6)";
   $rcon =pg_query_params($connection, $ins, $val_array);

   if(!$rcon){
        echo pg_last_error($connection);
   } else {
        echo "Record added to database"; //success confirmation
   }

?>

I tried this but it also shows blank page, The content of the VALUES here $1, $2.... are they equivalent to the values of the array? — Vvictory, Jun 29 '20 at 20:00
@Vvictory I updated the answer. The $1, $2, . . . are replaced safely by the corresponding values in the array. — Mike Organek, Jun 29 '20 at 20:08
This shows the error now.. I could work with this from here. :) — Vvictory, Jun 29 '20 at 20:33

Why is insert not working and not outputting any error on postresql?

1 Answers1

Linked