Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : Function with Dynamic MySQL Query - HELP

[PaddysPlace]

Hey guys.

First off, here's the function:

PHP Code:

##### Begin function get_cat($function_id)
// Selects all categories from database classified by ID
function get_cat($function_id)
{
  global $DB,$selected_cat,$selected_cats,$total_cats;
  db_connect();

 if($function_id != '')
 {
   $select_cat = "SELECT * FROM categories WHERE ID=$function_id";
   $selected_cat= mysql_query($select_cat) or die ("Couldn't select categories");
 }
 else
 {
   $select_cats = "SELECT * FROM categories";
   $selected_cats= mysql_query($select_cats) or die ("Couldn't select categories");
   $total_cats = mysql_num_rows($selected_cats);
 }
}
// End function get_cat 


As you can see I made it so if there's an ID specified, call it and save row in $selected_cat.

If there is no ID specifed, get them all and put in an array $selected_cats.

Right now I have to call it with:
get_cat($CATID) (To select specific)
get_cat('') (To select all)

Anyone know a better way to clean up my code?

Meh, I also feel so behind because everyone is using a DB class and I've been trying to read up on it but just can't get it down so I have to use old fashioned settings... What's the advantage?

Regards,
Patrick

AIM: WrldsGreatestOne (If you can help/explain how to optimize my database functions, please help!)

[Burhan]

If you don't want to pass an argument, don't pass ''. Just call the function.

Secondly, you function won't return any results because you are never fetching them. mysql_query() just runs the query and returns a handle. You then have to fetch your results.

Here is another way to write your same function

PHP Code:

//Set up a default value for the parameter.
function get_cat($function_id = NULL)
{
    global $DB,$selected_cat,$selected_cats,$total_cats;
  db_connect(); 

  $sql = "SELECT * FROM categories ";
  if (isset($function_id)) { $sql .= "WHERE ID = $function_id"; }
   $result = mysql_query($sql);
   if (!$result)
   {
        return mysql_errno()." : ".mysql_error();
    }
     while ($row = mysql_fetch_assoc($result))
    {
        $return[] = $row;
     }
      $return[] = mysql_num_results($result);
      return $return;
}

//Sample call -- all categories

$categories = get_cat(); 

/*Since valid results back are an array,
    we can use this check to see if our function worked or not. Just one way of doing things */
if (!is_array($categories))
{
    echo "Sorry, there was an error<br />";
    echo $categories;
} else { echo "<pre>"; print_r($categoies); echo "</pre>"; } 


Meh, I also feel so behind because everyone is using a DB class and I've been trying to read up on it but just can't get it down so I have to use old fashioned settings... What's the advantage?

Well, the advantage is that you don't have to repeat your code, and you can just start using the database without having to worry about connectiing, fetching, etc. Plus, most DB classes work with more than one type of database, which adds portability to your code.