Displaying multiple results from nested query?

Question

I am writing an appointment book type thing, and i have a table for just possible times, so my first query is to list all of the times, then my 2nd table is scheduled appointments, which lists inside the table the appointment if its time and date match the time and date pulled from the first query.

What i cannot figure out is if 2 entries on the 2nd query match the criteria to display both results at the same time.

Heres my setup:

$query = mysql_query("SELECT * FROM times", $db);
while ($row = mysql_fetch_array($query)){
    $time = $row[time];
    $query2 = mysql_query("SELECT * FROM appts WHERE time = '$time' AND date = '$date' ", $db);
    $apt = mysql_fetch_array($query2);
    $aptid = $apt[id];
    echo "<tr><td>";
    echo date('h:i A', strtotime($row[time]));
    echo "</td><td>$apt[name]</td></td><td>$apt[phone]</td><td>$apt[phone2]</td><td>$apt[src]</td><td>$apt[coach]</td><td>$apt[comments]</td><td>";
    if($aptid != ''){
        echo "<a href='index.php?date=$date&apptid=$aptid&action=delete'>Delete</a> &nbsp; <a href='index.php?date=$date&apptid=$aptid&action=x'>X</a> &nbsp; <a href='index.php?date=$date&apptid=$aptid&action=ns'>NS</a>";
    }
    echo "</td></tr>";
}

E.G.: 2 appointments set for 9am, its only displaying the first one. See screenshot

enter image description here

Could you post an excerpt of the appts table? It's hard to check the query without the structure/content. — Matthew FitzGerald-Chamberlain
– Matthew FitzGerald-Chamberlain, Commented Jun 24, 2013 at 15:03
Are you expecting no more than one appointment for one possible time? — jtomaszk
– jtomaszk, Commented Jun 24, 2013 at 15:04
MySQL isn't built to natively support nested resultsets. You'll need to implement your own way of iterating over appointments. — Harold
– Harold, Commented Jun 24, 2013 at 15:05
@MatthewFitzGerald-Chamberlain heres what the appts table looks like i.imgur.com/hHKVvRU.png — robz228
– robz228, Commented Jun 24, 2013 at 15:06

Amar Banerjee · Accepted Answer · 2013-06-24 15:07:11Z

5

Modify it to

$query = mysql_query("SELECT * FROM times", $db);
while ($row = mysql_fetch_array($query)){
    $time = $row[time];
    $query2 = mysql_query("SELECT * FROM appts WHERE time = '$time' AND date = '$date' ", $db);

    while($apt = mysql_fetch_array($query2))
   {
    $aptid = $apt[id];
    echo "<tr><td>";
    echo date('h:i A', strtotime($row[time]));
    echo "</td><td>$apt[name]</td></td><td>$apt[phone]</td><td>$apt[phone2]</td><td>$apt[src]</td><td>$apt[coach]</td><td>$apt[comments]</td><td>";
    if($aptid != ''){
        echo "<a href='index.php?date=$date&apptid=$aptid&action=delete'>Delete</a> &nbsp; <a href='index.php?date=$date&apptid=$aptid&action=x'>X</a> &nbsp; <a href='index.php?date=$date&apptid=$aptid&action=ns'>NS</a>";
    }
    echo "</td></tr>";
 }
}

You have to run another nested loop inside.

answered Jun 24, 2013 at 15:07

Amar Banerjee

5,0125 gold badges38 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

robz228 Over a year ago

Doesnt do exactly what i need but definitely how i need to do it. Thank you, i hopefully can figure it out from here!

immulatin · Accepted Answer · 2013-06-24 15:05:10Z

mysql_fetch_array will only fetch one row of data. From the php manual:

mysql_fetch_array — Fetch a result row as an associative array, a numeric array, or both

That means you need to loop to get multiple appointments, just like the first loop:

while($apt = mysql_fetch_array($query2)) {
    $aptid = $apt[id];
    echo "<tr><td>";
    echo date('h:i A', strtotime($row[time]));
    echo "</td><td>$apt[name]</td></td><td>$apt[phone]</td><td>$apt[phone2]</td><td>$apt[src]</td><td>$apt[coach]</td><td>$apt[comments]</td><td>";
    if($aptid != ''){
        echo "<a href='index.php?date=$date&apptid=$aptid&action=delete'>Delete</a> &nbsp; <a href='index.php?date=$date&apptid=$aptid&action=x'>X</a> &nbsp; <a href='index.php?date=$date&apptid=$aptid&action=ns'>NS</a>";
    }
    echo "</td></tr>";
}

Collectives™ on Stack Overflow

Displaying multiple results from nested query?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related