...

View Full Version : PHP query database based on date criteria



mbarandao
12-01-2010, 06:31 PM
Hello:

I have the following challenge I need assistance with. I have a mysql table


-- phpMyAdmin SQL Dump
-- version 3.3.3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 01, 2010 at 01:22 PM
-- Server version: 5.1.48
-- PHP Version: 5.2.13

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `shop`
--

-- --------------------------------------------------------

--
-- Table structure for table `billofservice`
--

CREATE TABLE IF NOT EXISTS `billofservice` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`dateofinsert` varchar(10) NOT NULL,
`invoicenum` varchar(15) NOT NULL DEFAULT '',
`servicedesc` varchar(255) NOT NULL DEFAULT '',
`clientID` varchar(15) NOT NULL,
`date` varchar(25) NOT NULL,
`servicearea` varchar(255) NOT NULL DEFAULT '',
`cost` varchar(15) NOT NULL,
`qty` varchar(3) NOT NULL DEFAULT '',
`price` varchar(3) NOT NULL DEFAULT '',
`tax` varchar(10) NOT NULL,
`total` varchar(10) NOT NULL,
`laborcost` varchar(10) NOT NULL,
`paid` varchar(10) NOT NULL,
`paymenttype` varchar(20) NOT NULL,
`Balancedue` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=35 ;

--
-- Dumping data for table `billofservice`
--

