PHP loop query not working

Question

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,

Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[a Kitten is strangled somewhere in the world](http://2.bp.blogspot.com/-zCT6jizimfI/UjJ5UTb_BeI/AAAAAAAACgg/AS6XCd6aNdg/s1600/luna_getting_strangled.jpg)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions. [Start here](http://php.net/manual/en/book.pdo.php) — RiggsFolly, Sep 22 '16 at 20:59
You shouldn't be using nested queries like that. They're highly inefficient. You should rewrite as a single `join`ed query. You already have joins in the second one, so expand those out to cover the parent/child queries as well. — Marc B, Sep 22 '16 at 21:03
Dont use the `@` error silencer. If you have errors, first display then and second Fix Them — RiggsFolly, Sep 22 '16 at 21:04
@Mark B, May u help me in this? as I don't know much about it, If u little expand the code as u suggested I will be thankful to you, I am still trying to get the right result. Thanks! — Arvind, Sep 22 '16 at 21:06
Your first query and its associated while loop will only remember the LAST row of the result set as you load a scalar variable `$es_touring_city = $row5['es_touring_city'];` 1 to many times. Which will only keep the last rows information — RiggsFolly, Sep 22 '16 at 21:08
You dont appear to terminate your second while loop i.e. no `}` — RiggsFolly, Sep 22 '16 at 21:09
@Mark B, Basically the first query giving four city name, and i want to display all four city in different row with four city as heading name and there content from second query.. — Arvind, Sep 22 '16 at 21:10
I find it difficult to believe this produces anything other than errors — RiggsFolly, Sep 22 '16 at 21:10
Add `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); to the top of your script after the ` — RiggsFolly, Sep 22 '16 at 21:12
@Arvind If you want to do that, the second query should be inside the first `while()` loop. You're not doing the second query until after you've finished the whole loop, and you overwrite `$es_touring_city` each time through the loop. — Barmar, Sep 22 '16 at 21:29
You don't need the first query at all. Just take the `WHERE` clause out of the second query and you'll get the information for all cities. Add `ORDER BY `es_touring_city` to get all the rows for the same city together, and then use the answer in the duplicate question to display the heading for each city. — Barmar, Sep 22 '16 at 21:32
@Barmar, Thanks for reply, I tried both way, when i tried with first one as u said, Its just showing 2 result, as i wanted them to show each row for one city, under each city i can add images with details of members. — Arvind, Sep 22 '16 at 21:46
@Barmar and when i tried with second all 4 city content coming same row, i mean one by one. no 4 row when displaying it — Arvind, Sep 22 '16 at 21:47
You need to start a new row and set `$counter` back to `0` whenever the city changes. — Barmar, Sep 22 '16 at 21:49
@Barmar May u make some changes in code and paste here, as i tried still facing problem, if u can, Thanks! — Arvind, Sep 22 '16 at 22:10
I gave you a link to another question that shows how to do it. If you can't generalize from examples like that, you'll never make it as a programmer. You can't expect people to do your work for you for free all the time. — Barmar, Sep 22 '16 at 22:15
When I closed it as a duplicate, there was a link to the other question that showed how to do it. — Barmar, Sep 22 '16 at 22:36

score 0 · Accepted Answer · answered Sep 22 '16 at 22:33

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
}

@barmer, Thank you so much, it seems working fine, I have edited and its working as i wanted, Only one problem, As every member uploads there near about 20 photos with same es_id, and first image always display on screen, not any images, so if just that part guide me so it will everything done! — Arvind, Sep 22 '16 at 23:37
@barmer, Thank you so much again I have find the solution for images too, Thank you so much! — Arvind, Sep 22 '16 at 23:48
Your original query just selected an image at random. I needed to select one image, so I arbitrarily picked `MAX(image)`. — Barmar, Sep 23 '16 at 00:57

PHP loop query not working

1 Answers1