Member Avatar for Venter

i' saved the key names as column names in database.so i want to insert i all the values in database.with out mentioning the column names in the database.

<html>
<body>

<form action="#" method="post">
Firstname: <input type="text" name="name">
Lastname: <input type="text" name="lname">
Age: <input type="text" name="age">
<input type="submit">
</form>

</body>
</html>
<?php
if ($_SERVER["REQUEST_METHOD"] == "POST") 
{
$con = mysql_connect("localhost","root","enter");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("test", $con);


$insData=($_POST);

$columns = implode(",",array_keys($insData));

echo $columns;

$escaped_values = array_map('mysql_real_escape_string', array_values($insData));
$values  = implode(",", $escaped_values);

echo $values;
$sql = "INSERT INTO name ($columns) VALUES ('$values')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";




mysql_close($con);
}
?>

wen code executes it's displaying error like this:name,lname,ageESWARA,Manikanta Varma,22Error: Column count doesn't match value count at row 1...
i'm having table name with 3columns like name,lname.age. ..can u please correct my code

  • 4 Contributors
  • 5 Replies
  • 533 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by diafol
Member Avatar for diafol

Try this to see what's happening:

$sql = "INSERT INTO name ($columns) VALUES ('$values')";
echo  "<br />" . $sql . "<br />";
Member Avatar for broj1

On line 35 the each of the values should be enclosed in quotes, not the whole string $values. Try this:

$values = "'" . implode("','", $escaped_values) . "'";

This code will produce a string: 'value1', 'value2' ..., 'valueN' which you can safely use in your query (remove the quotes around $values):

$sql = "INSERT INTO name ($columns) VALUES ($values)";
Edited by broj1
commented: good spot +14
Member Avatar for diafol

Good spot broj. By the same token, you could include backticks (`) in the keys -> fields implode.

Member Avatar for rsleventhal

I've been using a great function I found online (abeautifulsite.net) for inserting an array into a MySQL data table.

The function was designed to take an array (typically $_POST), allow you to exclude particular field names, provide the table name and the array name and let it fly. It is a *real* time saver for me and all credit to Cory LaViska for writing and sharing it.

function mysql_insert_array($table , $data, $exclude = array())
/**
Credit to Cory LaViska of abeautifulsite.net for this function

 Argument   Type            Explanation
 $table     String          The name of the database table to insert into
 $data      Array           The associative array containing fieldnames
                            as keys and values
 $exclude   String/Array    Optional string or array of field names
                            to exclude from the insertion. Useful for
                            excluding certain elements when using
                            this on $_POST
 */
{
    $fields = $values = array();

    if (!is_array($exclude)) {
        $exclude = array($exclude);
    }

    foreach(array_keys($data) as $key) {
        if (!in_array($key, $exclude)) {
            $fields[] = "`$key`";
            $values[] = "'" . mysql_real_escape_string($data[$key]) . "'";
        }
    }

    $fields = implode(",", $fields);
    $values = implode(",", $values);
    $sql = "INSERT INTO `$table` ($fields) VALUES ($values)";

    if (mysql_query($sql)) {
        return array("mysql_error" => false,
            "mysql_insert_id" => mysql_insert_id(),
            "mysql_affected_rows" => mysql_affected_rows(),
            "mysql_info" => mysql_info()
            );
    } else {
        return array("mysql_error" => mysql_error());
    }
}

HTH,
-Ray

Member Avatar for diafol

You could add gettype() and a loop to the above to apply escaping or "'" to strings. However, I don't think that placing '...' about numbers causes problems. Somebody correct me if I'm wrong.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.