Search query skips xx/08 & xx/09 data

[indiantown]

Hello All,

I have a simple query running from which I display on my page expiring coupons in ASC order,

$get_cats = "SELECT item_id, item_title, item_desc, item_subdesc, store_image, item_image, item_code, item_exp, dept_id FROM `store_item` WHERE `item_exp` LIKE '200%,%,%' ORDER BY `item_exp` ASC LIMIT 0, 25";

You can view the display page at

http://www.indian-town.com/coupon/s...ns/expiring.php

What I am facing problems is that the query does not displays the data for ex, 2005,05,08 & 2005,05,09 and as a matter of fact any data for the days xx/08 & xx/09.

When I change the query for ORDER BY 'item_id' then the search result included the 05,08 & 09 data.

I am lost.

Please help me with suggestions where I am missing.

 

[DRJ478]

Are you sure you wrote out the right thing?
How could it include xx/08 and xx/09 if your query uses xx,08 and xx,09 ?
The table is probably set up in an unintuitive way. Optimize it and make the field containing the date a column of date type. A simple query with the date type is much more efficient.

 

[indiantown]

Thanks for the reply......... I am enclsoing few raw data examples,

1. When I do a search with item_id all the data shows....... with xx/08 & xx/09

There is a java script which converts say for example 2005,5,8 to 2005/5/8.

SQL-query:
SELECT *
FROM `store_item`
WHERE `item_exp` LIKE '200%,%,%'
ORDER BY `item_id` ASC
LIMIT 0 , 30

Result
item_id item_title item_price item_desc item_subdesc item_code item_exp unknown_exp item_image dept_id merchant_id deal_coupon coupon_id sort_id store_image time_stamp

1031 Mother's Day Jewelry Sale NULL <a target="_blank" href="

http://click.linksynergy...

NULL None 2005,5,8 NULL

http://www.limogesjewelry.com/linkshare/mothersday...

53 NULL deal NULL jewelry limogesjewelry.gif 20050429222831

But when I do a query with item_exp... the data don't show and skip xx/08 & xx/09

SQL-query:
SELECT *
FROM `store_item`
WHERE `item_exp` LIKE '200%,%,%'
ORDER BY `item_exp` ASC
LIMIT 0 , 30

Result

2633 Free Shipping at Flowerstore.com NULL <a
href="

http://click.linksynergy.com/fs-bin/cli...

NULL None 2005,05,10 NULL

http://www.giftbaskets.com/GiftBaskets/Banners/FS_...

270 NULL deal NULL gifts flowerstore.gif 20050405170534

Appreciate if someone can also give a look and let me know how to solve the mystery.

Thanks in advance.

 

[degroat]

Why are your dates in your database with that format?

 

[DRJ478]

Both of the SQL statements have LIMIT clauses.
If there are more than 30 records a different subset will be shown. Are you aware of that?

 

[indiantown]

Thnaks for everyone trying to help me out:

Reply to degroat:

When I input the dates I input that as 2005,5,8. There is a javascript which does the calculation for the difference and displays in the format 05/08. You can view examples at:

http://indian-town.com/coupon/stores/coupons/expiring.php

The javascript is for:

1. Showing how many days are left
2. Shows tha date of expire
3. Shows "new" till expire date
4. After the date of expire would show that the coupon has expired.

example:

Reward your favorite Mom with a GENUINE DIAMOND TENNIS BRACELET & EARRINGS.
Reward your favorite Mom Sweepstakes
A $140 value! A new winner is chosen each week.
Coupon Code: None Expires: 05/12 ( 1 days left )

Reply to DRJ478:

The query I have donr using phpmyadmin and there where the default is 30 and is not to do anything with my normal query.

$get_cats = "SELECT item_id, item_title, item_desc, item_subdesc, store_image, item_image, item_code, item_exp, dept_id FROM `store_item` WHERE `item_exp` LIKE '200%,%,%' ORDER BY `item_exp` ASC LIMIT 0, 25";

where I am showing 25 on one page.

Once again thanks for the help in advance and hope to get some help to resolve.

regards

 

[DRJ478]

