Why does my 'database insert' not work in production?

[tdrsam]

I moved my web app from development (local machine) to production (web server) and the script I have that inserts to the database won't work.

This is the script:

<?php
include ('includes/DbCon.php');
	
//Set directory etc for image upload
$target_dir = "images/photo/";
$target_file = $target_dir . basename($_FILES["image"]["name"]);

if (move_uploaded_file($_FILES["image"]["tmp_name"], $target_file))
{
echo '<script type="text/javascript">';
echo 'alert("News Items Saved")';
echo '</script>';
} else {
echo "Sorry, there was an error with your file.";
}

// Variables
if (isset($_POST['headline']))$headline = $_POST['headline'];
if (isset($_POST['body']))$body = $_POST['body'];
if (isset($_POST['image']))$image = $_POST['image'];

//Insert into Db
if(isset($_POST['submit'])){
$query = "INSERT INTO news (`headline`, `body`, `image`) 
VALUES ('$headline', '$body', '".$_FILES['image']['name']."');";
$result = $mysqli->query($query);
echo '<script type="text/javascript">';
echo 'document.location.href = "/admin/admin-news.php";';
echo '</script>';
}
else
{
echo '<script type="text/javascript">';
echo 'alert("The news item was not able to be created. Please try again."). mysql_error()';
echo '</script>';
}
$mysqli->close();	
?>

Could it be server settings?

[Dormilich]

it could be. you’ll need to start debugging.

[computerfox]

And how should he "start debugging"?

@OP did it work in your test environment? Are the two environments the same? Do you have the same version of PHP and MySQL? Are the services running?

[tdrsam]

This is embarrassing.

I was connecting to the wrong database. So, yeah, fixed that problem.

However, although I'm getting the read from the database, I can't get the insert. I'm almost sure it should work. I even put a line to display an error message after the execution of the insert statement, but the browser goes straight past it. It's inserting the image to the directory it's supposed to, then displays the javascript alert, then goes on the databse insert, runs past that to the javascript redirect and goes to the page it's supposed to. So, everything seems to be working except for when I check the database and the values that were supposed to be inserted are not actually in there.

Can't figure it out.

[tdrsam]

I've been conversing with someone at the hosting company about this and they suggested a var_dump, which showed me that the values for first two variables are posting correctly, but there's no value (nor even a listing of the name) for the 'image' variable, which is a file upload.

Also, there's a record of the submit button in the var_dump. Is it supposed to do that? It returns a string of 16 characters, being 'Create News Item', which is the value given at the form, so it seems to be passing that value which I suspect could be a problem. I tried removing the value attribute from the submit button but that didn't change anything. I even tried changing the input type from input to button. Also nothing.

I also tested the delete part of the system I'm making and that won't interact with the Db either. I thought if anything would work it'd be that, so it seems like it might not be the way I've set out the scripts that interact with the database but possibly the forms I'm using or the file upload.

[computerfox]

Normally, there should be no need to talk to the hosting company. They're usually there just for the services and not the consulting. I've been there too, they're not very helpful....

Anyway, I modified the code:

<?php
 require('includes/DbCon.php');
 
 //Set directory etc for image upload
 $target_dir = "http://bytes.com/images/photo/";
 $target_file = $target_dir . basename($_FILES["image"]["name"]);
 
 //Insert into Db
 if(isset($_POST['submit'])){
 // Variables
  $headline=$_POST['headline'];
  $body=addslashes($_POST['body']);
  $image_path="default.png";
  if($_FILES['image']['name']!=""){
   if(move_uploaded_file($_FILES["image"]["tmp_name"], $target_file)){
    $image_path=$target_file;
   }
   else {
    ?><script>alert("There was an error uploading your image...");</script><?php
   }
  }
  //Completely off!
  //$query = "INSERT INTO news (`headline`, `body`, `image`)
  //VALUES ('$headline', '$body', '".$_FILES['image']['name']."');";
  //$result = $mysqli->query($query);
  //echo '<script type="text/javascript">';
  //echo 'document.location.href = "/admin/admin-news.php";';
  //echo '</script>';
  $query="insert into news (headline,body,image) values ('$headline','$body','$image_path')";
  $succ=$mysqli->query($query);
  if($succ){
    print '<meta http-equiv="refresh" content="0; url=./admin/admin-news.php" />'; 
    //Don't use JavaScript to redirect.  What if it's diabled?
   //Also, I believe the path is wrong for the redirect.
  }
  else{
   ?><script>alert("The news item was not able to be created.  Please try again...");</script><?php
  }
 } 
 //$mysqli->close();  //Not needed
