1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 7:09 am Apr 28, 2026

Forum Moderators: coopster

Message Too Old, No Replies

Why the query failed?

         

ckdoublenecks

3:40 pm on Apr 19, 2011 (gmt 0)

10+ Year Member



the below code inserts the fields correctly but when I submit I get the error message. Please tell me what's wrong with the code. 

<html><head>
<!--when the paidamt is keyed in, the current date, pd & paidsum are autoinserted-->
<script>
function $_(IDS) { return document.getElementById(IDS); }
function calculate_paid() {
 var pd = document.getElementById("pd");
 var datepaid = document.getElementById("datepaid");
 var amtdue = document.getElementById("amtdue");
 var paidamt = document.getElementById("paidamt");
 var paidsum = document.getElementById("paidsum");
// ********************set up date**********************
 var dateNow = new Date();
 var dayNow = dateNow.getDate();
 var datePaid = (dateNow.getMonth()+1)+"/"+dateNow.getDate()+"/"+dateNow.getFullYear();
datepaid.value = datePaid;
// ***************end date setup*************************
// ********accumulate charges into paidsum & set up pd*** 
amtdue.value = parseFloat(amtdue.value);
paidsum.value = parseFloat(paidsum.value) + parseFloat(paidamt.value);
pd.value = "P";
// ********************* end of calcs ******************
}
</script>
<script type="text/javascript">
window.google_analytics_uacct = "UA-256751-2";
</script>
<script type="text/javascript">
window.google_analytics_uacct = "UA-256751-2";
</script>
</head><body>
<?php
mysql_connect(localhost,root,"");
mysql_select_db(oodb) or die( "Unable to select database");
if(!empty($_POST["submit"]))
{
$invnum = $_POST['invnum'];
$query="SELECT * FROM oocust Where invnum='$invnum'";
$result=mysql_query($query);
if(mysql_num_rows($result))
{
echo "<form action='#' method='post'><b>Invoice Payment :<br /><br />
<table cellspacing=0 cellpadding=0 border=1>
 <tr>
 <th>Inv#</th>
 <th>ord#</th>
 <th>Name</th>
 <th>Description</th>
 <th>Paid Amt</th>
 <th>Amt Due</th>
 <th>Date Paid</th>
 <th>Code</th>
 <th>Total</th>
  </tr>";
while($row = mysql_fetch_assoc($result))
{
 echo "<tr>
<td><input type='text' size=5 name='invnum' value='" . $row['invnum'] . "'></td>
<td><input type='text' size=5 name='ordernum' value='" . $row['ordernum'] . "'></td>
<td><input type='text' size=25 name='bname' value='" . $row['bname'] . "'></td>
<td><input type='text' size=25 name='descr' value='" . $row['descr'] . "'></td>
<td><input type='text' size=7 id='paidamt' name='paidamt' value='" . $row['paidamt'] ."' 
onBlur='calculate_paid(this)'></td>
<td><input type='text' size=7 id='amtdue' name='charges' value='" . $row['amtdue'] ."'></td>
<td><input type='text' size=10 id='datepaid' name='datepaid' value='" . $row['datepaid'] . "'
onBlur='calculate_paid(this)'></td>
<td><input type='text' size=1 id='pd' name='pd' value='" . $row['pd'] . "'
onBlur='calculate_paid(this)'></td>
<td><input type='text' size=7 name='paidsum' value='" . $row['paidsum'] . "'
onBlur='calculate_paid(this)'></td> 
</tr>"; 
}
echo "</table>
<input type='submit' name='update' value='Make Payment' />
</form>";
}
else{echo "No listing for invoice no. $invnum.<br />Please select another.<br />";}
}
if(!empty($_POST["update"]))
{
$sql = "UPDATE payments SET
pd = '" . mysql_real_escape_string($_POST['pd']) . "',
datepaid = '" . mysql_real_escape_string($_POST['datepaid']) . "',
paidamt = '" . mysql_real_escape_string($_POST['paidamt']) . "',
amtdue = '" . mysql_real_escape_string($_POST['amtdue']) . "',
paidsum = '" . mysql_real_escape_string($_POST['paidsum']) . "'
WHERE invnum='".$_POST["invnum"]."'";
// **********************************************
mysql_query($sql) or die("Update query failed.");
// ***********************************************
echo "Record for invoice ".$_POST["invnum"]." has been updated";
}
?>
<form method="post" action="#">
<br />
<input type="text" name="invnum"/> <p>
<input type="submit" name="submit" value="select invoice no."/>
</form>
<script type="text/javascript"><!--
var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");
document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E"));
//-->
</script>
<script type="text/javascript"><!--
try {
var pageTracker = _gat._getTracker("UA-256751-2");
pageTracker._trackPageview();
} catch(err) {}
//-->
</script>
<script type="text/javascript"><!--
var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");
document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E"));
//-->
</script>
<script type="text/javascript"><!--
try {
var pageTracker = _gat._getTracker("UA-256751-2");
pageTracker._trackPageview();
} catch(err) {}
//-->
</script>
</body></html>

