automated my-sql-data insertion???

[andrecito]

is there a way to hand over a varying number of arguments to a function, which then inserts these arguments into a mysql-table?

 

the call would be something like:

 

insertNewRecord('name_of_field_1', $content_to_write_1, 'name_of_field_2', $content_to_write_2);

 

and the function:

 

function insertNewRecord() {

$num_args = func_num_args();

$args = func_get_args();

 

for ($n = 0; $n <= $num_args; $n = $n + 2) {

 

  $name_of_field = $args[$n];

          $content_to_write = $args[$n+1];

 

  mysql_query("INSERT INTO table_name ($name_of_field) VALUES ('$content_to_write')",  connectionID)

  or die ("Schreiben des Eintrags in die Datenbank nicht möglich.");

 

}

}

 

what happens is, that the for-command, logically, creates a new entry into the database with every loop.

 

what i need, instead, is a code that gives the same result, as

 

mysql_query("INSERT INTO table_name ($name_of_field_1, $name_of_field_2 ... n) VALUES ('$content_to_write_1', '$content_to_write_2' ... n)",  connectionID)

 

would do.

 

thank you very much in advance for any helpful idea.

cheers, andre

[trq]

Would be much easier and more efficient if you simply pass your function two arrays. You could even include the table name. eg;

 

<?php

  function insertrecord($tbl,$flds,$values) {
    $sql = "INSERT INTO $tbl (`" . implode("`,`",$flds) . "`) VALUES ('" . implode("','",$values) . "')";
    // execute query.
  }

  insertrecord('foo',array('name_of_field_1','name_of_field_2'),array( $content_to_write_1,$content_to_write_2));

?>

[aschk]

Put the query outisde the for loop...

 

par example:

<?php
function insertNewRecord() {
   $num_args = func_num_args();
   $args = func_get_args();

   for ($n = 0; $n <= $num_args; $n = $n + 2) {

      $name_of_field[] = $args[$n];
      $content_to_write[] = $args[$n+1];
   }

   mysql_query("INSERT INTO table_name (".implode(',',$name_of_field).") VALUES ('".implode(" ',' ",$content_to_write)."')",  connectionID)
           or die ("Schreiben des Eintrags in die Datenbank nicht möglich.");
}
?>

[andrecito]

smashing... you are great, guys!

 

now, just one little refinement to shorten the code:

 

imagine, the field's name is the same as the name of the var containing the content, e.g.

 

insertRecord(array('aaa','bbb'),array($aaa,$bbb));

 

is there any way i can change the functions code in such a way that i only have to pass the names once, and then use them as the field's and var's names?

 

cheers, andre

[trq]

Nope. Your using numericaly indexed arrays for starters. Your just going to add alot more complexity then need be.

 

I like to stick to the K.I.S.S priciples when programming. Keep it simple stupid.

[andrecito]

just asking, because if i deal with very long tables, i always have to write everything twice.

 

thought there would be an elegant way to simplify that.

[aschk]

Yes but it's not nice and very prone to error and unexpected behaviour:

for example:

<?php
${'aaa'} = 'aaa';
?>

 

So it's essentially a variable variable

e.g.

<?php
${$_POST['name']} = $_POST['name'];
?>

 

so if $_POST['name'] is "barry", you'll create a variable $barry and it'll be equal to "barry". So much like initialising it ($barry = "barry").

[aschk]

You could of course use an associative array an pass in a single array instead of two.

 

e.g.

<?php

$arr = array('aaa' => $aaa, 'bbb' => $bbb);
insertRecord($arr);

?>

[andrecito]

wonderful tips! thanks! i will look into that dynamic variable stuff a bit later.

 

i've got this wonderful piece of code now for the insertion of data:

mysql_query("INSERT INTO $table (".implode(',',$fields).") VALUES ('".implode(" ',' ",$values)."')", $connectionID)

 

---

 

could we develop a similar code for the selection and update process, too?

 

a function that updates my table if i call it with updateRecord(array('aaa','bbb'),array($aaa,$bbb));

 

the old code is: mysql_query("UPDATE $table SET aaa='$aaa', bbb='$bbb' WHERE id = '$id'", $connectionID)

 

cheers, andre

 

[aschk]

The process is similar, just build the query using a loop to go through the values.

This is where an associative array would be more semantic.

e.g.

<?php

// Function
function updateRecord($arr){

  $sql = "UPDATE $table SET ";
  foreach($arr as $key=>$value){
    $str[] = " $key = '$value' ";
  }
  $sql .= implode(",",$str);
  $sql .= "WHERE id = '$id' ";

  mysql_query($sql);

}

// Test
$arr = array('aaa'=>$aaa,'bbb'=>$bbb);
updateRecord($arr);

?>

[trq]

could we develop a similar code for the selection and update process, too?

 

Yes you could. You have some examples to work with, see how you go from there.

[andrecito]

:-)

 

i did thorpe, and it works well with the associated arrays. update and insert works great now! thank you!

 

there is only one last question, which i cannot solve. it is about reading the content of a table.

this is the old code that now has to be generalised, so that it works with arrays of variable sizes:

 

$result = mysql_query("SELECT * FROM $table WHERE id = $id", $connectionID)

$aaa = mysql_result($result, 0, "aaa");

$bbb = mysql_result($result, 0, "bbb");

 

is has to be transformed somehow into this direction (which doesnt work):

 

foreach($db as $field => $value) {

  $sql = '$result, 0, "'.$field.'"';

  $value = mysql_result($sql);

}

 

---

 

the final aim is the definition of an array in the beginning of the document, representing the fields and values of the table. then i can call the 3 different functions to update (solved), insert (solved) and read out.

 

$db = array('aaa' => $_POST['aaa'], 'bbb' => $_POST['bbb']);

 

function insertRecord($db) {

foreach($db as $field => $value) {

$fields .= $field.",";

$values .= "'".$value."',";

}

 

$sql = "INSERT INTO $GLOBALS[table][tr][td] ($fields) VALUES ($values)";

 

$success = mysql_query($sql, $GLOBALS[connectionID]);

}

 

 

function updateRecord($db) {

$sql = "UPDATE $GLOBALS[table][tr][td] SET ";

foreach($db as $field => $value) {

$sql .= "$field = '$value',";

}

$sql .= " WHERE id = '$GLOBALS[id]' ";

$success = mysql_query($sql, $GLOBALS[connectionID])

}