Quote:
Originally Posted by tangoforce
I know you're concerned at the speed of development but I urge you to reconsider doing your updates in one SQL statement. Why?.. Personal experience.
When I first got into PHP / Mysql years ago I wrote the biggest and best code for my site.. but I couldn't figure out why certain pages were so frustratingly slow compared to others. I put it down to my computer (which was running the wamp setup) not being up to the job. Only when I turned on mysqls query log did I realise why it was so slow. Some pages had 200 queries running and it was grinding down the mysql server. I certainly learned a lot about sql optimization that week! That was for one user - me. Imagine that multiplied by a few thousand users and it will crash even the most powerful servers.
|
Okay, Tango, based on my last post - and the need to "break" my code AGAIN to make it run better, I might as well at least take a peak into what you are suggesting?!
If I revert back to my original code which built my Inbox based on the results-set from my SELECT query - and not use an Array - then I have this code in my HTML section...
PHP Code:
// Loop through Messages.
while (mysqli_stmt_fetch($stmt6)){
echo "<tr" . (is_null($readOn) ? " class='pmRead'" : "") . ">
<td class='colSelect'>
<input id='"
. str2htmlentities($pmID)
. "' name=msgArray["
. str2htmlentities($pmID)
. "] type='checkbox' value='TRUE' />
</td>
<td class='colFlag'>"
. (($flag==TRUE) ? '<img src="/images/Flag.png" width="15" alt="" />' : '•') .
"</td>
<td>" . str2htmlentities($fromUsername) . "</td>
<td><a class='msgLink' href='/account/view-pm/incoming/"
. str2htmlentities($pmID)
. "'>"
. str2htmlentities($subject)
. "</a></td>
<td>"
. str2htmlentities($sentOn)
. "</td>\n
</tr>\n";
}
If I select 2 Messages in my Inbox, choose "Mark as Unread" and then press "Go", I get these results in my
msgArray[] array...
Code:
msgArray[6] = TRUE
msgArray[5] = TRUE
In the past, I would update Messages using this code...
PHP Code:
foreach($_POST['msgArray'] as $msgID => $msgValue){
// Build query.
$q1 = "UPDATE private_msg_recipient
SET read_on=NULL,
updated_on=NOW()
WHERE member_id_to=?
AND message_id=?
LIMIT 1";
// Prepare statement.
$stmt1 = mysqli_prepare($dbc, $q1);
// Bind variables to query.
mysqli_stmt_bind_param($stmt1, 'ii', $sessMemberID, $msgID);
// Execute query.
mysqli_stmt_execute($stmt1);
// Verify Update.
if (mysqli_stmt_affected_rows($stmt1)==1){
// Update Succeeded.
$redirectView = 'incoming';
}else{
// Update Failed.
}
Sorry if you guys already told me how to do this, but how would I take all of the values in my
msgArray - which comes from my Form - and convert it into a format so I could do just ONE UPDATE query, versus enumerating through each Array value, and running a separate UPDATE query?
Also, I am curious what everyone thinks about the change I mentioned in my last PM?
(In retrospect, it seems silly to run a query to populate my Inbox initially, them copy all of that data into an Array, then convert it to a format that can be passed in my $_POST array, and then convert it again, and then use it in my UPDATE query?! It would be much to just pass a "Checked"/"Not-Checked" value when the FOrm gets submitted...)
Thanks,
Debbie