Using a query twice on the same page

[Square1]

Hi all:

I am calling a dynamic drop box. the think is, I need to run this drop down in 2 separate spots on the same page. I am coding procedurally.

What is the best way to tackle this? I am sure writing it out twice isn't it! Do I make it an include? Thanks!!!

		$sql = "SELECT l_id, Player, Promoter, EventDate FROM Events WHERE (MailOrder = '1' OR AdvanceTix = '1') AND ListingDate =  convert(char(24),getdate(),1) ORDER BY Player, EventDate";

	$stmt = sqlsrv_query($conn, $sql);

	?>	

    <select name="l_id" size="1">
    <?php 
	echo "<option value = '" . $_SESSION["l_id"] . "'>" .$_SESSION["player"] . "  " . $_SESSION["promoter"] . " " . $_GET["eventDate"] . "</option>";	
	while($result = sqlsrv_fetch_array($stmt)){

	$rPlayer			=  	$result["Player"];
	$rPromoter			=	$result["Promoter"];
	$rL_id				=	$result["l_id"];

	if (isset($result["EventDate"])){
		$rEventDate 	= 	date_format($result["EventDate"],"m/d/Y");
	} elseif (isset($_GET["eventDate"])){
		$rEventDate 	=	date_format($_GET["eventDate"],"m/d/Y");
	} else {
		$rEventDate 	= "";
	}		

	echo "<option value = '" . $rL_id . "'>" . $rPlayer . "  " . $rPromoter . "  " . $rEventDate . "</option>";
	}
	?>
	</select>

[dagon]

don't echo the data, store it in a var, then you can echo it out latter in the 2 sports. putting it a function and returning the data may also be nicer (but only call the function once);

[NogDog]

As the "sqlsrv_*" functions you are using are not built-in PHP functions, I'm not sure what your specific capabilities are, but you might want to see if there is some sort of "rewind" or "data_seek" function in that set which will allow you to reuse the query result set by setting the row counter back to zero (the first row). (For example, see [man]mysql_data_seek/man for the MySQL function.)

Alternatively, you could dump the results into an array, then do a foreach() on that array in each place where you need it.

[NogDog]

PS: If the text being output is identical in each case, then Dagon's suggestion probably will be more efficient.

[Square1]

dagon;10978105 wrote:

don't echo the data, store it in a var, then you can echo it out latter in the 2 sports. putting it a function and returning the data may also be nicer (but only call the function once);

Let me make sure I have this straight. Are saying to take the WHOLE DEAL below and turn the whole thing into a var????

    $sql = "SELECT l_id, Player, Promoter, EventDate FROM Events WHERE (MailOrder = '1' OR AdvanceTix = '1') AND ListingDate =  convert(char(24),getdate(),1) ORDER BY Player, EventDate";

    $stmt = sqlsrv_query($conn, $sql);

    ?>    

    <select name="l_id" size="1">
    <?php
      echo "<option value = '" . $_SESSION["l_id"] . "'>" .$_SESSION["player"] . "  " . $_SESSION["promoter"] . " " . $_GET["eventDate"] . "</option>";    
    while($result = sqlsrv_fetch_array($stmt)){

    $rPlayer            =      $result["Player"];
    $rPromoter            =    $result["Promoter"];
    $rL_id                =    $result["l_id"];

    if (isset($result["EventDate"])){
        $rEventDate     =     date_format($result["EventDate"],"m/d/Y");
    } elseif (isset($_GET["eventDate"])){
        $rEventDate     =    date_format($_GET["eventDate"],"m/d/Y");
    } else {
        $rEventDate     = "";
    }        

    echo "<option value = '" . $rL_id . "'>" . $rPlayer . "  " . $rPromoter . "  " . $rEventDate . "</option>";
    }
    ?>
      </select>

[NogDog]

Instead of echoing the option tags in your loop, append them to a variable. Then echo that variable as needed.

<?php
$sql = "SELECT l_id, Player, Promoter, EventDate FROM Events WHERE (MailOrder = '1' OR AdvanceTix = '1') AND ListingDate =  convert(char(24),getdate(),1) ORDER BY Player, EventDate";
$stmt = sqlsrv_query($conn, $sql);
$options = "<option value = '" . $_SESSION["l_id"] . "'>" . $_SESSION["player"] . "  " . $_SESSION["promoter"] . " " . $_GET["eventDate"] . "</option>";
while ($result = sqlsrv_fetch_array($stmt)) {
   $rPlayer = $result["Player"];
   $rPromoter = $result["Promoter"];
   $rL_id = $result["l_id"];
   if (isset($result["EventDate"])) {
      $rEventDate = date_format($result["EventDate"], "m/d/Y");
   } elseif (isset($_GET["eventDate"])) {
      $rEventDate = date_format($_GET["eventDate"], "m/d/Y");
   } else {
      $rEventDate = "";
   }
   $options .= "<option value = '" . $rL_id . "'>" . $rPlayer . "  " . $rPromoter . "  " . $rEventDate . "</option>";
}
?>    

        <select name="l_id" size="1">
        <?php echo $options; ?>
        </select>
<!-- other stuff -->
        <select name="another_one">
        <?php echo $options; ?>
        </select>

[Square1]

Wow...thank you. It works but I am not sure WHY! I am not including the loop in the variable so how come the variable is looping?

[leatherback]

this line is in the loop:

   $options .= "<option value = '" . $rL_id . "'>" . $rPlayer . "  " . $rPromoter . "  " . $rEventDate . "</option>";

and does the meagic: It stores each option so that later you can use the $options variable to recreate the dropdown where you want to.

[Square1]

Is it the ".=" that is allowing it to store each option?

[bradgrafelman]

Square1;10978163 wrote:

Is it the ".=" that is allowing it to store each option?

Yep; learn more about that operator at the following manual page: [man]language.operators.assignment[/man].

[Square1]

Thank you!