Hi all:
I am running a SELECT query. This query requires SQL_CALC_FOUND_ROWS. There is a WHILE loop generated by this query that is building a table/rows, etc.
The problem is, within this WHILE loop I need to run an additional SELECT query. And by doing this, it is breaking SQL_CALC_FOUND_ROWS (it give a "0" answer").
After Googling this, it appears that I need to lock the initial query to let it run first, then run the inner query. But I am spinning my wheels getting it to work (assuming this is even correct).
Here is some edited code:
mysqli_query($link, "LOCK TABLES SHL_Events
INNER JOIN SHL_Players ON SHL_Events.plID = SHL_Players.plID
LEFT JOIN SHL_PlayerDetails ON SHL_Events.plID = SHL_PlayerDetails.plID
INNER JOIN SHL_Promoters ON SHL_Events.prID = SHL_Promoters.prID
INNER JOIN SHL_Sports ON SHL_Players.sportCode = SHL_Sports.sportCode
INNER JOIN SHL_Locations ON SHL_Events.loID = SHL_Locations.loID READ;");
if ($stmt = mysqli_prepare($link, "
SELECT
SQL_CALC_FOUND_ROWS
SHL_Players.player,
sport,
canceled,
eventDate,
startTime,
endTime,
SHL_Locations.location,
address,
city,
state,
phone,
zip,
advanceTix,
payPalEmail,
SHL_Promoters.promoter,
advanceTixWebLink,
mailOrder,
mailOrderWebLink,
promoterPhone,
promoterEmail,
listingType,
promoterWebSite,
tipAdditional,
tip,
notes,
SHL_Promoters.discountAmount,
blockDiscount,
SHL_Locations.loID,
SHL_Promoters.prID,
SHL_Locations.location2,
itemsDue,
startDate,
admissionID,
evID,
twitter,
facebook
FROM
SHL_Events
INNER JOIN SHL_Players ON SHL_Events.plID = SHL_Players.plID
LEFT JOIN SHL_PlayerDetails ON SHL_Events.plID = SHL_PlayerDetails.plID
INNER JOIN SHL_Promoters ON SHL_Events.prID = SHL_Promoters.prID
INNER JOIN SHL_Sports ON SHL_Players.sportCode = SHL_Sports.sportCode
INNER JOIN SHL_Locations ON SHL_Events.loID = SHL_Locations.loID
WHERE
".$vSearch." AND
eventDate >= CURDATE() AND
SHL_Events.hold = 'N'
GROUP BY
SHL_EventsID
ORDER BY
".$vOrderBy."
LIMIT
".(($pagination->get_page() - 1) * $records_per_page).", ".$records_per_page."
")) {
mysqli_stmt_bind_param($stmt, "sssssssssssssssssss",
$aPlayer,
$aState1,
$aState2,
$aState3,
$aEventDate,
$aSport,
$aPromoter,
$aPrID,
$aPlID,
$aNotes,
$aListingDate,
$aEvID,
$aDiscountAmount,
$aUpcoming,
$aTeam,
$aCollege,
$aAward,
$aEventMonth,
$aMailOrder
);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt,
$rPlayer,
$rSport,
$rCanceled,
$rEventDate,
$rStartTime,
$rEndTime,
$rLocation,
$rAddress,
$rCity,
$rState,
$rPhone,
$rZip,
$rAdvanceTix,
$rPayPalEmail,
$rPromoter,
$rAdvanceTixWebLink,
$rMailOrder,
$rMailOrderWebLink,
$rPromoterPhone,
$rPromoterEmail,
$rListingType,
$rPromoterWebSite,
$rTipAdditional,
$rTip,
$rNotes,
$rDiscountAmount,
$rBlockDiscount,
$rLoID,
$rPrID,
$rLocation2,
$rItemsDue,
$rStartDate,
$rAdmissionID,
$rEvID,
$rTwitter,
$rFacebook
);
?>
<tbody id="tbodyresults">
<?php
mysqli_stmt_store_result($stmt);//This is needed for a row count
$row_cnt = mysqli_stmt_num_rows($stmt);
if ($row_cnt==0){
echo "<tr><td class='color-box' style='text-align:center;' colspan = '9'>No results for that request. Please <a href='delSearch.php'>try another search</a>.</td></tr>";
} else {
while (mysqli_stmt_fetch($stmt)) {
//printf('<pre>%s</pre>', print_r($row, 1));
// Set SUPER listing style
switch ($rListingType){
case "S":
$vSuperBorder = "style ='border-top: 2px solid #F93; border-bottom: 2px solid #F93; background-color:#D8F8D8;'";
$vRedBall = "<img border='0' src='/img/shl/redballblinkw.gif' width='10' height='10' alt='red ball' />";
break;
default:
$vSuperBorder = "";
$vRedBall = "";
}
$vToday10 = strtotime('+10 day', strtotime($vToday));
$vToday10 = date('Y-m-j', $vToday10 );
$vEventDateDate = date_format(date_create($rEventDate),"Y-m-d");
if (($vToday < $vEventDateDate && $rListingType == "FR") || ($vToday10 < $vEventDateDate && $rListingType == "B")) { $vLimit = "*"; } else { $vLimit = "";}
?>
<tr>
<!--Arrow and *-->
<td class="tdresults" <?php echo $vSuperBorder; ?>>
<?php echo $vRedBall; ?>
<span class="redletter" style="font-weight:bold; font-size:18px"><?php echo $vLimit; ?></span>
</td>
<!--Athlete name/Sport-->
<td class="tdresults" <?php echo $vSuperBorder; ?>>
<span style="color:green; font-weight:bold; text-align:left;"><?php echo $rPlayer; ?></span>
<br />
<?php
echo $rSport;
if ($rNotes == "Canceled"){
echo "<br /><font class='redletter'>Canceled</font>";
} elseif ($rNotes == "Postponed. Awaiting new date."){
echo "<br /><font class='redletter'>Postponed</font>";
}
// Reminder section
if ($rDiscountAmount > 0 && ($rBlockDiscount <> "Y")){
$vDiscounted = "Y";
} else {
$vDiscounted = "N";
}
$vEventDateString = date_format(date_create($rEventDate),"Y-m-d");
if ($vDiscounted == "Y"){
$vLastReminderDate = strtotime('+3 day', strtotime(date('Y-m-j')));
} else {
$vLastReminderDate = strtotime('+14 day', strtotime(date('Y-m-j')));
}
$vLastReminderDate = date('Y-m-j', $vLastReminderDate);
$query314 = "SELECT meID, days, evID FROM SHL_Reminders314 WHERE evID = '".$rEvID."' AND meID = '".$slcustom30."'";
$result314 = mysqli_query($link, $query314);
$row314 = mysqli_fetch_array($result314, MYSQLI_ASSOC);
if (($row314["meID"] == $slcustom30) && ($row314["evID"] == $rEvID)){
$vSetReminder = "Y";
} else {
$vSetReminder = "N";
}
if ($vSetReminder == "Y"){
echo "<br /><span style='color:green; font-style:italic;'>Reminder Set!</span>";
} else {
if (empty($row314["canceled"]) && $vEventDateString > $vLastReminderDate){
echo "<br /><a class='Reminder_ColorBox' style='font-style:italic;' href='delReminderCB.php?discounted=".$vDiscounted."&evID=".$rEvID."'>Reminder</a>";
}
}
mysqli_free_result($result314);
// End reminder section
?>
</td>
</tr>
<?php
} // Close while (mysqli_stmt_fetch
} // Close row count
?>
</tbody>
</table>
<?php
mysqli_stmt_close($stmt);
mysqli_query($link, "UNLOCK TABLES;");
As mentioned this is edited code but you will see the LOCK, the main query, the "inner query" and the UNLOCK.
Any help or ideas appreciated.
Happy holidays!
