SQL query problem

[molokomesto650]

Hi all.

Would be very grateful if someone could help me here.

 

I am retrieving data from my database to build up and XML document.

I`m trying to get data form different tables and while the linking looks well done, I`m having problem to get the query right.

 

Here is my database:

 

CREATE TABLE employee
(       employeeid int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(30) NOT NULL,
        surname VARCHAR(30) NOT NULL,
        dob DATE NOT NULL,
        contact VARCHAR (7),
        address VARCHAR (50),
        rank ENUM('Employee', 'Team_leader', 'Admin')  NOT NULL
);


CREATE TABLE project
(
        projectid int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        projName VARCHAR(50) NOT NULL,
        dateStart DATE,
        comments VARCHAR(255),
        completed BOOLEAN
);


CREATE TABLE employee_project
(
        employeeid int UNSIGNED NOT NULL,
        projectid int UNSIGNED NOT NULL,
        description VARCHAR(255),

PRIMARY KEY (employeeid, projectid)
);


CREATE TABLE task
(
        taskid int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        description VARCHAR(255),
        completed BOOLEAN
);


CREATE TABLE project_task
(
        projectid int UNSIGNED NOT NULL,
        taskid int UNSIGNED NOT NULL,

PRIMARY KEY (projectid, taskid)

);

CREATE TABLE login
(
        user int NOT NULL,
        pass varchar(15) NOT NULL,
        level ENUM('Employee', 'Team_leader', 'Admin'),

PRIMARY KEY (user, pass)

);

 

And below is the querying part in the php page:

 

 $year = $_GET['txt_year'];
  
include("db_connect.php");

$sql_select = "SELECT p.projName,p.comments,ep.projectid,e.employeeid
	FROM project p,employee_project ep,employee e
	WHERE ep.employeeid=p.employeeid
		AND ep.projectid=p.project.id
		AND	projName = '$year'";
		  
$Rs = mysql_query($sql_select);
if (mysql_num_rows($Rs)<1)
{
    mysql_close($con);
    die("err");
}

 

Anybody can help? Thanks.

[molokomesto650]

sorry

I forgot to add:

the error I am getting is:

mysql_num_rows() expects parameter 1 to be resource, boolean given in

or it simply execute the die call of when num of rows <0.

 

What I`d like is to to build and XML doc with the fields queried.

 

 

[Pikachu2000]

That means the query is failing and returning a boolean FALSE to mysql_num_rows().

To see what that error is, change the query execution to this:

$Rs = mysql_query($sql_select) or die( '<br>Query string: ' . $sql_select . '<br>Returned error: ' . mysql_error() . '<br>');

[molokomesto650]

That means the query is failing and returning a boolean FALSE to mysql_num_rows().

To see what that error is, change the query execution to this:

$Rs = mysql_query($sql_select) or die( '<br>Query string: ' . $sql_select . '<br>Returned error: ' . mysql_error() . '<br>');

ok I tried that and I don`t have that error with the sql_num_rows function anymore.

But still, can`t figure the querying. The page just die as instructed if querying fails.

Very unsure how I`ve done that. Any help?

[Pikachu2000]

It should have returned an error, along with echoing the query string. Did it do that? If it did, paste the output here. Without it, there isn't much information to go on.

[molokomesto650]

actually yes

Query string: SELECT p.projName,p.comments,ep.projectid,e.employeeid FROM project p,employee_project ep,employee e WHERE ep.employeeid=p.employeeid AND ep.projectid=p.project.id AND projName = ''
Returned error: Unknown column 'p.employeeid' in 'where clause'

 

I did correct that as there really isn`t any employeeid field in the Project table.

I change it to ep.projectid=p.projectid.

The employee_project table links employee with project btw.

 

So no more of that previous error but it still does not retrieve that data from database and instead the page dies.

[PFMaBiSmAd]

If you look at the query that was echoed by that troubleshooting code, you will notice that the $year variable is empty and the WHERE clause is always FALSE, so, no rows will be returned.

[molokomesto650]

alright. Fixed it.....the problem was with the ajax script.

Thank you all for your help.