0

I am using OS Ticket and I have another site for Members Area,I
am trying to join the 3 tables that have the information that corresponds to the customer by EMAIL.

the first table is:qbcd_ticket:
ticket_id | number | user_id | user_email_id | status_id | dept_id | and more...
5     |  762086|   2     |      0    |      1|  1  |    

the next is qbcd_user_email
rows:

id | user_id | flags | address
2  |    2    |  0    |  example@demo.com

the last is: qbcd_user

id | org_id | default_email_id | status | name              | created             | updated 
2  |    0   |   2          |     0  |  Customer Name | 2017-03-03 10:44:28 | 2017-03-03 10:44:28

The information that I need to display, is all corresponding Tickets associated with the customer where it = the email address.

The database connection and display is something I can do, however, I do not know how to perform an interjoin or a join request.

the only static variable that will not change is $_SESSION['user_email']; which is logged by logging into the members area.

If you can assist me with this that would be great.

2
Contributors
2
Replies
36
Views
3 Months
Discussion Span
Last Post by cereal
0
<?php

$email = $_SESSION['user_email'];
$qry = "SELECT qbcd_user_email.address, qbcd_user_email.user_id FROM qbcd_user_email INNER JOIN qbcd_user ON qbcd_user.id = qbcd_user_email.user_id INNER JOIN  qbcd_ticket ON qbcd_ticket.user_id WHERE (qbcd_user_email.address = '.$email.') ORDER BY qbcd_ticket.ticket_id DESC";

$result = mysqli_query($link, $qry);

while ($row = mysqli_fetch_assoc($result)){
    echo $row['qbcd_ticket.number'];    
}

?>

Here is what i have tried so far, the result when i do a VAR_DUMP($qry); come up with string(287) "SELECT qbcd_user_email.address, qbcd_user_email.user_id FROM qbcd_user_email INNER JOIN qbcd_user ON qbcd_user.id = qbcd_user_email.user_id INNER JOIN qbcd_ticket ON qbcd_ticket.user_id WHERE (qbcd_user_email.address = '.patrick.kershner@gmail.com.') ORDER BY qbcd_ticket.ticket_id DESC".

The query seems to be working fine, but it is not displaying any results in the WHILE loop.

0

Hi,

look at the WHERE clause, you have:

WHERE (qbcd_user_email.address = '.patrick.kershner@gmail.com.')

So is going to search an email address with a leading and trailing dot, due to:

WHERE (qbcd_user_email.address = '.$email.')

Since you are using double quotes to enclose the query, you don't need the concatenation operator ., just change it to:

WHERE (qbcd_user_email.address = '$email')

This fixes the query, but read this thread about prepared statements:

It will help you to build safer queries.

Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.