PHP database SQL insert

Question

I am trying to write data form mine app to a external database. I just get no response form my PHP page. When I look at the variables that I send to the PHP page, they are received good and nothing goes wrong at that moment. But when I do an INSERT with SQL it goes wrong. (I think). When I go to mine PHPadmin page and I do next SQL command, it works:

INSERT INTO images (FBid,Datum,Lat,Longi,Image) 
VALUES ('1846465164',
'2016-08-25 14:14:15',10.5,5.69,'/9j/
 4AAQSkZJRgABAQAAAQABAAD/2wBDAAEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQE
 BAQEBQBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQH/2wBDAQEBAQEBAQEBAQEBAQEBAQEBAQEB')

So i have next database;

ID(PRIMARY KEY AUTOINCREMENT), 
FBid (varchar(255)), 
Datum (datetime), 
Lat (Double), 
Longi(Double), 
Image(Blob).

And this is my php page:

<?php



 if($_SERVER['REQUEST_METHOD']=='POST'){

   define('HOST','localhost');
 define('USER','XXXXXXXXX');
 define('PASS','XXXXXXXXX');
 define('DB','database2');

 $con = mysqli_connect(HOST,USER,PASS,DB) or die('Unable to Connect');

 $image = $_POST['image'];
 $FBid = $_POST['FBid'];
 $date = $_POST['Date'];
 $long = $_POST['long'];
 $lat = $_POST['lat'];

 $stmt = $con->prepare(
                "INSERT INTO images (FBid,Datum,Lat,Longi,Image) 
                 VALUES (:Fbid,:date,:lat,:long,:image)"); 
        $stmt->bindParam(":Fbid",$FBid);
        $stmt->bindParam(":date", $date);
        $stmt->bindParam(":lat", $lat);
        $stmt->bindParam(":long", $long);
        $stmt->bindParam(":image","s",$image);
        $stmt->execute();

$check = mysqli_stmt_affected_rows($stmt);

 if($check == 1){
 echo "Image Uploaded Successfully";
 }else{
 echo "Error Uploading Image";
 }
 mysqli_close($con);
 }else{
 echo "Error";
 }

Thank you guys!

Regards, Stijn

That looks like PDO code, not mysqli. Are you sure that's correct? Also there's no need to create intermediate variables like $FBid if they're only used once. Just put the $_POST data directly in the bindParam call. — tadman
– tadman, Commented Apr 14, 2016 at 17:18
@tadman I deleted the 'mysqli' but no difference. What is the problem with creating variabbles? — StijnB
– StijnB, Commented Apr 14, 2016 at 17:24
If you want to use named placeholders, which is a good idea, use PDO. mysqli doesn't support them. — tadman
– tadman, Commented Apr 14, 2016 at 18:25

Mawia HL · Accepted Answer · 2016-04-14 18:43:31Z

2

Looking at the database connection, you are using mysqli prepare wrongly. In the INSERT statement, it looks like a PDO version. If you want to use PDO version, have a look at this link. You can't mix PDO and mysqli. The procedural style for mysqli_prepare is like below:

$stmt = mysqli_prepare($con, "INSERT INTO images VALUES (?, ?, ?, ?, ?)");
if ( !$stmt ) {
   die('mysqli error: '.mysqli_error($con);
}
mysqli_stmt_bind_param($stmt, 'ssddb', $FBid,$date,$lat,$long,$image);
if ( !mysqli_stmt_execute($stmt)) {
   die( 'stmt error: '.mysqli_stmt_error($stmt) );
}

$check = mysqli_stmt_affected_rows($stmt);
if($check == 1){
 echo 'Image successfully uploaded';
}else{
 echo 'Error uploading image';
}
mysqli_stmt_close($stmt);

edited Apr 14, 2016 at 18:43

answered Apr 14, 2016 at 17:37

Mawia HL

3,6751 gold badge28 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

StijnB Over a year ago

Thank you for your answer, but it still don't work. I changed it to what you said. When i add an echo before the bind_param, is get the echo,. I place is behind the bind_param, I don't get the echo. I think something goes wrong there. I also did again an echo of al mine variables and they look like this: $FBid = 15325244 $date = 2015-08-25 14:14:14 $lat = 100.02 $long = 100.6 $image = /9j/...... So there is no problem I think.

Mawia HL Over a year ago

I updated my answer. Added mysqli_stmt_execute($stmt);

StijnB Over a year ago

No change. I placed the first if in comment, to check if there where any errors when I open the page in my browser. When I open it in my browser, i get this warning: Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given in /home/jail/home/sbosmans2/public_html/Android_app_server/upload.php on line 18

Mawia HL Over a year ago

Have you tried exactly with the updated version of my answer?

StijnB Over a year ago

I found the solution! Because in my table there is an auto-increment colum. I needed to add a 'null' value. $stmt = mysqli_prepare($con, "INSERT INTO images VALUES (null,?, ?, ?, ?, ?)"); Thank you! :)

|

Collectives™ on Stack Overflow

PHP database SQL insert

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related