Only displays one row of data

[digi duck]

Hi.

 

I am pretty new to this and am having difficulty looping through rows. I have the code below which first selects a name based upon the id sent from a GET on a separate page. It then has a second select query to get the data from all rows which have this name.  I then want it to display the name, and city once but all of the comments. The code below only shows one of each though. Thanks in advance

 

<?php 

$id = $_GET['id'];

// Retrieve data from database 
$sql="SELECT landlord_name AS name
FROM $tbl_name
WHERE id=$id";
$query=mysql_query($sql);

//loop through the rows
while ($rows = mysql_fetch_array($query)){

$name = $rows['name']; }

$sql2="SELECT city, landlord_name, landlord_comments
FROM $tbl_name
WHERE landlord_name= '$name'";

$query2=mysql_query($sql2) or die(mysql_error());

while ($data = mysql_fetch_array($query2)){ ?>


<table width="600" border="1" cellspacing="0" cellpadding="0" align="center">
<tr>	
<div id="name"><? echo $name ?></div>
<div id="city"><? echo $data['city']; ?></div>
<div id="comments"><? echo $data['landlord_comments']; ?></div>
</tr>
</table>
<? } //End While ?>

[jcbones]

Only use one query, also check to make sure id is set and not empty.

<?php //syntax highlighting.
if(!empty($_GET['id'])) {
$id = (int)$_GET['id'];
} else {
$id = 0;
}

$sql = "SELECTb.city, b.landlord_name, b.landlord_comments FROM $tbl_name AS a JOIN $tbl_name AS b ON a.landlord_name = b.landlord_name WHERE a.id = $id";

 

I believe that query will work, it hasn't been tested though.

[digi duck]

Thanks for your reply, however im new to php and dont fully understand what is going on. Could you explain it a bit to me please. Thanks a lot.

[jcbones]

Sure,

 

PHP is a programming language that works on the server side, MySQL is a database that resides on a server.  Most of the time, they are on the same server, although they don't have to be.  PHP is PHP, MySQL is MySQL, many confuse that MySQL is part of PHP, it is not, it has it's own language and it's own functions.

 

MySQL is a relational database, so tables can have relationships.  The way your table is set up, is that the rows in your table have a relationship based on the landlord's name.  We can use that relationship to get all the data for that landlord, no matter how we start to gather the data.

 

This query:

$sql = "SELECTb.city, b.landlord_name, b.landlord_comments FROM $tbl_name AS a JOIN $tbl_name AS b ON a.landlord_name = b.landlord_name WHERE a.id = $id";

does exactly what your TWO queries does.  It ask for the row specified by the id, then ties all of the rows in that table to the landlord_name back to the row with the correct id.

 

MySQL JOIN syntax (MySQL manual)