[nycphp-talk] inserting data into MySQL table.
Jon Baer
jonbaer at jonbaer.com
Mon Jul 23 11:55:11 EDT 2007
Id guess that one of your data types (or more) is still not valid for
the type declared but ~not sure if you know this~ but you can add
column names before your query ...
INSERT INTO my_table (col1_name, col2_name) VALUES (col1_value,
col2_value) // Try a few values up until you error out
First do a print_r($_POST) and maybe sure data is valid coming in
(and make sure to scrub it before you deploy), then dump the $query
itself to make sure it's valid.
Then look @ CakePHP to save yourself from getting this type of
headache :-)
- Jon
On Jul 23, 2007, at 11:24 AM, PaulCheung wrote:
> I have been having real difficulties try to insert a 20 column
> record into a table (using PHP not MySQL monitor). If I hardcode I
> can add a record .Taking the script that works I then doctor it
> substituting the hardcoding with $_POST values and then fall over
> (meaning no record is written). I know I have got to the point of
> writing away a record by putting a checkpoint just before the
> write. I have tested the insert-record-into-database with a smaller
> database of 4 columns and it all works fine, but with my MySQL 20
> columns it just doesn't work can anybody see what I am doing wrong?
>
> This works
> <html>
> <head>
> <title>Insert Entry Results</title>
> </head>
> <body>
> <h1>Entry Results</h1>
> <?php
> # WORKING STORAGE
> // create short variable names
> $A = 1;
> $B = 12345678;
> $C = 1234;
> $D = 001;
> $E = TESTA;
> $F = TESTB;
> $G = 2007-07-21;
> $H = TESTC;
> $J = TESTD;
> $K = TESTE;
> $L = TESTG;
> $M = TESTH;
> $N = TESTJ;
> $P = TESTK;
> $Q = TESTL;
> $R = TESTM;
> $S = TESTN;
> $T = TESTP;
> $U = TESTQ;
> $V = TESTR;
> # "environment" "user" "password"
> "database name"
> $db = new mysqli( "localhost", "paul", "enter",
> "test_db" )
> or die( "Sorry - could not connect to MySQL" );
>
> if (mysqli_connect_errno())
> {
> echo 'Error: Could not connect to database. Please try again
> later.';
> exit;
> }
> #
> echo ' CHECKPOINT ';
> echo $A.$B.$C.$D.$E.$F.$G.$H.$J.$K.$L.$M.$N.$P.$Q.$R.$S.$T.$U.$V;
> #
> $query = "insert into testdata_table values
> ( '".$A."', '".$B."', '".$C."', '".$D."', '".$E."',
> '".$F."', '".$G."', '".$H."', '".$J."', '".$K."', '".
> $L."', '".$M."',
> '".$N."', '".$P."', '".$Q."', '".$R."', '".$S."',
> '".$T."', '".$U."', '".$V."' )";
> $result = $db->query($query);
>
> if ($result)
> echo $db->affected_rows.' record inserted into database.';
>
> $db->close();
> ?>
> </body>
> </html>
>
> ==========================THIS DOESN'T
> WORK===============================================
> <html>
> <head>
> <title>insert into testdata_table</title>
> </head>
> <body>
> <h1>test_db - testdata_table</h1>
>
>
> <?php
> # WORKING STORAGE
> $A = 1;
> $B = 12345678;
> $C = 1234;
> #
> $D = $_POST['D'];
> $E = $_POST['E'];
> $F = $_POST['F'];
> $G = $_POST['datum'];
> $H = $_POST['H'];
> $J = $_POST['J'];
> $K = $_POST['K'];
> $L = $_POST['L'];
> $M = $_POST['M'];
> $N = $_POST['N'];
> $P = $_POST['P'];
> $Q = $_POST['Q'];
> $R = $_POST['R'];
> $S = $_POST['S'];
> $T = $_POST['T'];
> $U = $_POST['U'];
> $V = $_POST['testnote'];
>
> # "environment", "user", "password",
> "database name" )
> $db = new mysqli( "localhost", "paul", "enter",
> "test_db" )
> or die( "Sorry - could not connect to MySQL" );
>
> if (mysqli_connect_errno())
> {
> echo 'Error: Could not connect to database. Please try again
> later.';
> exit;
> }
> # TEMP TESTING CHECKPOINT is record correct?
> echo $A.$B.$C." ".$D.$E.$F.$G.$H.$J.$K.$L.$M.$N.$P.$Q.$R.$S.$T.
> $U.$V;
>
> # write "testdata_table
> $query = "insert into testdata_table values
> ('".$A."', '".$B."', '".$C."', '".$D."', '".$E."', '".$F."', '".
> $G."', '".$H."', '".$J."', '".$K."',
> '".$L."', '".$M."', '".$N."', '".$P."', '".$Q."', '".$R."'; '".
> $S."', '".$T."', '".$U."', '".$V."')";
>
>
>
> $result = $db->query($query);
> if ($result)
> echo $db->affected_rows.' inserted into test_db.';
> $db->close();
> ?>
> </body>
> </html>
> _______________________________________________
> New York PHP Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
>
> NYPHPCon 2006 Presentations Online
> http://www.nyphpcon.com
>
> Show Your Participation in New York PHP
> http://www.nyphp.org/show_participation.php
More information about the talk
mailing list