Dynamic array creation for mysql update - php

Question

How would I create an dynamic query like that in insertIntoDb for updateDbRecord? Given that I want to update a specific ID using update_id=$_GET['id']; since my ID is stored in my URL i.e. somepage.php?id=12

function insertIntoDb($db, $table, $carry, $carryUrl) {
    mysql_select_db($db) or die("Could not select database. " . mysql_error());
    $resultInsert = mysql_query("SHOW COLUMNS FROM " . $table);
    $fieldnames=array();
      if (mysql_num_rows($resultInsert) > 0) {
        while ($row = mysql_fetch_array($resultInsert)) {
            $fieldnames[] = $row['Field'];
            $values = array_intersect_key( $_POST, array_flip($fieldnames) );
        }
      }
      $sql = sprintf('INSERT INTO %s (%s) VALUES ("%s")', $table, 
      implode(', ', array_map('mysql_escape_string', array_keys($values))), implode('", "',array_map('mysql_escape_string', $values))); 
      mysql_query($sql);
      /* if ($carry == 'yes') {
        redirect($carryUrl.'?id='.$_REQUEST['id']);
      }
      else { echo '<div class="success">Data was entered into the database successfully!<br><a href="view.php?type=recent">View listing?</a></div>'; } */
}

function updateDbRecord($db, $table) {
    mysql_select_db($db) or die("Could not select database. " . mysql_error());
    $resultInsert = mysql_query("SHOW COLUMNS FROM " . $table . " WHERE Field NOT IN ('id')");
    $fieldnames=array();
      if (mysql_num_rows($resultInsert) > 0) {
        while ($row = mysql_fetch_array($resultInsert)) {
            $fieldnames[] = $row['Field'];
            $values = array_intersect_key( $_POST, array_flip($fieldnames) );
        }
      }
      #update syntax
}

Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information. — tereško
– tereško, Commented Aug 3, 2012 at 17:47

T I · Accepted Answer · 2012-07-01 11:12:39Z

1

something along these lines to create the update statement.

// todo sanitise with mysql_escape_string()
foreach($arr as $key => $v) {
    $val = is_numeric($v) ? $v : "'" . $v . "'";

    $set .= sprintf("%s=%s%s", $key, $val, ($v == end($arr) ? "" : ", "));
}

$sql = sprintf("UPDATE %s SET %s", $table, $set);

edited Jul 1, 2012 at 11:12

answered Jun 30, 2012 at 21:42

T I

9,9634 gold badges31 silver badges51 bronze badges

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

5 Comments

Alex Over a year ago

$sql = sprintf("UPDATE %s SET %s WHERE id=".$_GET['id'], $table, $set); doesnt seem to be working?

T I Over a year ago

@Alex have you checked the value of $sql, is the value of $_GET['id'] correct? why not do it like sprintf("UPDATE %s SET %s WHERE id=%s", $table, $set, $_GET['id']);

Alex Over a year ago

i made a few changes in the form by adding a hidden input field that gets the id as written in the url then posts it to the update sprintf. Working like a charm! thanks tom!

Alex Over a year ago

Tom it seems like there is a problem arising after all UPDATE projects SET project_name='asd', project_bold='sadasd', project_content='', WHERE id='12' - the last field in the array has a comma which is preventing it from working. is there a way of using implode to fix this?

T I Over a year ago

@Alex should be fixed, look at impode there is a function to implode associative arrays, here is an adaption. don't forget to pass $key and $val to mysql_real_escape_string or similar

Collectives™ on Stack Overflow

Dynamic array creation for mysql update - php

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related