Search engine result, two tables and while problem?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Housy
Forum Newbie
Posts: 4
Joined: Fri May 22, 2009 4:37 am

Search engine result, two tables and while problem?

Post by Housy »

Hello :)

I have a php mysql search engine. In database i have two tables, these are houses and images. All the data like house_id, title, content, price is stored in houses table. And then in table images i have columns image_id, house_id, image, image_type (this column has a value main or normal).

Well i actually know how to connect these two tables and how to show all columns in houses table but here is the problem because i only know how to show one image, image that has value "main" in column image_type. The question is, how to show all images, where image_type is same as "main" and same as "normal" and where house_id from table images belongs to house_id in houses table.

Here's the code that i tryed, but doesn't work as i want, it only works for the main picture:

Code: Select all

<?php
 
if(isset($_POST["find"])) {
    
$sql = "SELECT a.title,
               a.content,
               a.region,
               a.place,
               a.parcela,
               a.kvadratura,
               a.price,
               b.house_id,
               b.image,
               b.image_type
        FROM houses AS a,
             images AS b
        WHERE a.house_id = b.house_id
        AND a.region = '".mysql_real_escape_string($_POST["region"])."'
        AND b.image_type = '".mysql_real_escape_string("main")."'
        ORDER BY house_id DESC";
 
$res  = mysql_query($sql) or die(mysql_error());
$show = mysql_fetch_array($res);
 
while($show) { ?>
 
<div class="result">
<div class="result_top_left"></div><div class="result_top"></div><div class="result_top_right"></div>
<div class="result_content">
<div class="picture"><a href="images/houses/<?php print $show["house_id"]."/".$show["image"]; ?>" rel="lightbox"><img src="images/houses/<?php print $show["house_id"]."/thumbs/".$show["image"]; ?>" alt="<?php print $show["title"]; ?>" /></a></div>
<div class="description">
<p class="main_title"><?php print $show["title"]; ?></p>
<?php print $show["region"]; ?> regija, <?php print $show["place"]; ?><br />
Bivalna površina: <?php print $show["kvadratura"]; ?> m<sup>2</sup><br />
Površina parcele: <?php print $show["parcela"]; ?> m<sup>2</sup><br />
Cena: <?php print $show["price"]; ?> €
<p style="float: right; margin: 20px 0px 0px 0px; padding: 0px; border: none"><a href="javascript&#058;void(0);" onclick="shToggle('more'); return false;"><img src="images/more.png" alt="more" /></a></p>
</div>
<div style="display: none" id="more">
<p>OPIS</p>
<?php 
 
print "<p style='border-bottom: 1px black dashed'>".$show["content"]."</p>";
 
[b]HERE I WANT TO SHOW PICTURES (thumbnails) THAT BELONGS TO THIS house_id AND has value of "normal"[/b]
 
?>
</div>
</div>
<div class="result_down_left"></div><div class="result_down"></div><div class="result_down_right"></div>
</div>
    
<?php } } ?>
Can someone please tell me what exactly should i do to solve this, cause i have no clue :?

P.S. --> on some other forum they just told me to create another query and i tryed, but i was probably doing it wrong, i was trying to create while inside while but that is probably wrong, cause i got so many results.

Thank you,

Housy
Last edited by Benjamin on Fri May 22, 2009 9:47 am, edited 1 time in total.
Reason: Changed code type from text to php.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Search engine result, two tables and while problem?

Post by jayshields »

Try iterating your MySQL results as shown in the example from the PHP manual.

Code: Select all

while($row = mysql_fetch_assoc($result)) {
  echo $row['column1'] . $row['column2']; /* etc */
}
Housy
Forum Newbie
Posts: 4
Joined: Fri May 22, 2009 4:37 am

Re: Search engine result, two tables and while problem?

Post by Housy »

Ammm I'm not quite sure if you understood what exactly do i want. I'll try to explain more detailed. I Actually know how to readout the data but the problem is that i don't know how to combine everything.

Here is the picture so you could imagine everything easier.

Image

What i want here is to readout image with "main" value and all of the images with value "normal" and i dont know how to combine all this. Like i said in previous post, i only know how to do it for the main picture and all the data in houses table.