There is in my mind no conceivable way that a change in the ORDER clause makes a query return a different number of records.
Proof:
Execute the following 2 statements

SELECT count(*) FROM store_item WHERE item_exp LIKE '200%,%,%' ORDER BY `item_exp` ASC
**************
SELECT count(*) FROM store_item WHERE item_exp LIKE '200%,%,%' ORDER BY `item_id` ASC

Both will yield the same number of records. If not, let me know.

 

[indiantown]

DRJ 478 both show two different results:

SELECT count(*) FROM store_item WHERE item_exp LIKE '200%,%,%' ORDER BY `item_exp` ASC

list the items with expiry dates

example which I exported from database:

http://www.indian-town.com/forum/itemexp.htm

whereas

SELECT count(*) FROM store_item WHERE item_exp LIKE '200%,%,%' ORDER BY `item_id` ASC

list the items with id nos. as they were entered in the database.

example:

http://www.indian-town.com/forum/itemid.htm

 

[DRJ478]

I think you are so frustrated by now that you don't follow.
It is absolutely clear that the first 30 records ordered by a different column will be not the same set of records just reordered.
The ORDER clause applies to ALL records in the record set. The LIMIT clause returns only the subset of the entire result set.

If you actually had executed the two SQL statements I show above you'd seen that they return exactly the same number of records.

What you might want to do is combine the order and primarily sort by expiration date and then id or vice versa.

But you are looking at a subset of two entirely differently organized data sets.

 

[trollacious]

I've run into problems with '08' and '09', too. The problem is that PHP, as most C-derived languages (including Javascript) do, sees any number starting with a zero as octal. This means that after 07, the next number is 10. 08 and 09 CANNOT be valid octal numbers. If you use:

intval(number, 10);

you'll avoid this problem. Or you can treat the numbers as strings, which will avoid that problem.

Lee

 

[indiantown]

Lee,

Thanks, atleast now someone understands that I do really have some issue and not frustrated.

Can you please guide me more in depth that how should I proceed, means when u say use: intval(number, 10);, where and how I should use in the code

$get_cats = "SELECT item_id, item_title, item_desc, item_subdesc, store_image, item_image, item_code, item_exp, dept_id FROM `store_item` WHERE `item_exp` LIKE '200%,%,%' ORDER BY `item_exp` ASC LIMIT 0, 25";

I would really appreciate if you could spare some of your valuable time for me to get this resolved.

Thanks in advance.

regards

 

[trollacious]

I remember running into this very problem with dates in Javascript. I ended up there using parseInt() to convert the string to a numeric value.

I know almost nothing about SQL, but this section in your code

WHERE `item_exp` LIKE '200%,%,%'

is what checks for those numbers. Is there a way to force the query to read that data as a string rather than numbers? Once you do that, then you can use the PHP conversion function where ever you need.

I'm guessing that the percent signs are what reads the data as numbers, so you should change those to whatever character signifies reading data as strings instead.

What have you written to process the dates after obtaining the records? That might be causing the problem, too, if you're doing any calculations before displaying the data.

Lee

 

[DRJ478]

indiantown
It appears to me tou have been unable or unwilling to provide any feedback on the suggested tests. <wondering>Really, how do you expect TT to help you when you just take the input and not report anything back?</wondering>
You could at least acknowledge what has been said and let the TT members know that you understand what they are trying to say. The road goes two ways. So, please report something back.

Back to my initial suggestion:
Eliminate the whole issue by changing the table column to a valid date format. Formatting a date with commas is mildly said "odd". Most all common SQL database backends have date types which eliminate your problem.

Then you could also cut out the unreliable client side code that converts the comma separated date into a custom format. PHP has powerful functions to display a date in any imaginable format - you can even get it from MySQL with a query that way.

trollacious
The percent signs in the WHERE clause are wildcard characters and do not return anything. The % sign represents any number of characters, even the NULL char.
However, the octal numbers point is well taken and the OP could avoid all such trouble by just using the column type DATE.

 

[indiantown]

DRJ478,

I tried yesterday late till night but no success.

