homepage Welcome to WebmasterWorld Guest from 54.211.138.180
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Why the query failed?
ckdoublenecks




msg:4300470
 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




msg:4300472
 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




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

the error message was "query failed".

rocknbil




msg:4300548
 5:35 pm on Apr 19, 2011 (gmt 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)

ckdoublenecks




msg:4300561
 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




msg:4300754
 10:31 pm on Apr 19, 2011 (gmt 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?

ckdoublenecks




msg:4300761
 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




msg:4301218
 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




msg:4301400
 5:42 pm on Apr 20, 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'


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




msg:4301598
 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




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

-- dupe

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

g1smd




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

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

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

ckdoublenecks




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

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

rocknbil




msg:4302095
 7:12 pm on Apr 21, 2011 (gmt 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.

ckdoublenecks




msg:4302248
 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




msg:4302257
 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




msg:4302259
 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




msg:4302272
 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved