Inserting into mySQL with PHP but nothing shows up in phpmyadmin

Question

While trying to insert into a mySQL database I get no errors but nothing shows up in the phpmyadmin query. Here is the code for the php file that I am trying to insert with. I know this isn't the best way to do it but it's what I have so far.

<?php

require 'steamauth/steamauth.php';
require 'php/main.php';
require 'php/db.php';

$title = $_POST["title"];
$desc = $_POST["desc"];
$date = $_POST["date"];
$skills = $_POST["skills"];
$budget = $_POST["budget"];
$tc = $_POST["tc"];
$steamid = $steamprofile['steamid'];
$steamname = $steamprofile['personaname'];
$avatar = $steamprofile['avatarmedium'];
$dateposted = date("m-d-y");


// insert into db

if(1==1){

  $insert = $link->query("INSERT INTO livepost (title, desc, date, skills, budget, tc, steamid, steamname, avatar, dateposted)
  VALUES ('$title', '$desc', '$date' , '$skills' , '$budget', '$tc', '$steamid', '$steamname', '$avatar', '$dateposted')");

  header('Location: index');

} else {

  header('Location: index?insertdidntwork');

}

How do you know there are no errors, is display errors on? Does your code output anything at the end? it redirects but what page is this, the same as the redirect. How can you redirect to `index` maybe `index.php` or are you re-writing the urls. — ArtisticPhoenix, Jul 30 '16 at 09:35
I have error_reporting turned on just to make sure that I can see an error if one occurs, the page is blank when going to it, and yes I am re-writing the urls. — Sten Cooley, Jul 30 '16 at 09:39
@StenCooley look in the error logfile of your webserver. maybe you find some Output there — Jens, Jul 30 '16 at 09:41
@ArtisiticPhoenix Yes I know, I will be adding some sanitization later on when I get the main part working. — Sten Cooley, Jul 30 '16 at 09:42
As @Jens said you have keywords that need backticking. So there has to be query errors you are not seeing. — ArtisticPhoenix, Jul 30 '16 at 09:44
@Jens I don't see any errors in the XAMPP control panel or log file. — Sten Cooley, Jul 30 '16 at 09:45
@StenCooley Look this thread http://stackoverflow.com/questions/5127838/where-does-php-store-the-error-log-php5-apache-fastcgi-cpanel where you have to take a look — Jens, Jul 30 '16 at 09:46
Add `ini_set('display_errors', -1);` to the top of your page. — ArtisticPhoenix, Jul 30 '16 at 09:47
@ArtisiticPhoenix Ok I added it and it still shows nothing on the page. — Sten Cooley, Jul 30 '16 at 09:48
@ArtisiticPhoenix Should I go learn prepared statements and try that instead of this? — Sten Cooley, Jul 30 '16 at 09:49
@StenCooley - then error reporting is not on, `error_reporting(E_ALL); ini_set('display_errors', -1);` Or better, echo that query and paste it into phpmyadmin, let me know if it works... Prepared statements wont solve the issue, it's best to try the query right in the DB and cut all the PHP code out. Once you get the Query working, then work from there. I would just do `echo $insert;` right after where you define `$insert`. If the echo fails to print, you have a syntax error, also you can check the data is correct that way. — ArtisticPhoenix, Jul 30 '16 at 09:49
@PrashantSrivastav I think link works because I have another query setup the same exact way and it used $link just fine. — Sten Cooley, Jul 30 '16 at 10:01
@ArtisiticPhoenix Ok I made the query in phpmyadmin and it worked fine but when copy and pasting it and changing the variables it didnt work. — Sten Cooley, Jul 30 '16 at 10:02
@StenCooley - I don't understand, can you show what both queries look like. For example can you paste them in the question or a comment. — ArtisticPhoenix, Jul 30 '16 at 10:04
That is not a query it's php code that makes a query. the value of `$insert` is the query. Well minus the `$link->query()` method. For example the raw text of the query. If you cant paste that into the SQL window in phpmyadmin it's not the query. here is a big big hint, `'desc', 'date',` is in the fist query, as others have answered. There is 15 min of my life I wont get back. — ArtisticPhoenix, Jul 30 '16 at 10:12
@ArtisiticPhoenix O ya forgot to remove the php off of the first one! Here are both "queries". First Query: `INSERT INTO livepost (title, 'desc', 'date', skills, budget, tc, steamid, steamname, avatar, dateposted) // VALUES ('$title', '$desc', '$date' , '$skills' , '$budget', '$tc', '$steamid', '$steamname', '$avatar', '$dateposted')` Second Query: `INSERT INTO `livepost`(`postid`, `title`, `desc`, `date`, `skills`, `budget`, `tc`, `steamid`, `steamname`, `avatar`, `dateposted`) VALUES ([value-1],[value-2],[value-3],[value-4],[value-5],[value-6],[value-7],[value-8],[value-9],[value-10],[])` — Sten Cooley, Jul 30 '16 at 10:16
@StenCooley - `'$title'` is still php code... And this is some kind of placeholder `[value-1]` from phpmyadmin I presume. A query is like this `INSERT INTO livepost (title, 'desc', 'date', skills, budget, tc, steamid, steamname, avatar, dateposted) VALUES ( 'foobar' , 1, 'something' ... )` With actual data in it... — ArtisticPhoenix, Jul 30 '16 at 10:17
@ArtisiticPhoenix Ok now I get what you are saying. Seeing mohammed's answer shows me how to quote them when putting them into a query. — Sten Cooley, Jul 30 '16 at 10:20
@ArtisiticPhoenix Thank you for helping me resolve the issue. — Sten Cooley, Jul 30 '16 at 10:20
@StenCooley - those are not quotes around the `desc` and `date`, they are backtics ( ` ), same thing used to put code in these comments, same key as the tilde ( ~ ) but without the shift. That said, personally I would rename them, and avoid that issue altogether. Such as `desc` becomes `description` ? and `date` becomes `create_date` also avoid spaces in column names or you have same issue such that you would need backtics for `create date` — ArtisticPhoenix, Jul 30 '16 at 10:21
Yea that is how they are escaped in the DB, but it's best to avoid using those words and/or spaces. Then it is never an issue again. By the way `desc` is like `ORDER BY field DESC` descending. and date is like `WHERE DATE( created ) = '2016-01-01'` so you can query a datetime without the time component `( 2016-01-01 10:14:52 )`. For example these words have special meaning in the SQL language. It's kin to naming a php function `function`, you would never do `fuction function(){ }` in php. — ArtisticPhoenix, Jul 30 '16 at 10:25

Jens · Answer 1 · 2016-07-30T09:39:47.800

3

date and desc are keywords in mysql. You have to escape the column names:

  $insert = $link->query("INSERT INTO livepost (title, `desc`, `date`, skills, budget, tc, steamid, steamname, avatar, dateposted)
  VALUES ('$title', '$desc', '$date' , '$skills' , '$budget', '$tc', '$steamid', '$steamname', '$avatar', '$dateposted')");

Hint 1: learn about prepred statemts7

Hint 2: check for Errors after executing SQL Statements.

Hint 3: your if elsestatement is not checking if the insert works.

edited Jul 30 '16 at 09:39

answered Jul 30 '16 at 09:26

Jens

67,715
15
98
113

date is a keyword... desc is reserved word. Still a +1 ... lol – ArtisticPhoenix Jul 30 '16 at 09:38
1

@StenCooley - error reporting and display errors are 2 separate things, `ini_set('display_errors', -1);` – ArtisticPhoenix Jul 30 '16 at 09:45

score 0 · Answer 2 · answered Jul 30 '16 at 09:57

@Jens already pointed about keywords.

Using Prepared Statements.

<?php
if(1==1){

  $insert = $link->prepare("INSERT INTO livepost (title, `desc`, `date`, skills, budget, tc, steamid, steamname, avatar, dateposted) VALUES (?,?,?,?,?,?,?,?,?,?)");
  $insert->bind_param("sss",$title,$desc,$date,$skills,$budget,$tc,$steamid,$steamname,$avatar,$dateposted );
  $insert->execute();

  header('Location: index');

} else {

  header('Location: index?insertdidntwork');

}

May be what I think problem is in this line. $dateposted = date("m-d-y");

$dateposted = date("Y-m-d"); date should be in Y-m-d format.

Try once like this. And, Please respond back.

It spits out this error "Fatal error: Uncaught Error: Call to a member function bind_param() on boolean in H:\Websites\Xampp\htdocs\Freelanceforskins\post.php:27 Stack trace: #0 {main} thrown in H:\Websites\Xampp\htdocs\Freelanceforskins\post.php on line 27" — Sten Cooley, Jul 30 '16 at 10:06

score 0 · Accepted Answer · answered Jul 30 '16 at 10:05

maybe you can try this one and you should always use $mysqli->error() function to know what type of error you have

   <?php
$title = $_POST["title"];
$desc = $_POST["desc"];
$date = $_POST["date"];
$skills = $_POST["skills"];
$budget = $_POST["budget"];
$tc = $_POST["tc"];
$steamid = $steamprofile['steamid'];
$steamname = $steamprofile['personaname'];
$avatar = $steamprofile['avatarmedium'];
$dateposted = date("m-d-y");
// insert into db
if (1 == 1) {
    $insert = $link ->query("INSERT INTO livepost (`title`,`desc`,`date`,`skills`,`budget`,`tc`,`steamid`,`steamname`,`avatar`,`dateposted`)

    VALUES ('".$title."', '".$desc."', '".$date."' , '".$skills."' , '".$budget."', '".$tc."', '".$steamid."', '".$steamname."', '".$avatar."', '".$dateposted."')") or die($mysqli -> error . __LINE__);
    header('Location: index');
} else {
    header('Location: index?insertdidntwork');
}
?>

Thank you! This fixed it and inserted the data into the database. — Sten Cooley, Jul 30 '16 at 10:14

Inserting into mySQL with PHP but nothing shows up in phpmyadmin

3 Answers3