Jump to content
Larry Ullman's Book Forums

How To Fetch Mysqli Prepared Statement Out Side A Function


Recommended Posts

Hello,

 

 

I recently started using the mysqli prepared statement and I am not so good with them yet.

 

I have a mysqli prepared statement inside a function and it works fine but my problem is that I don't know how to fetch a mysqli prepared statement out side the function.

 

here is my function

 

 

function GetCountriesList()

{

global $mysqli;

 

//Build sql query

$sql ="SELECT CountryID, Name FROM country";

 

//Prepare the stmt

$stmt = $mysqli->prepare($sql);

 

//execute the stmt

$stmt->execute();

 

//store the result

$stmt->store_result();

 

//bind the result

$stmt->bind_result($CountryID, $Country);

$select= "<select name=\"cboCountry\">";

$select .="<option value=\"0\">الدولة</option>";

//fetch the result

while($stmt->fetch() )

{

$select .= "<option value=\"{$CountryID}\">{$Country}</option>";

}

$select .="</select>";

return $select;

}

 

 

How do I fetch the result out side the function ?

 

 

PHP/5.3.4

MySQL client version: mysqlnd 5.0.7-

Link to comment
Share on other sites

if the code you wrote it's correct than the function will return your $select, so I guess what you want is to display it in which case it should be as simple as
echo $select;

 

My function works, but what I want the fetching to happen out of the function not inside.

In the one above the fetching is happening inside the function and I call it on my page to dispay the drop down.

Link to comment
Share on other sites

Before I answer the question, I'd just like to say that I think this is a dubious approach: to execute the query within a function (requiring a global variable) but fetch the results outside of the function. That's just not a good use of a function at all, as its functionality is too tightly coupled to external code. You should either make it all internal to the function (as you have) or get rid of the function entirely.

Link to comment
Share on other sites

Thanks Larry

 

I have another question.

I have a page that display data from the database and I want to make sorting options by visibility and country.

 

How do I bind an optional parameter to the prepared statement ?

 

 

I know how to constrcut the the sql query but my problem is with the binding.

 

Here is my statement

 

 

function GetCategories()

{

global $mysqli;

 

$sql ="SELECT CategoryID, category.Title, Visible, Ordering, category_position.Title, CreationDate,

CreatedBy, LastEdited, LastEditedBy

FROM category

LEFT JOIN category_position USING(PositionID)

LEFT JOIN country_category USING(CategoryID)

ORDER BY Ordering ";

 

 

//prepare the stmt

$stmt = $mysqli->prepare($sql);

 

 

//execute the stmt

$stmt->execute();

 

//Store the result

$stmt->store_result();

 

//Bind the result to variables

$stmt->bind_result($CategoryID, $Title, $Visible, $Ordering, $Position, $CreationDate,

$CreatedBy, $LastEdited, $LastEditedBy);

 

//Get numRows

if($stmt->num_rows >0 )

{

//Fetch the array

while($stmt->fetch())

{?>

 

<tr>

<td><input type="checkbox" name="chkCategoryID[]" class="chk" value="<?php echo $CategoryID; ?>"/></td>

<td><?php echo $Title ;?></td>

<td><?php echo $Position ; ?></td>

<td>

<?php

if($Visible ==1)

{

echo '<img src="../images/icon-16-published.gif" width="16" height="16" alt="منشور" />';

}

else

{

echo '<img src="../images/icon-16-unPublished.gif" width="16" height="16" alt="منشور" />';

}

?>

</td>

<td><input type="text" name="txtOrdering" value="<?php echo $Ordering; ?>"/></td>

<td><?php echo $CreationDate; ?></td>

<td><?php echo $LastEdited; ?></td>

<td><?php echo GetUserByID($CreatedBy); ?></td>

<td><?php echo GetUserByID($LastEditedBy); ?></td>

</tr>

<?php }

}

 

//Close connection

$stmt->close();

 

//return the result

return $stmt;

 

}

Link to comment
Share on other sites

My sql query would be like this

 

$sql ="SELECT CategoryID, category.Title, Visible, Ordering, category_position.Title, CreationDate,

CreatedBy, LastEdited, LastEditedBy

FROM category

LEFT JOIN category_position USING(PositionID)

LEFT JOIN country_category USING(CategoryID)";

if($Visible ==1 OR $Visible ==1)

{

$sql .=" WHERE Visible = ? ";

}

$sql .=" ORDER BY Ordering ";

 

How do I do the binding if they user did not choose to sort the outpout ?

Link to comment
Share on other sites

You just check if the user selected to sort the output and append the appropriate clause to the query.

 

I know how to do the query but my problem is with this

 

$stmt->bind_param();

 

the number of variable and data type to bind it to the code above depend on the user, so I am not sure how many variable will be used.

How do I do it?

Link to comment
Share on other sites

 Share

×
×
  • Create New...