MySQL Join not producing any results

[MartynLearnsPHP]

I am trying to join two tables but I am producing no results. I'm convinced that I have entered everything correctly, but clearly I haven't? Can anyone spot my mistake?

<?php
require 'core/memberinit.php';
$member = new Member();
include 'timeago.php';

$memberid = $member->data() ->id;

if(isset($_GET['q'])) {
    $q = html_entity_decode($_GET['q']);

$query2 = DB::getInstance()->query("SELECT c.id c.time as time, c.message as message, c.removed1, c.removed2, 
			m1.member_first_name as firstname1, m1.member_last_name as lastname1, m1.username as username1,  
			m2.member_first_name as firstname2, m2.member_last_name as lastname2, m2.username as username2 
			FROM conversation c
				LEFT JOIN members m1 ON c.member1=m1.id
				LEFT JOIN members m2 ON c.member2=m2.id
			WHERE (c.id=$q OR c.originalid=$q)
			AND ((c.member1=$memberid AND c.removed1='No') 
			OR (c.member2=$memberid AND c.removed2='No'))
			ORDER BY c.id");
		
foreach ($query2->results() as $result2)
{
		echo $result2->firstname1;
		echo $result2->lastname1;
		echo $result2->username1;
		echo timeAgo(strtotime($result2->time));
		echo $result2->message;
		echo $result2->firstname2;
		echo $result2->lastname2;
		echo $result2->username2;
}
?>

Edited November 23, 2014 by MartynLearnsPHP

[NotionCommotion]

What are the values of $memberid and $q?  If they are undefined or not what you expected, there is your problem.  If they look applicable, then substitute them in your query and direct input the query into your DB, and see if you get any results.  If none, systematically remove some of the WHERE clauses until you find out which one is limiting.

[Barand]

Store your queries in a string before executing. That way, if you have problems, it is easy to echo the string and maybe spot any errors.

$sql = "SELECT c.id c.time as time, c.message as message, c.removed1, c.removed2,
            m1.member_first_name as firstname1, m1.member_last_name as lastname1, m1.username as username1,
            m2.member_first_name as firstname2, m2.member_last_name as lastname2, m2.username as username2
            FROM conversation c
                LEFT JOIN members m1 ON c.member1=m1.id
                LEFT JOIN members m2 ON c.member2=m2.id
            WHERE (c.id=$q OR c.originalid=$q)
            AND ((c.member1=$memberid AND c.removed1='No')
            OR (c.member2=$memberid AND c.removed2='No'))
            ORDER BY c.id";
echo "<pre>$sql</pre>";

[NotionCommotion]

I always store my queries as a string like Barand recommends.

 

I also typically use PDO's prepared statements, and use the following class to view the queries when troubleshooting

    public static function showQuery($sql, $data)
    {
        $keys = array();
        $values = array();

        # build a regular expression for each parameter
        foreach ($data as $key=>$value)
        {
            if (is_string($key)) {$keys[] = '/:'.$key.'/';}
            else {$keys[] = '/[?]/';}

            //if(is_numeric($value)) {$values[] = intval($value);}
            if(is_numeric($value)) {$values[] = $value;}
            else{$values[] = '"'.$value .'"';}
        }
        $sql = preg_replace($keys, $values, $sql, 1, $count);
        return $sql;
    }

[MartynLearnsPHP]

What are the values of $memberid and $q?  If they are undefined or not what you expected, there is your problem.  If they look applicable, then substitute them in your query and direct input the query into your DB, and see if you get any results.  If none, systematically remove some of the WHERE clauses until you find out which one is limiting.

$q and $memberid are working ok. $q is the conversation id value carried forward using Ajax and $memberid is the logged on user,

 

I've echoed the string and everything seems to be working correctly. With the example that I am currently working on, I am getting the following result:

SELECT c.id c.time as time, c.message as message, c.removed1, c.removed2, 
			m1.member_first_name as firstname1, m1.member_last_name as lastname1, m1.username as username1,  
			m2.member_first_name as firstname2, m2.member_last_name as lastname2, m2.username as username2 
			FROM conversation c
				LEFT JOIN members m1 ON c.member1=m1.id
				LEFT JOIN members m2 ON c.member2=m2.id
			WHERE (c.id=1 OR c.originalid=1)
			AND ((c.member1=76 AND c.removed1='No') 
			OR (c.member2=76 AND c.removed2='No'))
			ORDER BY c.id

Where $q is correctly showing as '1' which is the correct id of the message in my 'conversation' table that I have selected and '76' is the correct user id in my 'members' table.

[MartynLearnsPHP]

OK. I've been playing around with lots of different amendments, but without much luck, but it seems like nothing is being read from conversation table, and I just can't fathom out why. Even if I just echo out 'message' I get a blank result.

 

Does anybody have any suggestions or point me to a resource that might be able to help me?

 

Any help very gratefully received.

[Barand]

What does this query give

SELECT id, originalid, member1, member2, removed1, removed2
FROM conversation
WHERE (id=1 OR originalid=1)

[MartynLearnsPHP]

That is working. And if I swap 1 for $q, it brings in the respective information from my conversation table. So the problem is clearly with my select and joins. It even works with adding in the message and time columns.

 

But I need the joins in order to get the user information from my members table.

Edited November 24, 2014 by MartynLearnsPHP

[Barand]

I didn't ask if it worked, I asked what it produced. We cannot see your data from here (believe it or not) so if we don't get information that we ask for we can't help.

 

I wish you luck with the problem.

[MartynLearnsPHP]

Sorry. I misunderstood what you were looking for.

 

If I use the following query:

$query = DB::getInstance()->query("SELECT id, originalid, message, member1, member2, removed1, removed2
FROM conversation
WHERE (id=1 OR originalid=1)");
		
foreach ($query->results() as $result)
	{
		echo $result->id;
		echo $result->originalid;
		echo $result->member1;
		echo $result->member2;
		echo $result->removed1;
		echo $result->member2;
		echo $result->message;
	}

I get the following result:

107669NoNoAre we on for curry night this week?

 

Where:

ID is "1"

original ID is "0"

member1 is "76"

member 2 is "69"

removed1 is "No"

removed2 is "No"

and message is "Are we on for curry night this week?"

[MartynLearnsPHP]

Well, I've managed to get it working. I don't know if it affected things, but putting `` marks round the table names and swaaping the JOIN 'ON' options around seems to have been the core to it.

 

In case it helps anyone else in a similar position, my final working script was:

$query = DB::getInstance()->query ("SELECT c.id, c.originalid, c.title, c.message, c.member1, c.member2, c.time,
                                               m1.id as id1, m1.member_first_name as firstname1, m1.member_last_name as lastname1,
                                               m1.iusername as username1,
                                               m2.id as id1, m2.member_first_name as firstname2, m2.member_last_name as lastname2,
                                               m2.iusername as username2
                                               FROM `conversation` as c
                                               LEFT JOIN `members` as m1 ON m1.id=c.member1
                                               LEFT JOIN `members` as m2 ON m2.id=c.member2
                                               WHERE (c.cid=$q orc.originalid=$q)
                                               AND ((c.member1=$memberid AND c.removed1='NO')
                                               OR ((c.member2=$memberid AND c.removed2='NO')))
                                               ORDER BY c.id");

[Barand]

That is weird!

1. Nether "conversation" nor "members" is a reserved word so the `` should be unnecessary
2. A=B is the same B=A
3. That new query has a definite syntax error  - WHERE (c.cid=$q orc.originalid=$q)

[MartynLearnsPHP]

Please ignore the syntax error. I'm working on two computers and I mistyped when copying from one screen to the other.

 

From what I can tell, these are the only changes that I made to the original script, but it is now working beautifully.

 

That said, as I type this, I can see one other amendment that I've made - an inadvertent one - I missed out a comma after "SELECT c.id" on my initial coding.

[Barand]

 I missed out a comma after "SELECT c.id" on my initial coding.

Then that was your problem

[MartynLearnsPHP]

Yeah. I see that now. Two days lost trying to spot a missing comma.

[Barand]

Checking for an error message would've revealed that syntax error

[mac_gyver]

having error checking logic in your code is something you should ALWAYS have, from the start. not something you add after the fact.

 

for development, you would display any errors (both php and ones your application detects.) on a live server, you would log the errors instead and output an appropriate message to the visitor. you can handle the switch of where your application detected errors go to by using trigger_error(), which makes use of the php error_reporting/display_errors/log_errors settings.