Code: Select all

<?php
 
# How should look the query then, cause now it works only for the main image?
# Or do i need two queries and how should they be written?
 
$sql = "SELECT a.title,
               a.description,
               a.location,
               a.size,
               b.house_id,
               b.image
          FROM houses AS a,
               images AS b
          WHERE a.house_id = b.house_id
          AND a.region = '".mysql_real_escape_string($_POST["region"])."'
          AND b.image_type = '".mysql_real_escape_string("main")."'
          ORDER BY house_id DESC";
 
$res  = mysql_query($sql) or die(mysql_error());
$show = mysql_fetch_array($res);
 
while($show) {
 
# The main image
print "<img src='images/houses/".$show['house_id']."/thumbs/".$show['image']."' />";
 
# Content
print "<p>".$show['title']."</p>";
print "<p>".$show['description']."</p>";
print "<p>".$show['location']."</p>";
print "<p>".$show['size']."</p>";
 
# Here is the problem, how to readout this small images?
# Should it be one more while statement here or what?
print "Readout all normal images from table images";
 
}
 
?>
I hope i have explained everything more clearly now.

Tnx,

Housy
Last edited by Benjamin on Fri May 22, 2009 1:32 pm, edited 1 time in total.
Reason: Changed code type from text to php.
Housy
Forum Newbie
Posts: 4
Joined: Fri May 22, 2009 4:37 am

Re: Search engine result, two tables and while problem?

Post by Housy »

Ok i'm a little bit closer now. I have two rows in table houses and each house has 3 images, that means six rows in table images. Now i want to know how to compare house_id from both tables, because now all images are showed under the last result. I tryed to compare house_id with if statement in the second loop, something like that --> if($row["house_id"] == $show["house_id"]), but it doesn't work at all.

Code: Select all

<?php
 
# Get the house data
$sql = "SELECT * FROM houses
        WHERE region = '".mysql_real_escape_string($_POST['region'])."'
        ORDER BY house_id DESC";
$res = mysql_query($sql) or die(mysql_error());
 
# Get the images
$qry = "SELECT * FROM images";
$img = mysql_query($qry) or die(mysql_error());
 
while(($row = mysql_fetch_assoc($res)) == true) {
   
   print "<p>".$row['title']."</p>";
   print "<p>".$row['description']."</p>";
   print "<p>".$row['price']."</p>";
   print "<p>".$row['place']."</p>";
 
   while($show = mysql_fetch_assoc($img)) print "<img src='images/houses/".$show['house_id']."/thumbs/".$show['image']."' />";
 
}
 
?>
Tnx,

Housy
Last edited by Benjamin on Fri May 22, 2009 6:55 pm, edited 1 time in total.
Reason: Changed code type from text to php.
Housy
Forum Newbie
Posts: 4
Joined: Fri May 22, 2009 4:37 am

Re: Search engine result, two tables and while problem?

Post by Housy »

Problem solved :D The second query was outside the first loop and that is why it didnt work as i expected.

Code: Select all

<?php
 
/* Query for house data */
$sql = "SELECT * FROM houses
        WHERE region = '".mysql_real_escape_string($_POST['region'])."'
        ORDER BY house_id DESC";
 
$res = mysql_query($sql) or die(mysql_error());
 
while(($row = mysql_fetch_assoc($res)) == true) {
    
    print "<p>".$row['title']."</p>";
    print "<p>".$row['description']."</p>";
    print "<p>".$row['price']."</p>";
    print "<p>".$row['place']."</p>";
    
    /* Query for images */
    $qry = "SELECT * FROM images
            WHERE house_id = ".mysql_real_escape_string(intval($row["house_id"]))."
            AND image_type = '".mysql_real_escape_string("normal")."'";
    $img = mysql_query($qry) or die(mysql_error());
 
    while($show = mysql_fetch_assoc($img)) print "<img src='images/houses/".$show['house_id']."/thumbs/".$show['image']."' />";
 
}
 
?>
Tnx for help,

Housy
Last edited by Benjamin on Tue May 26, 2009 10:14 am, edited 1 time in total.
Reason: Changed code type from text to php.
Post Reply