Results 1 to 8 of 8

Thread: Problem with MAX

  1. #1

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

    Problem with MAX

    Hello.

    I've got afield in my MYSQL table called "reference".

    i *am* able to INSERT into the table - however i am having problems with SELECT MAX.

    Code:
        $mysqli = new mysqli( USERNAME / DATABASE ETC...);
       
        $sql="SELECT MAX(reference) AS maxi FROM Articles";
        
        
    $log.="SQL TEST $sql\n";
    
     $row = mysqli_query($mysqli, $sql);
     
    $log.="START LOOP ROW\nROW = $row\n";
    
    foreach($row as $ab => $ac)
    {
        $log .= "LOOP ROW '$ab' '$ac'\n";
    }
    $log.="END LOOP ROW\n";
    
    // $largestNumber = $row['MAXI'];        
    
    $log.="largestNumber = row['MAXI']; - $largestNumber\n";
    
    $largestNumber++;        
    $log.="largestNumber++;         - $largestNumber\n";
            
        echo "SQL '$sql' <br />\n";
        $log .= "SQL - '$sql'\n";
            $mysqli->close();
    i've added a lot of log reports, & there *IS* a figure of "1" in more than 1 field / row.

    from my log reports, i am NOT getting a max value of 1 when i use select max. i am getting blank results.

    i basically add one / 1 to the max value & then insert it into the table again in a new row. (the table has 3-4 fields)

    0 + 1 = 1

    so now i have a dozxen or so rows with a reference of "1"


    is there a problem with my usage of MAX ?

    there is no result / value in the foreach loop

  2. #2

    Join Date
    Sep 2002
    Location
    Auckland
    Posts
    1,356
    Rep Power
    17
    Hmm I've Changed my script & used the "Explain" ability. I'm still getti9ng blank results.

    Does "EXPLAIN" work on powweb ??

    $sql="SELECT MAX(reference) AS maxi FROM Articles";



    $sql="SELECT COUNT(*) FROM articles.tables WHERE table_schema = '[space]' AND table_name = '[Articles]'";

    $out = mysqli_query($mysqli, "EXPLAIN $sql");

    $log.="SQLOUT =
    ---
    $out\n
    ---
    ";




    $sql = mysqli_query($mysqli, "EXPLAIN SELECT * FROM `Articles` ORDER BY `reference` DESC LIMIT 1");

    $log.="SQL TEST EXPLAIN SELECT * FROM `Articles` ORDER BY `reference` DESC LIMIT 1\n";
    $log.="SQL =
    ---
    $sql\n
    ---
    ";

  3. #3

    Join Date
    Sep 2002
    Location
    Auckland
    Posts
    1,356
    Rep Power
    17
    Question: What is the correct way to have the "results" ofa mYSQLI statement echo'ed / copied into a log ?

    I am trying to debug a SHOW TABLES comand 7 i'm still getting balnk / no results.

    $out = mysqli_query($mysqli, "EXPLAIN SHOW TABLES");

    $log.="SQL SHOW =
    ---
    $out\n
    ---
    ";
    Should $out have all the text / output of the MYSQL statement ? (its blank ). Please advise as i've been trying to figure out this error for hours. But i can't debuga nything if i can't see any results / output.


    PS - the table / database names ARE correct



    EDIT:- I have found this in a diozen or so google results :-

    printf("Errormessage: %s\n", $mysqli->error);
    How do i log / save this result to a variable ? This produces an error :-
    $log.="Errormessage: %s\n", $mysqli->error);
    (it doesn't like the comma, & i've tried using a full stop. but it also produces an error. )

    ERROR:
    $log.="Errormessage: %s\n". $mysqli->error)."\n";

    I've got a logging routine to log the $log result to a file.

  4. #4

    Join Date
    Sep 2002
    Location
    Auckland
    Posts
    1,356
    Rep Power
    17
    still no results. No MYSQLI error file created.

    $out = mysqli_query($mysqli, "EXPLAIN SHOW TABLES");


    if ($mysqli->connect_errno) {

    $df = date("Ymd-H");
    $file = "/home/users/web/b2516/USERNAME/DIRECTORY/htdocs/cron/GENLOG/mysqlerror$df.err";
    $file1 = fopen($file, "a");
    printf($file1, "Connect failed: %s\n", $mysqli->connect_error);
    // fprintf($file1,"There are %u million bicycles in %s.",$number,$str);

    $log .= "SQL SHOW ERROR = Connect failed: %s\n" . $mysqli->connect_error . "\n";

    return;
    exit();
    }

    $log .= "SQL SHOW =
    ---
    $out\n
    ---
    ";
    $out is still blank

  5. #5

    Join Date
    Sep 2002
    Location
    Auckland
    Posts
    1,356
    Rep Power
    17
    Something weird is going on here :-


    Code:
        $log .= "CHECK PING SHOW\n";
        
    /* check if server is alive */
    if ($mysqli->ping()) {
        $log."Our connection is ok!\n";
    }
    else 
    {
        $log."Our connection is NOT ok ----------\n";
    }
        $log .= "END PING SHOW\n";

    I am getting this ion my log report ;-
    CHECK PING SHOW
    END PING SHOW
    but i'm not getting the connection or not connection error.

    how can i NOT be "connected" and NOT "not connected" at the same time ?

  6. #6
    Rick
    Join Date
    May 2002
    Location
    Minneapolis, MN
    Posts
    1,753
    Rep Power
    19
    I'd suspect your $mysqli object isn't connecting to the database/server. Try inserting:
    Code:
    if ($mysqli->connect_errno) {
        die('Connect Error: ' . $mysqli->connect_errno);
    }
    after the $mysqli = new mysqli() call.
    Rick Trethewey

  7. #7

    Join Date
    Sep 2002
    Location
    Auckland
    Posts
    1,356
    Rep Power
    17
    i am connected to the server (ONE of several) SHOW TABLES fuctions is working - "tablelists".

    I'll show my full code below, & then the Output below..

    Ps - this is a scheduled task - not online - does that matter ? I'm off out for the day but I'll try again & tweak the code when i come back.

    1) my ultimate goal is just to get the max number of a field / column.
    2) The field in MYSQL is set to BIGINT (not varchar etc..)


    Code:
    function Check_articles_for_max_number()
    {
        global $log;
        $log .= "\n------\nCheck_articles_for_max_number()\n";
        
        $mysqli = new mysqli(server, User, Password etc...);
     
        
    //     $out = mysqli_query($mysqli, "EXPLAIN SHOW TABLES");
    
    
    try {
         $out = mysqli_query($mysqli, "SHOW TABLES");
    } catch (Exception $e ) {
         echo "Service unavailable";
     $log.="Service unavailable ";
         echo "message: " . $e->message;   // not in live code obviously...
      $log.="message: " . $e->message;   // not in live code obviously...
    
    return;
         exit;
    }
         $out = mysqli_query($mysqli, "SHOW TABLES");
       
      $res = mysqli_query($mysqli,"SHOW TABLES");
      while($cRow = mysqli_fetch_array($res))
      {
        $got = $cRow[0];
      $log.="TABLELIST $got\n";
      }    
        
        
        
        if ($mysqli->connect_errno) {
            
            $df    = date("Ymd-H");
            $file  = "/home/users/web/DIRECTORY.../htdocs/cron/GENLOG/mysqlerror$df.err";
            $file1 = fopen($file, "a");
            printf($file1, "Connect failed: %s\n", $mysqli->connect_error);
            //  fprintf($file1,"There are %u million bicycles in %s.",$number,$str);
            
            $log .= "SQL SHOW ERROR = Connect failed: %s\n" . $mysqli->connect_error . "\n";
            
            return;
            exit();
        }
        
        $log .= "SQL SHOW  = 
    ---
    $out\n
    ---
    ";
        
        $log .= "CHECK PING SHOW\n";
        
    /* check if server is alive */
    if ($mysqli->ping()) {
        $log."Our connection is ok!\n";
    }
    else 
    {
        $log."Our connection is NOT ok ----------\n";
    }
        $log .= "END PING SHOW\n";
        
        
        $sql = "SELECT MAX(reference) AS maxi FROM Articles";
        
      $res = mysqli_query($mysqli,$sql);
      while($cRow = mysqli_fetch_array($res))
      {
        $got = $cRow[0];
      $log.="SELECT MAX $got\n";
      }

    Here is the reasult / output, i am getting a report / feedbback of TABLELIST.



    ------
    Check_articles_for_max_number()
    TABLELIST Articles
    TABLELIST pages
    SQL SHOW =
    ---


    ---
    CHECK PING SHOW
    END PING SHOW
    SELECT MAX 1
    1SQLOUT =
    ---


    ---
    SQL TEST EXPLAIN SELECT * FROM `Articles` ORDER BY `reference` DESC LIMIT 1
    SQL =
    ---


    ---
    START LOOP ROW
    ROW =
    END LOOP ROW
    largestNumber = row['MAXI']; -
    largestNumber++; - 1
    SQL - ''
    REFERENCE - 1
    END Check_articles_for_max_number()

    Further:-

    A) if i'm using an incorrect table name - it should give me an error. (no error = valid table name)
    B) if i'm using an incorrect field name - it should give me an error. (no error = valid field name)

    C) the main feedback is :-

    START LOOP ROW
    ROW =
    END LOOP ROW

    i'm not getting any output from the max of the articles. the field name / table name IS correct.

  8. #8

    Join Date
    Sep 2002
    Location
    Auckland
    Posts
    1,356
    Rep Power
    17
    Ok, i'm off out in 5 mins..

    Further -

    Code:
      $res = mysqli_query($mysqli,"SHOW TABLES");
      while($cRow = mysqli_fetch_array($res))
      {
        $got = $cRow[0];
      $log.="TABLELIST $got\n";
    
      $jum = mysqli_query($mysqli,"DESCRIBE $got");
      while($cRowa = mysqli_fetch_array($jum))
      {
        $gota = $cRowa[0];
      $log.="TABLELIST DETAIl  $gota\n";
    
    $test="";
    foreach ($cRowa as $red => $white)
    {
      $log.="'$red' - '$white', ";
    
    }
      $log.="\n";
    
    }

    Result (bit messy)

    TABLELIST Articles
    TABLELIST DETAIl URL
    '0' - 'URL', 'Field' - 'URL', '1' - 'varchar(100)', 'Type' - 'varchar(100)', '2' - 'NO', 'Null' - 'NO', '3' - 'PRI', 'Key' - 'PRI', '4' - '', 'Default' - '', '5' - '', 'Extra' - '',
    TABLELIST DETAIl Timer
    '0' - 'Timer', 'Field' - 'Timer', '1' - 'bigint(15)', 'Type' - 'bigint(15)', '2' - 'NO', 'Null' - 'NO', '3' - '', 'Key' - '', '4' - '', 'Default' - '', '5' - '', 'Extra' - '',
    TABLELIST DETAIl reference
    '0' - 'reference', 'Field' - 'reference', '1' - 'bigint(10)', 'Type' - 'bigint(10)', '2' - 'NO', 'Null' - 'NO', '3' - '', 'Key' - '', '4' - '', 'Default' - '', '5' - '', 'Extra' - '',
    TABLELIST pages
    TABLELIST DETAIl Source
    '0' - 'Source', 'Field' - 'Source', '1' - 'varchar(10)', 'Type' - 'varchar(10)', '2' - 'NO', 'Null' - 'NO', '3' - '', 'Key' - '', '4' - '', 'Default' - '', '5' - '', 'Extra' - '',
    TABLELIST DETAIl page
    '0' - 'page', 'Field' - 'page', '1' - 'smallint(4)', 'Type' - 'smallint(4)', '2' - 'NO', 'Null' - 'NO', '3' - 'PRI', 'Key' - 'PRI', '4' - '', 'Default' - '', '5' - '', 'Extra' - '',
    TABLELIST DETAIl Time
    '0' - 'Time', 'Field' - 'Time', '1' - 'int(13)', 'Type' - 'int(13)', '2' - 'NO', 'Null' - 'NO', '3' - '', 'Key' - '', '4' - '', 'Default' - '', '5' - '', 'Extra' - '',
    SQL SHOW =
    ---

Posting Permissions

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