NYCPHP Meetup

[Jon Baer]

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