get query from multiple table mysql

[lochoe]

Hi,actually i'm trying to build site using ezrealty component from joomla..(but for small area only just for fun)

i plan to create module (joomla) for locality (count), but with ezrealty, there are only module for category(count) in their option..

i just a chicken with php, so i dont know much to relate query from multiple table..

this is simple script to get data from the table:

php Code:

<?
$username="username";
$password="password";
$database="table";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM #_ezrealty ";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Property List</center></b><br><br>";

$i=0;
while ($i < $num) {

$cid=mysql_result($result,$i,"cid");
$locid=mysql_result($result,$i,"locid");
$address2=mysql_result($result,$i,"address2");
$postcode=mysql_result($result,$i,"postcode");
$price=mysql_result($result,$i,"price");
$smalldesc=mysql_result($result,$i,"smalldesc");
$listdate=mysql_result($result,$i,"listdate");

echo "<b>State: $cid</b><br>Location: $locid<br>Address: $address2<br>Postcode: $postcode<br>Price: $price<br>Desc: $smalldesc<br>Date added: $listdate<hr><br>";

$i++;
}

------> I get data like this

Property List

State: 2
Location: 34
Address: Prima Ville
Postcode: 52200
Price: 430000.00
Desc: Semi D 2 stry at Prima Ville Hill View. Nice view.
Date added: 2008-11-12

State: 2
Location: 20
Address: Malehawl Park
Postcode: 56110
Price: 690000.00
Desc: Fully and beautifully rennovated Spacious Hall, dinning and kithceh (douwstairs) 7 Family rooms upstairs, 1 rbedroom down Good Condition
Date added: 2008-11-12

when you see on state and location, it just show numbers exactly like in database, what i'm going now is to make it show the state and locality (which located in different table)(need character not number)

I read from some forum, the query must set like this..

$query="SELECT * FROM #_ezrealty INNER JOIN #_ezrealty_locality ON #_ezrealty.locid =
#_ezrealty_locality.locid ORDER BY RAND() LIMIT $count";

i try it... but no success... this is only my first move to build joomla module (learn php mysql) , my last work should look like front enormo.com (shows locality(count), still figure out, how to do that... errmm

any help please... a big thanx..

[edited by: jatar_k at 5:29 pm (utc) on Nov. 12, 2008]
[edit reason] no urls thanks [/edit]

[d40sithui]

I think i understand what you are trying to do...
You want to display the STATE and LOCATION fields as text, which is currently represented by their IDs (displayed). The text(title) counterpart is located in another table. Is this correct?
Try this::

$query = "SELECT t1.*, t2.* FROM #_ezrealty t1, #_ezrealty_locality t2 WHERE t1.locid=t2.locid";

[lochoe]

you mean just to change

$query="SELECT * FROM #_ezrealty ";

with

$query = "SELECT t1.*, t2.* FROM #_ezrealty t1, #_ezrealty_locality t2 WHERE t1.locid=t2.locid";

i get the same result...

its still appear number not the locality name..

this is table in #_ezrealty
<snip>

and this is table in #_ezrealty_locality
<snip>

how to let when it call locid and stid in #_ezrealty, then it will show "ezcity" in #_ezrealty_locality

[edited by: dreamcatcher at 7:59 am (utc) on Nov. 13, 2008]
[edit reason] No urls please! [/edit]

[d40sithui]

Well the * selection from tables will display everything including the IDs(numbers) and titles (if any). I do not know your table structure(fieldnames) and therefore used the "SELECT *". If you can "desc #_ezrealty" and "desc #_ezrealty_locality", we can come up with a better query.

[ngrant]

have you set the relations (foreign keys) for state and location?