mbabuskov

3:57 pm on Apr 19, 2011 (gmt 0)

10+ Year Member



Most probably the solution could be found by reading the error message which you (for reasons unknown) did not provide ;)

ckdoublenecks

5:09 pm on Apr 19, 2011 (gmt 0)

10+ Year Member



the error message was "query failed".

rocknbil

5:35 pm on Apr 19, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



$sql = "UPDATE payments SET
pd = '" . mysql_real_escape_string($_POST['pd']) . "',
datepaid = '" . mysql_real_escape_string($_POST['datepaid']) . "',
paidamt = '" . mysql_real_escape_string($_POST['paidamt']) . "',
amtdue = '" . mysql_real_escape_string($_POST['amtdue']) . "',
paidsum = '" . mysql_real_escape_string($_POST['paidsum']) . "'
WHERE invnum='".$_POST["invnum"]."'";
// **********************************************
mysql_query($sql) or die("Update query failed.");


Change that line to

mysql_query($sql) or die("Update query failed: " . mysql_error());

Which will give you more information. The syntax basically looks ok that I can see, which leaves (maybe) you are referencing a field that doesn't exist, or have a typo - PaidSum is not the same as paidsum.

It's also possible it's something in the data that is causing an error, temporarily add this before mysql_query:

echo $sql;
exit;

Then you can copy and paste the entire select statement in your command line mysql client (or phpMyAdmin)

ckdoublenecks

5:55 pm on Apr 19, 2011 (gmt 0)

10+ Year Member



I made the suggested adjustments (see below code) and got the message:
UPDATE payments SET pd = 'P', datepaid = '4/19/2011', paidamt = '100.00', amtdue = '', paidsum = '100' WHERE invnum='151'

