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

Question

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'];

Thanks Fred, No, its working fine. Like I said, query fetches all the data I want — stack kid, Aug 02 '16 at 11:54
I believe they are there when I use * or will I have to name each of the column that table contains. — stack kid, Aug 02 '16 at 11:57
let's clear up comment *lol* - See David's answer and eggyval's and now Gordon's. I'll delete this too shortly. Lordie, I need more coffee, *1000 cc's stat!!* haha I'll just sit still and watch. — Funk Forty Niner, Aug 02 '16 at 11:58
btw, you have a lot of questions where none of them were marked as being solved. It's best that you do, otherwise people will think they're still open/unsolved and may want to post more answers. Same thing goes for this one ;-) — Funk Forty Niner, Aug 02 '16 at 12:01
yeah, I am trying the answers. Well, I believe to mark someone's question right,I should vote it up. Isn't it? — stack kid, Aug 02 '16 at 12:17
as per your edit *"It's solved as David's suggest."* with the code. There's no need to update the question with that. Just accept his answer. If David didn't solve it completely, then you should inform him about it and place a comment under his answer. — Funk Forty Niner, Aug 02 '16 at 12:26
Yeah, but i still upvote all my answers as they help me to solve problems in different ways. I do read it, and please all... why -1, :( i did research before posting it, you all understand the question and that may help someone else like me... — stack kid, Aug 02 '16 at 12:27

score 1 · Accepted Answer · answered Aug 02 '16 at 11:56

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.

score 1 · Answer 2 · answered Aug 02 '16 at 11:59

1

Learn to use proper, explicit JOIN syntax. However, the answer to your question is to use table aliases.

One method is to pull all columns from the "a" table and name the specific columns you want from the "b" table:

SELECT a.*,
       b.query_email as b_query_email,
       b.query_id as b_query_id
FROM query a JOIN
     query b
     ON a.query_id = b.query_reply_id
WHERE a.query_id = '$_GET[q_id]' 
ORDER BY b.query_id DESC;

answered Aug 02 '16 at 11:59

Gordon Linoff

1,242,037
58
646
786

Thanks Gordon, I give it a try. I tried David's and it worked. – stack kid Aug 02 '16 at 12:29

score 0 · Answer 3 · edited May 23 '17 at 11:53

0

As documented under mysqli_fetch_assoc():

If two or more columns of the result have the same field names, the last column will take precedence. To access the other column(s) of the same name, you either need to access the result with numeric indices by using mysqli_fetch_row() or add alias names.

It may seem a pain to give columns aliases in a query that's using SELECT *, but you really shouldn't be doing that in production anyway. So, do as @David has suggested and explicitly query for your desired columns, giving them suitable aliases if required.

edited May 23 '17 at 11:53

Community

1
1

answered Aug 02 '16 at 11:58

eggyal

122,705
18
212
237

Thanks Eggyal, that is what I was looking for. I do read it. I will definitely try the numeric indices method too. – stack kid Aug 02 '16 at 12:32

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

3 Answers3