[SOLVED] MySQL + PHP Problem

[killah]

Ok i have 2 table's.

 

Table 1 => house_rentals | Structure:

rID

rOWNER

rRENTER

rWILL

rHOUSE

rPRICE

rDAYSL

 

Table 2 => house_owned | Structure:

oID

oOWNER

oWILL

oUPGRADES

oUPKEEP

oRENTED

oIMAGE

 

Ok this is what i want to do.

 

I have a main page. Called property's. this is for a rpg game. I can buy multiple houses and then either move into them or lease them to some one else. I got all that done. But when i lease it to some one it does not show up in the propertys main page. I tried left join but just never worked.

 

This is my current query:

 

$my_houses = mysql_query
("
	SELECT oHOUSE,oWILL,oRENTED,oIMAGE,oUPGRADES,oID FROM `house_owned` WHERE `oOWNER` = ".$ir['userid']." OR `oRENTED` = ".$ir['userid']
) or die(mysql_error());

 

Can anyone modify that to fit?

[killah]

I now have this query:

 

$my_houses = mysql_query
("
	SELECT 
		o.oHOUSE,o.oWILL,o.oRENTED,o.oIMAGE,o.oUPGRADES,o.oID,o.oOWNER
	FROM 
		`house_owned` `o`
	LEFT JOIN
		`house_rentals` `r`
	ON
		`r`.`rHOUSE` = `o`.`oID`
	WHERE 
		`o`.`oOWNER` = ".$ir['userid']." 
	OR 
		`o`.`oRENTED` = ".$ir['userid']."
	OR
		`r`.`rRENTER` = ".$ir['userid']
) or die(mysql_error());

 

How ever. My problem now is that it's showing me having 2 house's. 1 because i am renting it. 2 because some one else own's it.

[andy_b42]

Are oOWNER and rOWNER equivalent fields?

[killah]

Yes they are.

 

I tried

 

`r`.`rOWNER` != `o`.`oOWNER`

 

but i seem lost to where to put it.

[andy_b42]

When you do your join, you need to say on what field the tables join.

 

So your join will be something like:

 

      SELECT
         o.oHOUSE,o.oWILL,o.oRENTED,o.oIMAGE,o.oUPGRADES,o.oID,o.oOWNER
      FROM
         `house_owned` `o`
      LEFT JOIN
         `house_rentals` `r` on r.rOWNER = o.OWNER

 

I am not fully sure i understand what you are trying to achieve, but really you should specify how the tables are linked in a join.

[killah]

      ON
         `r`.`rHOUSE` = `o`.`oID`

 

I already have and if i remove that it displays everyone's house to me.

[andy_b42]

      ON
         `r`.`rHOUSE` = `o`.`oID`

 

I already have and if i remove that it displays everyone's house to me.

 

Sorry, i completely missed that.

 

Have you tried using an inner join?

[killah]

Explain the "inner join". My field lies with in CSS and PHP. I can make those valid up to any date. How ever mysql is not my type of thing 

[premiso]

http://en.wikipedia.org/wiki/Join_(SQL)

 

Search for "Inner Join"  that explains it fairly well.

[killah]

I tried

 

$my_houses = mysql_query
("
SELECT
	o.oHOUSE,o.oWILL,o.oRENTED,o.oIMAGE,o.oUPGRADES,o.oID,o.oOWNER
FROM
	`house_owned` `o`
INNER JOIN
	`house_rentals` `r`
ON
	`r`.`rOWNER` != `o`.`oOWNER`
WHERE
	`o`.`oOWNER` = ".$ir['userid']."
OR
	`o`.`oRENTED` = ".$ir['userid']."
AND
	`r`.`rHOUSE` = `o`.`oID`
") or die(mysql_error());

 

Just does not seem to work. Ive tried about 15+ way's now and not a single one work's.

[premiso]

Why are you doing ` around every little thing? It just makes it weird and odd.

 

   $my_houses = mysql_query
   ("
   SELECT
      o.oHOUSE,o.oWILL,o.oRENTED,o.oIMAGE,o.oUPGRADES,o.oID,o.oOWNER
   FROM
      house_owned o
   INNER JOIN
      house_rentals r
   ON
      r.rOWNER <> o.oOWNER
   WHERE
      o.oOWNER = ".$ir['userid']."
   OR
      o.oRENTED = ".$ir['userid']."
   AND
      r.rHOUSE = o.oID
   ") or die(mysql_error());

 

If you want it to be != you need to use <> in MySQL.

 

See what that gets you.

[killah]

Hi. Well i used your code above and it showed me all the houses in the list but only it displayed all the houses that people are living in but infact they all appeared as me as the only. So basicly it was displaying 992 row's but all of the houses were mine.

 

I then moved to this code:

 

$my_houses = mysql_query
("
	SELECT
		o.oHOUSE,o.oWILL,o.oRENTED,o.oIMAGE,o.oUPGRADES,o.oID,o.oOWNER
	FROM
		house_owned o
	INNER JOIN
		house_rentals r
	ON
		r.rHOUSE = o.oID
	WHERE
		o.oOWNER = ".$ir['userid']."
	OR
		o.oRENTED = ".$ir['userid']."
	AND
		r.rOWNER <> o.oOWNER
	") or die(mysql_error());

 

That work's but i own 4 houses and am renting 1 house.

 

The 1 house i am renting is showing and only 2 out of the 4 houses are showing.

[premiso]

      WHERE
         (o.oOWNER = ".$ir['userid']."
      OR
         o.oRENTED = ".$ir['userid'].")
      AND
         r.rOWNER <> o.oOWNER

 

Try that and see if you get the correct results.

[killah]

Only displays the house i am renting.

[premiso]

Can you describe to me how it is setup? Does not need to be hugely indepth.

 

Here is what I am getting:

 

You have two tables, the houses you own and the houses you rent. You want to display all the houses that you own and rent, but you do not want to display the houses that you are renting to which you are the owner?

 

Now, I see the inner join, you are trying to join rented rHouse to oID, are these to values coinciding with each other? I take it they are, just had to verify. Essentially that join, is limiting your query. It will show only houses that you are renting because of that portion:

 

   $my_houses = mysql_query
   ("
      SELECT
         o.oHOUSE,o.oWILL,o.oRENTED,o.oIMAGE,o.oUPGRADES,o.oID,o.oOWNER
      FROM
         house_owned o
      WHERE
        ( o.oOWNER = ".$ir['userid']."
      OR
         o.oRENTED = ".$ir['userid'].")

      ") or die(mysql_error());

 

Now where I am getting confused, is why are you trying to access the house rented table, if you are not even using that in your select statement?

 

I think the query above is what you want. Give it a try and see.

[killah]

I do not know how u did it. But you did it. Yes that is my explanation of the query to what you are getting. The query you posted. Works. How ever i got to run more test's from other people's view to see if it fully work's.

 

Thank's so much premiso. If there were karma here i would definatly give you a +1

[premiso]

Sometimes just writing out exactly what you want to get it can solve an issue. Especially with MySQL.

 

If you only want data from one table, 99% of the time your query only needs to run through that one table.