Limit in query not working?

[rocky48]

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

[Psycho]

"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.

[Barand]

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

[rocky48]

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.

[Psycho]

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.