get query from multiple table mysql

Hi,actually i'm trying to build site like enormo.com 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:

<?
$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++;
}

PHP:

------> 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..

 

You'll have to examine both tables, or better yet; fire up phpmyadmin and run the query from there to see what the error is.
The content of the tables could be out of alignment too perhaps.

 

What are the names of all the fields in both tables?

 

Hi sorry, i still figure it out.. i'm trying to see some module which could have similar features...property type(count)

anyway shoro, this is the table look like

this is main table at #_ezrealty,



and this is table at #_ezrealty_locality


its look like if i want to call the city and locality,

i need to make sure
if locid=loc. number and sid=state number, then it will call the ezcity name in #_ezrealty_locality... or else it just show number.. i'm trying to learn php... to understand how to do this..

 

good, you got phpmyadmin up

please run this query and return the results to us, including any err msg that might pop up.

SELECT * FROM #_ezrealty INNER JOIN #_ezrealty_locality ON #_ezrealty.locid =
#_ezrealty_locality.locid ORDER BY RAND() LIMIT 15;

coz it seems that the query is OK..

 

Hi reene7705,

i follow ur order, and try to run the query... at phpmyadmin..

i get this..


what is it mean??

no err popup, just like the screen shot above

 

well this query result means that out of your entire database, there's only 2 records in #_ezrealty that have corresponding records in #ezrealty_locality..

i dont know this plugin ezrealty but think you need to lookup in the documentation/source how it gets records in #_ezrealty_locality and when it links those records to #_ezrealty.