Mysql - help with left join

I'm trying to use a Left join query to get the last three results. If the user types in "carl" in the search field, it should return from Mysql carl, rick and peter because they have the same "pid" and they are below carl in the table.

   SELECT parent.* 
     FROM mytable parent 
LEFT JOIN mytable child 
       ON parent.p开发者_运维知识库id = child.pid 
    WHERE name LIKE '%carl%' 
 ORDER by `id` ASC;

+----+----------+------------+
| id |    pid   |     name   |
+----+----------+------------+
|  1 |     null |     dave   |
|  2 |        1 |     mike   |
|  3 |        1 |     carl   |
|  4 |        1 |     rick   |
|  5 |        1 |     peter  |

I get this error "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource..." when i try the query above.

There should be no table keywords in your FROM clause:

SELECT parent.* FROM parent LEFT JOIN child on parent.pid=child.pid WHERE name LIKE '%carl%' ORDER by `id` ASC

Always test if your result is valid and call mysql_error() if not, to find the reason for failure.

$result = mysql_query(...);
if (!$result) {
   // error in query
   echo mysql_error();

}

Do child/parent has a "name","id" column?

Change it something like:

SELECT parent.* FROM table parent LEFT JOIN table child on parent.pid=child.pid WHERE parent.name  LIKE '%carl%' ORDER by  parent.id ASC

Regards

First, always check for error status, or else you're troubleshooting blind. If the query has an error in it, mysql_query() returns false instead of a result resource. That's why mysql_fetch_assoc() can't succeed, because you're asking it to fetch results from false.

If you get false then there's an error in your query, and you should call mysql_error() to see the error message.

See "Example #1 Invalid Query" in the manual for mysql_query()

In your case, it could be because your table is named table. This is an SQL reserved words. You need to use back-ticks to delimit it.

Also you should qualify name with its table alias, otherwise it's ambiguous whether it's the name column from parent or child.

SELECT parent.* FROM `table` parent 
LEFT JOIN `table` child on parent.pid=child.pid 
WHERE parent.name LIKE '%carl%' ORDER by `id` ASC