select the first three consecutive months

newphpcoder · 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-05

2012-04-08

2012-05-01

2012-05-05

2012-05-08

2012-06-01

2012-06-05

2012-06-08

2012-07-01

2012-07-05

2012-07-08

2012-08-01

2012-08-05

2012-08-08

Now from this ETD data:

I need to select only are:

(from April - June)

2012-04-01

2012-04-05

2012-04-08

2012-05-01

2012-05-05

2012-05-08

2012-06-01

2012-06-05

2012-06-08

Thank you

requinix · 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).

newphpcoder · April 27, 2012

Without variable to check..I mean is their a mysql syntax

Thank you

requinix · April 27, 2012

Yes. There are two. I posted an EXACT usage of one and a big hint for using the other.

newphpcoder · 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

requinix · 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?

newphpcoder · April 27, 2012

Thats my question how does it possible?without variable?

Thank you

requinix · April 27, 2012

If you don't have any variables then how do you know which three months you want?

newphpcoder · April 27, 2012

So you mean it's not possible?

Thank you

requinix · 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.

newphpcoder · 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

requinix · April 27, 2012

Okay, so you're using MONTH()... Is there a problem?

newphpcoder · 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

requinix · April 27, 2012

Then for lack of a better understanding of what you're trying to say, no it's not possible.

newphpcoder · 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

Sign In

select the first three consecutive months

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information