PHP prepared statement breaks using Inner Join

574

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

I want toINNER JOIN both tables onsource_category_id. I've worked withINNER JOIN before with success. However, I am gettingFatal 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 theINNER JOIN code along withsource_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!

851

Answer

Solution:

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

Answer

Solution:

@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!

People are also looking for solutions to the problem: javascript - How to create thumbnail for uploaded images on DropZone.js?

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.