Welcome to WebmasterWorld Guest from 107.20.104.110

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Why the query failed?

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

New User

5+ Year Member

joined:Apr 16, 2011
posts: 23
votes: 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>
3:57 pm on Apr 19, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:Feb 25, 2011
posts: 91
votes: 0


Most probably the solution could be found by reading the error message which you (for reasons unknown) did not provide ;)
5:09 pm on Apr 19, 2011 (gmt 0)

New User

5+ Year Member

joined:Apr 16, 2011
posts: 23
votes: 0


the error message was "query failed".
5:35 pm on Apr 19, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


$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)
5:55 pm on Apr 19, 2011 (gmt 0)

New User

5+ Year Member

joined:Apr 16, 2011
posts: 23
votes: 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";
}
?>
10:31 pm on Apr 19, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


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?
10:45 pm on Apr 19, 2011 (gmt 0)

New User

5+ Year Member

joined:Apr 16, 2011
posts: 23
votes: 0


I don't understand what you mean "Did you paste it into your command line mysql editor"
11:53 am on Apr 20, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:Feb 25, 2011
posts: 91
votes: 0


He meant: did you try to execute the UPDATE statement directly to see if it works.
5:42 pm on Apr 20, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 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'


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.
12:59 am on Apr 21, 2011 (gmt 0)

New User

5+ Year Member

joined:Apr 16, 2011
posts: 23
votes: 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?
1:03 am on Apr 21, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member g1smd is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:July 3, 2002
posts:18903
votes: 0


-- dupe

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

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

Senior Member

WebmasterWorld Senior Member g1smd is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:July 3, 2002
posts:18903
votes: 0


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

See RFC 3339, ANSI X3.30 and NIST FIPS 4-1.
1:15 am on Apr 21, 2011 (gmt 0)

New User

5+ Year Member

joined:Apr 16, 2011
posts: 23
votes: 0


I'm looking those up now and should I change the date from varchar?
7:12 pm on Apr 21, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


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.
12:17 am on Apr 22, 2011 (gmt 0)

New User

5+ Year Member

joined:Apr 16, 2011
posts: 23
votes: 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";
?>
12:45 am on Apr 22, 2011 (gmt 0)

Full Member

joined:Sept 29, 2005
posts:216
votes: 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.
1:00 am on Apr 22, 2011 (gmt 0)

New User

5+ Year Member

joined:Apr 16, 2011
posts: 23
votes: 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
1:54 am on Apr 22, 2011 (gmt 0)

Full Member

joined:Sept 29, 2005
posts:216
votes: 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.