MySQL query to generate array, sort by field

[pixx66]

I have a MySQL table with a list of albums and there is a field called "views" with the number of views each album has received. I'm looking to generate an array of all the albums in the table and sort the array by the number of views (descending). I have a list of functions defined in a ContentController.php file. I created a new function called build_albumlist, which I've pasted below. The function "get_ip_log" already exists and works, and I used it as a template to create the "build_albumlist" function:

 

 

 

 

 

  public function build_albumlist(){

      return $this->select_raw("SELECT * FROM albums WHERE deleted = '0' ORDER BY views DESC",array(),'all');

  }

 

public function get_ip_log(){

      return $this->select_raw("SELECT * FROM sessions ORDER BY ID DESC",array(),'all');

  }

 

 

When I use the function, I get this warning:

 

 

Warning: mysql_real_escape_string() expects parameter 1 to be string, array given inC:\xampp\htdocs\Controllers\DBController.php on line 10

The "select_raw" function that I used in "build_albumlist" is defined in the DBController.php file, and is defined as below:

private function clean_array($params){

      $final=array();

      foreach($params as $param){

        $final[]=mysql_real_escape_string($param);

      }

      return $final;

  }

  public function select_raw($query,$params,$type=''){

      $query=str_replace("?","'%s'",$query);

      $final_query= call_user_func_array('sprintf', array_merge((array)$query, $this->clean_array($params)));

      if($type==''){

        $result=mysql_query($final_query) or die(mysql_error());

        return mysql_fetch_assoc($result);

      }

      elseif($type=='all'){

        $result=mysql_query($final_query) or die(mysql_error());

        $final=array();

        while($row=mysql_fetch_assoc($result)){

            $final[]=$row;

        }

        return $final;

      }

 

 

Does anyone know why the "build_albumlist" function is generating this warning, while the "get_ip_log" is not? Any help would be great, as I am obviously pretty new to this.

[Psycho]

private function clean_array($params){
      $final=array();
      foreach($params as $param){
         $final[]=mysql_real_escape_string($param);
      }
      return $final;
   }

 

The function has a parameter names $params and you are trying to use mysql_real_escape_string on the undefined variable $param.

 

Also, you might want to look into the function array_map() instead of building functionality that already exists in PHP.

[pixx66]

Thank you for the quick response.

 

I don't understand though why $param is defined when using "get_ip_log" and undefined when using "build_albumlist." They are essentially doing the same thing.

 

I am looking into array_map(). How would you recommend I use this? From my limited understanding, I would still need to generate the array of albums, which is where the problem is arising.

[Psycho]

Sorry, been a long day. I misread that code. It has nothing to do with the variable name. The problem must be with the value of one or more of the elements in the array. One of them must be a value that is not a string. Are you passing a multidimensional array perhaps? Try using a var_dump($params) on the first line of that method to verify the exact contents of the array.

 

Regarding the use of array_map. The code you have is basically doing the exact same thing that the function array_map() already does - except it is hard coded for mysql_real_escape_string(). So, instead of doing this:

private function clean_array($params){
      $final=array();
      foreach($params as $param){
         $final[]=mysql_real_escape_string($param);
      }
      return $final;
   }

 

You could just use this

private function clean_array($params)
{
    return array_map('mysql_real_escape_string', $params);
}

 

However, that would still produce the same error until you fix the original problem.

[pixx66]

Thank you for the last post.

 

I used the var_dump($params) you suggested and it produced the below. It makes sense that this is producing the warning, as I only should have an array of the IDs assigned to the albums, not all of the album data. THANK YOU FOR YOUR HELP!

 

array(0) { } array(1) { [0]=> array(12) { ["ID"]=> string(2) "33" ["name"]=> string(31) "Whiskey Stills & Sleeping Pills" ["description"]=> string(0) "" ["image"]=> string(2) "19" ["playlist"]=> string(2) "14" ["artist"]=> string(2) "24" ["added"]=> string(10) "1330114154" ["songs"]=> string(0) "" ["deleted"]=> string(1) "0" ["views"]=> string(3) "933" ["file"]=> string(2) "32" ["downloads"]=> string(2) "27" } } array(1) { [0]=> NULL } array(1) { [0]=> NULL }

 

array(1) { [0]=> array(12) { ["ID"]=> string(2) "26" ["name"]=> string(12) "Great Caesar" ["description"]=> string(0) "" ["image"]=> string(2) "16" ["playlist"]=> string(1) "7" ["artist"]=> string(2) "19" ["added"]=> string(10) "1327878813" ["songs"]=> string(0) "" ["deleted"]=> string(1) "0" ["views"]=> string(3) "932" ["file"]=> string(2) "31" ["downloads"]=> string(1) "2" } } array(1) { [0]=> NULL } array(1) { [0]=> NULL }

 

 

[pixx66]

Actually, there is still an issue with this. When I change the query to get just the "ID" for the album, it produces an array for each ID rather than a string with just the ID. The var_dump produces the text below:

 

 

array(0) { } array(1) { [0]=> array(1) { ["ID"]=> string(2) "33" } } array(1) { [0]=> NULL } array(1) { [0]=> NULL }[/size]

public function build_albumlist(){

      return $this->select_raw("SELECT ID FROM albums WHERE deleted = '0' ORDER BY views DESC",array(),'all');

  }

 

 

How do I make it query the ID number and make a string with the number rather than an array with the ID inside of it?

[Psycho]

If you are using one of the mysql_fetch functions they always return an array - even if there was only one field in the result list. So, if you are building your array using something like

         $result=mysql_query($final_query) or die(mysql_error());
         $final=array();
         while($row=mysql_fetch_assoc($result)){
            $final[]=$row;
         }

 

Then it will always create a mufti-dimensional array. It's right there in the very first line of the manual for mysql_fetch_assoc():

Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead

 

But, you first stated you were putting the results into an array so you could sort it by views. Why are you not sorting by views in the query. Why do you even need to create the array?