MySQLI Inner Join 2 Tables

Question

What am I doing wrong here. I have followed many examples but can't seem to get this working. I have 2 tables

Table => users

user_id
user_name
user_email
user_password
user_country
user_dobdate
user_company
user_code
user_status
user_type

Table => applications

apply_id
apply_from
apply_leave_type
apply_priority
apply_start_date
apply_end_date
apply_halfday
apply_contact
apply_reason
apply_status
apply_comment
apply_dated
apply_action_date

SQLI QUERY

$query = $db->select("SELECT users.user_id, app.apply_from FROM users INNER JOIN applications ON  users.user_id = app.apply_from WHERE users.user_code='1'");
$rows = $db->rows();
foreach ($rows as $apply){
$apply_id = $apply['apply_id'];
$apply_from = $apply['apply_from'];

Error Message

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in xxxxxxxxxxxxxxx line 26

Nope, That's not the correct answer. There's something wrong with the SQL QUERY... — user1772571, Aug 17 '13 at 11:26
Yes. EVERY time any mysql*-function complains about not getting a resource, but a boolean, the SQL query failed and needs to be inspected. IT'S LIKE THIS EVERY TIME. — Sven, Aug 17 '13 at 11:29

score 5 · Accepted Answer · answered Aug 17 '13 at 11:26

5

Your query;

SELECT users.user_id, app.apply_from 
FROM users 
INNER JOIN applications 
  ON  users.user_id = app.apply_from 
WHERE users.user_code='1'

...uses an alias app for the table application, but does not declare it.

INNER JOIN applications app

answered Aug 17 '13 at 11:26

Joachim Isaksson

176,943
25
281
294

score 3 · Answer 2 · edited Aug 17 '13 at 11:41

3

You have missed the alias name for table applications as app in join. Try the following:

SELECT users.user_id,app.apply_from 
FROM users 
INNER JOIN applications app ON users.user_id = app.apply_from 
WHERE users.user_code='1'

edited Aug 17 '13 at 11:41

Patrick Kostjens

5,065
6
29
46

answered Aug 17 '13 at 11:31

Bharathi

539
2
6
18

score 2 · Answer 3 · answered Aug 17 '13 at 11:25

2

Put abbreviation 'app' for applications table:

SELECT 
    users.user_id, 
    app.apply_from 
FROM
    users 
INNER JOIN
    applications AS app
ON
    users.user_id = app.apply_from
WHERE
    users.user_code='1'

answered Aug 17 '13 at 11:25

user4035

22,508
11
59
94

MySQLI Inner Join 2 Tables

3 Answers3