CODEWITHSUNDEEP
phpsqlpostgresqlpost
Kilisi
Kilisi

Reputation: 444

Limit to post for sql?

I have a need to update a table with 14 post variables. When I do exactly the same thing with 2 variables it works fine, but when I use 14 it doesn't update. I've tried all sorts of variations on the code, this is my latest which doesn't work, but doesn't give me an error either. Is it because I have so many post variables, when I print them they're all correct.

<?php print_r($_POST); ?>
<?php

$v = array();
$v[0] = $_POST['id'];
$v[1] = $_POST['first_name'];
$v[2] = $_POST['last_name'];
$v[3] = $_POST['address1'];
$v[4] = $_POST['address2'];
$v[5] = $_POST['address3'];
$v[6] = $_POST['address4'];
$v[7] = $_POST['no'];
$v[8] = $_POST['type'];
$v[9] = $_POST['reference'];
$v[10] = $_POST['relationship'];
$v[11] = $_POST['occupation'];
$v[12] = $_POST['comments'];
$v[13] = $_POST['source'];

$dbh = dbh_get();

$sql = "UPDATE transfer SET (first_name, last_name, address1, address2, address3, address4, no, type, reference, relationship, occupation, comments, source) VALUES ('.$v[1].','.$v[2].','.$v[3].','.$v[4].','.$v[5].','.$v[6].','.$v[7].','.$v[8].','.$v[9].','.$v[10].','.$v[11].','.$v[12].','.$v[13].') WHERE transfer_id='.$v[0].'";
$stmt = $dbh->prepare($sql);
$stmt->execute($v);

dbh_free($dbh)
?>

Originally I didn't use quotations just apostrophe's and had id last rather than first but didn't work either. Like this:-

$sql = 'UPDATE transfer SET first_name=?, last_name=?, address1=?, etc,..... WHERE id=?';

This worked ok when it was just two post variables, but when I scaled it up it broke.

Upvotes: 0

Views: 72

Answers (3)

Kalaivani M
Kalaivani M

Reputation: 1300

Try you update query like this 

    $sql = "UPDATE transfer SET
     pay_first_name = '".$v[1]."',
     pay_last_name  = '".$v[2]."',
      pay_address1  = '".$v[3]."', 
      pay_address2 = '".$v[4]."', 
      pay_address3 = '".$v[5]."', 
      pay_address4 = '".$v[6]."', 
      pay_contact_no = '".$v[7]."', 
      pay_id1_type = '".$v[8]."', 
      pay_id1_reference = '".$v[9]."', 
      relationship = '".$v[10]."', 
      occupation = '".$v[11]."', 
      comments = '".$v[12]."', 
      source = '".$v[13]."' WHERE transfer_id ='.$v[0];

Upvotes: 0

Aung
Aung

Reputation: 65

try prepared statement like this

$stmt = $dbh->stmt_init();
$stmt->prepare("UPDATE transfer SET first_name = ?, last_name = ?, address1 = ?, address2 = ?, address3 = ?, address4 = ?, no = ?, type = ?, reference = ?, relationship = ?, occupation = ?, comments = ?, source = ? WHERE id = ?");
$stmt->bind_param("sssssssssssssi", $v[1], $v[2], $v[3], $v[4], $v[5], $v[6], $v[7], $v[8], $v[9], $v[10], $v[11], $v[12], $v[13], $v[0]);
$stmt->execute() or die($stmt->error);
$stmt->close();

Upvotes: 1

Murali
Murali

Reputation: 430

Couple of suggestions:

  1. You can change php configuration during runtime. If you do not see errors you can switch display_errors option on.

    ini_set('display_errors','1');

  2. Use try-catch to get error stack
  3. Print the query built on the screen to see if there are any errors in them. Use the query printed on the screen in mysql terminal directly to check if it throws any errors
  4. use addslashes() to escape special characters in the values to be updated
  5. numbers (int, bigint, tinyint etc) need not be enclosed with quotes in the queries
  6. Using PDO and prepared statements are better approaches at performing db operations

As long as the query is formed properly there shouldn't be a problem in execution (irrespective of the no of post variables used).

You have started your query string with double quotes but are using single quoted while concatenating. See if changing that helps solve the issue:

$query = "UPDATE transfer SET pay_first_name ='".$v[1]."'";

Or

$query = "UPDATE transfer SET pay_first_name ='".addslashes($v[1])."'";

Upvotes: 1

Related Questions