2

i have here two tables (only the important columns listed) :

table1: unique_code(INT), spediteur(VARCHAR also Names),

table2: unique_code(INT), versendet(timestamp)

I have here a SELECT but that don't work and if the value is true - that means: just let me into the IF WHEN: spediteur value is Name and versendet = 0000-00-00 00:00:00 and unique_code = $value (unique_code/$value must be equal in this two tables).

I have tried with this:

$pdo = new PDO('myconnectionworks')
$stmt = pdo->prepare("SELECT * FROM table1 WHERE spediteur = 'Name' AND unique_code = $value
UNION ALL 
SELECT * FROM table2 WHERE versendet = '0000-00-00 00:00:00' AND unique_code = $value");
$stmt->execute([$value]);
$result = $stmt->fetch();
if ($result){ "the unique_number exists in both tables and matches the id with the value spediteur 'Dachser' in table1 AND the value versendet ='0000-00-00 00:00:00' in table2" } else { "anything doesn´t work" }

This does not work, as it don´t allow me to get into the if , just into the else section. IF is a response for a successful action (save a other value) and else was an Error Response.

Did you have hints for me?

EDIT with Solution. i have used a programm that helps me to figure out, what for a statement i can use. (dbForge QUery Builder for MySQL) Okay here ist the Statement:

$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
}?>

And i very relieved, that this tool saved my time. And as a learn experience, i can now look to the statement to understood how this works. Awesome!

If someone has a other solution, please let me know. Thank you!

kay_device
  • 33
  • 5
  • Unless your two tables have the same columns, a UNION query does not make sense. What do you mean by "don't work" and where is this IF statement you are talking about? – miken32 Nov 29 '19 at 20:37
  • not all columns are same. the table1 contains more of customerdata and table2 is for somie bundle of scanned number from datamatrixcode. my idea was just to proof over the uniquenumber but i must proof the name in table1 and proof that a timestamp doesnt exist. so when i timestamp is still there, the script must show me an error. – kay_device Nov 29 '19 at 20:51
  • So you’re wanting to find records in table1 that don’t have a corresponding time stamp in table2 when joined by the unique_code? – Tim Morton Nov 30 '19 at 02:07
  • Yes. I want to find records that have in table1 'specific name' value in spediteur and specific value '0000-00-00 00:00:00' and both are must have the same value 'unique_code'. – kay_device Nov 30 '19 at 10:34

0 Answers0