Arvind Arvind - 2 months ago 7
MySQL Question

PHP loop query not working

I need work with 2 tables, I tried with inner join, place of 4 result, its just showing two result from a table, I'm pasting both query, please look on it and help me, I will be thankful to all of you.

<?
$query5 = "SELECT * FROM escorts_touring order by es_tou_id";
$result5 = mysql_query($query5);
while($row5 = @mysql_fetch_array ($result5, MYSQL_ASSOC))
{
$es_touring_city = $row5['es_touring_city'];
}

?>

<?php

echo $sql="SELECT e.es_id, e.es_sex, e.service_type,
e.working_name, t.es_tou_id, t.es_id,
t.es_touring_city, t.es_touring_start_date,
t.es_touring_end_date
FROM escorts AS e
INNER JOIN escorts_touring AS t
ON e.es_id = t.es_id
where es_touring_city = '$es_touring_city'";
$result=mysql_query($sql);
$rowcount=mysql_num_rows($result);
$counter=0;
$count=0;

while($row = @mysql_fetch_array ($result, MYSQL_ASSOC))
{

if($counter++%4==0)print"</div><div class=\"row\"></div>";

$es_sex =$row['es_sex'];
$service_type=$row['service_type'];
$working_name=$row['working_name'];
$es_id=$row['es_id'];
$es_tou_id = $row['es_tou_id'];
$es_touring_city = $row['es_touring_city'];
$es_touring_start_date = $row['es_touring_start_date'];
$es_touring_end_date =$row['es_touring_end_date'];

$newstartDate = date("dS F, Y", strtotime($es_touring_start_date));
$newendDate = date("dS F, Y", strtotime($es_touring_end_date));

$query = "SELECT * FROM escorts_image where es_id = $es_id";
$result_image = @mysql_query ($query);
$row_image = @mysql_fetch_array ($result_image, MYSQL_ASSOC);
$image = $row_image['image'];
$dest="uploads";
?>


when I'm printing/echo

$es_touring_city = $row5['es_touring_city'];


its showing 4 result.

but when I'm using $es_touring_city in 2nd query, it just shows 2 results with image.

If i m not very clear talk.

Thanks,

Answer

Join everything into a single query. Order the results so that all the rows for the same es_id are together. Then check when $row['es_id'] changes, and start a new row.

<?php

$sql="SELECT e.es_id, e.es_sex, e.service_type, 
             e.working_name,  t.es_tou_id, t.es_id, 
             t.es_touring_city, t.es_touring_start_date, 
             t.es_touring_end_date, i.image
      FROM escorts AS e 
      INNER JOIN escorts_touring AS t ON e.es_id = t.es_id
      INNER JOIN (SELECT es_id, MAX(image) AS image
                  FROM escorts_image
                  GROUP BY es_id) AS i ON e.id = i.es_id
      ORDER BY e.es_id";
$result = mysql_query($sql);
$last_esid = null;
$counter = 0;

$dest = "uploads";

while ($row = mysql_fetch_assoc($result)) {
    $es_id=$row['es_id'];
    if ($counter++ == 4 || $es_id != $last_esid) {
        if ($last_esid) {
            echo "</div>";
        }
        echo "<div class='row'></div>";
        $last_esid = $es_id;
        $counter = 0;
    }

    $es_sex  =$row['es_sex'];
    $service_type=$row['service_type'];
    $working_name=$row['working_name'];
    $es_tou_id = $row['es_tou_id'];
    $es_touring_city = $row['es_touring_city'];
    $es_touring_start_date = $row['es_touring_start_date'];
    $es_touring_end_date =$row['es_touring_end_date'];
    $image = $row['image'];

    $newstartDate = date("dS F, Y", strtotime($es_touring_start_date));
    $newendDate = date("dS F, Y", strtotime($es_touring_end_date));

    // print the row
}