error: function fetch_all()

1Tsme1941

Member
Hi, once more trying to get a reasonable answer to this coding issue.
I'm a noobie with questions. I want to access a database table "payfile" and, based on the sum of
some of these fields, insert records into a database table "payhist". I realize now that I was in error
by trying to update and must insert records, with conditions, to a running history file. A lot of
research has gone into this and I thank so much for your patience and help. I'm lost from line 15-20.

code:
<?php
echo "<center>";echo date('m/d/y');echo "<br />";
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "prerentdb";
// Create connection
$mysqli = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli->connect_error;
exit();
}

$amtpaid = filter_input(INPUT_GET, 'amtpaid'); // line 15
$sql = "SELECT * FROM payfile WHERE amtpaid=?"; // FROM payfile where amtpaid!=' '";

$stmt = $mysqli->prepare($sql);
// $stmt->bind_param($id);
$payfile = $stmt->get_result()->fetch_all(); // line 20
$due=0;
while($obj = fetch_object()){

$due = $obj->prevbal + $obj->latechg + $obj->secdep + $obj->damage + $obj->courtcost + $obj->NSF;
$amtdue = $obj->amtdue + due;
// if no payment or partial payment, add $10 to latechg field and amount not paid to prevbal field
$latechg = 0;
$prevbal = 0;
if ($obj->amtpaid < $obj->amtdue) {
$latechg += 10;
$prevbal = $obj->amtdue - $obj->amtpaid;
}
// if over-payment subtract over-payment
// from prevbal field
if ($amtpaid > $amtdue ) {
$amtdue = $amtpaid - $amtdue;
}
// $nsf doesn't get used or saved
$secdep = $damage = $courtcost = $nsf = 0;
// refresh every record - give every record the below values
$amtpaid = '0.00';
$hudpay = '0.00';
$datepaid = ' ';
$comment = ' ';
$paidsum = '0.00';

// prepare and bind
$stmt = $mysqli -> prepare("INSERT INTO payhist
(tenant, unit, amtpaid, amtdue, prevbal,latechg, secdep, damage, courtcost, nsf, paidsum, hudpay,
datepaid, comment, phone, cell)
VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$stmt -> bind_param("ssiiiiiiiiiissss",'$tenant','$unit','$amtpaid','$amtdue','$prevbal','$latechg',
'$secdep','$damage','$courtcost', '$nsf','$paidsum','$hudpay','$datepaid','$comment','$phone','$cell');

// set parameters and execute
$tenant='tenant';
$unit='unit';
$amtdue='amtdue'; // decimal field, not null
$amtpaid='amtpaid'; // decimal field, not null
$duedate='duedate';
$datepaid='datepaid';
$prevbal="prevbal"; // decimal field, not null
$latechg='latechg'; // decimal field, not null
$secdep='secdep'; // decimal field, not null
$damage='damage'; // decimal field, not null
$courtcost='courtcost'; // decimal field, not null
$nsf='nsf'; // decimal field, not null
$paidsum='paidsum'; // decimal field, not null
$hudpay='hudpay'; // decimal field, not null
$comment='comment';
$phone='phone';
$cell='cell';

$stmt -> execute();

echo "New records created successfully";
$stmt -> close();
}
?>

messages:
Fatal error: Uncaught Error: Call to a member function fetch_all() on bool on line 20
 

Cromewell

Administrator
Staff member
You are not executing your query, so there is no result to fetch.

To execute your query you would need to call
PHP:
$stmt->execute();
But before that will work, you will need to provide a value for the parameter in your query (the ?) either with
PHP:
$stmt->bind_param($some_variable_that_has_an_appropriate_value);
Or by passing it directly in the execute
PHP:
$stmt->execute([$some_variable_that_has_an_appropriate_value]);
 

1Tsme1941

Member
Hi, thanks for advising with this. With the new code below I get no errors so I think I can assume
tht the connection is fine, it's not kicking re. the numerics, my syntacs is fine. whew. Not inserting
but I've had the question since I changed to the prepared re. how to code the select where statement
indicated below. I only want to insert another record into the "payhist" file if the next "payfile"
record has something in "amtpaid". Down at line 15-16 I run into the Where ? as I need
where amtpaid!=' '";. How to get around?

<?php
echo "<center>";echo date('m/d/y');echo "<br />";
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "prerentdb";
// Create connection
$mysqli = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($mysqli->connect_errno)
{
echo "Failed to connect to MySQL: " . $mysqli->connect_error;
exit();
}

$unit = filter_input(INPUT_GET, 'unit'); // line 15
$sql = "SELECT tenant,unit,amtdue,amtpaid,duedate,datepaid,prevbal,latechg,secdep,damage,courtcost,nsf,
paidsum,hudpay,comment,phone,cell FROM payfile WHERE unit=?"; // FROM payfile where amtpaid!=' '";

// proceed only if a query is executed
if($result = $mysqli->query($sql)){
$stmt = $mysqli->prepare($sql);
$stmt->get_result()->fetch_all();
while($obj = fetch_object()) {
print_r($row);
}

$due=0;
$due = $obj->prevbal + $obj->latechg + $obj->secdep + $obj->damage + $obj->courtcost + $obj->NSF;
$amtdue = $obj->amtdue + due;
// if no payment or partial payment, add $10 to latechg field and amount not paid to prevbal field
$latechg = 0;
$prevbal = 0;
if ($obj->amtpaid < $obj->amtdue)
{
$latechg += 10;
$prevbal = $obj->amtdue - $obj->amtpaid;
}
// if over-payment subtract over-payment
// from prevbal field
if ($amtpaid > $amtdue )
{
$amtdue = $amtpaid - $amtdue;
}
// $nsf doesn't get used or saved
$secdep = $damage = $courtcost = $nsf = 0;
// refresh every record - give every record the below values
$amtpaid = '0.00';
$hudpay = '0.00';
$datepaid = ' ';
$comment = ' ';
$paidsum = '0.00';

// prepare and bind
$stmt = $mysqli -> prepare("INSERT INTO payhist
(tenant, unit, amtpaid, amtdue, prevbal,latechg, secdep, damage, courtcost, nsf, paidsum, hudpay,
datepaid, comment, phone, cell)
VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$stmt -> bind_param("ssiiiiiiiiiissss",'$tenant','$unit','$amtpaid','$amtdue','$prevbal','$latechg',
'$secdep','$damage','$courtcost', '$nsf','$paidsum','$hudpay','$datepaid','$comment','$phone','$cell');

// set parameters and execute
$tenant='tenant';
$unit='unit';
$amtdue='amtdue'; // decimal field, not null
$amtpaid='amtpaid'; // decimal field, not null
$duedate='duedate';
$datepaid='datepaid';
$prevbal="prevbal"; // decimal field, not null
$latechg='latechg'; // decimal field, not null
$secdep='secdep'; // decimal field, not null
$damage='damage'; // decimal field, not null
$courtcost='courtcost'; // decimal field, not null
$nsf='nsf'; // decimal field, not null
$paidsum='paidsum'; // decimal field, not null
$hudpay='hudpay'; // decimal field, not null
$comment='comment';
$phone='phone';
$cell='cell';

if ($stmt->execute())
{ echo "Item created successfully"; }
else { echo "Failed to insert Item"; }

//var_dump($stmt);

$stmt->close();
$mysqli->close();
}
?>
 

Cromewell

Administrator
Staff member
I only want to insert another record into the "payhist" file if the next "payfile"
record has something in "amtpaid". Down at line 15-16 I run into the Where ? as I need
where amtpaid!=' '";. How to get around?
To include other conditions in a where clause use AND or OR to chain them. Keep in mind for something like WHERE a = 1 and b = 2 or c =3 you should add parentheses to make it clear which conditions should be grouped otherwise you will get unexpected results. WHERE (a = 1 and b = 2) or c =3

Just include "WHERE <whatever other clauses> AND amtpaid != '' ". It does not make much sense parameterizing the value if you are always going to check for it being blank. I would probably also suggest inserting a null value, rather than empty string and checking for not null instead.

SELECT tenant,unit,amtdue,amtpaid,duedate,datepaid,prevbal,latechg,secdep,damage,courtcost,nsf,
paidsum,hudpay,comment,phone,cell FROM payfile WHERE unit=? AND amtpaid is not null

However, from a logical flow point of view, you are probably better off reworking things so that you simply insert a row whenever a payment is received.
 
Top