PHP INSERT INTO not working, no errors

[sec12tst8r84]

Hi all, I'm trying to insert a row into a table via PHP. I have read multiple threads where other people have a similar issue but I can't seem to find the solution to mine. Hoping a fresh look will help.

A few things to note:

• A multiquery with an INSERT INTO in the same table works.

• This query exists in the middle of the code (not at the beginning like the multiquery). When I move the query to the beginning, still does not work.

• I have tried date formats, number formats, with and without single quotes.

• Inserting into a different table kind of works (the row gets inserted, category but the date and amount are not.

• The code works in myPHPAdmin

• I am using MAMP on a Mac

• There are no PHP errors or Apache errors.

• The MySQL errors are as follows:

2017-12-02 10:57:50 700000b4f000 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2017-12-02 10:57:50 700000b4f000 InnoDB: Error: Fetch of persistent statistics requested for table "ginkgo"."exptrans" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2017-12-02 10:57:50 700000b4f000 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2017-12-02 10:57:51 700000b4f000 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2017-12-02 10:57:51 700000b4f000 InnoDB: Error: Fetch of persistent statistics requested for table "ginkgo"."fintrans" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2017-12-02 10:57:51 700000b4f000 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2017-12-02 10:58:01 700000934000 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2017-12-02 10:58:01 700000934000 InnoDB: Recalculation of persistent statistics requested for table "ginkgo"."fintrans" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
2017-12-02 10:58:11 700000934000 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2017-12-02 10:58:11 700000934000 InnoDB: Recalculation of persistent statistics requested for table "ginkgo"."exptrans" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.

Here is my code:

$mysqli = new mysqli('localhost:8889','root','root','Ginkgo');
if($mysqli->connect_errno){
   echo ('Error connecting to MySQL server. '.$mysqli->connect_errno .' '.$mysqli->connect_error);
}

$sql = "INSERT INTO `FinTrans` (`Category`,`Location`,`FAmt`,`FDT`) values ('Tenant','514 Creekview',".$gross_rent[0].",'2000-01-01');";
echo $sql;

if( mysqli_query($mysqli,$sql))
{
    echo 'success';
}else{
    echo 'failed';
}

This is what is printed:

INSERT INTO FinTrans (Category,Location,FAmt,FDT) values ('Tenant','514 Creekview',3200,'2000-01-01');success

This is the structure of the table:

CREATE TABLE `FinTrans` (
        `Acct` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
        `Clients` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
        `Category` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
        `Counterparty` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
        `RentDate` date DEFAULT NULL,
        `Description` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
        `Location` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
        `Date Spent by Employee` date DEFAULT NULL,
        `Date Reimbursed by Client` date DEFAULT NULL,
        `ID` int(10) unsigned DEFAULT NULL,
        `FDT` date DEFAULT NULL,
        `FAmt` decimal(18,2) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Thank you much for your help.

Sharlene

[sec12tst8r84]

What I meant was, the row is not inserted into the table even though the code reports success.

[pbismad]

Is the database connection code you are showing, the connection code from the top of your script and you have just posted it here to show us what the connection code looks like, or is the database connection code you are showing here a second connection that's just for this query?

The reason I ask, is because the operating system you are using is case-sensitive, but the logged error messages contain all lower-case database and table names, which I don't think the database server will go to the trouble of converting to lower-case when logging them, and I suspect you may have two different databases and are selecting a different one for this query than what you have selected for the working query near the top of your code.

Could you post the connection code and working query you have at the top of your code, so that we can see if there is any thing different between them and this non-working code, particularly in the letter-case of the database and tables.

Do the errors you are posting only occur for this query or do you get errors like this for the working query you have near the top of your code?

You apparently are running query(ies) for a exptrans table as part of this code. Is there any chance the code you have posted has been modified to remove what you think is non-relevant code, but actually could be and is resulting in a different displayed result from what the actual code is producing? Is there any html markup as part of this code, that could be hiding output inside of html tags? To check this, does the 'view source' of the output in your browser show this same output you are posting here?

Where is the $gross_rent[0] value coming from and could it contain non-printing characters as part of its value? I'm thinking that the database server 'mode' could be set to a value that might not return an error for invalid data, resulting in the success message for a query that doesn't insert data. You could also be seeing this as the result of a bug somewhere in the database or php. What does adding var_dump($gross_rent[0]);, immediately before the echo $sql; statement show?

Are there any triggers set up in the database that could be affecting the apparent result of no data being inserted? If so, perhaps a later query is deleting the row that is being inserted? Likewise, are you using transactions in your code and the apparent result of no data being inserted is do to a transaction that is not being committed?

Lastly, the errors you are getting are due to a missing statistics table, likely the result of a software update where the code to update the table structures wasn't executed, and are not necessarily anything to do with your current problem. You can search on the web to find out how to fix the problem that is causing these errors.

[pbismad]

After writing all of that ^, I think the most likely cause is a transaction that isn't being committed.

However, based on the two table names, "exptrans" and "fintrans", do you have some code after the INSERT query that's deleting row(s), but is deleting them from the "fintrans" table, when it should be deleting from the "exptrans" table?

[sec12tst8r84]

Hi pbismad, Thank you for responding to my post. Below are my responses to your questions:

The database connection code is just for this query. I opened up the same connection for the first query but closed it when I was done with the first query. I've also tried leaving it open. In neither case did the row get inserted.

These are the errors that occur for the working query at the top of the code:

2017-12-02 19:31:02 700000934000 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2017-12-02 19:31:02 700000934000 InnoDB: Recalculation of persistent statistics requested for table "ginkgo"."fintrans" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
2017-12-02 19:31:12 700000934000 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.

The HTML source shows the following:

<html><head></head><body>INSERT INTO `FinTrans` (`Category`,`Location`,`FAmt`,`FDT`) values ('Tenant','514 Creekview',3200,'2000-01-01');success</body></html>

$gross_rent[0] comes from PHP calculations but for testing purposes, I deleted all of that and just created an array with one value at $gross_rent[0]. A vardump shows the value that I manually set it to.

There are no triggers set up in the database. I am unsure what you mean by transactions in my code. As of now, there is nothing after the second query.

I'm not actually sure how to set a secondary table to update automatically if I change the primary table. FinTrans (Final transaction table) is a select from the ExpTrans table. I haven't deleted any rows I don't think.

I've attached my full code including the working portion below:

<?php
       error_reporting(E_ALL);
       ini_set('display_errors', '1');

$mysqli = new mysqli('localhost:8889','root','root','Ginkgo');
if($mysqli->connect_errno){
	echo ('Error connecting to MySQL server. '.$mysqli->connect_errno .' '.$mysqli->connect_error);
}

//drop exptrans
$sql = "TRUNCATE ExpTrans; ";
//expand start and end dates
$sql .= "INSERT INTO ExpTrans (`Date`,`Acct`,`Clients`,`Category`,`Counterparty`,`RentDate`,`Description`,`Location`,`Amount`,`ST`,`EN`,`Date Spent by Employee`,`Date Reimbursed by Client`,`ID`,`caldate`) (SELECT `Date`,`Acct`,`Clients`,`Category`,`Counterparty`,`RentDate`,`Description`,`Location`,`Amount`,`ST`,`EN`,`Date Spent by Employee`,`Date Reimbursed by Client`,`ID`,`caldate` FROM trans t1 left JOIN cal On cal.caldate >= date_add(t1.ST, interval -1 MONTH) and cal.caldate <= t1.EN); ";
//add final date and if caldate is empty then use original date, otherwise, use caldate
//$sql .= "ALTER TABLE ExpTrans ADD FDT date; ";
$sql .= "UPDATE ExpTrans SET FDT = case when caldate is NULL then Date when caldate <= ST then ST else caldate end; ";
//add final amount and calculate out the monthly amount
//$sql .= "ALTER TABLE ExpTrans ADD FAmt decimal (18,2); ";
$sql .= "UPDATE ExpTrans set FAmt = case when ST is NULL then Amount else (Amount/(datediff(EN,ST)+1))*(day(last_day(FDT))-day(FDT)+1) end; ";
//drop fintrans
$sql .= "TRUNCATE FinTrans; ";
//only copy over everything that's not a transfer
$sql .= "insert into FinTrans (`Acct`,`Clients`,`Category`,`Counterparty`,`RentDate`,`Description`,`Location`,`Date Spent by Employee`,`Date Reimbursed by Client`,`ID`,`FDT`,`FAmt`) SELECT `Acct`,`Clients`,`Category`,`Counterparty`,`RentDate`,`Description`,`Location`,`Date Spent by Employee`,`Date Reimbursed by Client`,`ID`,`FDT`,`FAmt` FROM ExpTrans where `Category` <> 'Transfer'; ";
//null out anything that's blank
$sql .= "UPDATE FinTrans SET `Acct` = NULLIF(`Acct`, ''),`Clients` = NULLIF(`Clients`, ''),`Category` = NULLIF(`Category`, ''),`Description` = NULLIF(`Description`, ''),`Location` = NULLIF(`Location`, ''),`ID` = NULLIF(`ID`, ''); ";
//add a primary key
//$sql .= "ALTER TABLE FinTrans add column `idkey` int not null auto_increment first, add primary key (`idkey`); ";
//insert market value from propvalue
$sql .= "insert into FinTrans (`Category`,`FDT`,`FAmt`,`Location`) select 'Value',caldate,Price,'514 Creekview' FROM (SELECT * FROM propvalue t1 CROSS JOIN (SELECT caldate FROM cal) t2 WHERE t2.caldate >= t1.ST and t2.caldate <= t1.EN) t3; ";
//insert market rent from propvalue
$sql .= "insert into FinTrans (`Category`,`FDT`,`FAmt`,`Location`) select 'MktRent',caldate,Rent,'514 Creekview' FROM (SELECT * FROM propvalue t1 CROSS JOIN (SELECT caldate FROM cal) t2 WHERE t2.caldate >= t1.ST and t2.caldate <= t1.EN) t3; ";
//insert rent roll
$sql .= "insert into FinTrans (`Category`,`Counterparty`,`FDT`,`FAmt`,`Location`) select 'Tenant',Tenant,caldate,Rent,'514 Creekview' FROM (SELECT * FROM rentroll t1 CROSS JOIN (SELECT caldate FROM cal) t2 WHERE t2.caldate >= t1.startdate and t2.caldate <= t1.enddate) t3; ";

if (!$mysqli->multi_query($sql)) {
	echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

mysqli_close($mysqli);


    //this section is the one that's not working, where the row is not inserting into the FinTrans table.
$mysqli = new mysqli('localhost:8889','root','root','Ginkgo');
if($mysqli->connect_errno){
	echo ('Error connecting to MySQL server. '.$mysqli->connect_errno .' '.$mysqli->connect_error);
}

//gross rent sum
$gross_rent = array();
$gross_rent[0] = 3200;
$sql = "INSERT INTO `FinTrans` (`Category`,`Location`,`FAmt`,`FDT`) values ('Tenant','514 Creekview',".$gross_rent[0].",'2000-01-01');";
    echo $sql;

if( mysqli_query($mysqli,"INSERT INTO `FinTrans` (`Category`,`Description`,`Location`) values ('Tenant3','test','514 Creekview')"))
{
	echo 'success';
}else{
	echo 'failed';
}

var_dump($gross_rent[0]);

$mysqli->close();
?>

[pbismad]

It looks like the code should work or give the 'failed' message if it doesn't.

How are you determining that the last insert query isn't inserting data? The method you are using to look at the data may be where the problem lies.

As to what you are doing, I didn't wade through all the queries/data, but in general, having a 'secondary' table that holds derived data indicates a bad design. You should just query for any derived values when needed so that you won't have synchronization problems between the multiple tables.

[sec12tst8r84]

I check with Sequel Pro as well as within PhpMyAdmin.

That's a good idea. I'll fix the tables and queries once I figure out what's going on with this INSERT debacle.

[Weedpacket]

Having a missing system table (mysql.innodb_table_stats) seems odd. I doubt that it is directly any sort of cause (warning: I don't know that much about MySQL) but it can't be helping, and it might be missing for the same reason that your statement is failing.

The mention of transactions refers to http://www.php.net/manual/en/mysqli.quickstart.transactions.php and https://dev.mysql.com/doc/refman/5.7/en/commit.html

Incidentally, you can put the calculations you have in your UPDATE queries into the SELECT you're using to populate the corresponding INSERT; you don't have to wait to do it in a separate step:

TRUNCATE ExpTrans;
INSERT INTO ExpTrans
	(
		`Date`,
		Acct,
		Clients,
		Category,
		Counterparty,
		RentDate,
		Description,
		Location,
		Amount,
		ST,
		EN,
		'Date Spent by Employee',
		'Date Reimbursed by Client',
		ID,
		caldate,
		FDT,
		FAmt)
	SELECT
		`Date`,
		Acct,
		Clients,
		Category,
		Counterparty,
		RentDate,
		Description,
		Location,
		Amount,
		ST,
		EN,
		'Date Spent by Employee',
		'Date Reimbursed by Client',
		ID,
		caldate,
		CASE WHEN caldate is NULL then `Date` WHEN caldate <= ST THEN ST else caldate end,
		CASE ST IS NULL THEN Amount ELSE (Amount/(datediff(EN,ST)+1))*(day(last_day(FDT))-day(FDT)+1) end
	 FROM trans t1 left JOIN cal On cal.caldate >= date_add(t1.ST, interval -1 MONTH) and cal.caldate <= t1.EN);



TRUNCATE FinTrans;
INSERT INTO FinTrans(
		Acct,
		Clients,
		Category,
		Counterparty,
		RentDate,
		Description,
		Location,
		`Date Spent by Employee`,
		`Date Reimbursed by Client`,
		ID,
		FDT,
		FAmt)
	SELECT
		NULLIF(Acct, ''),
		NULLIF(Clients, ''),
		NULLIF(Category, ''),
		Counterparty,
		RentDate,
		NULLIF(Description, ''),
		NULLIF(Location, ''),
		`Date Spent by Employee`,
		`Date Reimbursed by Client`,
		NULLIF(ID, ''),
		FDT,
		FAmt FROM ExpTrans where Category <> 'Transfer';

And if ExpTrans isn't used anywhere else it is of course unnecessary to have it at all.

[pbismad]

It looks like the code should work or give the 'failed' message if it doesn't.

I'm going to add a qualification to this statement - provided that in the last posted code, the query you are actually executing "INSERT INTO FinTrans (Category,Description,Location) values ('Tenant3','test','514 Creekview')", which is different from the query you are echoing , is the data that you were looking for in the database table and didn't find it.

Did you add/change the query in the last posted code as a later test, or is this the code you have been executing for a while, but have been looking for the data in the echo'ed query and not finding that data?

[sec12tst8r84]

I commented out everything from the first query except the first sequel command of the first query. The INSERT worked. However, if I run more than one query in the first call, the INSERT does not work. Any ideas?

[sec12tst8r84]

pbismad;11064949 wrote:

I'm going to add a qualification to this statement - provided that in the last posted code, the query you are actually executing "INSERT INTO FinTrans (Category,Description,Location) values ('Tenant3','test','514 Creekview')", which is different from the query you are echoing , is the data that you were looking for in the database table and didn't find it.

Did you add/change the query in the last posted code as a later test, or is this the code you have been executing for a while, but have been looking for the data in the echo'ed query and not finding that data?

I was testing different things and so I was trying out different ways to run the same sequel statement. I've changed it to mysqli_query($mysqli,$sql) and it didn't make a difference.

[Weedpacket]

 mysqli_close($mysqli);

    //this section is the one that's not working, where the row is not inserting into the FinTrans table.
$mysqli = new mysqli('localhost:8889','root','root','Ginkgo');
if($mysqli->connect_errno){
    echo ('Error connecting to MySQL server. '.$mysqli->connect_errno .' '.$mysqli->connect_error);
} 

It's a little odd that you're disconnecting and then immediately reconnecting.

But I kind of wonder if there is something about using a multi query here; you check whether the first statement (

TRUNCATE ExpTrans

) succeeded or not, but you don't check any further (from the [man]mysqli.multi-query[/man] page: "Returns FALSE if the first statement failed. To retrieve subsequent errors from other statements you have to call mysqli_next_result() first.").

[sec12tst8r84]

@

You may be right. I think it's due to the multi query. If I comment all the other lines of the first query out except for

TRUNCATE ExpTrans;

then TRUNCATE and INSERT both work. I'm slowly cleaning up my code as I learn more about how best to do things. I have removed the disconnect and reconnect that's in the middle.

[sec12tst8r84]

Thank you very much everyone. I couldn't figure out what was wrong with the multi-query so I just split each line out as separate queries. Everything now works. 🙂