Results 1 to 6 of 6

Thread: Odd behavior - SELECT - Missing row

  1. #1

    Join Date
    Sep 2002
    Location
    Auckland
    Posts
    1,356
    Rep Power
    17

    Odd behavior - SELECT - Missing row

    Hello.

    When i run this line in MYSQL PHPadmin, i get TWO results (two types of data & the count of each).

    Code:
    SELECT Code, COUNT( * ) FROM NWtopics GROUP BY Code;
    HOWEVER, when i run this in my php script, i only get ONE row. not two.

    I googled & found some cases refer to the MYSQL table needing to be repaired. I did that & still same result.

    Ive got 91 rows in total.. - Not many.

  2. #2
    Registered
    Join Date
    Aug 2015
    Location
    United States
    Posts
    1
    Rep Power
    0

    Odd behavior - SELECT - Missing row

    Try This

    HTML Code:
    SELECT COUNT(Code) FROM NWtopics GROUP BY Code;

  3. #3

    Join Date
    Sep 2002
    Location
    Auckland
    Posts
    1,356
    Rep Power
    17
    Hmmm

    i got better results from my 1st code.

    hi1

    Did query 'SELECT COUNT(Code) FROM NWtopics GROUP BY Code;'
    count_topic 1
    Array
    (
    [0] => 16
    [COUNT(Code)] => 16
    )
    Did query 'SELECT Code, COUNT( * ) FROM NWtopics GROUP BY Code;'
    count_topic 1
    Array
    (
    [0] => comet
    [Code] => comet
    [1] => 16
    [COUNT( * )] => 16
    )

  4. #4

    Join Date
    Sep 2002
    Location
    Auckland
    Posts
    1,356
    Rep Power
    17
    Well.. I'm not sure what happened with MYSQL - none of the SQL commands worked.

    But switched to MYSQLI, & found this working script. I got all 3 results i am expecting.

    Code:
    $user_arr=array();
    
    $mysqli = new mysqli("SERVER", "USER", "PASSWORD", "DATABASE");
    
    if ($log > 0) {echo " GOT HERE 1a <br />\n";}
    
    /* check connection */
    if ($mysqli->connect_errno) {
        printf("Connect failed: %s\n", $mysqli->connect_error);
        exit();
    }
    
    if ($log > 0) {echo " GOT HERE 1b <br />\n";}
    $query="SELECT Code, COUNT(*) FROM NWtopics GROUP BY Code;";
    if ($result = $mysqli->query($query)) {
        printf("Select returned %d rows.\n", $result->num_rows);
    }
    
    if($result){
         // Cycle through results
        while ($row = $result->fetch_object()){
            $user_arr[] = $row;
        }
    
        /* free result set */
        $result->close();
    }
    
    echo "<pre>\n";
    print_r($user_arr);
    echo "</pre>\n";

  5. #5

    Join Date
    Sep 2002
    Location
    Auckland
    Posts
    1,356
    Rep Power
    17
    This is my weekend project - a simple website page has turned into a project all on its self.

    (it should only take me an hour to complete in full, & its taken a week JUST to get the data out of the database)

    I am getting the data i need, however its an "OBJECT" - not plain array...

    I basically need a normal / regular array that i can do a "foreach" command on, but i cant use that on an object


    the output i'm getting (prinr_r on the object)


    Array
    (
    [0] => stdClass Object
    (
    [Code] => comet
    [COUNT(*)] => 16
    )

    [1] => stdClass Object
    (
    [Code] => craft
    [COUNT(*)] => 75
    )

    [2] => stdClass Object
    (
    [Code] => location
    [COUNT(*)] => 13
    )

    )

  6. #6

    Join Date
    Sep 2002
    Location
    Auckland
    Posts
    1,356
    Rep Power
    17
    YAY - Ive fixed it.. a few days of crash-course in OBJECTS

    The key piece is here - $user_arr[$x]["Code"] = $row->Code;

    You need to know the name of the field in advance & assign it - You cant randomly get data into an array without specifically mentioning the field/column name.
    (if anyone can advise otherwise, let me know)

    Code:
    $mysqli = new mysqli("SERVER", "USER", "PASSWORD", "database");
    
    /* check connection */
    if ($mysqli->connect_errno) {
        printf("Connect failed: %s\n", $mysqli->connect_error);
        exit();
    }
    
    $query="SELECT Code, COUNT(*) as counter FROM NWtopics GROUP BY Code;";
    if ($result = $mysqli->query($query)) {
        printf("Select returned %d rows.\n", $result->num_rows);
    }
    
    $user_arr=array();
    if($result){
         // Cycle through results
         $x=11000;
        while ($row = $result->fetch_object()){
     $code=$row->Code;
     $counter=$row->counter;
            $user_arr["$code"] = $counter;
        }
    
        /* free result set */
        $result->close();
    }
    
    foreach($user_arr as $ab => $ac)
    {
    echo "USERARR1  '$ab' '$ac'<br />\n";
    }
    EDIT: changed while loop - Efficient storage of new array

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •