Ethan_Hunt Posted March 9, 2023 Share Posted March 9, 2023 Hi Mates, the problem that I have is, i want to enter in two text field date and filter the SQL query. after submit the button, i don't receive or see any error after executing! my db is oracle and my sys date format is like : 08.03.23. based on my assumption, something with my date format is not correct in my code. <?php include 'orc_php.php'; $post_at = ""; $post_at_to_date = ""; $queryCondition = ""; if(!empty($_POST["search"]["post_at"])) { $post_at = $_POST["search"]["post_at"]; list($fid,$fim,$fiy) = explode("-",$post_at); $post_at_todate = date('Y-m-d'); if(!empty($_POST["search"]["post_at_to_date"])) { $post_at_to_date = $_POST["search"]["post_at_to_date"]; list($tid,$tim,$tiy) = explode("-",$_POST["search"]["post_at_to_date"]); $post_at_todate = "$tiy-$tim-$tid"; } $queryCondition .= "and auf.aufdzhost BETWEEN '$fiy-$fim-$fid' AND '" . $post_at_todate . "'"; } $sql = "select distinct auf.aufdzhost , auf.aufnr , te.tenam , art.artnr , aup.aupmgist from auf_a_v auf , te_a_v te , artall_v art , aup_a_v aup where auf.aufid = te.aufid and aup.aufid = auf.aufid and art.artid = aup.artid and auf.aufGrpNam like 'NATCAN%' and aup.aupmgist != 0 " . $queryCondition . " ORDER BY AUFDZHOST desc"; $result = oci_parse($conn,$sql); ?> <<html> <head> <title>Filter Date</title> <script src="https://code.jquery.com/jquery-1.9.1.js"></script> <link rel="stylesheet" href="https://code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css"> <style> .table-content{border-top:#CCCCCC 4px solid; width:50%;} .table-content th {padding:5px 20px; background: #F0F0F0;vertical-align:top;} .table-content td {padding:5px 20px; border-bottom: #F0F0F0 1px solid;vertical-align:top;} </style> </head> <body> <div class="demo-content"> <h2 class="title_with_link">Filter Date</h2> <form name="frmSearch" method="post" action=""> <p class="search_input"> <input type="text" placeholder="From Date" id="post_at" name="search[post_at]" value="<?php echo $post_at; ?>" class="input-control" /> <input type="text" placeholder="To Date" id="post_at_to_date" name="search[post_at_to_date]" style="margin-left:10px" value="<?php echo $post_at_to_date; ?>" class="input-control" /> <input type="submit" name="go" value="Search" > </p> <?php if(!empty($result)) { ?> <table class="table-content"> <thead> <tr> <th width="30%"><span>Post Title</span></th> <th width="50%"><span>Description</span></th> <th width="20%"><span>Post Date</span></th> </tr> </thead> <tbody> <?php oci_execute($result); while($row = oci_fetch_array($result)) { ?> <tr> <td><?php echo $row["AUFDZHOST"]; ?></td> <td><?php echo $row["ARTNR"]; ?></td> <td><?php echo $row["TENAM"]; ?></td> </tr> <?php } ?> <tbody> </table> <?php } ?> </form> </div> If I don't use the text field and directly enter the date in my SQL query in PHP codes, then I get the result. But in fact, I need to filter the form input text in web and not in background. thank you for your helps. Ali Quote Link to comment Share on other sites More sharing options...
Barand Posted March 9, 2023 Share Posted March 9, 2023 Dates in formats other than yyyy-mm-dd (or similar) don't play nice when being sorted or compared with other dates. Also ensure that, when using "BETWEEN A AND B" that A is not greater than B. Quote Link to comment Share on other sites More sharing options...
Ethan_Hunt Posted March 9, 2023 Author Share Posted March 9, 2023 I've changed to $post_at_todate = date('yyyy-mm-dd'); but still no result. $post_at_todate = date('yyyy-mm-dd'); Quote Link to comment Share on other sites More sharing options...
Barand Posted March 9, 2023 Share Posted March 9, 2023 If you want current date... $post_at_todate = date('Y-m-d'); Quote Link to comment Share on other sites More sharing options...
kicken Posted March 9, 2023 Share Posted March 9, 2023 Echo out your query so you can see what query is being generated. Quote Link to comment Share on other sites More sharing options...
Ethan_Hunt Posted March 9, 2023 Author Share Posted March 9, 2023 that is first result with echo out: after filtering( I used many different date format but none of it has worked) so basically it this format should work: 09-MAR-23 nothing!! Quote Link to comment Share on other sites More sharing options...
Barand Posted March 9, 2023 Share Posted March 9, 2023 1 hour ago, Ethan_Hunt said: $queryCondition .= "and auf.aufdzhost BETWEEN '$fiy-$fim-$fid' AND '" . $post_at_todate . "'"; If you echo $queryCondition, what do you get? And what does the value in aufdzhost look like? Quote Link to comment Share on other sites More sharing options...
Ethan_Hunt Posted March 9, 2023 Author Share Posted March 9, 2023 so aufdzhost is a date type column in oracle db. my query looks like below when I execute it in SQL Developer. here is NLS: here is how sysdate in SQL Plus looks like: Quote Link to comment Share on other sites More sharing options...
Solution kicken Posted March 9, 2023 Solution Share Posted March 9, 2023 6 minutes ago, Ethan_Hunt said: my query looks like below when I execute it in SQL Developer. Did you get that query from your code by echoing out $sql? echo $sql; $result = oci_parse($conn,$sql); The question is whether the query your generating is what you're expecting it to be, so you will want to echo it and verify it. If you can copy/paste the echo'ed query from the page into sql developer and get results, but get no results in PHP then maybe there is some environment difference or the code for reading the results is problematic. I've not used oracle so cannot say much about it. Quote Link to comment Share on other sites More sharing options...
Ethan_Hunt Posted March 9, 2023 Author Share Posted March 9, 2023 got it:) the correct format is like that: you saved my project!! appreciate you all Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.