0

is there a more efficient way of doing the following?

$total_a = mysql_query("SELECT `id` FROM `table` WHERE `this` = 'that'");
$total_b = mysql_num_rows($total_a);

if(!$total_b)
{
    echo 'no results';
}
else
{
    $a = mysql_query("SELECT `id`, `time` FROM `table` WHERE `this` = 'that' ORDER BY `time` DESC");
    while($b = mysql_fetch_assoc($a))
    {
        echo $b['id'].'-'.$b['time'].'<br />';
    }
}

there is no other way around than using two queries for this, is there?

Improve this question

4 Answers 4

Reset to default
2

You're retrieve the same thing twice now, right? If some data exists according to query 1, retrieve that data again with query 2 and display it. Why not simply use the second query?

$sql = "SELECT id, time FROM table WHERE this = 'that' ORDER BY time DESC";
$res = mysql_query($sql);
if (mysql_num_rows($res)) {
  while ($b = ...) {
    ...
  }
} else {
  echo 'no results';
}
Improve this answer
1

You should be able to reuse the query like so:

$result = mysql_query("SELECT `id`, `time` FROM `table` WHERE `this` = 'that' ORDER BY `time` DESC");
$num_rows = mysql_num_rows($result);

if(!$num_rows)
{
    echo 'no results';
}
else
{
    while($row = mysql_fetch_assoc($result))
    {
        echo $row['id'].'-'.$row['time'].'<br />';
    }
}
Improve this answer
0

Fundamentally they are the same query are they not?!

Why can't you do:

$sql = "SELECT `id`, `time` FROM `table` WHERE `this` = 'that' ORDER BY `time` DESC";
$result = mysql_query($sql);

if(mysql_num_rows($result)){
    while($b = mysql_fetch_array($result))
    {
        echo $b['id'].'-'.$b['time'].'<br />';
    }
}
else{
  // no rows
}
Improve this answer
-1

just use:

$a = mysql_query("SELECT `id`, `time` FROM `table` WHERE `this` = 'that' ORDER BY `time` DESC");
while($b = mysql_fetch_assoc($a))
{
    echo $b['id'].'-'.$b['time'].'<br />';
}

why counting?

you only should count the possible rows if you do something like

if($count){
echo "starting the stuff";
$a = mysql_query("SELECT `id`, `time` FROM `table` WHERE `this` = 'that' ORDER BY `time` DESC");
while($b = mysql_fetch_assoc($a))
{
    echo $b['id'].'-'.$b['time'].'<br />';
}
echo "ending the stuff";
}
Improve this answer
3
  • the count is needed only if the "starting the stuff" and the "ending the stuff" is required to show only in case the result set would not be empty
    – ITroubs
    Commented Oct 5, 2010 at 9:18
  • disagree - I think that OP wants a viable way of returning a different output if there are no results returned. Counting the rows is perfectly fine.
    – Thomas Clayson
    Commented Oct 5, 2010 at 9:20
  • i never said it wasn't fine to count the rows. if it realy matters how many rows he has and if the resulting rowset isn't empty then the count shurely makes sense
    – ITroubs
    Commented Oct 5, 2010 at 9:22