newphpcoder Posted April 27, 2012 Share Posted April 27, 2012 Hi.. I need help in selecting first 3 Consecutive Months from my table ETD which type is Date. for example in my ETD: 2012-04-01 2012-04-01 2012-04-05 2012-04-08 2012-04-08 2012-05-01 2012-05-01 2012-05-05 2012-05-08 2012-05-08 2012-06-01 2012-06-01 2012-06-05 2012-06-08 2012-06-08 2012-07-01 2012-07-01 2012-07-05 2012-07-08 2012-07-08 2012-08-01 2012-08-01 2012-08-05 2012-08-08 2012-08-08 Now from this ETD data: I need to select only are: (from April - June) 2012-04-01 2012-04-01 2012-04-05 2012-04-08 2012-04-08 2012-05-01 2012-05-01 2012-05-05 2012-05-08 2012-05-08 2012-06-01 2012-06-01 2012-06-05 2012-06-08 2012-06-08 Thank you Quote Link to comment https://forums.phpfreaks.com/topic/261683-select-the-first-three-consecutive-months/ Share on other sites More sharing options...
requinix Posted April 27, 2012 Share Posted April 27, 2012 WHERE date >= "2012-04-01" AND date Or a nicer looking BETWEEN if you can determine the last day of the third month (eg, 2012-06-30). Quote Link to comment https://forums.phpfreaks.com/topic/261683-select-the-first-three-consecutive-months/#findComment-1340968 Share on other sites More sharing options...
newphpcoder Posted April 27, 2012 Author Share Posted April 27, 2012 Without variable to check..I mean is their a mysql syntax Thank you Quote Link to comment https://forums.phpfreaks.com/topic/261683-select-the-first-three-consecutive-months/#findComment-1340970 Share on other sites More sharing options...
requinix Posted April 27, 2012 Share Posted April 27, 2012 Yes. There are two. I posted an EXACT usage of one and a big hint for using the other. Quote Link to comment https://forums.phpfreaks.com/topic/261683-select-the-first-three-consecutive-months/#findComment-1340976 Share on other sites More sharing options...
newphpcoder Posted April 27, 2012 Author Share Posted April 27, 2012 you mean this: WHERE date >= "2012-04-01" AND date < "2012-07-01" but I don't have variable with contain those data :2012-04-01 and 2012-07-01 I mean mysql syntax for exampe MONTH(ETD) Thank you Quote Link to comment https://forums.phpfreaks.com/topic/261683-select-the-first-three-consecutive-months/#findComment-1340978 Share on other sites More sharing options...
requinix Posted April 27, 2012 Share Posted April 27, 2012 Sure, you can use MONTH(). But if you don't have any variables then what are you going to use it with? Quote Link to comment https://forums.phpfreaks.com/topic/261683-select-the-first-three-consecutive-months/#findComment-1340981 Share on other sites More sharing options...
newphpcoder Posted April 27, 2012 Author Share Posted April 27, 2012 Thats my question how does it possible?without variable? Thank you Quote Link to comment https://forums.phpfreaks.com/topic/261683-select-the-first-three-consecutive-months/#findComment-1340982 Share on other sites More sharing options...
requinix Posted April 27, 2012 Share Posted April 27, 2012 If you don't have any variables then how do you know which three months you want? Quote Link to comment https://forums.phpfreaks.com/topic/261683-select-the-first-three-consecutive-months/#findComment-1340985 Share on other sites More sharing options...
newphpcoder Posted April 27, 2012 Author Share Posted April 27, 2012 So you mean it's not possible? Thank you Quote Link to comment https://forums.phpfreaks.com/topic/261683-select-the-first-three-consecutive-months/#findComment-1340987 Share on other sites More sharing options...
requinix Posted April 27, 2012 Share Posted April 27, 2012 Not as you're describing it. But that's okay because what you're describing is not true. Maybe this will be easier: Please post the code you have now. The stuff into which you're trying to insert this query. Quote Link to comment https://forums.phpfreaks.com/topic/261683-select-the-first-three-consecutive-months/#findComment-1340994 Share on other sites More sharing options...
newphpcoder Posted April 27, 2012 Author Share Posted April 27, 2012 Now this is my code: <?php error_reporting(E_ALL ^ E_NOTICE); date_default_timezone_set("Asia/Singapore"); //set the time zone $con = mysql_connect('localhost', 'root',''); if (!$con) { echo 'failed'; die(); } mysql_select_db("mes", $con); ?> <html> <head> <title>Sales Order</title> <link rel="stylesheet" type="text/css" href="kanban.css" /> <script type="text/javascript"> function upload_so(){ document.salesorder.action="import_salesorder.php"; document.salesorder.submit(); alert("Sales Order Successfully Uploaded."); window.location="SalesOrder.php"; } function disp(){ var FromMonth = document.getElementById('FromMonth').value; var ToMonth = document.getElementById('ToMonth').value; document.salesorder.action="salesorder.php?FromMonth="+FromMonth+"&ToMonth="+ToMonth; document.salesorder.submit(); } </script> </head> <body> <form enctype="multipart/form-data" action="import_salesorder.php" method="post" name="salesorder"> <div id="upload"> <fieldset> <legend>Upload Sales Order</legend> <input type="hidden" name="MAX_FILE_SIZE" value="10000000000" /> <table width="500"> <tr> <td>Sales Order:</td> <td><input type="file" name="file" /></td> <td><input type="button" value="Upload" onclick="upload_so()"/></td> <a href="import_salesorder.php">up</a> </tr> </table> </fieldset> </div> <input type="hidden" value="<?php echo $date_upload; ?>" id="date_upload" name="date_upload"> </div> <div id="From_Month"> <label>From Month:</label> <select name="FromMonth"> <option value="Select">Select</option> <option value="01" <?php if($_POST['FromMonth'] == '01') echo "selected='selected'"; ?>>January</option> <option value="02" <?php if($_POST['FromMonth'] == '02') echo "selected='selected'"; ?>>February</option> <option value="03" <?php if($_POST['FromMonth'] == '03') echo "selected='selected'"; ?>>March</option> <option value="04" <?php if($_POST['FromMonth'] == '04') echo "selected='selected'"; ?>>April</option> <option value="05" <?php if($_POST['FromMonth'] == '05') echo "selected='selected'"; ?>>May</option> <option value="06" <?php if($_POST['FromMonth'] == '06') echo "selected='selected'"; ?>>June</option> <option value="07" <?php if($_POST['FromMonth'] == '07') echo "selected='selected'"; ?>>July</option> <option value="08" <?php if($_POST['FromMonth'] == '08') echo "selected='selected'"; ?>>August</option> <option value="09" <?php if($_POST['FromMonth'] == '09') echo "selected='selected'"; ?>>September</option> <option value="10" <?php if($_POST['FromMonth'] == '10') echo "selected='selected'"; ?>>October</option> <option value="11" <?php if($_POST['FromMonth'] == '11') echo "selected='selected'"; ?>>November</option> <option value="12" <?php if($_POST['FromMonth'] == '12') echo "selected='selected'"; ?>>December</option> </select> <label>To Month:</label> <select name="ToMonth"> <option value="Select">Select</option> <option value="01" <?php if($_POST['ToMonth'] == '01') echo "selected='selected'"; ?>>January</option> <option value="02" <?php if($_POST['ToMonth'] == '02') echo "selected='selected'"; ?>>February</option> <option value="03" <?php if($_POST['ToMonth'] == '03') echo "selected='selected'"; ?>>March</option> <option value="04" <?php if($_POST['ToMonth'] == '04') echo "selected='selected'"; ?>>April</option> <option value="05" <?php if($_POST['ToMonth'] == '05') echo "selected='selected'"; ?>>May</option> <option value="06" <?php if($_POST['ToMonth'] == '06') echo "selected='selected'"; ?>>June</option> <option value="07" <?php if($_POST['ToMonth'] == '07') echo "selected='selected'"; ?>>July</option> <option value="08" <?php if($_POST['ToMonth'] == '08') echo "selected='selected'"; ?>>August</option> <option value="09" <?php if($_POST['ToMonth'] == '09') echo "selected='selected'"; ?>>September</option> <option value="10" <?php if($_POST['ToMonth'] == '10') echo "selected='selected'"; ?>>October</option> <option value="11" <?php if($_POST['ToMonth'] == '11') echo "selected='selected'"; ?>>November</option> <option value="12" <?php if($_POST['ToMonth'] == '12') echo "selected='selected'"; ?>>December</option> </select> <input type="button" name="display" value="display" onclick="disp()" id="display"> </div> <div id="kanban_table"> <?php echo "<table border='.2'>"; echo "<th>ETD</th> <th>PO No.</th> <th>SKU Code</th> <th>Description</th> <th>PO Req</th> <th>Compound</th> <!--<th>Compound Kg</th>--> "; $FromMonth = $_POST['FromMonth']; $ToMonth = $_POST['ToMonth']; $sql = "INSERT INTO so_month (FromMonth, ToMonth) VALUES ('$FromMonth', '$ToMonth') ON DUPLICATE KEY UPDATE FromMonth = '$FromMonth', ToMonth = '$ToMonth'"; $res = mysql_query($sql,$con); $sql = "SELECT ETD, PO_No, SKUCode, Description, POReq, Comp FROM sales_order WHERE MONTH(ETD) BETWEEN '$FromMonth' AND '$ToMonth' ORDER BY ETD, PO_No"; $res = mysql_query($sql, $con); while($row = mysql_fetch_assoc($res)){ $ETD = $row['ETD']; $PO_No = $row['PO_No']; $SKUCode = $row['SKUCode']; $Description = $row['Description']; $POReq = $row['POReq']; $Comp = $row['Comp']; echo "<tr> <td>$ETD</td> <td>$PO_No</td> <td>$SKUCode</td> <td>$Description</td> <td>$POReq</td> <td>$Comp</td> <!--<td>$CompKg</td>--> <!--<td>$Cloth</td>--> </tr>"; } ?> </div> </form> </body> </html> Thank you Quote Link to comment https://forums.phpfreaks.com/topic/261683-select-the-first-three-consecutive-months/#findComment-1341002 Share on other sites More sharing options...
requinix Posted April 27, 2012 Share Posted April 27, 2012 Okay, so you're using MONTH()... Is there a problem? Quote Link to comment https://forums.phpfreaks.com/topic/261683-select-the-first-three-consecutive-months/#findComment-1341006 Share on other sites More sharing options...
newphpcoder Posted April 27, 2012 Author Share Posted April 27, 2012 I just wnat ot know how can I select the first consecutive months without variable to check, I mean whole mysql syntax. Is it possible? Thanks Quote Link to comment https://forums.phpfreaks.com/topic/261683-select-the-first-three-consecutive-months/#findComment-1341007 Share on other sites More sharing options...
requinix Posted April 27, 2012 Share Posted April 27, 2012 Then for lack of a better understanding of what you're trying to say, no it's not possible. Quote Link to comment https://forums.phpfreaks.com/topic/261683-select-the-first-three-consecutive-months/#findComment-1341010 Share on other sites More sharing options...
newphpcoder Posted April 27, 2012 Author Share Posted April 27, 2012 Thank you... I tried this Update Query: \ UPDATE sales_order SET FromMonth = '05', ToMonth = '07' WHERE MONTH(ETD) BETWEEN '05' AND '07' ORDER BY SKUCode; //it did not update table but no error. when i try to select : SELECT ETD FROM sales_order WHERE MONTH(ETD) BETWEEN '05' AND '07'; //it display ETD Why it did not update? Thank you Quote Link to comment https://forums.phpfreaks.com/topic/261683-select-the-first-three-consecutive-months/#findComment-1341014 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.