?>

Besides the notes I left, please remember the number one rule for debugging->Clean up the code.

You might also want to review the PHP Bible:

http://php.net/manual/en/index.php

Another useful link:

http://devzone.zend.com/6/php-101-php-for-the-absolute-beginner/

Good luck!

[tdrsam]

Thanks for the help and especially for taking your time to rewrite that code for me. I wish I could say that the problem is solved, but unfortunately not. I used your code and got the same result. I even changed the 'if success - redirect' to a var_dump and got the same result too. This one really has me stumped.

[computerfox]

Do me a favor, right before the insert, can you print out all the variables and put the results in this thread?

Also, now I really don't understand the hype, but the newer version of MySQL tends to be messy. Would it be possible to move back to the older version?

Also, please add this block after the if($succ):

else{
 print $mysqli->error();
}

Old MySQL:

<?php
 require('includes/DbCon.php');
 
 $target_dir = "http://bytes.com/images/photo/";
 $target_file = $target_dir . basename($_FILES["image"]["name"]);
 
 if(isset($_POST['submit'])){
  $headline=$_POST['headline'];
  $body=addslashes($_POST['body']);
  $image_path="default.png";
  if($_FILES['image']['name']!=""){
   if(move_uploaded_file($_FILES["image"]["tmp_name"], $target_file)){
    $image_path=$target_file;
   }
   else {
    ?><script>alert("There was an error uploading your image...");</script><?php
   }
  }
  $query="insert into news (headline,body,image) values ('$headline','$body','$image_path')";
  $succ=mysql_query($query);
  if($succ){
    print '<meta http-equiv="refresh" content="0; url=./admin/admin-news.php" />'; 
  }
  else{
   ?><script>alert("The news item was not able to be created.  Please try again...");</script><?php
  }
 } 
?>

The old version of MySQL assumes that the following connection is made in the connect file:

<?php 
 $user="";
 $pass="";
 $db_name="";
 mysql_connect("localhost",$user,$pass);
 mysql_select_db($db_name);
?>

[computerfox]

Wait a minute... I just noticed the target directory.....
Is that an example? That upload won't actually work. You need the internal path of the server, not the URL.

[tdrsam]

I didn't actually change the target directory at all. I checked if the imagers I'd uploaded had gone into the directory and they're there, so I left that line as is.

I printed all the variable and this is the result:

Test 3Test 1,2

I added the error message in the else statement but it printed the variables instead. I'm going to try the old MySQL version now.

[tdrsam]

Your not going to believe this;

I swapped to the old version of the MySQL statements and now the database interactions are processing. When I check what's in the database, everything I inserted just now are in there.

However, after I hit the submit button, I get an error saying that the statements are deprecated and that I have to use either mysqli or PDO instead.

Maybe I'll have to try writing it in PDO and see if that works.

[computerfox]

I believe it. Like I keep saying, the new version of MySQL is terrible. If the old version now works, I would keep it that way. Are the deprivation messages posting to the front end? If not, just leave it.

[tdrsam]

The problem now is that the error messages ARE posting to the front end, and they're cancelling the redirect. I wonder if there's a way to force the redirect? Maybe with a timer or something?

[Dormilich]

there’s a reason why the old mysql functions are deprecated. (implicit globals, no prepared statements, etc.)

personally I find PDO easier to handle than MySQLi and it’s definitely a huge improvement over the old mysql functions.

[tdrsam]

I just tried the PDO functions and they made a fair bit of sense and were pretty easy to write, but I don't think the server I'm using has it installed and it's probably not likely that I'll be able to get it installed.