cgustaf
asked on
PHP VARIABLES IN MySQL QUERY
How do I replace the hard coded dates in a query such as this with PHP variables whose values are entered into a form:
$query_rs1_amt = "SELECT SUM(t_dontn.amt) AS inc_amt, date, ck_nbr FROM t_dontn WHERE ck_nbr <> 'Manual Entry' AND date BETWEEN '2013-07-01' AND '2013-07-31'";
I work in Dreamweaver MX, but have a tiny bit of knowledge of how PHP variables work in the Dreamweaver MX environment -- although I have not figure out how to enter variables in the DW variables entry form, I just deal with them on the code page.
Here are the queries and variables currently on the page:
$query_rs1_amt = "SELECT SUM(t_dontn.amt) AS inc_amt, date, ck_nbr FROM t_dontn WHERE ck_nbr <> 'Manual Entry' AND date BETWEEN '2013-07-01' AND '2013-07-31'";
I work in Dreamweaver MX, but have a tiny bit of knowledge of how PHP variables work in the Dreamweaver MX environment -- although I have not figure out how to enter variables in the DW variables entry form, I just deal with them on the code page.
Here are the queries and variables currently on the page:
mysql_select_db($database_ibtib_dbc, $ibtib_dbc);
$query_rs1_amt = "SELECT SUM(t_dontn.amt) AS inc_amt, date, ck_nbr FROM t_dontn WHERE ck_nbr <> 'Manual Entry' AND date BETWEEN '2013-07-01' AND '2013-07-31'";
$rs1_amt = mysql_query($query_rs1_amt, $ibtib_dbc) or die(mysql_error());
$row_rs1_amt = mysql_fetch_assoc($rs1_amt);
$totalRows_rs1_amt = mysql_num_rows($rs1_amt);
mysql_select_db($database_ibtib_dbc, $ibtib_dbc);
$query_rs2_exp = "SELECT SUM( t_expend.exp_amt) AS exp_amt, paid_date FROM t_expend WHERE paid_date BETWEEN '2013-07-01' AND '2013-07-31'";
$rs2_exp = mysql_query($query_rs2_exp, $ibtib_dbc) or die(mysql_error());
$row_rs2_exp = mysql_fetch_assoc($rs2_exp);
$totalRows_rs2_exp = mysql_num_rows($rs2_exp);
mysql_select_db($database_ibtib_dbc, $ibtib_dbc);
$query_rs3 = "SELECT t_dontn.date, DATE_FORMAT(t_dontn.date,'%M') AS manad, DATE_FORMAT(t_dontn.date, '%Y') AS don_year FROM t_dontn WHERE t_dontn.date between '2013-07-01' AND '2013-07-31' ORDER BY date DESC ";
$rs3 = mysql_query($query_rs3, $ibtib_dbc) or die(mysql_error());
$row_rs3 = mysql_fetch_assoc($rs3);
$totalRows_rs3 = mysql_num_rows($rs3);
mysql_select_db($database_ibtib_dbc, $ibtib_dbc);
$query_rs4 = "SELECT t_beg_bal.id, t_beg_bal.beg_bal, t_beg_bal.date FROM t_beg_bal ORDER BY date DESC ";
$rs4 = mysql_query($query_rs4, $ibtib_dbc) or die(mysql_error());
$row_rs4 = mysql_fetch_assoc($rs4);
$totalRows_rs4 = mysql_num_rows($rs4);
mysql_select_db($database_ibtib_dbc, $ibtib_dbc);
$query_rs5_dates = "SELECT t_beg_bal.beg_date, t_beg_bal.date, DATE_FORMAT(date,'%Y') as don_year, DATE_FORMAT(date, '%M') AS manad FROM t_beg_bal ORDER BY date DESC";
$rs5_dates = mysql_query($query_rs5_dates, $ibtib_dbc) or die(mysql_error());
$row_rs5_dates = mysql_fetch_assoc($rs5_dates);
$totalRows_rs5_dates = mysql_num_rows($rs5_dates);
// variables for the reconciliation form
$beg_bal = $row_rs4['beg_bal'];
$inc_amt = $row_rs1_amt['inc_amt'];
$total_inc = $inc_amt + $beg_bal;
$exp_amt = $row_rs2_exp['exp_amt'];
$total_exp = $exp_amt;
$grand_total = $total_inc - $total_exp;
$don_year = $row_rs5_dates['don_year'];
$inc_manad = $row_rs5_dates['manad'];
$end_date = $row_rs5_dates['date']
?> 
Ahh, Dreamweaver... How many times are you going to select the same data base?
This article tells how to handle DATETIME values in PHP and MySQL.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
See http://www.laprbass.com/RAY_temp_cgustaf.php
This article tells how to handle DATETIME values in PHP and MySQL.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
See http://www.laprbass.com/RAY_temp_cgustaf.php
<?php // RAY_temp_cgustaf.php
error_reporting(E_ALL);
// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28232891.html
// $query_rs1_amt = "SELECT SUM(t_dontn.amt) AS inc_amt, date, ck_nbr FROM t_dontn WHERE ck_nbr <> 'Manual Entry' AND date BETWEEN '2013-07-01' AND '2013-07-31'";
// IF THE DATA HAS BEEN POSTED
if (!empty($_POST))
{
// CONVERT EXTERNAL DATA TO UNIX TIMESTAMPS
$alpha = strtotime($_POST['a']);
$omega = strtotime($_POST['z']);
// IF CONVERSION WORKS
if ($alpha)
{
if ($omega)
{
// SET ISO-8601 DATE STRINGS
$alpha = date('Y-m-d', $alpha);
$omega = date('Y-m-d', $omega);
}
else { /* HANDLE OMEGA ERROR */ }
}
else { /* HANDLE ALPHA ERROR */ }
// CONSTRUCT THE QUERY
$query = "SELECT SUM(t_dontn.amt) AS inc_amt, date, ck_nbr FROM t_dontn WHERE ck_nbr <> 'Manual Entry' AND date BETWEEN '$alpha' AND '$omega'";
var_dump($query);
}
$form = <<<EOD
<form method="post">
START: <input name="a" />
ENDOF: <input name="z" />
<input type="submit" />
</form>
EOD;
echo $form;
To use a php variable in a query, just put it in as you would in any php syntax. Just for example using $manualentry and $date1 and $date2 php variables:
$query_rs1_amt = "SELECT SUM(t_dontn.amt) AS inc_amt, date, ck_nbr FROM t_dontn WHERE ck_nbr <> '$manualentry' AND date BETWEEN '$date1' AND '$date2' ";
or even
mysql_query( "SELECT SUM(t_dontn.amt) AS inc_amt, date, ck_nbr FROM t_dontn WHERE ck_nbr <> '$manualentry' AND date BETWEEN '$date1' AND '$date2' ", $ibtib_dbc)
$query_rs1_amt = "SELECT SUM(t_dontn.amt) AS inc_amt, date, ck_nbr FROM t_dontn WHERE ck_nbr <> '$manualentry' AND date BETWEEN '$date1' AND '$date2' ";
or even
mysql_query( "SELECT SUM(t_dontn.amt) AS inc_amt, date, ck_nbr FROM t_dontn WHERE ck_nbr <> '$manualentry' AND date BETWEEN '$date1' AND '$date2' ", $ibtib_dbc)
Using BETWEEN ... AND ... in the query may lead to error here. Please be careful.
(and please be careful when recommending it)
I don't see a definition of the table `t_dontn` or know what the data type of field `date` is, nor do I know if that table/field stores whole dates only or dates plus time.
As a user I might enter 2013-08-01 and 2013-08-31 for my range
and I am mentally expecting ALL of August to be reported.
BUT
The safest, most reliable and robust method for selecting information over a date range does NOT use between: use a combination of >= with < (date wanted + 1)
AND ( `date` >= '2013-08-01' AND `date` < ('2013-08-31'+1) ) *
This method will work regardless of date/time precision in that table and field.
please refer to: "Beware of Between"
* choose desired method of adding one day (could be in PHP or MySQL)
(and please be careful when recommending it)
I don't see a definition of the table `t_dontn` or know what the data type of field `date` is, nor do I know if that table/field stores whole dates only or dates plus time.
As a user I might enter 2013-08-01 and 2013-08-31 for my range
and I am mentally expecting ALL of August to be reported.
BUT
AND `date` BETWEEN '2013-08-01' AND '2013-08-31'
WILL NOT include ALL of August! (it will be one day short of that)The safest, most reliable and robust method for selecting information over a date range does NOT use between: use a combination of >= with < (date wanted + 1)
AND ( `date` >= '2013-08-01' AND `date` < ('2013-08-31'+1) ) *
This method will work regardless of date/time precision in that table and field.
please refer to: "Beware of Between"
* choose desired method of adding one day (could be in PHP or MySQL)
@Ray, does this warrant another PHP mysql API warning?
ASKER
Thanks to all of you who responded to my question.
PORTLETPAUL:
"Beware of Between" says "in SQL 'between' is used in the 'inclusive' sense of "jet between cities". The jet takes off from one city and lands in another. Both cities are included.
This seems to indicate a +1 for the end_date is not necessary. If it was, then beg_date would have to have a -1, wouldn it?
YODERCM:
Simply placing the variable into the query, replacing the hard-coded date, does not seem to work. Have tried it in various ways. The variables echo just fine on my page, but they don't work in the query. Not sure if PHP processings is an issue -- that is, if the variables are declared below the query, perhaps this is the reason the query does not work
ARGENTI:
I tested the GET suggestion, but could not make it work.
RAY_PASEUR
This is an excellent suggestion, as usual. Don't know if I'm capable of implementing it, but will try.
I ran the live example and it works great. Just what is needed. There is one change that I'd like: Instead of having to type, 2013-08-01, I prefer 130801 (i.e. yymmdd) for simplicity. MySQL converts 130801 to 2013-08-01 just fine.
PORTLETPAUL:
"Beware of Between" says "in SQL 'between' is used in the 'inclusive' sense of "jet between cities". The jet takes off from one city and lands in another. Both cities are included.
This seems to indicate a +1 for the end_date is not necessary. If it was, then beg_date would have to have a -1, wouldn it?
YODERCM:
Simply placing the variable into the query, replacing the hard-coded date, does not seem to work. Have tried it in various ways. The variables echo just fine on my page, but they don't work in the query. Not sure if PHP processings is an issue -- that is, if the variables are declared below the query, perhaps this is the reason the query does not work
ARGENTI:
I tested the GET suggestion, but could not make it work.
RAY_PASEUR
This is an excellent suggestion, as usual. Don't know if I'm capable of implementing it, but will try.
I ran the live example and it works great. Just what is needed. There is one change that I'd like: Instead of having to type, 2013-08-01, I prefer 130801 (i.e. yymmdd) for simplicity. MySQL converts 130801 to 2013-08-01 just fine.
Instead of having to type, 2013-08-01, I prefer...You do not have to type any particular format of the date, as long as it is useful with strtotime(). Try running the script and putting in interesting date variants like "yesterday" and "next Tuesday." The point of using strtotime() and date() is to normalize and filter the external input. The correct normalization for a DATETIME string is the ISO-8601 standard. Please read the article I linked above, and you will understand why I recommend this.
And here is the other Dreamweaver "gotcha" that you will need to figure out. PHP is doing away with MySQL; the API is deprecated in current PHP and will be removed. So you'll need to use a different API -- either MySQLi or PDO. This article explains why PHP is doing this and what you must do to keep your scripts running.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
>>>Simply placing the variable into the query, replacing the hard-coded date, does not seem to work. Have tried it in various ways.
It works fine for me. I'm working in straight php, but not sure why that would matter. Make sure you are using the quotes correctly.
Could you please post the exact query where you tried this and it failed, including the output of
echo MySQL_error();
immediately following that query.
It works fine for me. I'm working in straight php, but not sure why that would matter. Make sure you are using the quotes correctly.
Could you please post the exact query where you tried this and it failed, including the output of
echo MySQL_error();
immediately following that query.
The "beware of between" issue is applicable to DATETIME column types, when the programming naively uses DATE (not DATETIME) information in the query WHERE clause. See the article for the difference between the ISO-8601 values for "today" and "now." "Today" gives us the instant of beginning at midnight like 2013-09-07T00:00:00 whereas "Now" gives us something with a time-of-day like 2013-09-07T08:15:33.
If you are using a DATE column, there is no problem about the last day, because the inclusive nature of BETWEEN will work to your advantage, but that is not the case with a DATETIME column.
If you want to get all of the events of today based on a DATETIME column, your BETWEEN clause would need to include all of the hours, minutes and seconds of today, something like these values:
$alpha = date('c', strtotime('Today'));
$omega = date('c', strtotime('Today +1 DAY - 1 SECOND'));
... BETWEEN '$alpha' AND '$omega'
Using these, you will get inclusive values and the rendered clause will look like this:
...BETWEEN '2013-09-07T00:00:00' AND '2013-09-07T23:59:59'
The result of the query will include all of today, through the last second before the calendar flips over to tomorrow.
Hope that helps, ~Ray
If you are using a DATE column, there is no problem about the last day, because the inclusive nature of BETWEEN will work to your advantage, but that is not the case with a DATETIME column.
If you want to get all of the events of today based on a DATETIME column, your BETWEEN clause would need to include all of the hours, minutes and seconds of today, something like these values:
$alpha = date('c', strtotime('Today'));
$omega = date('c', strtotime('Today +1 DAY - 1 SECOND'));
... BETWEEN '$alpha' AND '$omega'
Using these, you will get inclusive values and the rendered clause will look like this:
...BETWEEN '2013-09-07T00:00:00' AND '2013-09-07T23:59:59'
The result of the query will include all of today, through the last second before the calendar flips over to tomorrow.
Hope that helps, ~Ray
ASKER
RAY_PASEUR
Hi -- I have attempted to implement your solution but am not yet successful. There is an issue with the variables $alpha and $omega. The error message is:
==========================
Notice: Undefined variable: alpha in /home/ibtib/public_html/db
Notice: Undefined variable: omega in /home/ibtib/public_html/db
Notice: Undefined variable: alpha in /home/ibtib/public_html/db
Notice: Undefined variable: omega in /home/ibtib/public_html/db
Notice: Undefined variable: alpha in /home/ibtib/public_html/db
Notice: Undefined variable: omega in /home/ibtib/public_html/db
========================
If you have suggestions on how to fix this, I'd appreciate it. Thanks.
I attach the code for the entire page, less the connection.
Hi -- I have attempted to implement your solution but am not yet successful. There is an issue with the variables $alpha and $omega. The error message is:
==========================
Notice: Undefined variable: alpha in /home/ibtib/public_html/db
Notice: Undefined variable: omega in /home/ibtib/public_html/db
Notice: Undefined variable: alpha in /home/ibtib/public_html/db
Notice: Undefined variable: omega in /home/ibtib/public_html/db
Notice: Undefined variable: alpha in /home/ibtib/public_html/db
Notice: Undefined variable: omega in /home/ibtib/public_html/db
========================
If you have suggestions on how to fix this, I'd appreciate it. Thanks.
I attach the code for the entire page, less the connection.
<?php require_once(); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO t_beg_bal (beg_bal, beg_date, `date`) VALUES (%s, %s, %s)",
GetSQLValueString($_POST['stmt_beg_bal'], "double"),
GetSQLValueString($_POST['beg_date'], "date"),
GetSQLValueString($_POST['date'], "date"));
mysql_select_db($database_ibtib_dbc, $ibtib_dbc);
$Result1 = mysql_query($insertSQL, $ibtib_dbc) or die(mysql_error());
$insertGoTo = "monthly_enter_stmt_bal_resp.php";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}
// RAY_temp_cgustaf.php
error_reporting(E_ALL);
// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28232891.html
// $query_rs1_amt = "SELECT SUM(t_dontn.amt) AS inc_amt, date, ck_nbr FROM t_dontn WHERE ck_nbr <> 'Manual Entry' AND date BETWEEN '2013-07-01' AND '2013-07-31'";
// IF THE DATA HAS BEEN POSTED
if (!empty($_POST))
{
// CONVERT EXTERNAL DATA TO UNIX TIMESTAMPS
$alpha = strtotime($_POST['beg_date']);
$omega = strtotime($_POST['date']);
// IF CONVERSION WORKS
if ($alpha)
{
if ($omega)
{
// SET ISO-8601 DATE STRINGS
$alpha = ('Y-m-d', $alpha);
$omega = ('Y-m-d', $omega);
}
else { /* HANDLE OMEGA ERROR */ }
}
else { /* HANDLE ALPHA ERROR */ }
// CONSTRUCT THE QUERY
//$query = "SELECT SUM(t_dontn.amt) AS inc_amt, date, ck_nbr FROM t_dontn WHERE ck_nbr <> 'Manual Entry' AND date BETWEEN '$alpha' AND '$omega'";
//var_dump($query);
}
?>
<!-- $form = <<<EOD--><style type="text/css">
<!--
body,td,th {
font-family: Geneva, Arial, Helvetica, sans-serif;
font-size: 12px;
}
.style1 { font-size: 14px;
font-weight: bold;
color: #999999;
}
.style1 { font-size: 14px;
font-weight: bold;
color: #666666;
}
-->
</style>
<!-- Queries here -->
<?php
//mysql_select_db($database_ibtib_dbc, $ibtib_dbc);
//$query_rs1_amt = "SELECT SUM(t_dontn.amt) AS inc_amt, date, ck_nbr FROM t_dontn WHERE ck_nbr <> 'Manual Entry' AND date BETWEEN '$alpha' AND '$omega'";
//$query_rs1_amt = "SELECT SUM(t_dontn.amt) AS inc_amt, date, ck_nbr FROM t_dontn WHERE ck_nbr <> 'Manual Entry' AND date BETWEEN '2013-08-01' AND '2013-08-31'";
//$rs1_amt = mysql_query($query_rs1_amt, $ibtib_dbc) or die(mysql_error());
//$row_rs1_amt = mysql_fetch_assoc($rs1_amt);
//$totalRows_rs1_amt = mysql_num_rows($rs1_amt);
mysql_select_db($database_ibtib_dbc, $ibtib_dbc);
$query_rs1 = "SELECT SUM(amt) AS inc_amt, date, ck_nbr FROM t_dontn WHERE ck_nbr <> 'Manual Entry' AND date BETWEEN '$alpha' AND '$omega'";
$rs1 = mysql_query($query_rs1, $ibtib_dbc) or die(mysql_error());
$row_rs1 = mysql_fetch_assoc($rs1);
$totalRows_rs1 = mysql_num_rows($rs1);
mysql_select_db($database_ibtib_dbc, $ibtib_dbc);
$query_rs2_exp = "SELECT SUM(t_expend.exp_amt) AS exp_amt, paid_date FROM t_expend WHERE paid_date BETWEEN '$alpha' AND '$omega'";
//$query_rs2_exp = "SELECT SUM( t_expend.exp_amt) AS exp_amt, paid_date FROM t_expend WHERE paid_date BETWEEN '2013-08-01' AND '2013-08-31'";
$rs2_exp = mysql_query($query_rs2_exp, $ibtib_dbc) or die(mysql_error());
$row_rs2_exp = mysql_fetch_assoc($rs2_exp);
$totalRows_rs2_exp = mysql_num_rows($rs2_exp);
mysql_select_db($database_ibtib_dbc, $ibtib_dbc);
$query_rs3 = "SELECT date, DATE_FORMAT(date,'%M') AS manad, DATE_FORMAT(date, '%Y') AS don_year FROM t_dontn WHERE date BETWEEN '$alpha' AND '$omega' ORDER BY date DESC ";
//$query_rs3 = "SELECT t_dontn.date, DATE_FORMAT(t_dontn.date,'%M') AS manad, DATE_FORMAT(t_dontn.date, '%Y') AS don_year FROM t_dontn WHERE t_dontn.date between '2013-08-01' AND '2013-08-31' ORDER BY date DESC ";
$rs3 = mysql_query($query_rs3, $ibtib_dbc) or die(mysql_error());
$row_rs3 = mysql_fetch_assoc($rs3);
$totalRows_rs3 = mysql_num_rows($rs3);
mysql_select_db($database_ibtib_dbc, $ibtib_dbc);
$query_rs4 = "SELECT t_beg_bal.id, t_beg_bal.beg_bal, t_beg_bal.date FROM t_beg_bal ORDER BY date DESC ";
$rs4 = mysql_query($query_rs4, $ibtib_dbc) or die(mysql_error());
$row_rs4 = mysql_fetch_assoc($rs4);
$totalRows_rs4 = mysql_num_rows($rs4);
mysql_select_db($database_ibtib_dbc, $ibtib_dbc);
$query_rs5_dates = "SELECT t_beg_bal.beg_date, t_beg_bal.date, DATE_FORMAT(date,'%Y') as don_year, DATE_FORMAT(date, '%M') AS manad FROM t_beg_bal ORDER BY date DESC";
$rs5_dates = mysql_query($query_rs5_dates, $ibtib_dbc) or die(mysql_error());
$row_rs5_dates = mysql_fetch_assoc($rs5_dates);
$totalRows_rs5_dates = mysql_num_rows($rs5_dates);
// variables for the reconciliation form
$beg_bal = $row_rs4['beg_bal'];
$inc_amt = $row_rs1['inc_amt'];
$total_inc = $inc_amt + $beg_bal;
$exp_amt = $row_rs2_exp['exp_amt'];
$total_exp = $exp_amt;
$grand_total = $total_inc - $total_exp;
$don_year = $row_rs5_dates['don_year'];
$inc_manad = $row_rs5_dates['manad'];
$beg_date = $row_rs5_dates['beg_date'];
$end_date = $row_rs5_dates['date']
?>
<!-- End Queries -->
<table width="600" border="1" align="center">
<tr>
<td valign="top"><table width="600" align="center">
<tr>
<td width="299" valign="top"><div align="left"><a href="../../switch.php">Home</a><br>
<a href="javascript:window.print()">Print</a> </div></td>
<td width="297" valign="top"><div align="right"><a href="../enter_don.php">Donation Entry</a><br>
<a href="../enter_disb.php">Expenditure Entry </a></div></td>
</tr>
<tr>
<td colspan="2" valign="top"><div align="center"><span class="style1">IBT NA Monthly Statement Reconciliation</span></div></td>
</tr>
<tr>
<td colspan="2" valign="top"><div align="center"><?php echo $inc_manad; ?>, <?php echo $don_year; ?></div></td>
</tr>
<tr>
<td colspan="2" valign="top"><div align="center"><?php echo "Statement Date: $end_date"; ?></div></td>
</tr>
<tr>
<td colspan="2" valign="top"> </td>
</tr>
<tr>
<td colspan="2" valign="top"><form action="<?php echo $editFormAction; ?>" name="form1" method="POST">
<p></p>
<table width="600" align="center">
<tr valign="middle">
<td width="294" height="36"><div align="right">Enter Statement Start Date (yymmdd): </div></td>
<td width="294"><input name="beg_date" id="beg_date" /></td>
</tr>
<tr valign="middle">
<td height="33"><div align="right">Enter Statement.End Date (yymmdd):</div></td>
<td><input name="date" id="date" /></td>
</tr>
<tr valign="middle">
<td><div align="right">Enter Statement Beginning Balance: </div></td>
<td><input name="stmt_beg_bal" type="text" id="stmt_beg_bal"></td>
</tr>
<tr>
<td colspan="2" valign="top"><div align="center">
<hr>
</div></td>
</tr>
<tr>
<td colspan="2" valign="top"> </td>
</tr>
<tr>
<td colspan="2" valign="top"><div align="right"><?php echo "$inc_manad Beginning Balance: $",number_format($beg_bal,2);?></div></td>
</tr>
<tr>
<td colspan="2" valign="top"><div align="right"><?php echo "$inc_manad Donations Income: $",number_format($inc_amt,2); ?></div></td>
</tr>
<tr>
<td colspan="2" valign="top"><div align="right"><?php echo "Total $inc_manad Income: $",number_format($total_inc,2); ?></div></td>
</tr>
<tr>
<td colspan="2" valign="top"><div align="right"><?php echo "Total $inc_manad Expenditures: $",number_format($total_exp,2); ?></div></td>
</tr>
<tr>
<td colspan="2" valign="top"><div align="right">-------------------------------------------------------- </div></td>
</tr>
<tr>
<td colspan="2" valign="top"><div align="right"><?php echo "Total outgoing $inc_manad Balance: $",number_format($grand_total,2);?></div></td>
</tr>
<tr>
<td colspan="2" valign="top"> </td>
</tr>
<tr>
<td colspan="2" valign="top"> </td>
</tr>
<tr>
<td colspan="2" valign="top"><?php echo "Beg date $beg_date"?></td>
</tr>
<tr>
<td colspan="2" valign="top"><?php echo "End date $end_date"?></td>
</tr>
<tr>
<td colspan="2" valign="top"><div align="center">
<input type="submit" />
</div></td>
</tr>
</table>
<input type="hidden" name="MM_insert" value="form1">
</form></td>
</tr>
<tr>
<td colspan="2" valign="top"> </td>
</tr>
</table></td>
</tr>
</table>
<p><!-- EOD;
echo $form; -->
</p>
<?php
mysql_free_result($rs1);
mysql_free_result($rs2_exp);
mysql_free_result($rs3);
mysql_free_result($rs4);
mysql_free_result($rs5_dates);
?>
Sorry, I find all this stuff that Dreamweaver creates to be mostly incomprehensible. The example I posted was a "teaching example" for the purpose of illustrating the principles of how to handle DATE and DATETIME values in PHP and MySQL. While it's true that Dreamweaver can generate PHP and MySQL code, it's never a "drop-in" operation when you need to modify the generated code. My guess is that "beg_date" and "date" might match up with $alpha and $omega, but that's just a guess. And I expect that these variables would need to be created before the $insertSQL on line 34. It looks like the script redirects (line 47) before the variables get created.
I think this is a task for a professional PHP developer, so you might consider someone on eLance who works with Dreamweaver and is accustomed to modifying Dreamweaver-generated PHP.
I think this is a task for a professional PHP developer, so you might consider someone on eLance who works with Dreamweaver and is accustomed to modifying Dreamweaver-generated PHP.
>>I prefer 130801 (i.e. yymmdd)
please don't rely on 2 digits for year, that's never a good idea. A standards based approach will endure and although its a 4 more characters 2013-08-01 is 2013-08-01 without needing mental arithmetic (i.e. more maintainable and legible). It's also more "parsable" if that should ever be needed (e.g. global edits), and easily distinguished from an integer.
Just a thought.
please don't rely on 2 digits for year, that's never a good idea. A standards based approach will endure and although its a 4 more characters 2013-08-01 is 2013-08-01 without needing mental arithmetic (i.e. more maintainable and legible). It's also more "parsable" if that should ever be needed (e.g. global edits), and easily distinguished from an integer.
Just a thought.
Somehow you are not setting $alpha and $omega.
Echo them out immediately before the failing query and see what they are.
Echo them out immediately before the failing query and see what they are.
Wait a second ... you have a field named date? You have to use backticks `date` for the field name, since date is a reserved keyword.
greetings cgustaf, , you do not seem to Follow the working Rules for PHP variables, and you have copied and pasted Ray's code, either without looking at it to UNDERSTAND what it does and more important here WHERE IT DOES IT.
you get this error - Notice: Undefined variable: alpha in /home/ibtib/public_html/db
because you do NOT follow the PHP variable rules, in that you MUST set a VALUE into a variable (initialize it) Before you use it! so if you start on line 107 and move UPWARD in your code until you see this -
$alpha =
if you do not see it then you HAVE TO ADD IT to give $alpha a value, however on line 59 you have -
$alpha = strtotime($_POST['beg_date
So ??? why is it crapin out on you, $alpha is set, right? NO NO NO, $alpha is not set, can you tell me WHY?
it is because ray has this -
// IF THE DATA HAS BEEN POSTED
if (!empty($_POST)) { // here is what you do NOT understand ! !
but your MySQL code below that is crap without the $_POST, and yet you have FAILED to move all of the MySQL code inside the brackets for
if (!empty($_POST)) {
so it does NOT run without the $_POST and the $alpha $omega
this is PHP form programming 101, the basic stuff you have to know if you do any database programming from a form $_POST.
you have blindly copied rays code you leave in lines like -
else { /* HANDLE ALPHA ERROR */ }
he capitalized it so you would fill in some error code there, but you did NOT, this should be filled out
Be warned that Dreamweaver does not do PHP well at all, and you will have to learn PHP even if you use Dreamweaver.
you get this error - Notice: Undefined variable: alpha in /home/ibtib/public_html/db
because you do NOT follow the PHP variable rules, in that you MUST set a VALUE into a variable (initialize it) Before you use it! so if you start on line 107 and move UPWARD in your code until you see this -
$alpha =
if you do not see it then you HAVE TO ADD IT to give $alpha a value, however on line 59 you have -
$alpha = strtotime($_POST['beg_date
So ??? why is it crapin out on you, $alpha is set, right? NO NO NO, $alpha is not set, can you tell me WHY?
it is because ray has this -
// IF THE DATA HAS BEEN POSTED
if (!empty($_POST)) { // here is what you do NOT understand ! !
but your MySQL code below that is crap without the $_POST, and yet you have FAILED to move all of the MySQL code inside the brackets for
if (!empty($_POST)) {
so it does NOT run without the $_POST and the $alpha $omega
this is PHP form programming 101, the basic stuff you have to know if you do any database programming from a form $_POST.
you have blindly copied rays code you leave in lines like -
else { /* HANDLE ALPHA ERROR */ }
he capitalized it so you would fill in some error code there, but you did NOT, this should be filled out
Be warned that Dreamweaver does not do PHP well at all, and you will have to learn PHP even if you use Dreamweaver.
ID: 39472867
arbitrary deductions of time units so that the criteria suit the syntax = potential gaps
It boils down to a choice of:
a technique which works always (not using between), or
a technique that can fail (using between)
happy to leave it as "agree to disagree";
I will however reserve the right to pester on this topic as it can be a disaster
(I have seen several such)
arbitrary deductions of time units so that the criteria suit the syntax = potential gaps
from 5.6.4 up there are now fractional seconds:
http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html
+ If you go to the bother of deducting a second, why not just add 1 day instead?
http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html
(and note this is a sleeping bug maker in the making too)
How does one guarantee that a transaction does not occur after 59:59:59 and before 00:00:00?+ If you go to the bother of deducting a second, why not just add 1 day instead?
It boils down to a choice of:
a technique which works always (not using between), or
a technique that can fail (using between)
happy to leave it as "agree to disagree";
I will however reserve the right to pester on this topic as it can be a disaster
(I have seen several such)
I used to think DATE and TIME were reserved words in MySQL, but apparently they are not. However, I still would begin to itch if I used a data type as a variable name.
http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-5.html
http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-5.html
ASKER
Here is a working solution to this question.
Syntax required to place variables into MySQL queries in Dreamweaver MX
In the page code, the variables are initialized above the queries in which they are used. The values are entered on a form by the user.
Initializing the variables
--------------------------
mysql_select_db($database_
$query_rs5_dates = "SELECT t_beg_bal.begdate, t_beg_bal.enddate, DATE_FORMAT(enddate,'%Y') as don_year, DATE_FORMAT(enddate,'%M') AS manad FROM t_beg_bal
ORDER BY enddate DESC";
$rs5_dates = mysql_query($query_rs5_dat
$row_rs5_dates = mysql_fetch_assoc($rs5_dat
$totalRows_rs5_dates = mysql_num_rows($rs5_dates)
$begdate = $row_rs5_dates['begdate'];
$enddate = $row_rs5_dates['enddate'];
The syntax necessary to make the variables insert the values in the query
--------------------------
...BETWEEN '".$begdate."' AND '". $enddate ."'";
Note that the outer quotes around the variables are single quotes. The inner are double. Also note the periods
Here is a complete query were this code is used.
--------------------------
mysql_select_db($database_
$query_rs1_amt = "SELECT SUM(t_dontn.amt) AS inc_amt, t_dontn.date, t_dontn.ck_nbr FROM t_dontn WHERE ck_nbr <> 'Manual Entry' AND date BETWEEN '".$begdate."' AND '". $enddate ."'";
$rs1_amt = mysql_query($query_rs1_amt
$row_rs1_amt = mysql_fetch_assoc($rs1_amt
$totalRows_rs1_amt = mysql_num_rows($rs1_amt);
mysql_select_db($database_
Syntax required to place variables into MySQL queries in Dreamweaver MX
In the page code, the variables are initialized above the queries in which they are used. The values are entered on a form by the user.
Initializing the variables
--------------------------
mysql_select_db($database_
$query_rs5_dates = "SELECT t_beg_bal.begdate, t_beg_bal.enddate, DATE_FORMAT(enddate,'%Y') as don_year, DATE_FORMAT(enddate,'%M') AS manad FROM t_beg_bal
ORDER BY enddate DESC";
$rs5_dates = mysql_query($query_rs5_dat
$row_rs5_dates = mysql_fetch_assoc($rs5_dat
$totalRows_rs5_dates = mysql_num_rows($rs5_dates)
$begdate = $row_rs5_dates['begdate'];
$enddate = $row_rs5_dates['enddate'];
The syntax necessary to make the variables insert the values in the query
--------------------------
...BETWEEN '".$begdate."' AND '". $enddate ."'";
Note that the outer quotes around the variables are single quotes. The inner are double. Also note the periods
Here is a complete query were this code is used.
--------------------------
mysql_select_db($database_
$query_rs1_amt = "SELECT SUM(t_dontn.amt) AS inc_amt, t_dontn.date, t_dontn.ck_nbr FROM t_dontn WHERE ck_nbr <> 'Manual Entry' AND date BETWEEN '".$begdate."' AND '". $enddate ."'";
$rs1_amt = mysql_query($query_rs1_amt
$row_rs1_amt = mysql_fetch_assoc($rs1_amt
$totalRows_rs1_amt = mysql_num_rows($rs1_amt);
mysql_select_db($database_
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for cgustaf's comment #a39478911
for the following reason:
This solution was found outside Experts Exchange.
Accepted answer: 0 points for cgustaf's comment #a39478911
for the following reason:
This solution was found outside Experts Exchange.
This solution was found outside Experts Exchange.???
How could that be?
cgustaf posted his question at 2013-09-06 at 19:06:25.
The first answer (mine) was posted at 2013-09-06 at 19:15:08, followed by a plethora of other answers in order to clarify things for him.
If we stick to the question I consider my answer to be a good one.
Shall I remind the author this is not a place to do somebody's homework, but to help people with good answers to their questions, with information they can use in order to help them solve their problems! Not to do their homework, not to give them the code fully written!
cgustaf, sorry I missed your question regarding between
"Beware of Between" says "in SQL 'between' is used in the 'inclusive' sense of "jet between cities". The jet takes off from one city and lands in another. Both cities are included.
This seems to indicate a +1 for the end_date is not necessary. If it was, then beg_date would have to have a -1, wouldn it?
+1 for the end date is absolutely needed - if you do NOT use between
The biggest disadvantage of between is that it DOES include the upper boundary, and this can lead to error (i.e. it can produce an "overlap" with the "next period")
Some believe that deducting a small time unit from the upper boundary is a safe solution to that problem. My argument is that this is not a safe solution, as fails if the underlying data precision is increased beyond 1 second, which is exactly what has happened as at MySQL 5.6.4
All code that has previously deducted 1 second to compensate for 'between' is now open to a gap in their date ranges of 1 second. Might sound trivial until financial transactions in a 24x7 system suddenly disappear from reports.
if you do NOT use between, but instead use a combination of
( field >= low_value and field < (high_value+1) )
then this will work for every precision of date/time information
and because it is so robust you can use it all the time without needing to worry about the underlying data precision (i.e. code commonality).
btw: it's not homework is it?
"Beware of Between" says "in SQL 'between' is used in the 'inclusive' sense of "jet between cities". The jet takes off from one city and lands in another. Both cities are included.
This seems to indicate a +1 for the end_date is not necessary. If it was, then beg_date would have to have a -1, wouldn it?
+1 for the end date is absolutely needed - if you do NOT use between
The biggest disadvantage of between is that it DOES include the upper boundary, and this can lead to error (i.e. it can produce an "overlap" with the "next period")
Some believe that deducting a small time unit from the upper boundary is a safe solution to that problem. My argument is that this is not a safe solution, as fails if the underlying data precision is increased beyond 1 second, which is exactly what has happened as at MySQL 5.6.4
All code that has previously deducted 1 second to compensate for 'between' is now open to a gap in their date ranges of 1 second. Might sound trivial until financial transactions in a 24x7 system suddenly disappear from reports.
if you do NOT use between, but instead use a combination of
( field >= low_value and field < (high_value+1) )
then this will work for every precision of date/time information
and because it is so robust you can use it all the time without needing to worry about the underlying data precision (i.e. code commonality).
btw: it's not homework is it?
ASKER
Thanks, PortletPaul, looks like a reasonable argument. I understand the <= etc. route. In fact I attempted to use it early on, but the code failed -- but now I understand it failed for for other reasons.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Found the solution outside Experts Exchange -- and shared it.
In this case, whe running your script you will have your variables $_GET['DateStart'] and $_GET['DateEnd'] filled with your input dates.
Now you will have to change your PHP code like this:
Open in new window
This is just the beginning of your job, as you should consider testing your variables and using PDO or mysqli extension with prepared queries, to avoid SQL injection attacks.