Query was empty error

catapult

I have written a script to either insert or update a record depending on a parameter passed to it. The insert works fine but I have a problem with the update. I'm using ADODB and fetch the record as a template and then use the "GetUpdateSQL()" function to update the record. Here is the script...........

<?php
require_once("adodb/adodb.inc.php");
require_once("dbconnect.inc");

//create array containing posted data
$data=array();
$data['CustFirstname']=$POST['CustFirstname'];
$data['CustSurname']=$POST['CustSurname'];
$data['CustAdd1']=$POST['CustAdd1'];
$data['CustAdd2']=$POST['CustAdd2'];
$data['CustTown']=$POST['CustTown'];
$data['CustCounty']=$POST['CustCounty'];
$data['CustPostcode']=$POST['CustPostcode'];
$data['CustDaytime']=$POST['CustDaytime'];
$data['CustEvening']=$POST['CustEvening'];
$data['CustMobile']=$POST['CustMobile'];
$data['CustEmail']=$POST['CustEmail'];
$data['CustHearAbout']=$POST['CustHearAbout'];

//make db connection
$db = db_connect();
$db->debug=1;

//editing a record?
$CustID=$_POST['CustID'];
if($CustID<=0)$CustID=-1;

//select a record as template
$query="SELECT * FROM customer WHERE CustID=".$CustID;
$db->SetFetchMode(ADODB_FETCH_ASSOC);
$result = $db->Execute($query) or die("Error in query: $updateSQL. " . $db->ErrorMsg());

echo $result; THIS IS WHERE I HAVE A PROBLEM

//are we updating or inserting
if($CustID>=1)
{
//update data from array using the template
$updateSQL = $db->GetUpdateSQL($result,$data);
$db->Execute($updateSQL) or die("Error in query: $updateSQL. " . $db->ErrorMsg());
$db->Close();
}
else
{
//insert data from array using the template
$insertSQL = $db->GetInsertSQL($result,$data);
$insertresult = $db->Execute($insertSQL) or die("Error in query: $insertSQL. " . $db->ErrorMsg());
if($insertresult)$CustID=$db->Insert_ID();
$db->Close();
}

echo "<a href=\"index.php?CustID=".$CustID."\"><< Back</a>";
?>

After selecting a record as the template if I don't echo the "$result" i get the debug error "1065: Query was empty". If I echo the "$result" everything works fine apart from the fact I see the query on screen :mad:

Help! It's driving me nuts!!!

catapult

For anyone that's interested here's what I found.

If the form data is not different from that already in the database it doesn't pick out the fields in the template record.

So if the form data posted to the script hasn't changed the variable "$updateSQL" may be NULL.

To get round this I put an if statement around
"$db->Execute($updateSQL) or die("Error in query: $updateSQL. " . $db->ErrorMsg());"
testing "$updateSQL" for a NULL value.

So my script now looks like this:

<?php
require_once("adodb/adodb.inc.php");
require_once("dbconnect.inc");

//make db connection
$db = db_connect();
$db->debug=0;

//editing a record?
$CustID=$_POST['CustID'];
if($CustID<=0)$CustID=-1;

//are we updating or inserting
if($CustID>=1)
{
//update data from array using the template
$updateSQL = $db->GetUpdateSQL($result,$data);
if($updateSQL!="") HERE IS THE CHANGE
{
$db->Execute($updateSQL) or die("Error in query: $updateSQL. " . $db->ErrorMsg());
}
$db->Close();
}
else
{
//insert data from array using the template
$insertSQL = $db->GetInsertSQL($result,$data);
$insertresult = $db->Execute($insertSQL) or die("Error in query: $insertSQL. " . $db->ErrorMsg());
if($insertresult)$CustID=$db->Insert_ID();
$db->Close();
}

echo "<a href=\"index.php?CustID=".$CustID."\"><< Back</a>";
?>

Hope this helps someone one day 🙂