Updating a Row in PostgreSQL with PHP

Question

I was wondering what the syntax was in PHP to update a row in a PostgreSQL database. I have made a login page that checks a UserName and Password from a database, then it goes to a page where it displays all the user info from the database for that user name. I am trying to allow the user to change some of the columns, like password, name, etc. So I added another page that has fields for each of the columns I want to change.

This is the code I have for the query:

if(array_key_exists('save',$_POST))
{
$firstname=$_POST['ifirstname'];
$lastname=$_POST['ilastname'];
$email=$_POST['iemail'];
$password=$_POST['ipassword'];

    $conn_string='host=#### port=#### dbname=###### user=####### password=######';
    $dbconn=pg_connect($conn_string) or die('Connection failed');

$query="UPDATE project.customer SET FirstName='$firstname',
LastName='$lastname',Email='$email',Password='$password')
    WHERE UserName=$1";

    $result=pg_query($dbconn,$query);
    $row_count= pg_num_rows($result);
            pg_free_result($result);
        pg_close($dbconn);
   }

This is for the fields:

    <div id="header">UPDATE USER INFO</div>
    <form id="testform" name="testform" method="post" action="" >
        <p> <label for="ifirstname">First Name:</label> 
          <input name="ifirstname" type="text" id="ifirstname"/>
      </p>
        <p> <label for="ilastname">Last Name:</label>
          <input name="ilastname" type="text" id="ilastname"/>
      </p>
        <p> <label for="iemail">E-Mail:</label>
            <input name="iemail" type="text" id="iemail"/>
        </p>
        <p> 
            <label for="ipassword">Password:</label>
          <input name="ipassword" type="password" id="ipassword"/>
      </p>
        <p> 
            <label for="iconfpass">Confirm Password:</label>
          <input name="iconfpass" type="password" id="iconfpass"/>
      </p>
        <p> 
            <input type="submit" name="save" value="Register"/>
        </p>
    </form>

You need to make sure you escape your strings. I can't stress enough how important that is to prevent SQL-injection attacks. You can use either something like PHP's addslashses() or PDO parameterized queries. — slashingweapon
– slashingweapon, Commented Apr 1, 2012 at 18:22
Why don't you use pg_query_params() to avoid SQL injection? addslashes() isn't 100% safe and PDO is only useful when you support multiple databases brands. — Frank Heikens
– Frank Heikens, Commented Apr 1, 2012 at 18:52

safarov · Accepted Answer · 2012-04-01 18:23:42Z

2

I think it must be like this. Also make user to write old password when changing data for security reason. Also dont forget to filter your data before using in query to avoid sql injection attacks

$query="UPDATE project.customer 
        SET (FirstName,LastName,Email,Password) = 
        ('$firstname','$lastname','$email','$password')
        WHERE UserName= '$1' and Password = '$oldpassword'";

edited Apr 1, 2012 at 18:23

answered Apr 1, 2012 at 18:12

safarov

7,7942 gold badges38 silver badges52 bronze badges

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

Comments

Jordan · Accepted Answer · 2012-04-01 18:16:31Z

0

Why not just use standard SQL syntax?

Update project.customer Set
    "FirstName" = '$firstname',
    ...
Where ...

The main difference in Postgres is that you usually quote the column names.

answered Apr 1, 2012 at 18:16

Jordan

32.7k6 gold badges59 silver badges67 bronze badges

Collectives™ on Stack Overflow

Updating a Row in PostgreSQL with PHP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related