bahaa Posted April 8, 2012 Share Posted April 8, 2012 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 More sharing options...
masterlayouts Posted April 9, 2012 Share Posted April 9, 2012 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; Link to comment Share on other sites More sharing options...
bahaa Posted April 9, 2012 Author Share Posted April 9, 2012 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 More sharing options...
Larry Posted April 9, 2012 Share Posted April 9, 2012 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 More sharing options...
bahaa Posted April 9, 2012 Author Share Posted April 9, 2012 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 More sharing options...
bahaa Posted April 9, 2012 Author Share Posted April 9, 2012 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 More sharing options...
Larry Posted April 9, 2012 Share Posted April 9, 2012 You just check if the user selected to sort the output and append the appropriate clause to the query. Link to comment Share on other sites More sharing options...
bahaa Posted April 9, 2012 Author Share Posted April 9, 2012 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 More sharing options...
Recommended Posts