Shoutbox

What I am trying to do, is query 2 tables, and list all  the differences between integer fields, if the difference is > 10. I have been thinking about ways to do this, and havent really come up with one.

What I would really like to do is create something that prints this to a table, using PHP, giving the old record above, and the new changed record below it. Anyone got any ideas?

Are the table definitions the same? As in each column is the same? Are you trying to do it based on rows? Like does TableA.Row1 = TableB.Row1?

The 2 table definitions are exactly the same, one is just an older backup I have.

Im trying to do it per record, I updated the first post a bit with a better description.

My table structure is something like:

quote:

---

CREATE TABLE `upgrades` (
  `steamid` varchar(20) NOT NULL DEFAULT '',
  `vip` tinyint(1) NOT NULL DEFAULT '0',
  `playerupgrade1` int(5) NOT NULL DEFAULT '1',
  `playerupgrade2` int(5) NOT NULL DEFAULT '1',
  `playerupgrade3` int(5) NOT NULL DEFAULT '1',
  `playerupgrade4` int(5) NOT NULL DEFAULT '1',
  `playerupgrade5` int(5) NOT NULL DEFAULT '1',
  `playerupgrade6` int(5) NOT NULL DEFAULT '1',
  `playerupgrade7` int(5) NOT NULL DEFAULT '1',
  `playerupgrade8` int(5) NOT NULL DEFAULT '1',
  `upgradepoints` int(5) NOT NULL DEFAULT '30',
  `zombieupgradepoints` int(5) NOT NULL DEFAULT '10',
  `bonusupgradepoints` int(5) NOT NULL DEFAULT '0',
  `success` tinyint(1) NOT NULL DEFAULT '1',
  `playermodel` varchar(255) NOT NULL DEFAULT ''

---

What I am looking to compare are the playerupgrade1-4 fields from each record if the difference is > 10.

Really crappy way of doing it... not sure if it works but give it a shot.

sql code:

---

SELECT * FROM upgrades A
    INNER JOIN upgrades_backup B ON B.steamid = A.steamid
WHERE (
    A.playerupgrade1 > CAST ( B.playerupgrade1 + 10 AS INT )
    OR A.playerupgrade2 > CAST ( B.playerupgrade2 + 10 AS INT )
    OR A.playerupgrade3 > CAST ( B.playerupgrade3 + 10 AS INT )
    OR A.playerupgrade4 > CAST ( B.playerupgrade4 + 10 AS INT )
    )

---

Just tried it:

quote:

---

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT )
    OR A.playerupgrade2 > ( CAST ( B.playerupgrade2 + 10 AS INT )
    OR' at line 4

---

Running MySQL 5.1, if that makes a difference.

Oops I screwed up

sql code:

---

SELECT * FROM upgrades A
    INNER JOIN upgrades_backup B ON B.steamid = A.steamid
WHERE (
        A.playerupgrade1 > ( B.playerupgrade1 + 10)
        OR A.playerupgrade2 > ( B.playerupgrade2 + 10 )
        OR A.playerupgrade3 > ( B.playerupgrade3 + 10 )
        OR A.playerupgrade4 > ( B.playerupgrade4 + 10 )
    )

---

That seems to work nicely, thanks a lot

Now I just have to wait for it to compare 60,000 records

quote:

---

Originally posted by Jimbo
... list all  the differences between integer fields if the difference is > 10...

---

Meaning you need to check if A is more than 10 bigger than B AND if A is more than 10 smaller than B, correct?. So...

quote:

---

Originally posted by matty
A.playerupgrade1 > ( B.playerupgrade1 + 10)

---

That will not list values in B which are at least 10 bigger than A; it will only list values in B which are at least 10 less than A!

Thus the correct clause would then be:

sql code:

---

...
WHERE (
    ABS(A.playerupgrade1 - B.playerupgrade1) > 10
    OR ABS(A.playerupgrade2 - B.playerupgrade2) > 10
    OR ABS(A.playerupgrade3 - B.playerupgrade3) > 10
    OR ABS(A.playerupgrade4 - B.playerupgrade4) > 10
)

---

ABS() = take the absolute value of the number





------------


EDIT:

quote:

---

Originally posted by Jimbo
Well, the nature of my table, makes it impossible for the record to decrease from B to A

---

Ah... In that case I never said anything

Well, the nature of my table, makes it impossible for the record to decrease from B to A, and all I wanted to do was list values in B that are at least 10 less than A. Maybe I should have worded what I required a bit easier, Matty's query seemed to work fine though.

If you want to speed it up you should use indexs.