PHP not inserting into MySQL db

Question

I'm to post to a table in my localhosted MySQL database, but when i run the script the page stays white but there is nothing posted in the table. I'll add my code for clarification. Im using USBWebserver to host the apache and MySQL server on.

    <?php
//connectie leggen met input data
//$con = 

//variablen input invullen (bijv $gebruikeragenda = $_POST['gebruikeragenda'];
$gebruikeragenda = 'Testafspraak'; //hierin specifieren op welke account dit evenement geplaatst moet worden
$tegebruikenagenda = ''; //hierin specifieren we de agenda waarin het evenement geplaatst moet worden
$begintijd = '2014-01-13 15:30:00';         //begintijd specifieren
$eindtijd = '2014-01-13 16:30:00';          //eindtijd specifieren
$medeaanwezige = '';        //emailadressen van andere aanwezige tijdens dit evenement

//connectie leggen met MySQL(aanpasbaarnaar SQLite indien nodig)
$con=mysqli_connect("localhost:3307","root","usbw","baikal");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

//input van hierboven ombouwen en in de database van Baikal bouwen
$calendardata = $gebruikeragenda;                       
$uri = 'default';
$lastmodified = $begintijd;     //laatst gemodificeerd
$calenderid = '1';              //het id van de agenda waarbij de event hoort
$etag = 'niks';
$size = '200';                      //grootte   
$componenttype = '';            //wordt er gebruik gemaakt van een notificatie, voer hier dan VTODO in
$firstoccurence = $begintijd;   //begintijd van evenement
$lastoccurence = $eindtijd;     //eindtijd van evenement

//gegevens in de database plaatsen
mysqli_query($con,"INSERT INTO calendarobjects (calendardata, uri, calendarid, lastmodified, etag, size, componenttype, firstoccurence, lastoccurence) VALUES (".$calendardata.", ".$uri.", 
".$lastmodified.",".$etag.",".$size.",".$componenttype.",".$firstoccurence.",".$lastoccurence.")");
mysqli_close($con);

Wrap string values in quotes.... or even better switch to MySQLi/PDO prepared statements with bind variables — Mark Baker, Commented Jan 13, 2014 at 15:45
String values need to be in quotes. I'd also suggest you use prepared statements. — ajtrichards, Commented Jan 13, 2014 at 15:46
By building SQL statements with outside variables, you are leaving yourself wide open to SQL injection attacks. Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. bobby-tables.com/php has examples to get you started, and this question has many examples in detail. — Andy Lester, Commented Jan 13, 2014 at 15:57

Andy · Accepted Answer · 2014-01-13 15:50:51Z

2

Your columns and values do not match. You are attempting to insert 8 values into 9 columns.

Specifically, it appears you are missing a calendarid.

Second, your strings need to be wrapped in quotes.

Finally, you are vulnerable to SQL injections. Consider using prepared statements.

answered Jan 13, 2014 at 15:50

Andy♦

50.7k62 gold badges178 silver badges239 bronze badges

The code as written isn't vulnerable to injections, because all his variables are hardcoded.
– thelr
Commented Jan 13, 2014 at 15:53
1

@thelr, you make a valid point. I assumed those were just hard coded for purposes of posting the question though.
– Andy ♦
Commented Jan 13, 2014 at 15:54
Yup, btw do you have any idea why the MySQL table only shows 2014 at the lastmodified field while i've put in a whole datetime string?
– joostmakaay
Commented Jan 13, 2014 at 15:57
@joostmakaay That'll depend on how the field is defined. It is probably better asked in another question though. If your other date fields show correctly, perhaps ensure the lastmodified one is defined the same way.
– Andy ♦
Commented Jan 13, 2014 at 16:02

Add a comment |

Philipp · Accepted Answer · 2014-01-13 15:48:20Z

You have to wrap your string's in single or double quotes and keep sure, the number of params match the number of values..(calendarid is missing..)

mysqli_query($con,"INSERT INTO calendarobjects
    (calendardata, uri, calendarid, lastmodified, etag, size, componenttype, firstoccurence, lastoccurence)
  VALUES
    ('$calendardata', '$uri', 'missing calendarid', '$lastmodified', '$etag','$size','$componenttype','$firstoccurence','$lastoccurence')");
mysqli_close($con);

Btw.. your code is vulnerable to SQL injections, so you have to use real_escape_string(link) or better prepared statements!

Awlad Liton · Accepted Answer · 2014-01-13 15:49:19Z

You need to use quotation for values.

You have 9 attributes and 8 values. You have missed one value in your query.Set another value in your query and Try this:

 <?php
    //connectie leggen met input data
    //$con = 

    //variablen input invullen (bijv $gebruikeragenda = $_POST['gebruikeragenda'];
    $gebruikeragenda = 'Testafspraak'; //hierin specifieren op welke account dit evenement geplaatst moet worden
    $tegebruikenagenda = ''; //hierin specifieren we de agenda waarin het evenement geplaatst moet worden
    $begintijd = '2014-01-13 15:30:00';         //begintijd specifieren
    $eindtijd = '2014-01-13 16:30:00';          //eindtijd specifieren
    $medeaanwezige = '';        //emailadressen van andere aanwezige tijdens dit evenement

    //connectie leggen met MySQL(aanpasbaarnaar SQLite indien nodig)
    $con=mysqli_connect("localhost:3307","root","usbw","baikal");
    // Check connection
    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    //input van hierboven ombouwen en in de database van Baikal bouwen
    $calendardata = $gebruikeragenda;
    $uri = 'default';
    $lastmodified = $begintijd;     //laatst gemodificeerd
    $calenderid = '1';              //het id van de agenda waarbij de event hoort
    $etag = 'niks';
    $size = '200';                      //grootte   
    $componenttype = '';            //wordt er gebruik gemaakt van een notificatie, voer hier dan VTODO in
    $firstoccurence = $begintijd;   //begintijd van evenement
    $lastoccurence = $eindtijd;     //eindtijd van evenement
    $sql = "INSERT INTO calendarobjects (calendardata, uri, calendarid, lastmodified, etag, size, componenttype, firstoccurence, lastoccurence) VALUES ('$calendardata', '$uri',
    '$lastmodified','$etag','$size','$componenttype','$firstoccurence','$lastoccurence')";
    //gegevens in de database plaatsen
   if( mysqli_query($con,$sql)){
    echo "successully inserted";  
   }else{
    echo "something wrong";
   }
    mysqli_close($con);

Collectives™ on Stack Overflow

PHP not inserting into MySQL db

3 Answers 3

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Linked

Related