Updating specific area of table in postgresql

Question

Ok I am trying to update a specific area of a table in postgresql

I want it to find the user that goes along with the table

and then update the information I have

like in this case the email is the user name that it needs to look for.

it needs to add in areas like $aboutSelf, $hobbies, $music, $tv, $sports

so ya I have no idea how to do this lol ^.^ I only know how to add stuff from scratch. like create a non existing user

CREATE TABLE chatterprofileinfo(
    Id SERIAL,
    email VARCHAR(255) NOT NULL PRIMARY KEY,
    aboutSelf VARCHAR(255),
    hobbies VARCHAR(255),
    music VARCHAR(255),
    tv VARCHAR(255),
    sports VARCHAR(255),
    lastLogin DATE
);

The PHP im currently using

<?php

$error=false;

$aboutSelfError="";
$hobbiesError="";
$musicError="";
$tvError="";
$sportsError="";

if($_SERVER["REQUEST_METHOD"] == "GET") {

    $aboutSelf="";
    $hobbies="";
    $music="";
    $tv="";
    $sports="";
    $error=false;

}
else if($_SERVER["REQUEST_METHOD"] == "POST") {

    $error=false;

    $aboutSelf=trim($_POST["aboutSelfTA"]);
    $hobbies=trim($_POST["hobbiesTA"]);
    $music=trim($_POST["musicTA"]);
    $tv=trim($_POST["tvTA"]);
    $sports=trim($_POST["sportsTA"]);

    if(strlen($aboutSelf)>255) {

        $aboutSelfError="Maximum of 255 characters please shorten";

        $error=true;

    }

    if(strlen($hobbies)>255) {

        $hobbiesError="Maximum of 255 characters please shorten";

        $error=true;

    }

    if(strlen($music)>255) {

        $musicError="Maximum of 255 characters please shorten";

        $error=true;

    }

    if(strlen($tv)>255) {

        $tvError="Maximum of 255 characters please shorten";

        $error=true;

    }

    if(strlen($sports)>255) {

        $sportsError="Maximum of 255 characters please shorten";

        $error=true;

    }

}

?>

Answer 1

Use the UPDATE query?

I use W3schools.com for most of my SQL references. Very handy site!

Might be too generalized an answer but can't go off much more without a schema.

Answer 2

see http://www.postgresql.org/docs/8.1/static/sql-update.html

UPDATE
  users
SET
  aboutSelf='...',
  hobbies='...',
  music='...',
  tv='...',
  sports='...'
WHERE
  email='something'

edit: a self-contained example using pg_prepare():

$pg = pg_connect("dbname=test user=localonly password=localonly");
if ( !$pg ) {
  die('connect failed ');
}

// create a temporary/test table
pg_query($pg, '
  CREATE TEMPORARY TABLE tmpchatter (
    id SERIAL, 
    email varchar,
    aboutSelf varchar,
    hobbies varchar,
    UNIQUE (email)
  )
');

// fill in some test data
pg_query("INSERT INTO tmpchatter(email, aboutSelf, hobbies) VALUES ('emailA','aboutA','hobbiesA')") or die(pq_last_error($pg));
pg_query("INSERT INTO tmpchatter(email, aboutSelf, hobbies) VALUES ('emailB','aboutB','hobbiesB')") or die(pq_last_error($pg));
pg_query("INSERT INTO tmpchatter(email, aboutSelf, hobbies) VALUES ('emailC','aboutC','hobbiesC')") or die(pq_last_error($pg));

// let's see what we've got so far
$result = pg_query('SELECT email,aboutSelf,hobbies FROM tmpchatter') or die(pq_last_error($pg));
echo "query result #1:\n";
while ( false!==($row=pg_fetch_row($result)) ) {
  echo join(', ', $row), "\n";
}

// now let's update a specific record
// the "interesting" part

// first the parameters we want to use
$email = 'emailB';
$about = 'about me....';
$hobbies = 'breathing, eating';

// prepare the statement. Put placeholders where you want to "insert" parameters
pg_prepare($pg, '', '
  UPDATE
    tmpchatter
  SET
    aboutSelf = $1,
    hobbies = $2
  WHERE
    email = $3
') or die(pg_last_error());

// execute the statement + provide the parameters
// With prepared statements you don't have to worry about escaping the values to avoid sql injections
pg_execute($pg, '', array($about, $hobbies, $email)) or die(pg_last_error());

// let's check the result
$result = pg_query('SELECT email,aboutSelf,hobbies FROM tmpchatter') or die(pq_last_error($pg));
echo "query result #2:\n";
while ( false!==($row=pg_fetch_row($result)) ) {
  echo join(', ', $row), "\n";
}

prints

query result #1:
emailA, selfA, hobbiesA
emailB, selfB, hobbiesB
emailC, selfC, hobbiesC

query result #2:
emailA, selfA, hobbiesA
emailC, selfC, hobbiesC
emailB, about me...., breathing, eating