Results 1 to 6 of 6

Thread: SQL Joins Help

  1. #1
    Join Date
    May 2012
    Posts
    210
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default SQL Joins Help

    Hi

    I am having some trouble with SQL JOINS as always got bit confused by them and if to use JOIN, LEFT JOIN etc

    I am trying to get id, ref, role from jobs table and id, name from employers table cause employers add jobs from a form into a db and I want to display the jobs they add on their profile page so they can edit the job advert or delete the job advert once filled

    Hope that makes sense

    Below is SQL query I have so far

    PHP Code:
    SELECT j.idj.refj.rolej.divisionj.locationj.salaryj.description FROM jobs AS j LEFT JOIN `employees` AS e.id j.id 
    I get the following error though on the employer-profile.php page

    Warning: mysqli::query(): (42000/1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.id = j.id' at line 1 in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/security-site/employer-profile.php on line 60 Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.id = j.id' at line 1

    Thank you in advance

    Ian

  2. #2
    Join Date
    May 2012
    Posts
    210
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default

    Sorry a quick update, I think I have figured out bit more, if I am right the same id column had to be in the other table as well so I added memberID to the jobs table so it matches up with memberID that is in the employers table

    I still cant work out the JOINS though, sorry

  3. #3
    Join Date
    May 2012
    Posts
    210
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default

    Think I just sussed it, is working perfect now

    just a last little question

    How do I get the memberID added into jobs table as well when they add a job that adds memberID into the employers table?

  4. #4
    Join Date
    May 2012
    Posts
    210
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default

    Thought I sussed it until I logged in as another employer which is memberID 12 and the two jobs posted from memberID 5 are showing

    Do I need a where clause in the SQL query

    would it be something like WHERE memberID = 'memberID';

  5. #5
    Join Date
    May 2012
    Posts
    210
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default

    I got is working in phpmyadmin using the following if I enter the memberID manually i.e. 5 or 12 but would it be WHERE employers.memberID = 'employers.memberID';

    SELECT employers.memberID, jobs.id, jobs.ref, jobs.role, jobs.division, jobs.location, jobs.salary, jobs.description, jobs.datecreated, jobs.dateupdated
    FROM employers
    INNER JOIN jobs ON employers.memberID = jobs.memberID
    WHERE employers.memberID = '5';

  6. #6
    Join Date
    May 2012
    Posts
    210
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default

    Think I sorted it, it is working perfect now, on employers profile page, I have the following SQL query just in case it helps others

    I added the following line above the SQL SELECT query

    Code:
    $id = $_SESSION["memberID"]; // store the user id into session
    then my SQL query line looks like the following

    Code:
    // get the records from the database
    if ($result = $mysqli->query("SELECT employers.memberID, jobs.id, jobs.ref, jobs.role, jobs.division, jobs.location, jobs.salary, jobs.description, jobs.datecreated, jobs.dateupdated
    FROM employers 
    	INNER JOIN jobs ON employers.memberID = jobs.memberID WHERE employers.memberID='$id'"))

Similar Threads

  1. Complex Joins
    By timbim in forum MySQL and other databases
    Replies: 3
    Last Post: 11-02-2010, 11:09 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •