Substring Index

[blackxs]

So, I have this code
$count_me_bal = $db->get_results("SELECT * FROM medexp WHERE staffid= '".$get_staff2->id."' AND SUBSTRING_INDEX( date, '/', -1 ) ='".$_GET[year]."' AND SUBSTRING_INDEX( SUBSTRING_INDEX( date , '/' , 2 ) , '/' , - 1 ) <='".$_GET[month] ."' ");

Somehow the equal(=)is working but the less than or equal (<=) did not working. Please help.

Thanks

[TheMadScientist]

Hi blackxs,

Welcome to WebmasterWorld!

My guess would be it's because you're using MySQL, not PHP and I think you need to use separate statements for each option < OR =.

$count_me_bal = $db->get_results("SELECT * FROM medexp WHERE staffid= '".$get_staff2->id."' AND SUBSTRING_INDEX( date, '/', -1 ) ='".$_GET[year]."' AND (SUBSTRING_INDEX( SUBSTRING_INDEX( date , '/' , 2 ) , '/' , - 1 ) <'".$_GET[month] ."' OR SUBSTRING_INDEX( SUBSTRING_INDEX( date , '/' , 2 ) , '/' , - 1 ) ='".$_GET[month] ."') ");

[blackxs]

hi thanks for replying.

I tried your code but still not working. But will try again searching for answer. Yeah i guess maybe because i'm using Mysql.

[blackxs]

Hi again,
Sorry, but i think your code is working. The only problem is that i get correct and incorrect results and at the same time. It must be my other coding. Will try sort it out. Thanks TheMadScientist!

[rocknbil]

This is the really really hard way to do what I think you're trying to do. :-)

I can gather that you're using a varchar() field to store a date. This is a very bad idea, as you're already discovering. You've only cracked the first "bad egg," there will be many more, dates are an important part of a functioning database app and this is only going to get worse. I know the reasoning behind using the formatted date as varchar, it's just that using the mysql date_format() [dev.mysql.com] function, there's really no reason to. So if you're just building this, you can save a lot of headaches by backing up a step and changing that field type to a valid mysql date format. I'll show you how. Also don't use "date" as a field name, or you'll have to work around that too.

I'm presuming this is a nested query, and that staffid is a numeric field, so quotes on it are not necessary. Look at how easy your job will be:

if (($_GET['year'] > 0) and ($_GET['month'] > 0)) { 
 $year = $_GET['year']; 
 $month = $_GET['month']; 
} 
else { 
 echo "Ha. We just avoided injection via these two variables."; 
 exit; 
} 

// As said, I can tell this is a nested query,
// so after your outer query . . . .
$this_id=$get_staff2->id;
$query = "select * from medexp where staffid=$this_id and extract( [dev.mysql.com]year from sdate)='$year'";
$query .= " and extract( [dev.mysql.com]month from sdate)<='$month'";
$count_me_bal = $db->get_results($query);

(Note the links in the example)

You're probably thinking, no way, not goin' back . . . but trust me, using a date field in this way is going to hound you forever. If it's a new project, it's as easy as

alter table medexp change `date` sdate date not null;

If it has actual data you don't want to lose, also easy.

alter table medexp add sdate date not null;

then write a small script to split up the current data and insert it into the new column, then drop the old column.

$query = "select id,`date` from medexp"; 
$result=mysql_query($query); 
while ($row=mysql_fetch_array($result)) { 
 list($m,$d,$y)=explode('/',$row[1]); 
 $newdate = "$y-$m-$d"; 
 $query = "update medexp set sdate='$newdate' where id=$row[0]"; 
 $r=mysql_query($query); 
} 

To get a slash-syntax date format,

select date_format( [dev.mysql.com]sdate,'%m/%d/%Y') from medexp
--> 01/08/2009

Or, you could keep banging away at the substring workaround . . .

[TheMadScientist]

Hmmmm... I don't do either, so thanks for the info rocknbil.
I always store dates as a unix time stamp, because I can store it numerically and it's consistent with PHP, but I'm not a 'power MySQL user' either. LOL.

[rocknbil]

You can probably still use extract() on a timestamp, you just have to nest a from_unixtime() [dev.mysql.com] inside it. Standard date/time types are just more "legible" when you're looking at raw records.

[TheMadScientist]

Cool. Thanks!

Do you know how standard date/times are for selection speed v timestamp?

One of the reasons I don't use MySQL more is I've done quite a bit of work to make sure my PHP is super fast and don't feel like taking all the time to research speed and efficiency in another lang...

BTW: Sorry for hijacking your thread blackxs. :)

[rocknbil]

No I don't. As mentioned, in the old days, date and datetime types were accessed like a quantified varchar field so they definately were slower in some of the earlier v4 versions. Just always used what worked best.

[TheMadScientist]

Thanks Again!
See you in another thread...