Welcome to WebmasterWorld Guest from 54.226.62.251

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Why the query failed?

     

ckdoublenecks

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



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)



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)



the error message was "query failed".

rocknbil

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

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 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)



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 rocknbil is a WebmasterWorld Top Contributor of All Time 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)



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)



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 rocknbil is a WebmasterWorld Top Contributor of All Time 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)



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 g1smd is a WebmasterWorld Top Contributor of All Time 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 g1smd is a WebmasterWorld Top Contributor of All Time 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)



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 rocknbil is a WebmasterWorld Top Contributor of All Time 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)



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)



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month