MySqli insert not working

  • (2 Pages)
  • +
  • 1
  • 2

20 Replies - 763 Views - Last Post: 22 September 2013 - 10:55 AM Rate Topic: -----

#1 danbywinby  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 53
  • Joined: 06-January 13

MySqli insert not working

Posted 10 September 2013 - 05:41 AM

This is my first time using mysqli usually i use mysql and i have no problems with that but ive been told i need to start using mysqli so i decided to give it a shot. And ive ran into a few problems with it lol.

My code is below:

//SQL TO INSERT DRIVERS LOG DETAILS INTO DATABASE

	$db = new mysqli('localhost','Dan','TrucksYay','DansDB');

	if ($db->connect_errno) {
    die('Connect Error: ' . $db->connect_errno);
	}

	$stmt = $db->prepare("insert into jwtdriverslogs
		(RowID,DriverID,LogID,DriverNumber,Division,Truck,Cargo,DepotLeave,CityLeave,
		CountryLeave,DayStart,TimeStart,MileageStart,DepotArrive,CityArrive,
		CountryArrive,DayEnd,TimeEnd,MileageEnd,FuelAmount,OnTimeOrLate)
	 	VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

	if($stmt === FALSE) {
    	die('prepare() failed: ' . htmlspecialchars($db->error));
    }

    if($stmt->prepare() === FALSE) {
    	die('prepare() failed: ' . htmlspecialchars($stmt->error));
	}

    $MileageTotal=$MileageEnd-$MileageStart;

    $Var1 = NULL;

    //var_dump($MileageTotal);

	$stmt->bind_param("iiiissssssssisssssiis",
		$Var1,
		$userID,
		$DriverNumber,
	// 	COALESCE((Select MAX(LogID) from jwtdriverslogs tab2 where 
	// tab2.DriverID = '$userID'),0)+1,
		$DriverNumber,
		$Division,
		$TruckDriving,
		$Cargo,
		$DepotLeaving,
		$CityLeaving,
		$CountryStart,
		$DayStart,
		$TimeStart,
		$MileageStart,
		$DepotArriving,
		$CityArriving,
		$CountryEnd,
		$DayEnd,
		$TimeEnd,
		$MileageEnd,
		$FuelAmount,
		$OnTimeOrLate);

	if($stmt === FALSE) {
    	die('bind_param() failed: ' . htmlspecialchars($db->error));
    }

    if($stmt->bind_param() === FALSE) {
    	die('bind_param()() failed: ' . htmlspecialchars($stmt->error));
	}

	$stmt->execute();

	if($stmt === FALSE) {
    	die('execute() failed: ' . htmlspecialchars($db->error));
    }

    if($stmt->execute() === FALSE) {
    	die('execute() failed: ' . htmlspecialchars($stmt->error));
	}

	$stmt->close();


And the only errors i am getting are in the php error log and the errors are:

[08-Sep-2013 21:00:51 Europe/Minsk] PHP Warning: mysqli_stmt::prepare() expects exactly 1 parameter, 0 given in C:\inetpub\wwwroot\hosting\Dan\JWT\drivers-log-send.php on line 371
[08-Sep-2013 21:00:51 Europe/Minsk] PHP Warning: Wrong parameter count for mysqli_stmt::bind_param() in C:\inetpub\wwwroot\hosting\Dan\JWT\drivers-log-send.php on line 410

Is This A Good Question/Topic? 0
  • +

Replies To: MySqli insert not working

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3480
  • View blog
  • Posts: 10,028
  • Joined: 08-June 10

Re: MySqli insert not working

Posted 10 September 2013 - 05:55 AM

error #1, prepare() requires you to pass in an SQL query. and I have no idea, what that test is supposed to do, the failure test you have already made.

error #2, same as above, you test the return value of the call of interest, not the return value of a new call afterwards. you need to capture the return value of the bind_param() call, though.

line #68, same as error #2, only this is a valid call and you should get a corresponding insertion.

summary: drop the tests on lines #19, #58, #68 completely. they are plain wrong there (esp since you have already done the tests).

This post has been edited by Dormilich: 10 September 2013 - 05:58 AM

Was This Post Helpful? 2
  • +
  • -

#3 danbywinby  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 53
  • Joined: 06-January 13

Re: MySqli insert not working

Posted 10 September 2013 - 01:21 PM

Thank you for that it is now working however it is adding two rows each time instead of just one and the code i try to use to make the Log ID isn't working.

Here is my full code atm:

//SQL TO INSERT DRIVERS LOG DETAILS INTO DATABASE

	$db = new mysqli('localhost','Dan','TrucksYay','DansDB');

	if ($db->connect_errno) {
    die('Connect Error: ' . $db->connect_errno);
	}

	$stmt = $db->prepare("insert into jwtdriverslogs
		(RowID,DriverID,LogID,DriverNumber,Division,Truck,Cargo,DepotLeave,CityLeave,
		CountryLeave,DayStart,TimeStart,MileageStart,DepotArrive,CityArrive,
		CountryArrive,DayEnd,TimeEnd,MileageEnd,FuelAmount,OnTimeOrLate)
	 	VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

	if($stmt === FALSE) {
    	die('prepare() failed: ' . htmlspecialchars($db->error));
    }

    $MileageTotal=$MileageEnd-$MileageStart;

    $Var1 = NULL;

    //var_dump($MileageTotal);

	$stmt->bind_param("iiiissssssssisssssiis",
		$Var1,
		$userID,
		$DriverNumber,
		//COALESCE((Select MAX(LogID) from jwtdriverslogs tab3 where tab3.DriverID = '$userID'),0)+1,
		$DriverNumber,
		$Division,
		$TruckDriving,
		$Cargo,
		$DepotLeaving,
		$CityLeaving,
		$CountryStart,
		$DayStart,
		$TimeStart,
		$MileageStart,
		$DepotArriving,
		$CityArriving,
		$CountryEnd,
		$DayEnd,
		$TimeEnd,
		$MileageEnd,
		$FuelAmount,
		$OnTimeOrLate);

	if($stmt === FALSE) {
    	die('bind_param() failed: ' . htmlspecialchars($db->error));
    }

	$stmt->execute();

	if($stmt === FALSE) {
    	die('execute() failed: ' . htmlspecialchars($db->error));
    }

	$stmt->close();


And this is an exact piece of code that isnt working:

COALESCE((Select MAX(LogID) from jwtdriverslogs tab3 where tab3.DriverID = '$userID'),0)+1,


Which i dont understand because it worked fine before when i wasnt using mysqli but this is the error i get with it:

[10-Sep-2013 23:06:13 Europe/Minsk] PHP Parse error: syntax error, unexpected 'MAX' (T_STRING) in C:\inetpub\wwwroot\hosting\Dan\JWT\drivers-log-send.php on line 384
Was This Post Helpful? 0
  • +
  • -

#4 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3480
  • View blog
  • Posts: 10,028
  • Joined: 08-June 10

Re: MySqli insert not working

Posted 10 September 2013 - 01:29 PM

donít you have commented that line out? the PHP parser shouldnít even see that.

anyways, delete it if itís not used.
Was This Post Helpful? 0
  • +
  • -

#5 danbywinby  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 53
  • Joined: 06-January 13

Re: MySqli insert not working

Posted 11 September 2013 - 12:15 AM

Yeah i have commented it out because it is not working and so i have replaced it by adding in the DriverNumber twice but i want that piece of code to work so i can add in the LogID but atm its not working even when i uncomment it.

This post has been edited by Dormilich: 11 September 2013 - 12:57 AM

Was This Post Helpful? 0
  • +
  • -

#6 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3480
  • View blog
  • Posts: 10,028
  • Joined: 08-June 10

Re: MySqli insert not working

Posted 11 September 2013 - 01:00 AM

if you want that piece of code to work, you have to put it in the query. all data passed in via bind_param() are treated as data and not as executable SQL code (I mean, thatís the concept of Prepared Statements, fighting SQL Injection (and that is what you tried) by separating SQL and data).
Was This Post Helpful? 0
  • +
  • -

#7 danbywinby  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 53
  • Joined: 06-January 13

Re: MySqli insert not working

Posted 21 September 2013 - 10:54 AM

Hi

Below is my code:

//SQL TO INSERT DRIVERS LOG DETAILS INTO DATABASE

$db = new mysqli('localhost','Dan','TrucksYay','DansDB');

if ($db->connect_errno) {
die('Connect Error: ' . $db->connect_errno);
}

$stmt = $db->prepare("insert into jwtdriverslogs
	(RowID,DriverID,LogID,DriverNumber,Division,Truck,Cargo,DepotLeave,CityLeave,CountryLeave,DayStart,TimeStart,MileageStart,DepotArrive,CityArrive,CountryArrive,DayEnd,TimeEnd,MileageEnd,FuelAmount,OnTimeOrLate,TotalMileage)
	VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

if($stmt === FALSE) {
	die('prepare() failed: ' . htmlspecialchars($db->error));
}

$MileageTotal=$MileageEnd-$MileageStart;

$Var1 = NULL;

//$LogNumber = COALESCE((Select MAX(LogID) from jwtdriverslogs where DriverID = '$userID'),0)+1;

//var_dump($MileageTotal);

var_dump($DriverNumber);

$stmt->bind_param("iiisssssssssisssssiisi",
	$Var1,
	$userID,
	$DriverNumber,
	$DriverNumber,
	$Division,
	$TruckDriving,
	$Cargo,
	$DepotLeaving,
	$CityLeaving,
	$CountryStart,
	$DayStart,
	$TimeStart,
	$MileageStart,
	$DepotArriving,
	$CityArriving,
	$CountryEnd,
	$DayEnd,
	$TimeEnd,
	$MileageEnd,
	$FuelAmount,
	$OnTimeOrLate,
	$MileageTotal);

if($stmt === FALSE) {
	die('bind_param() failed: ' . htmlspecialchars($db->error));
}

$stmt->execute();

if($stmt === FALSE) {
	die('execute() failed: ' . htmlspecialchars($db->error));
}

$stmt->close();


But the exact line i am having problems with is:

$LogNumber = COALESCE((Select MAX(LogID) from jwtdriverslogs where DriverID = '$userID'),0)+1;


It used to work fine before i started using mysqli but now it isn't working and i don't know why.

The error i am getting is:

[21-Sep-2013 16:26:00 Europe/Minsk] PHP Parse error: syntax error, unexpected 'MAX' (T_STRING) in C:\inetpub\wwwroot\hosting\Dan\JWT\drivers-log-send.php on line 372
Was This Post Helpful? 0
  • +
  • -

#8 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3480
  • View blog
  • Posts: 10,028
  • Joined: 08-June 10

Re: MySqli insert not working

Posted 21 September 2013 - 11:00 AM

string must be either wrapped in quotes (single or double) or one of the HEREDOC/NEWDOC styles.
Was This Post Helpful? 0
  • +
  • -

#9 danbywinby  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 53
  • Joined: 06-January 13

Re: MySqli insert not working

Posted 21 September 2013 - 02:14 PM

Tried changing it to:

$LogNumber = COALESCE((Select MAX('LogID') from jwtdriverslogs where DriverID = '$userID'),0)+1;


Still not work.

And same error
Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3190
  • View blog
  • Posts: 10,677
  • Joined: 12-December 12

Re: MySqli insert not working

Posted 21 September 2013 - 02:24 PM

COALESCE is a mysql function, not a PHP function. You are essentially assigning a string-value to $logNumber, so it should be quoted, in full:

$LogNumber = "COALESCE((Select MAX(LogID) from jwtdriverslogs where DriverID = '$userID'),0)+1";

This post has been edited by andrewsw: 21 September 2013 - 02:27 PM

Was This Post Helpful? 0
  • +
  • -

#11 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3480
  • View blog
  • Posts: 10,028
  • Joined: 08-June 10

Re: MySqli insert not working

Posted 21 September 2013 - 03:26 PM

additionally, be aware that SQL code does not work when passed via Prepared Statement (though I remember telling you that before).
Was This Post Helpful? 0
  • +
  • -

#12 danbywinby  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 53
  • Joined: 06-January 13

Re: MySqli insert not working

Posted 22 September 2013 - 08:54 AM

View Postandrewsw, on 21 September 2013 - 02:24 PM, said:

COALESCE is a mysql function, not a PHP function. You are essentially assigning a string-value to $logNumber, so it should be quoted, in full:

$LogNumber = "COALESCE((Select MAX(LogID) from jwtdriverslogs where DriverID = '$userID'),0)+1";


I changed it to that but now it doesn't actually execute the code it just sees it as text for example i vardump $LogNumber and get this:

string(74) "COALESCE((Select MAX(LogID) from jwtdriverslogs where DriverID = '1'),0)+1"

Which is not what i want
Was This Post Helpful? 0
  • +
  • -

#13 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3710
  • View blog
  • Posts: 5,958
  • Joined: 08-June 10

Re: MySqli insert not working

Posted 22 September 2013 - 09:02 AM

I've merged your new thread with your old one. Don't post your question multiple times in different threads!

As for the question: this is a SQL statement. Either execute it first and put the resulting value into the INSERT, or find a way to include it in the actual INSERT statement. As Dormilich said already, passing SQL commands as parameters like that doesn't work; MySQL will see it as a value, not a command.
Was This Post Helpful? 0
  • +
  • -

#14 danbywinby  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 53
  • Joined: 06-January 13

Re: MySqli insert not working

Posted 22 September 2013 - 09:07 AM

Sorry about that to me they seemed to be different but obviously they weren't.

But i tried to include it in the insert before and it didn't work because i was told that you can't put code in insert statements.

This post has been edited by andrewsw: 22 September 2013 - 09:14 AM
Reason for edit:: Removed previous quote

Was This Post Helpful? 0
  • +
  • -

#15 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3190
  • View blog
  • Posts: 10,677
  • Joined: 12-December 12

Re: MySqli insert not working

Posted 22 September 2013 - 09:21 AM

You can use expressions in INSERT statements. However, I doubt that you can read a MAX value from the same table while inserting. (Someone may correct me on this as I've never tried it: SET field = (SELECT COALESCE(..))" ?)

I think you'll need to execute the (SELECT) COALESCE statement first to obtain the MAX+1 value, then include this value as a parameter for your INSERT statement.

This post has been edited by andrewsw: 22 September 2013 - 09:25 AM

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2