[RESOLVED] How can I INSERT/UPDATE complete table into mysql table?

shersaha

How can I update/insert one complete table into mysql table. Input data will be from another page (a form html/php) and data will be update/insert into mysql table.

table would be like this :

total coloum-- 11 , total row -- 7.

I want update in one go.. it may be using loop or otherwise.. please help me..

Thanks in advance..

NogDog

You could use the REPLACE SQL command with multiple input sets, something like:

REPLACE INTO table_name (`col1`, `col2`, `col3`)
VALUES ('val11', 'val12', 'val13'), ('val21', 'val22', 'val23'), ('val31', 'val32', 'val33')

The above would insert 3 rows of 3 columns, replacing any existing rows which have the same value for any primary key or unique index columns.

So if you could grab the inputs from your form page and create a 2-dim array of values...

$myArray:
Array
(
    [0] => Array
        (
            [col1] => val11
            [col2] => val12
            [col3] => val13
        )

[1] => Array
    (
        [col1] => val21
        [col2] => val22
        [col3] => val23
    )

[2] => Array
    (
        [col1] => val31
        [col2] => val32
        [col3] => val33
    )

)

...then you could implode everything together into your value expression:

$values = "";
foreach($myArray as $data)
{
   $values = "('" . implode("','", $data) . "'),";
}
$values = trim($values, ','); // get rid of trailing comma
$query = "REPLACE INTO table_name ('col1', 'col2', 'col3') VALUES $values";

shersaha

Thanks NogDog for your reply.. But I have some problem with it.. My code is like thi :

<?php
$con = mysql_connect("$host","$user","$password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("my_db", $con);

$myArray=
Array
(
[0] => Array
(
[firstname] => '$POST[firstname]'
[lastname] => '$POST[lastname]'
[age] => '$_POST[age]'
[update_date]=>now()
)

[1] => Array
    (
        [firstname1] => '$_POST[firstname]'
        [lastname1] => '$_POST[lastname]'
        [age] => '$_POST[age]'
         [update_date]=>now()
    )

[2] => Array
    (
        [firstname2] => '$_POST[firstname]'
        [lastname1] => '$_POST[lastname]'
        [age] => '$_POST[age]'
         [update_date]=>now()
    )

)

$values = "";
foreach($myArray as $data)
{
$values = "('" . implode("','", $data) . "'),";
}
$values = trim($values, ','); // get rid of trailing comma
$query = "REPLACE INTO table_name ('firstname', 'lastname', 'age','update_date')

VALUES $values";

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

mysql_close($con)
?>

error code is like this :
Parse error: parse error, expecting `')'' in f:....\www\insert_old.php on line 13

please help.. thanks in advance

NogDog

The array definition would be more like:

$myArray= array
(
   array
   (
      'firstname' => $_POST['firstname'],
      'lastname' => $_POST['lastname'],
      'age' => $_POST['age'],
      'update_date' => 'now()';
   ),
   array
   (
      'firstname' => $_POST['firstname'],
      'lastname' => $_POST['lastname'],
      'age' => $_POST['age'],
      'update_date' => 'now()';
   ),
   array
   (
      'firstname' => $_POST['firstname'],
      'lastname' => $_POST['lastname'],
      'age' => $_POST['age'],
      'update_date' => 'now()';
   )
);

Of course, the $_POST values being assigned should be different in each section; but I don't know what your form looks like and how the fields are named.

shersaha

Thanks NogDog for your reply.. Though some problem solved still there is some problem. Now I can insert multiple row in a single insert/replace using array. but in my code one small problem persist.
when this code : 'update_date' => 'now()'; is executed ... error shows like this :
Parse error: parse error, expecting `')'' in f:...\insert_old.php on line 20

my mysql data base have total five coloum :

personID --- int(11) not null autoincrement
FirstName --- varchar(25) not null
LastName --- varchar(15) not null
Age ---------int(2) not null
Update_date -- timestamp.

My form have three coloum FirstName,LastName,Age , PersonId will be automatically updated automatically. update_date will be update through action page through now() function.

If I use : 'update_date' => 'now()' then not a single error is shown but update_date coloum is not updated : its default value 0000:00:00 00:00:00 is kept intact.

If I use 'update_date' => now() i.e. without quote of now() function then it shows errors like this : Fatal error: Call to undefined function: now() in ....

now My question is ,is it possible to update current timestamp in mysql through php page or not using mysql function now().

thanks in advance..

shersaha

How can I insert timestamp in mysql database from php web page?

bradgrafelman

Generate a MySQL DATETIME value with PHP?

date('Y:m:d H:i:s');

Or, have MySQL do it:

INSERT INTO myTable (dateField, otherField) VALUES ( NOW(), 'blah' );

Note that you don't want quotes around the NOW() function.

shersaha

Thanks bradgrafelman,

your first option : date('YⓂd H:i:s'); is working fine.. but your second option
INSERT INTO myTable (dateField, otherField) VALUES ( NOW(), 'blah' ); is not woking ..even
using now() in small. It shows error :
'update_date' => now() i.e. without quote of now() function then it shows errors like this : Fatal error: Call to undefined function: now() in ....

is there any way using now()/NOW() function in php ? please write back..

Thanks

bradgrafelman

The only way to imitate now() in PHP is to use the date() function as I described.

The problem is that your PHP code is automatically adding quotes around each array value (with the implode() call). So, when you add 'update_date' => 'now()' to the array, now() will have quotes around it. Obviously doing 'update_date' => now() isn't going to work - PHP thinks you're trying to call a function, and besides this isn't where the strings are being added.

With the way your code is, using the date() is your only option (without re-working the implode() call and every array item).

shersaha

Thanks bradgrafelman for your reply..