the update didn't occur and note that the paidsum has no decimal 
<?php
mysql_connect(localhost,root,"");
mysql_select_db(oodb) or die( "Unable to select database");
if(!empty($_POST["submit"]))
{
$invnum = $_POST['invnum'];
$query="SELECT * FROM oocust Where invnum='$invnum'";
$result=mysql_query($query);
if(mysql_num_rows($result))
{
echo "<form action='#' method='post'><b>Invoice Payment :<br /><br />
<table cellspacing=0 cellpadding=0 border=1>
 <tr>
 <th>Inv#</th>
 <th>ord#</th>
 <th>Name</th>
 <th>Description</th>
 <th>Paid Amt</th>
 <th>Amt Due</th>
 <th>Date Paid</th>
 <th>Code</th>
 <th>Total</th>
 </tr>";
while($row = mysql_fetch_assoc($result))
{
 echo "<tr>
<td><input type='text' size=5 name='invnum' value='" . $row['invnum'] . "'></td>
<td><input type='text' size=5 name='ordernum' value='" . $row['ordernum'] . "'></td>
<td><input type='text' size=25 name='bname' value='" . $row['bname'] . "'></td>
<td><input type='text' size=25 name='descr' value='" . $row['descr'] . "'></td>
<td><input type='text' size=7 id='paidamt' name='paidamt' value='" . $row['paidamt'] ."' 
onBlur='calculate_paid(this)'></td>
<td><input type='text' size=7 id='amtdue' name='charges' value='" . $row['amtdue'] ."'></td>
<td><input type='text' size=10 id='datepaid' name='datepaid' value='" . $row['datepaid'] . "'
onBlur='calculate_paid(this)'></td>
<td><input type='text' size=1 id='pd' name='pd' value='" . $row['pd'] . "'
onBlur='calculate_paid(this)'></td>
<td><input type='text' size=7 name='paidsum' value='" . $row['paidsum'] . "'
onBlur='calculate_paid(this)'></td> 
</tr>"; 
}
echo "</table>
<input type='submit' name='update' value='Make Payment' />
</form>";
}
else{echo "No listing for invoice no. $invnum.<br />Please select another.<br />";}
}
if(!empty($_POST["update"]))
{
$sql = "UPDATE payments SET
pd = '" . mysql_real_escape_string($_POST['pd']) . "',
datepaid = '" . mysql_real_escape_string($_POST['datepaid']) . "',
paidamt = '" . mysql_real_escape_string($_POST['paidamt']) . "',
amtdue = '" . mysql_real_escape_string($_POST['amtdue']) . "',
paidsum = '" . mysql_real_escape_string($_POST['paidsum']) . "'
WHERE invnum='".$_POST["invnum"]."'";
// **********************************************
echo $sql; 
exit; 
mysql_query($sql) or die("Update query failed: " . mysql_error());
// ***********************************************
echo "Record for invoice ".$_POST["invnum"]." has been updated";
}
?>

rocknbil

10:31 pm on Apr 19, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Re-read my post, there are two distinct things to do to find out why it won't update. Don't do them both at once, first one, then the other. Of course it won't update as posted - it exits just before the select statement is executed.

As far as I can tell there is nothing wrong with your select statement. Did you paste it into your command line mysql editor, and if you did, what happened?

ckdoublenecks

10:45 pm on Apr 19, 2011 (gmt 0)

10+ Year Member



I don't understand what you mean "Did you paste it into your command line mysql editor"

mbabuskov

11:53 am on Apr 20, 2011 (gmt 0)

10+ Year Member



He meant: did you try to execute the UPDATE statement directly to see if it works.

rocknbil

5:42 pm on Apr 20, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



I made the suggested adjustments (see below code) and got the message:
UPDATE payments SET pd = 'P', datepaid = '4/19/2011', paidamt = '100.00', amtdue = '', paidsum = '100' WHERE invnum='151'


Right, how did you create your database? phpMyAdmin? Copy that select statement and hit one of the SQL tabs, paste it in, see if it works on it's own.

I'll add, something is fishy about that date format. mysql date formats are in yyyy-mm-dd format, if you're using a plain text field (varchar, text, etc.) for dates you are robbing yourself of all the functions mysql has for date math. If it's a varchar field, datediff won't work. See the link I posted for dates in mysql - you can store it as yyyy-mm-dd but using the format function extract it into any format you want for display.

ckdoublenecks

12:59 am on Apr 21, 2011 (gmt 0)

10+ Year Member



thanks, that's what I wanted to hear - the solution, I mean. The date was created in phpmyadmin and it is varchar.
In US everyone uses mm/dd/yyyy and so that's what I wanted them to enter in the input program. So, can I reformat the date before this program? Is it possible? Can the date be reformatted as it is entered?

g1smd

1:03 am on Apr 21, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month



-- dupe

[edited by: g1smd at 1:11 am (utc) on Apr 21, 2011]

g1smd

1:09 am on Apr 21, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month



You're better off sticking with YYYY-MM-DD here.

See RFC 3339, ANSI X3.30 and NIST FIPS 4-1.

ckdoublenecks

1:15 am on Apr 21, 2011 (gmt 0)

10+ Year Member



I'm looking those up now and should I change the date from varchar?

rocknbil

7:12 pm on Apr 21, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Well, not **everyone** - but many . . .

Can the date be reformatted as it is entered?


Into the database? You mean you're having them manually enter mm/dd/yyyy? Very user-error prone. You should probably do that with select lists, then compose it as you need it. Otherwise something like
list ($mm,$dd,$yyyy) = explode('/',$_POST['your-date']);
$mynewdate = "$yyyy-$mm-$dd";

