0

ich have here an statement from my threat before PHP MySQL How to select 2 specific and 1 common value over 2 tables? and something confuses me, but first the section of important code:

$stmt = $pdo->prepare("SELECT
  table1.spediteur,
  table2.versendet,
  table2.unique_code,
  table1.unique_code
FROM table1
  INNER JOIN table2
    ON table1.unique_code = table2.unique_code
WHERE table1.unique_code = $scanned_number
AND table2.unique_code = $scanned_number
GROUP BY table1.spediteur,
         table2.versendet,
         table2.unique_code,
         table1.unique_code
HAVING table1.spediteur = 'Dachser'
AND table2.versendet = '0000-00-00 00:00:00'
");

$stmt->execute([$scanned_number]);
$result = $stmt->fetch();
if ($result) { 
?> the scanned number matches the given parameter 

<?php 
$sql = "UPDATE table2 SET versendet = date('Y-m-d H:i:s') WHERE unique_code = $scanned_number";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();

} else { 
?> the scanned number don´t matches 
<?php
}?>

This function just look for some similar and existing values. It works on my PC via Xampp PHP Version 7.3.7 / Laptopversion on Xampp is 7.1.11

On my PC the server character set is cp1252 West European (latin1) On my Laptop it is cp1252 West European (latin1) , too. utf8mb4 has the same results. Okay , now the last sequence: AND table2.versendet = '0000-00-00 00:00:00' see, if there's that there.

On my PC this is enough for my Code and if the saved value is '0000-00-00 00:00:00' -> the code update it with current datetime.

If i start this code on my Laptop, the Code saye, no there is nothing with '0000-00-00 00:00:00' and gives me an error message. But if i change that code to AND table2.versendet = date('Y-m-d' H:i:s' = '0000-00-00 00:00:00') it works like on my PC and i absolutly don´t know why.

So, if anyone understand me, please tell: who is my mistake. Thanks in advance :)

kay_device
  • 33
  • 5
  • A GROUP BY clause with no aggregating functions doesn't make much sense, so shall we just start over. – Strawberry Dec 02 '19 at 17:02
  • Please add your mysql version of your xampp servers and also show us the difference between both results.. tp reproduce your problem it helps if you show us a dbfiddle version of your qiery with data – nbk Dec 02 '19 at 17:07
  • `SET versendet = date('Y-m-d H:i:s')` - Are you trying to execute a PHP function inside an SQL query? – Paul Spiegel Dec 02 '19 at 17:48
  • `if ($result)` - Doesn't tell you if any rows have been found. It only tells you if the query has been executed successfully. – Paul Spiegel Dec 02 '19 at 17:49
  • @Paul Spiegel thank you for your asking. I am not an expert, just learn more over trial and error. You helps me, too. The code below from Strawberry works. – kay_device Dec 02 '19 at 18:03

1 Answers1

1

Here's an example of a valid query. While it might not solve all your issues, it is at least a better place to start...

SELECT DISTINCT t1.unique_code -- pointless to select both columns when we know (because we have specified) that both hold the same value
              , t1.spediteur   -- obviously we know that this will be 'Dachser'
              , t2.versendet   -- and this will be '0000-00-00 00:00:00'
                               -- (assuming correspondong rows exist)
           FROM table1 t1
           JOIN table2 t2
             ON t2.unique_code = t1.unique_code
          WHERE t1.unique_code = ?
            AND t1.spediteur = 'Dachser'
            AND t2.versendet = '0000-00-00 00:00:00'
Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • Thank you so much. Code does exactly what i need and yes - works on both Computers. Also i think my mistakes was mixing PHP Code into a SQL Statement and using a Query Editor - because from that Editor i have nothing to learn. This Statement from you looks so easy and i am surprised and respect people like you to sharing this knowledge. I must have a look what this DISTINCT does. I am impressed. Thank you so much and have a nice afternoon. *sohappy* – kay_device Dec 02 '19 at 18:28