0

My project is a webapp that reads a database and sends emails. I inserted the database today and with the "example" one was working okay but did not have so many rows as the real one.

My problem is that something(database side) in one of the queries that I do is making the $results empty.

$selected_category = (isset($_POST['category'])) ? $_POST['category'] : null;
$group = (isset($_POST['group'])) ? $_POST['group'] : null;

if ($selected_category) {

    if ($selected_category && $group) {
        //para se for selecionado o grupo

        if ($selected_category == "-1") {
            //para todas as categorias juntas
            if ($group == "-1") {
                $results = DB::query("SELECT category, name, email FROM clients LIMIT 100");
            } else {
                $results = DB::query("SELECT category, name, email FROM clients LIMIT %i, 100",$group );
            }

        } else {
            //para as categorias
            if ($group == "-1") {
                $results = DB::query("SELECT category, name, email FROM clients WHERE category = %i LIMIT 100", $selected_category );
            } else {
                $results = DB::query("SELECT category, name, email FROM clients WHERE category = %i LIMIT %i, 100", $selected_category, $group );
            }
        }

error_log(mysql_error());
        echo json_encode($results);
    } else {
        //para se for selecionada a categoria
        //se for -1 manda todas as categorias
        if ($selected_category == "-1") {
            //MAL
            $count = DB::query("SELECT COUNT(category) AS cat FROM clients");
            $count["category"] = "all";
            echo json_encode($count);
            //$results = DB::query("SELECT category, name, email FROM clients");
        } else {
            $count = DB::query("SELECT COUNT(category) AS cat FROM clients WHERE category = %i", $selected_category );
            //$results = DB::query("SELECT category, name, email FROM clients WHERE category = %i", $selected_category );
            $cat = DB::query("SELECT name FROM categories WHERE super_id = %i", $selected_category);
            $count["category"] = $cat;
            echo json_encode($count);
        }


    }

    //echo json_encode($results);
}

This is the part where I think the error is. Notice that the variables I change are the $category and to select the limit in the query ($group).

A practicable example is when I make $selected_category = -1 (that means I want all the categories) and I make $group = -1 (the first 100 rows) or $group = 100 (the 100 rows after the 100º row), mysql returns the correct values. But when I make $group = 200 (to select the 100 rows after the row 200º), mysql does not return anything and I get and empty array.

Do any of you have any idea that could be corrupting my data? I have trimmed it already and it did nothing. I'm really stuck and I need to finish this untill next friday or I don't get paid!

Thank you all! And hope someone can help me.

EDIT: So, I have already did: 1 - changed the database to utf-8 following this answer; 2 - changed the two lines that say latin1 in the meekrodb file to utf8; 3 - Added the ut8_encode php function to the $results 

$json = utf8_encode(json_encode( $results )); 
        if ( $json === false ) echo "ERROR: " . json_last_error_msg(); else echo $json;

Now I no longer have the utf_8 json error, but $results still come empty.

EDIT2 I passed all of this to the production server and guess what.... it works. Somethings I can't understand yet. Thank you all that spent time with my problem! :)

Community
  • 1
  • 1
spkkya
  • 21
  • 8
  • What does `SELECT COUNT(*) FROM clients` say? – Kenney Nov 11 '15 at 17:49
  • What database extension are you using in the `DB` class – RiggsFolly Nov 11 '15 at 17:51
  • It says 510. Maybe something in the data is corrupted in some rows but I can't know what.. – spkkya Nov 11 '15 at 17:51
  • What do you think `%i` is supposed to be doing? Do you mean `$i`?? Although I dont see where `$i` is being set to anything – RiggsFolly Nov 11 '15 at 17:53
  • Hm. Could you print the query just before you execute it, just to be sure it is exactly `SELECT category, name, email FROM clients LIMIT 200, 100`? – Kenney Nov 11 '15 at 17:53
  • I am using meekrodb so I guess it is the way to save that space for the variable that comes after. I don't know where I set the database extension but it works in most of the cases.. Just in a few it doesn't.. Thanks for giving some thought on it guys – spkkya Nov 11 '15 at 17:55
  • I dont recognise a `DB::query()` function that takes one or two extra parameters, so you had better show us the DB Class as well, or at least the `query()` method – RiggsFolly Nov 11 '15 at 17:56
  • @RiggsFolly [see here](http://meekro.com/docs.php#anchor_query) – Kenney Nov 11 '15 at 17:56
  • @Kenny Ah thanks. Not come across that one before. So it looks like MYSQLI made to look like PDO. Wow thats a productive concept – RiggsFolly Nov 11 '15 at 17:58
  • I don't reaaly know how to print the query, but it has to be ok cause you remembered me to var_dump($results) and I console.log(response) from AJAX request and I got the results I want. But if I json_encode($results), they disappear.. – spkkya Nov 11 '15 at 18:02
  • Ah, there might be an encoding error. Test the result value of [json_encode](http://php.net/manual/en/function.json-encode.php) for `false`, and use [json_last_error_msg](http://php.net/manual/en/function.json-last-error-msg.php) if it is. – Kenney Nov 11 '15 at 18:07
  • Could you elaborate on how to do it? Thank you very much! – spkkya Nov 11 '15 at 18:10
  • `$json = json_encode( $results ); if ( $json === false ) echo "ERROR: " . json_last_error_msg(); else echo $results`. (I added links to the documentation, you should check them out). – Kenney Nov 11 '15 at 18:11
  • ERROR: Malformed UTF-8 characters, possibly incorrectly encoded. I'm starting to use ajax, I didn't know about this. Thanks for the help! Could you lead me on what to do with this error? – spkkya Nov 11 '15 at 18:14
  • You're welcome, and sure; if you can't [set the client connection encoding for mysql](http://stackoverflow.com/questions/10552528/pdo-equivalent-of-mysql-client-encoding) because of meekro, you can use [mb_convert_encoding](http://nl1.php.net/manual/en/function.mb-convert-encoding.php) and [mb_detect_encoding](http://nl1.php.net/manual/en/function.mb-detect-encoding.php) if you don't know the database encoding. You'll probably have to `array_map` the results to fix certain columns. – Kenney Nov 11 '15 at 18:19
  • Or, [convert the db to utf8](http://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8). – Kenney Nov 11 '15 at 18:21
  • @Kenney I will try that as soon as possible and post the result. Once again, thank you very much! – spkkya Nov 11 '15 at 18:57
  • I have converted db to utf-8 through [this](http://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8) answer. Still the same error... Any suggestion? – spkkya Nov 11 '15 at 22:48

0 Answers0