Convert "INSERT" MySQL query to Postgresql query

Question

I`m stuck for some time to fix this trouble. I followed this article https://www.sitepoint.com/creating-a-scrud-system-using-jquery-json-and-datatables/ to create SCRUD System. But I stuck when I need to add a new record to PostgreSQL.

The working MySQL part of the code is:

$db_server   = 'localhost';
$db_username = 'root';
$db_password = '123456';
$db_name     = 'test';
$db_connection = mysqli_connect($db_server, $db_username, $db_password, $db_name);
$query = "INSERT INTO it_companies SET ";
if (isset($_GET['rank']))         { $query .= "rank         = '" . mysqli_real_escape_string($db_connection, $_GET['rank'])         . "', "; }
if (isset($_GET['company_name'])) { $query .= "company_name = '" . mysqli_real_escape_string($db_connection, $_GET['company_name']) . "', "; }
if (isset($_GET['industries']))   { $query .= "industries   = '" . mysqli_real_escape_string($db_connection, $_GET['industries'])   . "', "; }
if (isset($_GET['revenue']))      { $query .= "revenue      = '" . mysqli_real_escape_string($db_connection, $_GET['revenue'])      . "', "; }
if (isset($_GET['fiscal_year']))  { $query .= "fiscal_year  = '" . mysqli_real_escape_string($db_connection, $_GET['fiscal_year'])  . "', "; }
if (isset($_GET['employees']))    { $query .= "employees    = '" . mysqli_real_escape_string($db_connection, $_GET['employees'])    . "', "; }
if (isset($_GET['market_cap']))   { $query .= "market_cap   = '" . mysqli_real_escape_string($db_connection, $_GET['market_cap'])   . "', "; }
if (isset($_GET['headquarters'])) { $query .= "headquarters = '" . mysqli_real_escape_string($db_connection, $_GET['headquarters']) . "'";   }
$query = mysqli_query($db_connection, $query);

I managed to write this and it fails to work for PostgreSQL:

$conn_string = "dbname=test user=postgres password=123456";
$query = "INSERT INTO it_companies VALUES ";
if (isset($_GET['rank']))         { $query .= "('" . pg_escape_string($db_connection, $_GET['rank'])         . "', "; }
if (isset($_GET['company_name'])) { $query .= "'" . pg_escape_string($db_connection, $_GET['company_name']) . "', "; }
if (isset($_GET['industries']))   { $query .= "'" . pg_escape_string($db_connection, $_GET['industries'])   . "', "; }
if (isset($_GET['revenue']))      { $query .= "'" . pg_escape_string($db_connection, $_GET['revenue'])      . "', "; }
if (isset($_GET['fiscal_year']))  { $query .= "'" . pg_escape_string($db_connection, $_GET['fiscal_year'])  . "', "; }
if (isset($_GET['employees']))    { $query .= "'" . pg_escape_string($db_connection, $_GET['employees'])    . "', "; }
if (isset($_GET['market_cap']))   { $query .= "'" . pg_escape_string($db_connection, $_GET['market_cap'])   . "', "; }
if (isset($_GET['headquarters'])) { $query .= "'" . pg_escape_string($db_connection, $_GET['headquarters']) . "');";   }
$query = pg_query($db_connection, $query);

The message I gets from the system is: "Add request failed: parsererror"

The Edit and remove functions are working well.

I follow to build this clause from the PGSQL site example:

INSERT INTO films VALUES
    ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');

Any what I`m doing wrong? Thanks!

UPDATE The echo of the query and the error was the id column. In Mysql code there was no problem with the ID colum. Why when i use pgsql it does?:

INSERT INTO it_companies (rank,company_name,industries,revenue,fiscal_year,employees,market_cap,headquarters) 
VALUES ('1', 'asd', 'asd', '1', '2000', '2', '3', 'asdf');

    Warning: pg_query(): Query failed: ERROR: duplicate key value violates unique constraint "it_companies_pkey" DETAIL: Key (company_id)=(2) already exists. in C:\WEB\Apache24\htdocs\datatableeditor\data.php on line 121
        {"result":"error","message":"query error"

,"data":[]}

UPDATE2 The working code with one bug:

$query = "INSERT INTO it_companies (rank,company_name,industries,revenue,fiscal_year,employees,market_cap,headquarters) VALUES ";
if (isset($_GET['rank']))         { $query .= "('" . $_GET['rank']         . "', "; }
if (isset($_GET['company_name'])) { $query .= "'" . $_GET['company_name'] . "', "; }
if (isset($_GET['industries']))   { $query .= "'" . $_GET['industries']   . "', "; }
if (isset($_GET['revenue']))      { $query .= "'" . $_GET['revenue']      . "', "; }
if (isset($_GET['fiscal_year']))  { $query .= "'" . $_GET['fiscal_year']  . "', "; }
if (isset($_GET['employees']))    { $query .= "'" . $_GET['employees']    . "', "; }
if (isset($_GET['market_cap']))   { $query .= "'" . $_GET['market_cap']   . "', "; }
if (isset($_GET['headquarters'])) { $query .= "'" . $_GET['headquarters'] . "') RETURNING company_id;";   }
echo $query;

After this query, the message "Add request failed: parsererror" is still there. But after a manual refresh of the page, the new data is saved. Any idea why this message apears and not loading the data automatically?

UPDATE 3 - Success I forgot to remove echo $query; from the code causing the error message. All works now. Thanks for the help to all! :)

Take a look at the contents of $query before you actually try to run the query. It likely does not contain what you think it does. — Dave
– Dave, Commented Jan 29, 2019 at 13:39
I don't know anything about PHP but I suspect 1 of the isset returns false, either rank or headquarters, resulting in ( or ) missing, therefore creating a syntax error. Even if I am wrong, consider putting ( and ) outside of the ifs as they will always be required. — FXD
– FXD, Commented Jan 29, 2019 at 13:43

Juan Carlos Oropeza · Accepted Answer · 2019-01-29 13:50:55Z

1

You need a little more work in your query string building.

You only add the open parenthesis ( if rank is present

You only add the closing parenthesis ) if headquarters is present.

Also you need specify what field column get which value, otherwise you end with headquarter name into the fiscal_year field. If columns are not specified the values are add it on the same order as define on the table.

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

And as other have comment check the $query to see what you have.

edited Jan 29, 2019 at 13:50

answered Jan 29, 2019 at 13:44

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

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

7 Comments

Hristian Yordanov Over a year ago

I filled up all fields. At this time I don't know how to echo the code to see more info. For the column names, I will try. Any idea how to escape "(" in the $query variable? And i have one column for id auto increment.

Juan Carlos Oropeza Over a year ago

How you do debug in php? echo is the way to show the variable content php.net/manual/es/function.echo.php

Hristian Yordanov Over a year ago

I know it but I don't understand the program logic in rest of the code, Now I managed to get the echo. I updated the question. Sorry

Juan Carlos Oropeza Over a year ago

What column is the autonumeric? rank? In that case you shouldnt include that column and let db create a different number for you.

Hristian Yordanov Over a year ago

I have company_id integer NOT NULL DEFAULT nextval('it_companies_company_id_seq'::regclass) .

|

Collectives™ on Stack Overflow

Convert "INSERT" MySQL query to Postgresql query

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related