Database Query inside a function

Discussion in 'PHP' started by frik.groenewald, Aug 24, 2012.

0
  1. #1
    Flow: Site is loaded and a query populate a table with categories. The categories are hyper linked to a function that will execute a new query based on the selected hyper link. The first step works and the value is passed to the function. After the test echo, nothing happens. My php knowledge is not that advance that I can find the issue, and this is also the first time I have used functions. Any and all help/suggestions will be appreciated. Kind regards,
    PHP:

     
    Last edited: Aug 24, 2012
    frik.groenewald, Aug 24, 2012 IP
  2. DMacedo

    DMacedo Active Member

    Messages:
    4
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    88
    #2
    Your issue is with the connection to MySQL not being sent into the function.

    Notice you're calling mysqli_query($mysqli, $Retrieve) but you're not sending $mysqli into the myQuery function.

    Cheers
     
    DMacedo, Aug 24, 2012 IP
    frik.groenewald likes this.
  3. frik.groenewald

    frik.groenewald Peon

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Thank you, that did the trick. On the same subject (functions) Now that I'm able to execute the query, the second part is to populate a table that sits outside the the function. Any pointers on how to populate the table outside the function?

    This Works:

     function myQuery($cat){
    global $mysqli;
    if (!empty($cat)){
    echo 'we wil search for :'.$cat;//It works fine to this point
    
    $Retrieve = "SELECT Business_Name FROM business WHERE (Business_Cat like '$cat')";
    if ($result1 = mysqli_query($mysqli, $Retrieve)) {

       while ($row = mysqli_fetch_assoc($result1)) {
        echo '<td><a href="?run='.$row["Business_Name"].'">'.$row["Business_Name"].'</a></td>';
        
    }

    /* free result set */
    mysqli_free_result($result);
}
    }
    
}
    PHP:
    This doesn't
    
<?php
require_once ('connection/moot.php');

///// functions /////

function myQuery($cat){
    global $mysqli;
    if (!empty($cat)){
    echo 'we wil search for :'.$cat;//It works fine to this point
    
    $Retrieve = "SELECT Business_Name FROM business WHERE (Business_Cat like '$cat')";
    
    }
    
}




///// START /////
$query ="SELECT * FROM business WHERE Business_MonthAug='1'";
?>
<html>
<body>
<?php
if (isset($_GET['run'])) $linkchoice=$_GET['run'];
else $linkchoice='';

switch($linkchoice){

default :
    $cat=$linkchoice;
    myQuery($cat);
    break;

}

?>

<table>
  <tr>
    <?php

if ($result = mysqli_query($mysqli, $query)) {

       while ($row = mysqli_fetch_assoc($result)) {
        echo '<td><a href="?run='.$row["Business_Cat"].'">'.$row["Business_Cat"].'</a></td>';
        
    }

    /* free result set */
    mysqli_free_result($result);
}

?>
  </tr>
  <tr><?php
  if ($result1 = mysqli_query($mysqli, $Retrieve)) {

       while ($row = mysqli_fetch_assoc($result1)) {
        echo '<td><a href="?run='.$row["Business_Name"].'">'.$row["Business_Name"].'</a></td>';
        
    }

    /* free result set */
    mysqli_free_result($result);
}
?> </tr>
</table>
</body>
</html>
    PHP:
     
    Last edited: Aug 24, 2012
    frik.groenewald, Aug 24, 2012 IP
  4. DMacedo

    DMacedo Active Member

    Messages:
    4
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    88
    #4
    Again, you have to be aware of the context of what's inside and outside the function (and please don't start abusing the global keyword).
    You can't just move text around, without knowing or thinking about what you're doing, and expect it to work... :)

    You just moved the if condition and the loop to the outside of the function, but you left the $Retrieve behind... etc...
    There's a lot on this code that hurts my eyes, I just went ahead and solved your first issue ;)

    <edit>
    To make a constructive answer a bit more complete, you're thinking wrong about the structure: creating functions when they probably aren't needed. And as a major security issue, you're not sanitizing user input; that's a good way to get in trouble quickly.

    I'll give you a quick but more lasting advice: get one of the simple frameworks around and start fresh.

    If you're learning, you might as well learn correctly instead of guessing and follow some sort of structure. It might be a bit more complex than just throwing spaghetti code together, but at least you'll have to think about it...
     
    DMacedo, Aug 24, 2012 IP
  5. frik.groenewald

    frik.groenewald Peon

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Thanks for the advice (sorry for your eyes, I have no formal training). I have it working now (time for your glasses)

    
<?php
require_once ('connection/moot.php');
$test1='';
///// functions /////

function myQuery($cat){
    global $mysqli;
    global $test1;
    if (!empty($cat)){
    echo 'we wil search for :'.$cat;//It works fine to this point
    
    $Retrieve = "SELECT Business_Name FROM business WHERE (Business_Cat like '$cat')";
    $test1=$Retrieve;
    }
    
}

......

<?php
  if (!empty($test1)){
  echo "back down here";
  if ($result1 = mysqli_query($mysqli, $test1)) {

       while ($row = mysqli_fetch_assoc($result1)) {
        echo '<td><a href="?run='.$row["Business_Name"].'">'.$row["Business_Name"].'</a></td>';
        
    }

    /* free result set */
    mysqli_free_result($result1);
}
  }
?>
    PHP:
     
    frik.groenewald, Aug 24, 2012 IP