rocky48 Posted April 27, 2015 Share Posted April 27, 2015 (edited) I have a query that filters the data (based on week numbers) so that it displays the data in Quarters. I have the same logic in two scripts, in one it works, but in the other it does not. The only difference is that the one that does not work it is preceeded by a form with two selections (country & Quarter), whereas the one that works has only one selection (Quarter). This is the query that works: include('connect_visits.php'); doDB7(); $limitStart = $_POST['QUARTER'] - 13; $Visit_data="SELECT Visits15.ID, Visits15.WVisits, Visits15.WCom, WeekNo15.WNo, WeekNo15.WCom FROM Visits15 LEFT JOIN WeekNo15 ON Visits15.ID = WeekNo15.WNo ORDER BY Visits15.ID LIMIT {$limitStart}, 13"; $Visit_data_res = mysqli_query($mysqli, $Visit_data) or die(mysqli_error($mysqli)); $display_block = " <table width=\"20%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\" BGCOLOR=\"white\" > <tr> <th>Week No</th> <th>Week Commencing</th> <th>Visits</th> </tr>"; while ($C_info = mysqli_fetch_array($Visit_data_res)) { $Cid = $C_info['ID']; $Visits = ($C_info['WVisits']); $WeekNo = ($C_info['WCom']); //add to display $display_block .= " <tr> <td width=\"1%\" valign=\"top\">".$Cid."<br/></td> <td width=\"8%\" valign=\"top\">".$WeekNo."<br/></td> <td width=\"5%\" valign=\"top\">".$Visits."<br/></td> </td></tr>"; } mysqli_free_result($Visit_data_res); ?> Here is the query that does not work: <? include('connect_visits.php'); doDB7(); echo $_POST['QUARTER']," ", $_POST['country']; echo $_POST['QUARTER'] - 13; $limitStart = $_POST['QUARTER'] - 13; $Visit_data="SELECT WeekNo15.WNo, WeekNo15.WCom, Countries.Country, ctryvisits15.CVisits FROM ctryvisits15 LEFT JOIN Countries ON ctryvisits15.country = Countries.CID LEFT JOIN WeekNo15 ON ctryvisits15.WNo=WeekNo15.WNo WHERE Countries.CID = '{$_POST['country']}' ORDER BY ctryvisits15.WNo LIMIT {$limitStart}, 13"; $Visit_data_res = mysqli_query($mysqli, $Visit_data) or die(mysqli_error($mysqli)); $display_block = " <table width=\"20%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\" BGCOLOR=\"white\" > <tr> <th>Week No</th> <th>Week Commencing</th> <th>Visits</th> </tr>"; while ($C_info = mysqli_fetch_array($Visit_data_res)) { $Cid = $C_info['WNo']; $Visits = ($C_info['CVisits']); $WeekNo = ($C_info['WCom']); //add to display $display_block .= " <tr> <td width=\"1%\" valign=\"top\">".$Cid."<br/></td> <td width=\"8%\" valign=\"top\">".$WeekNo."<br/></td> <td width=\"5%\" valign=\"top\">".$Visits."<br/></td> </td></tr>"; } mysqli_free_result($Visit_data_res); $CNo2Cty ="SELECT CID, Country FROM Countries WHERE CID='{$_POST['country']}'"; $CNo2Cty_res =mysqli_query($mysqli, $CNo2Cty) or die(mysqli_error($mysqli)); if (mysqli_num_rows($CNo2Cty_res)<1){ $display_block="<p><em>Invalid Country, Please try again</em></p>"; } while ($C_name = mysqli_fetch_array($CNo2Cty_res)) { $Country = ($C_name['Country']); } mysqli_free_result($CNo2Cty_res); mysqli_close($mysqli); ?> I added a couple of echos to see if the POST's where working and the $limitstart was being calculated OK. Which it was! It would appear that the LIMIT clause is not working in the incorrect script, as the list for the first quarter shows weeks greater than 13, when the 1st Quarter is selected and nothing when 2nd Quarter is selected. I am using the LIMIT offset, count parameters, which for quarter 2 will be LIMIT 13, 13, that is display data from row 14 and display 13 rows (NB. rows number starts at 0). Can anyone explain why this query is not working for the script with 2 POST's? Edited April 27, 2015 by rocky48 Quote Link to comment Share on other sites More sharing options...
Psycho Posted April 27, 2015 Share Posted April 27, 2015 "Not working" is not helpful to solve your problem. You do state . . . as the list for the first quarter shows weeks greater than 13, when the 1st Quarter is selected and nothing when 2nd Quarter is selected. I am using the LIMIT offset, count parameters, which for quarter 2 will be LIMIT 13, 13, that is display data from row 14 and display 13 rows But, that doesn't make sense to me. A LIMIT has nothing to do with what row a record is in the database. It has to do with the order of the rows in the result set. One way to determine if a LIMIT is working correctly is to run the query without a limit and see what is returned. It makes no sense to use limit to determine the records for a quarter - that should be determined by a date value in the query. Quote Link to comment Share on other sites More sharing options...
Barand Posted April 27, 2015 Share Posted April 27, 2015 If "Wno" in your table is the week number then use "... WHERE Wno BETWEEN 1 AND 13" in your query to get the first quarter and not rely on LIMIT Quote Link to comment Share on other sites More sharing options...
Solution rocky48 Posted May 13, 2015 Author Solution Share Posted May 13, 2015 Must have been on the juice the day I wrote the script. Firtstly I was sorting on the ID and not the Week No (WNo). Also the script I wrote to upload the stats was not writing to the Week No (WCom), which was badly named as it stored the week No. These are the changes I made to the incorrect script: $limitStart = $_POST['QUARTER'] - 13; $Visit_data="SELECT Visits15.ID, Visits15.WVisits, Visits15.WCom, WeekNo15.WNo, WeekNo15.WCom FROM Visits15 LEFT JOIN WeekNo15 ON Visits15.WCom = WeekNo15.WNo <<< Changed here ORDER BY Visits15.WCom <<< Changed here LIMIT {$limitStart}, 13"; $Visit_data_res = mysqli_query($mysqli, $Visit_data) or die(mysqli_error($mysqli)); $display_block = " <table width=\"20%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\" BGCOLOR=\"white\" > <tr> <th>Week No</th> <th>Week Commencing</th> <th>Visits</th> </tr>"; while ($C_info = mysqli_fetch_array($Visit_data_res)) { $Cid = $C_info['WNo']; <<< Changed here $Visits = ($C_info['WVisits']); $WeekNo = ($C_info['WCom']); Psycho - I take your point that limit does only work on the results set, but if you keep the data tidy it should not cause a problem. I found this out when I inadvertantly put the same data in twice. Barand - Thanks for the tip. I did not change it in this case, but I'll remember that in the future. Quote Link to comment Share on other sites More sharing options...
Psycho Posted May 13, 2015 Share Posted May 13, 2015 Psycho - I take your point that limit does only work on the results set, but if you keep the data tidy it should not cause a problem. I found this out when I inadvertantly put the same data in twice. If you are determining the records to display for a quarter based on the order they exist in a table, then it is the wrong approach. There should be no need to keep data "tidy" for it to work. The data is the data - is the data. You should not need to manipulate the data in order to retrieve the results correctly. That just adds overhead. I should be able to add a new record to a table for the beginning of the year and my query to return the 1st quarter results will include it - even if it was added after or between records for April or May. 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.