INSERT INTO `billofservice` (`id`, `dateofinsert`, `invoicenum`, `servicedesc`, `clientID`, `date`, `servicearea`, `cost`, `qty`, `price`, `tax`, `total`, `laborcost`, `paid`, `paymenttype`, `Balancedue`) VALUES
(14, '2010-11-23', '16253', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', '1234dc', 'November 18, 2010', 'Suspension', '$234', '1', '', '$42.30', '$747.30', '', '$500', '', '$247.30'),
(15, '2010-11-22', '18719', 'Information updated\r\nOil changed\r\nbrakes installed', 'dg9642', 'November 18, 2010', 'Transmission', '$123', '3', '', '$31.14', '$550.14', '', '$232', '', '$318.14'),
(16, '2010-11-20', '12451', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', 'mossa01', 'November 18, 2010', 'Transmission', '$324', '1', '', '$43.20', '$763.20', '', '$213', '', '$550.20'),
(17, '2010-10-12', '1347', 'New brakes installed\r\nOil Change Performed', 'mossa01', 'November 19, 2010', 'Other', '$234', '1', '', '$18.54', '$327.54', '', '$327.54', '', '$0.00'),
(18, '2010-09-13', '25486', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', 'md66894', 'November 19, 2010', 'Steering', '$123', '3', '', '$0.00', '$519.00', '', '$0.00', '', '$519.00'),
(19, '2010-07-12', '25194', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', 'md66894', 'November 19, 2010', 'Steering', '$234', '1', '', '$30.30', '$535.30', '$225.00', '$535.30', '', '$0.00'),
(20, '2010-11-01', '22442', 'Oil change', 'mossa01', 'November 19, 2010', 'Suspension', '$34', '1', '', '$3.84', '$67.84', '$30.00', '$67.84', 'Cash', '$0.00'),
(21, '0', '16726', 'test', 'md66894', 'November 22, 2010', 'Transmission', '$455', '4', '', '$118.20', '$2088.20', '$150.00', '$0.00', 'Select One', '$2088.20'),
(22, '0', '27128', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', 'mossa01', 'November 22, 2010', 'Suspension', '$32', '3', '', '$10.26', '$181.26', '$75.00', '$100', 'Select One', '$81.26'),
(23, '0', '28389', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', 'mossa01', 'November 22, 2010', 'Transmission', '$32', '1', '', '$0.00', '$182.00', '$150.00', '$0.00', 'Select One', '$182.00'),
(24, '0', '4993', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', '1111va', 'November 23, 2010', 'Transmission', '$343', '1', '', '$38.58', '$681.58', '$300.00', '$324', 'Select One', '$357.58'),
(25, '0', '20821', 'information update', 'mossa01', 'November 23, 2010', 'Steering', '$435', '3', '', '$91.80', '$1621.80', '$225.00', '$0.00', 'Select One', '$1621.80'),
(26, '0', '24231', '4Click here to add details of today\\\\\\\\\\\\\\''s service4', 'mossa01', 'November 22, 2010', 'Suspension', '$0.00', '1', '', '$0.00', '$0.00', '$0.00', '$0.00', 'Select One', '$0.00'),
(27, '0', '2396', 'Click here to add details of today\\\\\\\\\\\\\\''s servicet', 'mossa01', 'November 22, 2010', 'Suspension', '$0.00', '1', '', '$0.00', '$0.00', '$0.00', '$0.00', 'Select One', '$0.00'),
(28, '0', '9535', 'Information update', 'dg9642', 'November 24, 2010', 'Steering', '$324', '2', '', '$56.88', '$1004.88', '$300.00', '$1234', 'Discover', '$-229.12'),
(29, '0', '30526', 'Information update\r\nOil change\r\nNew brakes', '7436gd', 'November 24, 2010', 'Other', '$34', '2', '', '$8.58', '$151.58', '$75.00', '$151.58', 'Cash', '$0.00'),
(30, '0', '25553', 'Oil change\r\nBrakes\r\nNew Transmission', 'mossa01', 'November 24, 2010', 'Multiple', '$4526', '1', '', '$304.32', '$5376.32', '$300.00', '$3049', 'Check', '$2327.32'),
(31, '2010-11-10', '18266', 'We are performing an oil change\r\nChanging new brakes\r\nNew tires', 'md66894', 'November 25, 2010', 'Brake', '$232', '3', '', '$50.76', '$896.76', '$150.00', '$0.00', 'Cash', '$896.76'),
(32, '2010-11-27', '15818', 'information update', 'md66894', 'November 29, 2010', 'Engine Mechanical', '$223', '3', '', '$53.64', '$947.64', '$225.00', '$32', 'AMEX', '$915.64'),
(33, '2010-11-29', '23986', 'Information update', 'md66894', 'November 29, 2010', 'Engine Mechanical', '$352', '3', '', '$81.36', '$1437.36', '$300.00', '$0.00', 'Cash', '$1437.36'),
(34, '2010-11-30', '22756', 'oil change\r\nnew brakes', 'mossa01', 'November 30, 2010', 'Air Intake', '$26', '1', '', '$3.66', '$64.66', '$35.00', '$0.00', 'Cash', '$64.66');


I want to be able to perform a sql statment from the website that takes the value of those two dates (ie: November 29, 2010 and November 01, 2010) and performs a sum calculation of column within the table called "tax". The trick is that the value of those two dates are passed to the php script via url and processed partial in the following way:



<?php
$var = $_REQUEST['theDate'];//@$_GET['q'] ;
$trimmed = trim($var); //trim whitespace from the stored variable


Any thoughts on how I can I achieve this task!
Mossa

Lamped
12-01-2010, 06:47 PM
I think the main problems are the varchar format of the date_of_insert field, and the general date-time format you're using. I always use unix timestamps and int fields to save this aggravation. With that in mind and that I could be wrong, I think something like this is close to your solution:


$date1 = '2010-05-30';
$date2 = '2010-06-15';
$res = mysql_query('SELECT * FROM `billofservice` WHERE STR_TO_DATE('.$date1.', "%Y-%m-%d") > STR_TO_DATE(`date_of_insert`, "%Y-%m-%d") AND STR_TO_DATE('.$date2.', "%Y-%m-%d") < STR_DATE(`date_of_insert`, "%Y-%m-%d")');
$total = 0;
while ($row = mysql_fetch_assoc($res)) {
$total += $row['tax'];
}

mbarandao
12-01-2010, 07:50 PM
Thanks Lamped for the response. I have modified your suggestion to include variable being passed through url. However, I'm getting a "Couldn't execute query" error. Specifically: "Error: FUNCTION shop.STR_DATE does not exist"

here is the code:


<?php
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost","xxxxxxxx","xxxxxxxxxxxx"); //(host, username, password)

mysql_select_db("shop") or die("Unable to select database"); //select which database we're using
$date1 = $_REQUEST['theDate'];
$date2 = $_REQUEST['currentdate'];
//echo "$date1"; echo" $date2";

$query= 'SELECT * FROM `billofservice` WHERE STR_TO_DATE('.$date1.', "%Y-%m-%d") > STR_TO_DATE(`date_of_insert`, "%Y-%m-%d") AND STR_TO_DATE('.$date2.', "%Y-%m-%d") < STR_DATE(`date_of_insert`, "%Y-%m-%d")';
$result = mysql_query($query) or die("Couldn't execute query");
$total = 0;
while($row = mysql_fetch_assoc($result)) {
$total += $row['tax'];
}
?>


Any ideas, what I'm overlooking?

Lamped
12-01-2010, 08:02 PM
The last STR_TO_DATE in the query is actually STR_DATE, change that and see what happens. Yes, that was my fault :)

I take no responsibility if your PC blows up.

mbarandao
12-01-2010, 08:09 PM
Thanks for the humor!

The Last STR_TO_DATE being


AND STR_DATE('.$date2.', "%Y-%m-%d")


I've changed to STR_DATE with no change in the outcome.

Lamped
12-01-2010, 08:10 PM
No, I mean it should be STR_TO_DATE, not STR_DATE. Sorry, I worded it badly.

mbarandao
12-01-2010, 08:20 PM
That was it! Error disappeared, but the total is not echo'ed.

Lamped
12-01-2010, 08:40 PM
Did you put echo($total); at the bottom?

mbarandao
12-01-2010, 08:51 PM
this is the full code:


<?php
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost","XXXX","XXXXX"); //(host, username, password)

mysql_select_db("shop") or die("Unable to select database"); //select which database we're using
$date1 = $_REQUEST['theDate'];
$date2 = $_REQUEST['currentdate'];
echo"<br/>";
echo "Total Sales Taxes Collected For the Following Period";
echo "<br/>";
echo "From $date1"; echo" To $date2";
echo"<br/>";
echo"<br/>";

$query= 'SELECT * FROM `billofservice` WHERE STR_TO_DATE('.$date1.', "%Y-%m-%d") > STR_TO_DATE(`date_of_insert`, "%Y-%m-%d") AND STR_TO_DATE('.$date2.', "%Y-%m-%d") < STR_TO_DATE(`date_of_insert`, "%Y-%m-%d")';
$result=mysql_query($query);
if(!$result){die("Error: ".mysql_error());
}
$total = 0;
while($row = mysql_fetch_assoc($result)) {
$total += $row['tax'];
}
?>


Additionally, you earlier mentioned that it is best to use UNIX_TIMESTAMP for date insertion. I added the following to my record insert statement:


UNIX_TIMESTAMP(now()

but I'm getting this error:


Error: Incorrect parameter count in the call to native function 'UNIX_TIMESTAMP'

Lamped
12-01-2010, 09:08 PM
I didn't say it's best to use unix timestamps, but I do. I don't use a special database field for them either, I just use int and do date arithmetic in partly in PHP. I'm not suggesting you should do this. A datetime field instead of varchar might be better for date_of_insert though.

At the bottom, your code isn't showing an echo, so do this:

while($row = mysql_fetch_assoc($result)) {
$total += $row['tax'];
}
echo($total);

mbarandao
12-01-2010, 09:43 PM
echo($total);

simply prints the value of $total=0. presumably my datetime field might be the reason...I'm debugging!

Thanks for the elab on the unix timestamps

Lamped
12-01-2010, 10:23 PM
If you changed it to a datetime field, the code will probably need changing, as the STR_TO_DATE shouldn't be necessary on the date_of_insert fields. You'd also need to ensure it's converted the dates correctly.

If you haven't changed the date_of_insert field type, I tend to pop the query into phpmyadmin and debug it directly in there.

mbarandao
12-01-2010, 10:50 PM
Yes, I did make the change --except to "date" instead of datetime, as time is not necessary for the task.

I also went into phpmyadmin and modified the date manually...and still, no change. I'm still at it!

For the code modification --with the date field set to "date" would I simply remove the STR_TO_DATE from the statement--or something else?

Using the UNIX_TIMESTAMP(now() is still dishing out an error:


Error: Incorrect parameter count in the call to native function 'UNIX_TIMESTAMP'
Any idea as to why?

Here is my new record statement:


$mysql_query=("insert billofservice (date_of_insert,clientID,invoicenum,date,servicearea, servicedesc, cost, qty, price,tax, total,paid, balancedue,laborcost,paymenttype) VALUES (UNIX_TIMESTAMP(now(),'$clientID2','$invoicenum','$date','$servicearea', '$servicedesc', '$cost', '$qty', '$price','$tax','$total','$paid', '$due','$laborcost', '$paymenttype'))");

Lamped
12-01-2010, 11:04 PM
Just use NOW(), forget about UNIX_TIMESTAMP(). The reason for the error, btw, is you didn't close the parenthesis properly.

If you're storing it as a date field, you don't need to use STR_TO_DATE(`date_of_insert`...) because it's already a date. You need to use STR_TO_DATE(yourphpvar...) to convert it into the same date format.

Lamped
12-01-2010, 11:08 PM
If you still can't get it working, I'll stop giving you vague and poor advice. Give me a dump of the table from phpmyadmin, the 2 dates you're checking between and I'll debug it.

mbarandao
12-02-2010, 05:13 AM
Lamped, thanks for the continued attempted to help me resolve my issue. I have exported a dump file ---here it is:



-- phpMyAdmin SQL Dump
-- version 3.3.3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 02, 2010 at 12:00 AM
-- Server version: 5.1.48
-- PHP Version: 5.2.13

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `shop`
--

-- --------------------------------------------------------

--
-- Table structure for table `billofservice`
--

CREATE TABLE IF NOT EXISTS `billofservice` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date_of_insert` date NOT NULL,
`invoicenum` varchar(15) NOT NULL DEFAULT '',
`servicedesc` varchar(255) NOT NULL DEFAULT '',
`clientID` varchar(15) NOT NULL,
`date` varchar(25) NOT NULL,
`servicearea` varchar(255) NOT NULL DEFAULT '',
`cost` varchar(15) NOT NULL,
`qty` varchar(3) NOT NULL DEFAULT '',
`price` varchar(3) NOT NULL,
`tax` varchar(10) NOT NULL,
`total` varchar(10) NOT NULL,
`laborcost` varchar(10) NOT NULL,
`paid` varchar(10) NOT NULL,
`paymenttype` varchar(20) NOT NULL,
`Balancedue` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=37 ;

--
-- Dumping data for table `billofservice`
--

INSERT INTO `billofservice` (`id`, `date_of_insert`, `invoicenum`, `servicedesc`, `clientID`, `date`, `servicearea`, `cost`, `qty`, `price`, `tax`, `total`, `laborcost`, `paid`, `paymenttype`, `Balancedue`) VALUES
(14, '2010-11-09', '16253', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', '1234dc', 'November 18, 2010', 'Suspension', '$234', '1', '', '$42.30', '$747.30', '', '$500', '', '$247.30'),
(15, '2010-11-11', '18719', 'Information updated\r\nOil changed\r\nbrakes installed', 'dg9642', 'November 18, 2010', 'Transmission', '$123', '3', '', '$31.14', '$550.14', '', '$232', '', '$318.14'),
(16, '2010-11-02', '12451', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', 'mossa01', 'November 18, 2010', 'Transmission', '$324', '1', '', '$43.20', '$763.20', '', '$213', '', '$550.20'),
(17, '2010-11-01', '1347', 'New brakes installed\r\nOil Change Performed', 'mossa01', 'November 19, 2010', 'Other', '$234', '1', '', '$18.54', '$327.54', '', '$327.54', '', '$0.00'),
(18, '2010-10-13', '25486', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', 'md66894', 'November 19, 2010', 'Steering', '$123', '3', '', '$0.00', '$519.00', '', '$0.00', '', '$519.00'),
(19, '2010-10-05', '25194', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', 'md66894', 'November 19, 2010', 'Steering', '$234', '1', '', '$30.30', '$535.30', '$225.00', '$535.30', '', '$0.00'),
(20, '2010-11-20', '22442', 'Oil change', 'mossa01', 'November 19, 2010', 'Suspension', '$34', '1', '', '$3.84', '$67.84', '$30.00', '$67.84', 'Cash', '$0.00'),
(21, '2010-11-12', '16726', 'test', 'md66894', 'November 22, 2010', 'Transmission', '$455', '4', '', '$118.20', '$2088.20', '$150.00', '$0.00', 'Select One', '$2088.20'),
(22, '2010-10-09', '27128', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', 'mossa01', 'November 22, 2010', 'Suspension', '$32', '3', '', '$10.26', '$181.26', '$75.00', '$100', 'Select One', '$81.26'),
(23, '2010-11-15', '28389', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', 'mossa01', 'November 22, 2010', 'Transmission', '$32', '1', '', '$0.00', '$182.00', '$150.00', '$0.00', 'Select One', '$182.00'),
(24, '2010-11-10', '4993', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', '1111va', 'November 23, 2010', 'Transmission', '$343', '1', '', '$38.58', '$681.58', '$300.00', '$324', 'Select One', '$357.58'),
(25, '2010-11-26', '20821', 'information update', 'mossa01', 'November 23, 2010', 'Steering', '$435', '3', '', '$91.80', '$1621.80', '$225.00', '$0.00', 'Select One', '$1621.80'),
(26, '2010-11-15', '24231', '4Click here to add details of today\\\\\\\\\\\\\\''s service4', 'mossa01', 'November 22, 2010', 'Suspension', '$0.00', '1', '', '$0.00', '$0.00', '$0.00', '$0.00', 'Select One', '$0.00'),
(27, '2010-10-13', '2396', 'Click here to add details of today\\\\\\\\\\\\\\''s servicet', 'mossa01', 'November 22, 2010', 'Suspension', '$0.00', '1', '', '$0.00', '$0.00', '$0.00', '$0.00', 'Select One', '$0.00'),
(28, '2010-12-24', '9535', 'Information update', 'dg9642', 'November 24, 2010', 'Steering', '$324', '2', '', '$56.88', '$1004.88', '$300.00', '$1234', 'Discover', '$-229.12'),
(29, '2010-10-08', '30526', 'Information update\r\nOil change\r\nNew brakes', '7436gd', 'November 24, 2010', 'Other', '$34', '2', '', '$8.58', '$151.58', '$75.00', '$151.58', 'Cash', '$0.00'),
(30, '2010-11-14', '25553', 'Oil change\r\nBrakes\r\nNew Transmission', 'mossa01', 'November 24, 2010', 'Multiple', '$4526', '1', '', '$304.32', '$5376.32', '$300.00', '$3049', 'Check', '$2327.32'),
(31, '2010-10-23', '18266', 'We are performing an oil change\r\nChanging new brakes\r\nNew tires', 'md66894', 'November 25, 2010', 'Brake', '$232', '3', '', '$50.76', '$896.76', '$150.00', '$0.00', 'Cash', '$896.76'),
(32, '2010-11-25', '15818', 'information update', 'md66894', 'November 29, 2010', 'Engine Mechanical', '$223', '3', '', '$53.64', '$947.64', '$225.00', '$32', 'AMEX', '$915.64'),
(33, '2010-11-12', '23986', 'Information update', 'md66894', 'November 29, 2010', 'Engine Mechanical', '$352', '3', '', '$81.36', '$1437.36', '$300.00', '$0.00', 'Cash', '$1437.36'),
(34, '2010-10-26', '22756', 'oil change\r\nnew brakes', 'mossa01', 'November 30, 2010', 'Air Intake', '$26', '1', '', '$3.66', '$64.66', '$35.00', '$0.00', 'Cash', '$64.66'),
(35, '2010-12-01', '20240', 'information update', 'md66894', 'December 01, 2010', 'Engine Mechanical', '$324', '3', '', '$67.32', '$1189.32', '$150.00', '$0.00', 'Discover', '$1189.32'),
(36, '2010-12-01', '107', 'information update', 'md66894', 'December 01, 2010', 'Driveshaft ', '$453', '3', '', '$90.54', '$1599.54', '$150.00', '$0.00', 'Discover', '$1599.54');


So essentially, I want to be able to run a query that looks into this table based on two date variables passed from a page (ie: 2010/11/01 and 2010/12/01) and
1.) list(echo) three particular columns (ie: date, invoicenum and tax) ordered by invoicenum
2.) sum up the amounts under tax column and echo total

This statement represents a search for total tax amount collect by the business during a particular time period --in this case the two variables passed via url.

Note: with the rewrite in accordance of the last guidance, I'm getting this error:


Error: Operand should contain 1 column(s)


Google search concerning this error yields nothing substantial...

Lamped
12-02-2010, 11:24 AM
SELECT SUM(SUBSTRING(`tax`,2)) AS `total` FROM `billofservice` WHERE STR_TO_DATE("2010-11-29", "%Y-%m-%d") > `date_of_insert` AND STR_TO_DATE("2010-11-01", "%Y-%m-%d") < `date_of_insert`

This worked. Changes:
1. I changed the dates to include quotes, whoops.

2. I notice your tax field is a varchar and includes the currency symbol ($), I fixed this and made it auto-total for you with SUM(SUBSTRING(`tax`,2)) AS `total` - this strips the currency symbol, adds all the tax fields and renames it to total.

3. Added sanitisation to the PHP version

4. Removed STR_TO_DATE(`date_of_insert`) because date_of_insert is already a date field, and doesn't need converting.

The PHP including sanitisation would be:

$res = mysql_query('SELECT SUM(SUBSTRING(`tax`,2)) AS `total` FROM `billofservice` WHERE STR_TO_DATE("'.mysql_real_escape_string($date1).'", "%Y-%m-%d") > `date_of_insert` AND STR_TO_DATE("'.mysql_real_escape_string($date2).'", "%Y-%m-%d") < `date_of_insert`';
$row = mysql_fetch_assoc($res);
echo($row['total']);

mbarandao
12-02-2010, 01:43 PM
Thanks for the rewrite Lamped. If my understanding is correct, I need not change anything within the db --which I did not. I replaced the statement with the one above --removing completely the one we worked on yesterday. I added a missing closing parenthesis to the rewrite. The following error is produced:



Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Resource id #3' at line 1


The code inside phymyadmin ran as a sql query works as stated!

Lamped
12-02-2010, 02:05 PM
Thanks for the rewrite Lamped. If my understanding is correct, I need not change anything within the db --which I did not. I replaced the statement with the one above --removing completely the one we worked on yesterday. I added a missing closing parenthesis to the rewrite. The following error is produced:



Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Resource id #3' at line 1


The code inside phymyadmin ran as a sql query works as stated!

You've put the variable holding the mysql_connect result into a quoted section, like "$db", or similar. PHP is converting the "resource" into a string. It should be mysql_query('somesql', $db);

mbarandao
12-02-2010, 02:13 PM
this is what I have:



$res=mysql_query ('SELECT SUM(SUBSTRING(`tax`,2)) AS `total` FROM `billofservice` WHERE STR_TO_DATE("'.mysql_real_escape_string($date1).'", "%Y-%m-%d") > `date_of_insert` AND STR_TO_DATE("'.mysql_real_escape_string($date2).'", "%Y-%m-%d") < `date_of_insert`');



Additionally, I just notice something I believe may be a problem. The variable $date1 and $date2 have values of yyyy/mm/dd whereas the date field in db has its value as yyyy-mm-dd. Presumably this may present an issue --is this correct? if so, Can I correct the date field to presents its value as yyyy/mm/dd?

Lamped
12-02-2010, 02:23 PM
this is what I have:



$res=mysql_query ('SELECT SUM(SUBSTRING(`tax`,2)) AS `total` FROM `billofservice` WHERE STR_TO_DATE("'.mysql_real_escape_string($date1).'", "%Y-%m-%d") > `date_of_insert` AND STR_TO_DATE("'.mysql_real_escape_string($date2).'", "%Y-%m-%d") < `date_of_insert`');



Additionally, I just notice something I believe may be a problem. The variable $date1 and $date2 have values of yyyy/mm/dd whereas the date field in db has its value as yyyy-mm-dd. Presumably this may present an issue --is this correct? if so, Can I correct the date field to presents its value as yyyy/mm/dd?

I don't see anything wrong with the line of code you've posted. Re-post the lot so I can make sure something daft isn't happening.

Forget about the date_of_insert values now, those are internal to MySQL now. We should amend the code to work with the yyyy/mm/dd $date format though:



$res=mysql_query ('SELECT SUM(SUBSTRING(`tax`,2)) AS `total` FROM `billofservice` WHERE STR_TO_DATE("'.mysql_real_escape_string($date1).'", "%Y/%m/%d") > `date_of_insert` AND STR_TO_DATE("'.mysql_real_escape_string($date2).'", "%Y/%m/%d") < `date_of_insert`');

mbarandao
12-02-2010, 02:30 PM
<?php
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost","cxxxxxx","xxxxxxxxx"); //(host, username, password)

mysql_select_db("shop") or die("Unable to select database"); //select which database we're using
$date1 = $_REQUEST['theDate'];
$date2 = $_REQUEST['currentdate'];
echo"<br/>";
echo "Total Sale Taxes Collected For the Following Period";
echo "<br/>";
echo "From $date1"; echo" To $date2";
echo"<br/>";
echo"<br/>";

$res=mysql_query ('SELECT SUM(SUBSTRING(`tax`,2)) AS `total` FROM `billofservice` WHERE STR_TO_DATE("'.mysql_real_escape_string($date1).'", "%Y/%m/%d") > `date_of_insert` AND STR_TO_DATE("'.mysql_real_escape_string($date2).'", "%Y/%m/%d") < `date_of_insert`');
$result=mysql_query($res);
if(!$result){die("Error: ".mysql_error());
}
//$total=0;
$row = mysql_fetch_assoc($res);
echo($row['total']);
//}
//echo($total);
//$result = mysql_query("SELECT Count(*) as numrecords FROM billofservice WHERE date_of_insert > '" . $date1 . "' AND date_of_insert < '" . $date2 . "'");
//now we get number of rows:
//while ($row = mysql_fetch_assoc($result)) {
//echo $row["numrecords"];
//}
//mysql_free_result($result);

?>

Lamped
12-02-2010, 02:39 PM
I don't see how that error could have come from that code. I've looked over it a dozen times now, and I just don't see it.

Is it being cached?

Can someone else see something I'm blind to?

mbarandao
12-02-2010, 02:49 PM
Quite vexing!!! I'm still going through it...I've cleared all caches and used other browsers even used other machines to test

Lamped
12-02-2010, 03:02 PM
The worst thing about support through a forum like this, is trying to debug remotely. Since I have your database on my local mysql server, I'll debug the code myself and get back to you soon-ish(tm).

mbarandao
12-02-2010, 03:10 PM
Very well! I'll continue to hunt for the bug on my end as well...Thanks for all your help--very gentleman of you!

Lamped
12-02-2010, 03:16 PM
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost","cxxxxxx","xxxxxxxxx"); //(host, username, password)

mysql_select_db("shop") or die("Unable to select database"); //select which database we're using

$from = $_REQUEST['theDate'];
$to = $_REQUEST['currentdate'];
echo("
<br/>
Total Sale Taxes Collected For the Following Period
<br/>
From $from To $to
<br/>
<br/>
");

$res=mysql_query('SELECT SUM(SUBSTRING(`tax`,2)) AS `total` FROM `billofservice` WHERE STR_TO_DATE("'.mysql_real_escape_string($from).'", "%Y/%m/%d") < `date_of_insert` AND STR_TO_DATE("'.mysql_real_escape_string($to).'", "%Y/%m/%d") > `date_of_insert`');

if(!$res){
die("Error: ".mysql_error());
}

$row = mysql_fetch_assoc($res);
echo($row['total']);

Fixed and tidied up a bit.

mbarandao
12-02-2010, 03:26 PM
Great News! At first test, receiving positive results.
Good stuff, my man!
..care to explain what was causing the issue?

Lamped
12-02-2010, 03:44 PM
$result=mysql_query($res);

should not have been there - it's awkward to explain. mysql_query saves the details as a resource, and you used the resource to run a query, when it should have been a string. In either case, that line wasn't necessary.

I got the comparison operators (< and >) the wrong way round, because I'm just that smart.

I think that was it.

mbarandao
12-02-2010, 04:17 PM
Good to know! Again thank you very much for your time and expert knowledge in this area.

The very best,
Mossa



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum