php postgresql check if record already before inserting

Question

I cannot figure out the proper wording/syntax for the query statement. I want to check if there is a school already in our table (names schools) that matches the school name. If so, the state it already exists. Any input would be great!

  //create a connection (the connection is made in another php file)
  $obj = new MyConnection();
  $obj->setConn();
  $obj->displayValues();
  $qObj = new MyCode();
  $qObj->setConn( $obj->getConn());

  //Query to put all the school data into db
  $q1 = "INSERT INTO schools(
                 SchoolName,
                 SchoolWebsite,
                 City,
                 State,
                 Locale,
                 Sector,
                 Tuition,
                 GradRate,
                 FacToStudRatio,
                 StudentPop,
                 FreshmenPop,
                 PercentWomen,
                 PercentMen,
                 PercentAdmitted,
                 AverageGpa,
                 AverageScore)

        VALUES ('$SchoolName',
                '$SchoolWebsite',
                '$City',
                '$State',
                '$Locale',
                '$Sector',
                '$Tuition',
                '$GradRate',
                '$FacToStudRatio',
                '$StudentPop',
                '$FreshmenPop',
                '$PercentWomen',
                '$PercentMen',
                '$PercentAdmitted',
                '$AverageGpa',
                '$AverageScore')";


  $findResult = pg_query( 'SELECT * FROM schools WHERE schoolname =.$SchoolName);

  if ($findResult != 0) 
  {
    echo "School Record Already Exists<br/>";
    die;
  }

  $qObj->setQuery($q1);
  $qObj->runQuery();

_side note:_ to check a `School Record Already Exists` i recommend you to use `SELECT SchoolName FROM schools WHERE schoolname ='.$SchoolName` instead of fetching all columns(avoid `*`) — Vivek S., Sep 18 '14 at 11:11

score 0 · Answer 1 · answered Sep 18 '14 at 10:29

0

You have ' missing in your code

$findResult = pg_query( 'SELECT * FROM schools WHERE schoolname ='.$SchoolName)
                                                                 ^

answered Sep 18 '14 at 10:29

Manwal

23,450
12
63
93

score 0 · Answer 2 · edited May 23 '17 at 11:57

This is related to the "upsert" problem - it's an insert-if-not-exists. Doing this is harder than you'd expect and very prone to race conditions.

The best solution is always a unique constraint or unique index. Try the insert, and if you get an error, you know the row already exists.

The alternative is to LOCK TABLE ... IN EXCLUSIVE MODE the table, so you guarantee nobody else can insert an identical row into it at the same time. That doesn't scale well though.

php postgresql check if record already before inserting

2 Answers2