should do it. When extracting you'd use the format methods from the date and time page [dev.mysql.com] of the manual.

select date_format('2011-04-21',"%m/%d/%Y");
select date_format(curdate(),"%m/%d/%Y");
select date_format(database_fieldname,"%m/%d/%Y");

I'm looking those up now and should I change the date from varchar?


alter table yourtable change yourfield yournewfieldname date not null default '0000-00-00';

You can keep it the same field name if you want.

ckdoublenecks

12:17 am on Apr 22, 2011 (gmt 0)

10+ Year Member



I got this message when I executed the below code ?
Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in C:\xampp\htdocs\invoice\invcalc.php on line 22

<?php
$stat = mysql_connect(localhost,root,"");
$stat = mysql_select_db(oodb) or die( "Unable to select database");
$query = "SELECT id, tax,charges,datediff(curdate(),duedate) AS dayslate FROM oocust WHERE pd = ' '";
$stat = @mysql_fetch_assoc(mysql_query($query));
$result= mysql_query("select * from oocust");
while($row=mysql_fetch_array($result))
{
$id=$row['id']; 
$tax = $row['tax'];
$amtdue = $row['amtdue'];
$shipamt = $row['shipamt'];
$charges = $row['charges'];
$tax = $charges * .06;
$amtdue = $charges + $tax + $shipamt;
$days_late = ($row['dayslate'] > 0)?$row['dayslate'] : 0; 
$sql = "UPDATE oocust SET tax = '$tax', amtdue = '$amtdue', dayslate = '$days_late' WHERE 

duedate<sysdate;
mysql_query($sql) ;
$err=mysql_error();
if($err!="")
 {
 echo "Error in $sql: $err\n";
 }
 }
echo "Invoice Prep completed";
 ?>

johnblack

12:45 am on Apr 22, 2011 (gmt 0)



Change


$sql = "UPDATE oocust SET tax = '$tax', amtdue = '$amtdue', dayslate = '$days_late' WHERE

duedate<sysdate;


to

$sql = "UPDATE oocust SET tax = " . $tax . ", amtdue = " . $amtdue . ", dayslate = " . $days_late . " WHERE duedate < sysdate";

I'm assuming the columns tax, amtdue, dayslate are numeric.

ckdoublenecks

1:00 am on Apr 22, 2011 (gmt 0)

10+ Year Member



yes, the tax and amtdue are decimal(2) and the dayslate is int(4). here is the results:
Error in UPDATE oocust SET tax = 9.6, amtdue = 169.6, dayslate = 0 WHERE duedate < sysdate: Unknown column 'sysdate' in 'where clause' Error in UPDATE oocust SET tax = 5.28, amtdue = 110.28, dayslate = 0 WHERE duedate < sysdate: Unknown column 'sysdate' in 'where clause' Error in UPDATE oocust SET tax = 0.6, amtdue = 10.6, dayslate = 0 WHERE duedate < sysdate: Unknown column 'sysdate' in 'where clause' Error in UPDATE oocust SET tax = 18, amtdue = 318, dayslate = 0 WHERE duedate < sysdate: Unknown column 'sysdate' in 'where clause' Error in UPDATE oocust SET tax = 3.6, amtdue = 73.6, dayslate = 0 WHERE duedate < sysdate: Unknown column 'sysdate' in 'where clause' Error in UPDATE oocust SET tax = 3, amtdue = 53, dayslate = 0 WHERE duedate < sysdate: Unknown column 'sysdate' in 'where clause' Error in UPDATE oocust SET tax = 1.908, amtdue = 43.708, dayslate = 0 WHERE duedate < sysdate: Unknown column 'sysdate' in 'where clause' Invoice Prep completed

johnblack

1:54 am on Apr 22, 2011 (gmt 0)



It's expecting to find a column called sysdate on the table oocust.

Within knowing your exact data schema, I can't really suggest a fix to the query. Is sysdate supposed to be a system variable like NOW() or does it exist on another table, in which case you'll need to add a join to the query.

Hope that helps.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 7:09 am Apr 28, 2026