Query Help

[eluser]RMinor[/eluser]
I am trying to get all of the information about a user from four different tables. The query I am using is below:

Code:

$query = $this->db->query("SELECT * FROM user INNER JOIN user_resume ON user_id = user_resume_user INNER JOIN user_video ON user_id = user_video_user INNER JOIN user_pdf ON user_id = user_pdf_user WHERE user_id = ?", array($user_id));

I am getting an empty result set even though I should be getting one row. Can anyone push me in the right direction to solving this?

[eluser]CroNiX[/eluser]
You should include the table names and not just the field names when using joins. Are you sure you're not getting an error with this about unambiguous field names? Is DB debugging turned on?

[eluser]RMinor[/eluser]
All I am getting is an empty result set. I have debugging set at 2.

[eluser]CroNiX[/eluser]
Database debugging...not php debugging....

[eluser]RMinor[/eluser]
Sorry, I checked and it is enabled. I really didn't see any message in the logs that helps me out with this though.

[eluser]InsiteFX[/eluser]
Check to see if you are generating the correct query.

Code:

// your query here then below
$str = $this->db->last_query();

[eluser]RMinor[/eluser]
Everything looks good with the query. Here is what was returned from that:

SELECT * FROM user INNER JOIN user_resume ON user_id = user_resume_user INNER JOIN user_video ON user_id = user_video_user INNER JOIN user_pdf ON user_id = user_pdf_user WHERE user_id = '1'

Do I need to somehow group the joins? I was looking on the MySQL forum and from what I gathered my query should work.

[eluser]InsiteFX[/eluser]
I found this that may or may not help you.

Multiple inner joins with different where-conditions

So from this I think your last inner join needs to be a left join.

[eluser]vitoco[/eluser]
First, if you use INNER JOIN you are telling to the db explicitly that must be al least 1 resume, 1 video and 1 pdf related to the user, to return a row. Also, the number of rows returned will be all rows in table * all rows in all the other tables. So try with this query to check number of items per table, associated with the user and start from there.

Code:

SELECT
  user.*  ,
  COUNT( user_resume.user_resume_user ) AS num_resumes ,
  COUNT( user_video.user_video_user  ) AS num_videos ,
  COUNT( user_pdf.user_pdf_user ) AS num_pdfs
FROM
  user
LEFT JOIN
  user_resume
ON
  user_id = user_resume_user
LEFT JOIN
  user_video
ON
  user_id = user_video_user
LEFT JOIN
  user_pdf
ON
  user_id = user_pdf_user
WHERE
  user_id = ‘1’
GROUP BY
  user_id

Saludos

[eluser]RMinor[/eluser]
Thanks for everyone's help in this matter. I switched all of my joins to left joins and it works. I really appreciate the help!