I will list all the steps how we enter thattill how it get displayed. I may be doing wrong........ so please bear with me and suggest where and how we should make changes.

Step 1.
There is javascript which converts the date entered through mysql database,

script
var montharray=new Array("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
var newimage='<img src="/new.gif">'
var expired='<img src="/exp.gif">'
var today=new Date()
var one_day=1000*60*60*24
var monnum
var daynum
function exp(yr,m,d){
var todayy=today.getYear()
monnum=m
if (m <10) monnum="0"+m
daynum=d
if (d <10) daynum="0"+d
if (todayy < 1000)
todayy+=1900
var todaym=today.getMonth()
var todayd=today.getDate()
var expiredstring=montharray[todaym]+" "+todayd+", "+todayy
var newstring=montharray[m-1]+" "+d+", "+yr
var dispstring=monnum+"/"+daynum
var difference=(Math.round((Date.parse(newstring)-Date.parse(expiredstring))/(24*60*60*1000))*1)
if (difference>=0)
document.write(""+dispstring+""+(newimage)+""+" "+"<small><font color='red' face='Arial'><b>"+"( "+difference+" days left"+" )"+"</b></font></small>")
else if (difference<0)
document.write(""+dispstring+" "+(expired)+"")
}

Step2:
The data 1s entered via phpmyadmin in the database. All the fields are entered and the date is entered in the format for ex. 2005,5,8

Step3:
To display the data we use the php script to display on the page:

script
<?php
//connect to database
$conn = mysql_connect("localhost", "xxxx", "xxxx") or die(mysql_error());
mysql_select_db("xxxx",$conn) or die(mysql_error());

$display_block = "";
$dept_id = $_GET['dept_id'];


//show categories first
$get_cats = "SELECT item_id, item_title, item_desc, item_subdesc, store_image, item_image, item_code, item_exp, dept_id FROM `store_item` WHERE `item_exp` LIKE '200%,%,%' ORDER BY `item_exp` ASC LIMIT 0, 25";
$get_cats_res = mysql_query($get_cats) or die(mysql_error());

if (mysql_num_rows($get_cats_res) < 1) {
$display_block = "<P><em>Sorry, no categories to browse.</em></p>";
} else {
while ($cats = mysql_fetch_array($get_cats_res)) {
$item_id = $cats[item_id];
$item_title = stripslashes($cats[item_title]);
$item_desc = stripslashes($cats[item_desc]);
$item_code = stripslashes($cats[item_code]);
$item_exp = stripslashes($cats[item_exp]);
$item_image = stripslashes($cats[item_image]);
$store_image = stripslashes($cats[store_image]);
$item_subdesc = stripslashes($cats[item_subdesc]);

$display_block .= "<p><img src=\"/coupon/images/smalllogos/$store_image\"><br><img align=right vspace=0 border=0 src=\"$item_image\"><br><strong>$item_title</strong><br>$item_desc<br>$item_subdesc<br>Coupon Code: <b>$item_code</b> &nbsp;&nbsp; Expires:&nbsp;<script type='text/javascript'>exp($item_exp)</script><br><a href=\"/refer.html\"><img src=\"/emailwin.gif\" border=0\">Tell a Friend</a> <a href=\"/index.cgi\"><img src=\"/images/pm.gif\" border=0\">Discuss</a><a href=\"/pricegrabber.com/\"><img src=\"/compare.gif\" border=0\">Compare</a><br></p>";


}
}
?>

<BODY>

<? print $display_block; ?>

</BODY>

May I request that please suggest me step by step that what and where I should make the chnages.

Yes, I am really lost.

regards

 

[DRJ478]

Allright. I have a few questions:
1. Why is it necessary to have the WHERE clause at all? Are there any records that have something else in item_exp?
2. '...there is a javascript which converts date entered throug MySQL...' Why don't you just calculate all that stuff in PHP?

Main suggestion:
Change the column (or add a column) that is real date format. Then there will be no ambiguities. No 2005,5,9 or 2005,05,09, just one format which is treated the same all over.

There is an excellent FAQ faq434-3493 about calculating date differences. Using PHP will calculate even if the visitor has JavaScript turned off.