Member Avatar for iamjmurphy

Can someone help me please!

I have 2 tables one memberlist and the other results, a user makes a selection and it's entered into the memberlist and saved, the results table in then update (after a race event). I would like to compare the values in both table against each, if the values match in the corresponding columns the user is awarded points and if not points are deducted. I am very new to PHP and would really appreciate the help thanks.

  • 2 Contributors
  • 2 Replies
  • 88 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by iamjmurphy

Dani AI

Generated

Good that worked it out and that asked for table details — the join key (race_id, user_id, or whatever links a pick to a result) really determines the simplest solution. Two reliable patterns are: (A) do the comparison and scoring in SQL with a single UPDATE+JOIN when the rule is simple, or (B) fetch joined rows into PHP and apply more complex logic before updating. The SQL route is faster and atomic; the PHP route is easier to test and extend.

Example: single-statement scoring in MySQL (adapt column names and point values):

UPDATE memberlist m
JOIN results r ON m.race_id = r.race_id
SET m.points = m.points + CASE
  WHEN TRIM(LOWER(m.pick)) = TRIM(LOWER(r.result)) THEN 10
  ELSE -5
END
WHERE r.processed = 1;

Example: PDO workflow for more complex scoring or multi-column comparisons:

$pdo->beginTransaction();
$stmt = $pdo->query("SELECT m.id, m.pick, r.result, m.points
                     FROM memberlist m
                     JOIN results r ON m.race_id = r.race_id
                     WHERE r.processed = 1");
$upd = $pdo->prepare("UPDATE memberlist SET points = :points WHERE id = :id");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  $pick = trim(strtolower($row['pick']));
  $res  = trim(strtolower($row['result']));
  $delta = ($pick === $res) ? 10 : -5;
  $upd->execute([':points' => max(0, $row['points'] + $delta), ':id' => $row['id']]);
}
$pdo->commit();

Practical notes: run a SELECT version first to preview changes; handle NULLs, whitespace and case (TRIM/LOWER or appropriate COLLATE); use indexes on join columns for performance; wrap bulk updates in a transaction and back up data before running mass updates.

Recommended Answers

All 2 Replies

Member Avatar for darkagn

Hi iamjmurphy and welcome to DaniWeb :)

Can you please provide more information of your table structures and which fields you are trying to compare? It seems like it should be a fairly simple task, but I'm unsure exactly what you are trying to achieve.

Cheers
darkagn

Member Avatar for iamjmurphy

Hi iamjmurphy and welcome to DaniWeb :)

Can you please provide more information of your table structures and which fields you are trying to compare? It seems like it should be a fairly simple task, but I'm unsure exactly what you are trying to achieve.

Cheers
darkagn

Hi Darkagn,

Thank you for your reply but I was able to work it out after a bit of thought, I had a few silly mistakes but eventually I got it.
Thanks again.
iamjmurphy.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.