PHP prepared statement breaks using Inner Join

Question

This is MySQLi extension. I have two tables, sources and source_categories. In sources there is a column that stores the source category id, it is called, source_category_id as a foreign key. In the source_categories table, source_category_id is the primary key and source_category_name holds the actual category names. Pretty basic.

I want to INNER JOIN both tables on source_category_id. I've worked with INNER JOIN before with success. However, I am getting Fatal error: Call to a member function bind_param() on a non-object when I go to test the page.

The prepared statement has only one placeholder as you'll see below, it is fed from a variable which contains a query string value.

This doesn't work:

$sql = 'SELECT source_category_id, source_by, source_name, source_contact, source_category_name
    FROM sources INNER JOIN source_categories
    ON sources.source_category_id = source_categories.source_category_id
    WHERE source_type = ?
    ORDER BY source_name ASC';
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $ps);    
$stmt->bind_result($source_category_id, $source_by, $source_name, $source_contact, $source_category_name);  
$stmt->execute();
$stmt->store_result();
$numRows = $stmt->num_rows;

However, omitting the INNER JOIN code along with source_category_name and $source_category_name from their respective places like so:

$sql = 'SELECT source_category_id, source_by, source_name, source_contact
    FROM sources
    WHERE source_type = ?
    ORDER BY source_name ASC';
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $ps);    
$stmt->bind_result($source_category_id, $source_by, $source_name, $source_contact); 
$stmt->execute();
$stmt->store_result();
$numRows = $stmt->num_rows;

works just fine, but I want the category names too.

I'm obviously missing something really stupid or I'm righteously violating syntax somewhere, but my tired eyes and hurting brain can't find the problem(s).

Any help would be greatly appreciated. Thank you!

You may get your answer from [here](http://stackoverflow.com/questions/18921670/fatal-error-call-to-a-member-function-bind-param-on-a-non-object) — Shaunak Shukla, Apr 20 '15 at 06:19
There are too many solutions on [SO](http://stackoverflow.com/search?q=Fatal+error%3A+Call+to+a+member+function+bind_param%28%29+on+a+non-object) — Shaunak Shukla, Apr 20 '15 at 06:21
possible duplicate of [Reference - What does this error mean in PHP?](http://stackoverflow.com/questions/12769982/reference-what-does-this-error-mean-in-php) — Shaunak Shukla, Apr 20 '15 at 06:26
@ShaunakShukla I have been pouring over SO and the internet for hours and didn't want to post until necessary. Funny thing is, I read that post you linked to. it didn't help. Also, it's not a duplicate of the question you mentioned. I want to know why my query fails, not what the error means. Thanks though! — wordman, Apr 20 '15 at 06:28
`SELECT sources.source_category_id` because otherwise `source_category_id` is ambiguous. — Ja͢ck, Apr 20 '15 at 06:48
@Ja͢ck I have seen it done that way and the way I did it (from a book) but you seem to be on to something here. Problem's not solved yet but you gave the first clear bit of useful code to work with. Tomorrow I refine it, the answer's yours if it works, thanks! — wordman, Apr 20 '15 at 07:16
@Ja͢ck with fresh eyes and mind, I finished working on your suggestion and it works like a charm. Thank you for shedding the necessary light! — wordman, Apr 20 '15 at 16:55

score 0 · Answer 1 · answered Apr 20 '15 at 05:48

change

$sql = 'SELECT source_category_id, source_by, source_name, source_contact, source_category_name
    FROM sources INNER JOIN source_categories
    ON sources.source_category_id = source_categories.source_category_id
    WHERE source_type = ?
    ORDER BY source_name ASC';

to

$sql = 'SELECT source_category_id, source_by, source_name, source_contact, source_category_name
    FROM sources INNER JOIN source_categories
    ON sources.source_category_id = source_categories.source_category_id
    WHERE sources.source_type = ?
    ORDER BY sources.source_name ASC';

I tried that, it still gives me the same error, Thank you though! — wordman, Apr 20 '15 at 05:50

score 0 · Answer 2 · answered Apr 20 '15 at 16:58

@Jack hit it on the head, many thanks for the help. Here's the working query:

$sql = 'SELECT sources.source_category_id, sources.source_by, sources.source_name, sources.source_contact, source_categories.source_category_name
    FROM sources INNER JOIN source_categories
    ON sources.source_category_id = source_categories.source_category_id
    WHERE sources.source_type = ?
    ORDER BY sources.source_name ASC';
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('s', $ps);    
$stmt->bind_result($source_category_id, $source_by, $source_name, $source_contact, $source_category_name);  
$stmt->execute();
$stmt->store_result();
$numRows = $stmt->num_rows;

Again, many thanks!

PHP prepared statement breaks using Inner Join

2 Answers2