Jump to content


Photo

Search/Split/Compare


Best Answer Psycho, 28 March 2013 - 11:24 PM

Then convert them to timestamps. MySQL has a ton of date/time functions. Just go read up on them to see what one meets your needs.

 

http://dev.mysql.com...ction_timestamp

 

$sql = "SELECT *
        FROM $db_tb_name
        WHERE `$db_tb_atr_name` LIKE '%$query%'
        AND `6` BETWEEN TIMESTAMP('$mydate1')
                    AND TIMESTAMP('$mydate2')";
Go to the full post


  • Please log in to reply
8 replies to this topic

#1 osyrys14

osyrys14

    Advanced Member

  • Members
  • PipPipPip
  • 42 posts

Posted 28 March 2013 - 10:32 PM

I'm trying to make a search where I pull information out of the column that is date/time (2013-03-29 14:00:00) and have a form search the information by a range provided.  I was helped in another thread and I was incorrect in the procedure I need to do, but this is the code I'm using to select * from the db, but what I need to do is display just the ones between the date chosen on the form that submits the query.  Any and all help is greatly appreciated!

 

$mydate1 = sprintf('%4d-%02d-%02d', 
    intval($_GET['year']), intval($_GET['month']), intval($_GET['day']));
$mydate2 = sprintf('%4d-%02d-%02d', 
    intval($_GET['year2']), intval($_GET['month2']), intval($_GET['day2']));

$sql = "SELECT *
        FROM $db_tb_name
        WHERE `$db_tb_atr_name` LIKE '%$query%'
        AND `6` > '$mydate1' AND  `6` < '$mydate2'";

 

 



#2 Psycho

Psycho

    Advanced Member

  • Gurus
  • 10,715 posts
  • LocationCanada

Posted 28 March 2013 - 10:59 PM

You have a field named '6'? Interesting.

 

$sql = "SELECT *
FROM $db_tb_name
WHERE `$db_tb_atr_name` LIKE '%$query%'
AND `6` BETWEEN '$mydate1' AND '$mydate2'";

The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#3 osyrys14

osyrys14

    Advanced Member

  • Members
  • PipPipPip
  • 42 posts

Posted 28 March 2013 - 11:08 PM

It's a dummy database to get the information functional and I have not gotten header names from the client yet so right now, they are all 1, 2, 3, 4, 5, 6, 7, 8, etc.  :)  

 

That gets me the correct query string, but since the query is set to just a date format, and the table is date/time, I'm still getting no results.  Is there a way to split at the space between the date and time and search that way?  The format in the db is 2013-03-29 14:00:00 and the form/search is only 2013-03-29.

 

Thanks so much for the help!



#4 Psycho

Psycho

    Advanced Member

  • Gurus
  • 10,715 posts
  • LocationCanada

Posted 28 March 2013 - 11:24 PM   Best Answer

Then convert them to timestamps. MySQL has a ton of date/time functions. Just go read up on them to see what one meets your needs.

 

http://dev.mysql.com...ction_timestamp

 

$sql = "SELECT *
        FROM $db_tb_name
        WHERE `$db_tb_atr_name` LIKE '%$query%'
        AND `6` BETWEEN TIMESTAMP('$mydate1')
                    AND TIMESTAMP('$mydate2')";

The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#5 osyrys14

osyrys14

    Advanced Member

  • Members
  • PipPipPip
  • 42 posts

Posted 28 March 2013 - 11:33 PM

I get this as what it's looking for...

 

 

  1. SELECT * FROM cdr WHERE `8` LIKE '%%' AND `6` BETWEEN TIMESTAMP('2012-09-02') AND TIMESTAMP('2012-09-03')

and no search results



#6 Psycho

Psycho

    Advanced Member

  • Gurus
  • 10,715 posts
  • LocationCanada

Posted 28 March 2013 - 11:42 PM

Probably because of this

WHERE `8` LIKE '%%'

 

I tested the process of using TIMESTAMP() for a string formatted as a date against a field that was a datetime - so I know it works.


The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#7 osyrys14

osyrys14

    Advanced Member

  • Members
  • PipPipPip
  • 42 posts

Posted 28 March 2013 - 11:48 PM

I put something in the text box on the search, now it gets this

 

 

  1. SELECT * FROM cdr WHERE `8` LIKE '%EFM%' AND `6` BETWEEN TIMESTAMP('2012-09-02') AND TIMESTAMP('2012-09-03')

It's searching 2 fields, first one (8) is an ID code, the other we've been working on is a time/date stamp.  Is it possible to make them work together?



#8 osyrys14

osyrys14

    Advanced Member

  • Members
  • PipPipPip
  • 42 posts

Posted 28 March 2013 - 11:55 PM

Nevermind, it's working.  



#9 osyrys14

osyrys14

    Advanced Member

  • Members
  • PipPipPip
  • 42 posts

Posted 28 March 2013 - 11:59 PM

Thank you so much for your help!






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com