stack kid stack kid - 4 months ago 10
MySQL Question

how to call table names in an email when you SELECT same table twice as a and b?

I am calling a table named

query
twice in mysql query. It gives me all the details required to attach and send with email. However, When I send email, it fetches the partial data. I think it is happening because of similar column names both tables have.

I tried to use
$mail->addAddress($row_mail['a.query_email'], $row_mail['a.query_name']);
to extract data from table
query a
then it gives me error
Notice: Undefined index: a.query_email
.

$sql_mail = "SELECT * FROM query a, query b WHERE a.query_id = '$_GET[q_id]' AND
a.query_id = b.query_reply_id ORDER BY b.query_id DESC";

$run_mail = mysqli_query($conn, $sql_mail);
$row_mail = mysqli_fetch_assoc($run_mail);

$mail->addAddress($row_mail['query_email'], $row_mail['query_name']);

foreach (explode(',', $row_mail['query_reply_files']) as $file_send) {
$path = "../files/queryfiles/".$file_send;
$mail->addAttachment($path, $file_send); }

$mail->isHTML(true); // Set email format to HTML

$bodyContent = $row_mail['query_reply_message'];

Answer

If the column names are ambiguous or in some way unknown, make them explicit. So instead of this:

SELECT * FROM query a, query b ...

Something more like this:

SELECT
  a.query_email AS query_email,
  a.query_name AS query_name,
  ...
FROM query a, query b
...

Be precise in what's being queried. It makes it easier and more consistent for your other code, and provides more explicit instructions to the query engine. Reducing ambiguity is always a good thing.