1

I am trying to search two tables, match the results and then concatenate the answer... Only finding results >= today's date. This will then give the user the option to delete the selected from the DB. So...

Table 1 called Prog_name

  • id prog_name
  • 1 Breakfast
  • 2 Mid Morning
  • 3 Afternoon

Table 2 called talk_ups

  • id date_tx prog_name (prog_name value = prog_name.id)
  • 1 2017-06-30 2
  • 2 2017-07-03 1
  • 3 2017-07-01 3

The result I am after is something like: "01-07-2017, Afternoon". But I do also need the talk_ups.id to ensure it only deletes the correct record.

I managed to figure out how to get the name to match the talk_ups.prog_name value:

'$sql. = "SELECT talk_ups.prog_name, prog_name.id as progID, prog_name.prog_name as theName FROM prog_name, talk_ups WHERE talk_ups.prog_name = prog_name.id";'

But I can't figure out how to do the two searches and end up with the right result and how to separate out the results to then concatenate them.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Jon Talbot
  • 13
  • 2

1 Answers1

1

You can use JOIN with WHERE condition, e.g.:

SELECT pn.id, pn.prog_name, tu.date_tx
FROM prog_name pn JOIN talk_ups tu ON pn.id = tu.prog_name
WHERE tu.date_tx > NOW();
Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
  • Ok, thanks for that. How does the result appear so I can put that into a dropdown list? This is the version (and yes I know it's wrong) I have at the moment, how do I need to adjust it? `$result = mysqli->query($sql); while ($row = $result->fetch_assoc()){ echo "";` – Jon Talbot Jun 29 '17 at 22:30
  • Wrap the query into php code as explained [here](https://stackoverflow.com/questions/5189662/populate-a-drop-down-box-from-a-mysql-table-in-php)? – Darshan Mehta Jun 29 '17 at 22:32
  • I apologise for my dumbness, so it's currently set up like this: `` – Jon Talbot Jun 29 '17 at 22:45
  • Sorry about the formatting, I can't seem to get it to do anything helpful. – Jon Talbot Jun 30 '17 at 07:25
  • @JonTalbot can you add these details into the question? – Darshan Mehta Jun 30 '17 at 07:42