Trouble refining queries to MySQL database from PHP

I have an application which I inherited.  The file upload page is horrendously slow, and I am trying to speed it.  In multiple locations there is a select query followed by a loop through each value with an update or insert.  I am going to be trying to combine each of these into a single roundtrip to the server.  Unfortunately my knowledge of SQL, while it is growing, is still not up to this task, so any rewrite on this would be very helpful.  I know it will involve a join but I am still not certain how to join on an insert/update statement.

Thanks,
Geoff
$MTDtoDay1=mysqli_query($OpenDB,"
	select
		DataHistory.CollectorCode as CodeHistory,
		sum(DataHistory.CollectComm) as CommHistory,
		DataNew.CollectorCode as CodeNew,
		DataNew.CollectComm as CommNew
	from
		DataNew,
		DataHistory
	where
		DataNew.CollectorCode=DataHistory.CollectorCode and
		DataHistory.DateNumber>'$DateDayEndofLastMonth'
	group by
		DataHistory.CollectorCode
	order by
		DataHistory.CollectorCode");
 
 
	while ($MTDtoDay = mysqli_fetch_assoc($MTDtoDay1))
	{
		extract($MTDtoDay);
 
		// Subtract current MTD commission figures from DataHistory total
		// to get today net change commission amount
		$CommChange=$CommNew-$CommHistory;
 
	// Update DataNew with net change comm for the day
	mysqli_query($OpenDB,"
		Update
			DataNew
		set
			CollectComm='$CommChange'
		where
			CollectorCode='$CodeNew'");
	}

Open in new window

LVL 10
GeoffSuttonAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
You are welcome.  Just let me know if you have issues.

And, you are on MySQL...I think I was coding this in SQL Server, so try this syntax below but premise is the same, you can join to do the update no problem.
$MTDtoDay1=mysqli_query($OpenDB,"
      update DataNew n 
          inner join (select CollectorCode, sum(CollectComm) as CommHistory from DataHistory where DateNumer > '$DateDayEndofLastMonth' group by CollectorCode) h 
          on n.CollectorCode = h.CollectorCode
      set n.CollectComm = n.CollectComm - h.CommHistory     
      ");

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Hello GeoffSutton,

This one query should work to update the DataNew table as you need if my understanding of current code is correct.

Solution basics:
+Use derived table in from/inner join to have the sum already calculated
+Now join on CollectorCode, so you now have updatable query
+Use aliases to facilitate the update using a join

Just execute this query and will do both steps in one.

Hopefully that helps.

Best regards,

mwvisa1
$MTDtoDay1=mysqli_query($OpenDB,"
      update n
      set n.CollectComm = n.CollectComm - h.CommHistory     
      from
            DataNew n 
			inner join (select CollectorCode, sum(CollectComm) as CommHistory from DataHistory where DateNumer > '$DateDayEndofLastMonth' group by CollectorCode) h 
			on n.CollectorCode = h.CollectorCode
      ");

Open in new window

0
 
GeoffSuttonAuthor Commented:
I see how you are working this:  The update does not have the join, but the select inside the update handles the join.  I have never seen Update From before... I have always worked under Update... Set col=val.  Thank you for showing me this.  I will put it into practice tomorrow morning.  I can see that, if the syntax works (as I am sure it will) even if there are small issues they will merely be matters of tweaking.  This is how to do what I wanted to do.  Thank you.

Geoff
0
 
GeoffSuttonAuthor Commented:
Thanks.  Trying it now.
0
 
Kevin CrossChief Technology OfficerCommented:
Glad that worked!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.