accessing a set of values through single query in postgres

Question

Im trying to use postgres for the first time.. I want to retrieve the value company name from a the values inside an array named testArray

The ajax request that I wrote is

$(document).ready(function()
{

var testArray=[0001001871, 0001001843, 0001001853];



        $.ajax({
        type:'GET',
        url: 'php/databaseAccess.php',
        data: {'CIKs': testArray},
        success: function(success)
            {
            document.write(success);
            }



    });
 });

and the PHP file that Ive used to access db is

<?php
 $link = pg_connect("host=hostname dbname=dbNAme user=username password=password");
 if(!$link)
 {
 //echo "Connection cannot be established";
 exit;
  }

 else
 {
 $cik_array = $_GET['CIKs'];
 $safe_ciks = array();
 foreach($cik_array as $cik)
 {
$safe_ciks[] = pg_escape_string($cik);
}
$in_string = "'" . implode("','", $safe_ciks) . "'";

 $result = pg_exec($link, "select company_name from company_names where cik=".$in_string);

 if (!$result) {
 //echo "<br> Could not successfully run query from DB";
exit;
}

else
{
 echo "<br> Query Executed Successfully<br>";

 echo pg_result($result, 0);
 }
 }
 ?>

This code will only output the company name with cik 0001001807, I want to get the company names of all values inside the array "testArray" through a single query itself.. How is that possible?

heffo · Accepted Answer · 2012-06-18 05:05:45Z

2

The SQL Query should be

SELECT 
    company_name
FROM
    company_names
WHERE
    cik= IN('0001001807','0001001843', '0001001853')

This will return all company names with a CIK in that list. The IN parameter accepts a comma separated list of values.

In terms of passing the Array of CIKs, you could change the "data" parameter of the Ajax request to send the CIKs.

        data: {'CIKs': testArray},

Which I believe would pass all of the CIKs to databaseAccess.php, and could then be accessed by the following command

$cik_array = $_GET['CIKs'];
//escape the CIKs, to prevent SQL injection
$safe_ciks = array();
foreach($cik_array as $cik)
{
    $safe_ciks[] = pg_escape_string($cik);
}
//get the CIKs in a format compatible with the IN function
$in_string = "'" . implode("','", $safe_ciks) . "'";

That would produce $in_string, which could be used with the SQL query above.

answered Jun 18, 2012 at 5:05

heffo

682 silver badges4 bronze badges

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

2 Comments

user1371896 Over a year ago

its giving me the error "Query failed: ERROR: syntax error at or near "," LINE 1: ...ompany_name from company_names where cik='1001871','1001843'... "

user1371896 Over a year ago

"select company_name from company_names where cik=('0001001807','0001002811')"; is this the right syntax?? Im nt getting results with this syntax

Collectives™ on Stack Overflow

accessing a set of values through single query in postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related