query failedERROR: prepared statement "my_query" does not exist

Question

Hello All

I'm trying to insert a form data into my postgreSQL DB in heroku through PHP and i tried all the solutions here but nothing solved my problem!. I can connect to the database but no operation worked well to me!. This error lead me to craziness!.

my code is:

<?php


   $db_conn = pg_connect(" host="" port=5432 dbname="" user="" password="" ");

    if(!$db_conn){
             echo "Error : Unable to connect the database\n";
          } 


  if (isset($_POST['savedata'])) {


   $fn    =  $_POST ['fullname']; 
   $em    =  $_POST ['email']; 
   $ag    =  $_POST ['age'];
   $ge    =  $_POST ['gender'] ; 
   $ci    =  $_POST ['city'] ; 
   $de    =  $_POST ['degree']; 
   $ex    =  $_POST ['experience'];
   $jo    =  $_POST ['job']; 



  if($fn != "" and $em != "" and $ag != "" and $ge != "" and $ci != "" and $de != "" and $ex != "" and $jo != "") {


  $data1="something to test";



  $result = pg_prepare($db_conn, "my_query", "INSERT INTO members (fullname, email, age, gender, city, degree, experience, job) VALUES ($fn, $em, $ag, $ge, $ci, $de, $ex, $jo)");
  $result = pg_execute($db_conn, "my_query", array($data1));

  if (!$result){
    error_reporting(E_ALL);
    die("query failed".pg_last_error());
  }

  else {

  echo "<script>";
  echo "document.querySelector('.myalert').style.display = 'block';";
  echo "setTimeout(function(){
        document.querySelector('.myalert').style.display = 'none';
        window.location.replace('home');
      },5000);"; 
  echo "</script>";

 }

  }

  else {
    echo "<script>";
    echo "document.querySelector('.myalert1').style.display = 'block';";
    echo "setTimeout(function(){
        document.querySelector('.myalert1').style.display = 'none';
      },2000);"; 
    echo "</script>";
  }

 }


?>

Answer 1

You have syntax error in your code at the very first line.

Parse error: syntax error, unexpected '" port=5432 dbname="' (T_CONSTANT_ENCAPSED_STRING), expecting ',' or ')

There are also some other issues and oddities so it was just easier to rewrite some parts of your code.

I would also advice you to pay little more attention about the coding style, indentations and etc. It would be significantly easier to read and help you if the code were styled properly. PSR-2 Style Guide would be good place to start.

So here's the rewritten code, but note that I don't have PostgreSQL installed and that's why the code below isn't tested in any way. It should work, but there's also possibility that it doesn't.

See the comments in the code for further explanation.

// Change these credentials according to your needs, but without any quotes
$db_conn = pg_connect("host=localhost port=5432 dbname=mydb user=user password=pwd");

if (!$db_conn) {
    die("Error : Unable to connect the database\n");
}

if (isset($_POST['savedata'])) {
    $member_details = array(
        $_POST['fullname'],
        $_POST['email'],
        $_POST['age'],
        $_POST['gender'],
        $_POST['city'],
        $_POST['degree'],
        $_POST['experience'],
        $_POST['job']
    );
}

// Iterates through the array and checks if there's any items of which has no proper value
foreach ($member_details as $key => $val) {
    if (empty($val)) {
        die($key . ' is empty.');
    }
}

// Query inside single quotes, also variable names must be $1, $2, $3 etc
$query = 'INSERT INTO members (fullname, email, age, gender, city, degree, experience, job) VALUES ($1, $2, $3, $4, $5, $5, $7, $8)';

$result = pg_prepare($db_conn, "my_query", $query);
$result = pg_execute($db_conn, "my_query", $member_details);

if (!$result) {
    // error actions
} else {
    // success actions
}