0

I have many SQL statements in my code and I'm trying to convert to PDO as I just learned about it and I like it.

My question is, can you update just 1 or 2 fields that are in the Prepared statement or does it not work like that.

I have tables and as users go around my site, certain fields get updated, such as "lastlogin" or "my address" etc but all that is stored in one "user" table with 24+ columns.

Is it possible to use 1 PDO statement to update those fields individually or do i have to write a new PDO statement for every variation I want to update. like if I want to update the city and the state, I have to have a separate PDO for that. or if i want to do address and zipcode, a separate one for that, etc

I'm trying to decide if using prepared statements solely is worth it (because I would have to write 1000's of variations for a 50 column table row) or if I should only use prepared statements for big entries and just do it the other way (seen here) and sanitize as needed.

$sql = "DELETE FROM addresses WHERE usernumber = '$usernumber' and number = '$add_to_del'";
$result = mysqli_query($conn,$sql);
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
DerekConlon
  • 353
  • 1
  • 5
  • 17
  • How does "the other way" reduce the number of sql statements you need to write?? – Steve Oct 19 '15 at 21:59
  • I was thinking the PDO method would reduce the code I have to write because I could just call my prepared statement (so I wouldn't have to write the "UPDATE table SET" or "SELECT * FROM table" over and over and over again but as I'm doing more research after writing this question I'm wondering if I truly understand PDO. I might have to look into it more because it looks like I can prepare statements as I write them rather than having to pass variables... – DerekConlon Oct 19 '15 at 22:03
  • You could get all fancy with `Stored Procedures` - pass in table name, fields name and value as parameters – Professor Abronsius Oct 19 '15 at 22:09

2 Answers2

0

The answer is: Yes.

PDO is just like mysql_ an abstraction layer, but a significantly better one.

To rewrite your example query in PDO you would do something like:

$sql = 'DELETE FROM addresses WHERE usernumber = :usernumber and number = :add_to_del';
$sth = $db->prepare($sql);
$sth->execute(array(':usernumber' => $usernumber, ':add_to_del' => $add_to_del));

I think you have misinterpreted what PDO is. PDO is just like mysql_ an abstraction layer, but is more secure (because of prepared statements) and has more features than the old depreceated mysql_ library.

It is highly advised that you refactor your code to use something like PDO or MySQLi instead. mysql_ (mysql_query, etc) is deprecated which means its no longer supported by PHP and will be removed (if not already) in a future version.

John Svensson
  • 392
  • 1
  • 6
  • I am using mysqli_ (not mysql_) but that requires me to perform my own sanitation against injections and that is why i'm trying to learn PDO is to remove the "human error" factor from my code in regards to sql injections – DerekConlon Oct 19 '15 at 22:12
  • 2
    @DerekConlon mysqli_ has prepared statements, you don't have to do your own sanitation. – Barmar Oct 19 '15 at 22:12
  • @DerekConlon See `mysqli_prepare()` and `mysqli_stmt_bind_param()`. – Barmar Oct 19 '15 at 22:13
  • Ah, I made my assumptions to quickly. Then I encourage you to take a look at the functions @Barmar mentioned above. You should always use prepared statements to insert data coming from users :) – John Svensson Oct 19 '15 at 22:24
0

I understand your question as:

Can I execute queries: UPDATE my_table SET a=1 and UPDATE my_table SET b=2 using only one statement

The answer is NO. Prepared statements won't solve your problem. What you can do is to compose a full update SQL statement and run it (one and the same) with different values, passing existing values for not modified fields.

For example you have a table:

|a|b|c|d|e|
-----------
|1|1|1|1|1|
|2|2|2|2|2|
|3|3|3|3|3|

You want to update a=5 for first row, c=7 for second and e=9 for last. So prepared statement will look like (anonymous parameters):

UPDATE my_table SET a=?, b=?, c=?, d=?, e=? WHERE a=?

And you fire it with data: 

array(5,1,1,1,1,1)
array(2,2,7,2,2,2)
array(3,3,3,3,9,3)
Community
  • 1
  • 1
Ostin
  • 1,511
  • 1
  • 12
  • 25