Grow your CSS skills. Land your dream job.

MySQL class – output issues

  • # April 6, 2012 at 6:06 am

    Hi guys,

    I’m using a PHP class for my database connections as follows:


    //Query rowset from the database.
    //returns a full row or rows.
    //return value is an associative array with column names as keys.
    public function query($sql) {
    $DBresult = mysql_query($sql);
    if (empty($DBresult)) {
    $DCResults = "";
    return $DCResults;
    } elseif(mysql_num_rows($DBresult) == 1) {
    $DCResults = $this->processRowSet($DBresult, true);
    } else {
    $DCResults = $this->processRowSet($DBresult);
    }
    mysql_free_result($DBresult);
    mysql_close();
    return $DCResults;
    }

    //takes a mysql row set and returns an associative array, where the keys
    //in the array are the column names in the row set. If singleRow is set to
    //true, then it will return a single row instead of an array of rows.
    public function processRowSet($rowSet, $singleRow=false)
    {
    $resultArray = array();
    while($row = mysql_fetch_assoc($rowSet)) {
    array_push($resultArray, $row);
    }
    if($singleRow === true)
    return $resultArray[0];
    return $resultArray;
    }

    If there is one row returned, it returns a simple associative array. In the case of more than one row returned, it returns an array of arrays. This is fine normally, but I’ve run into an issue.

    On queries where I’m not sure how many rows will come back (one or many), I can’t seem to write the loop code that can handle this properly. How can I detect from the resulting array which type it is?

    xpy
    # April 6, 2012 at 7:03 am

    I believe you should always handle your queries as if they produce more than one rows.
    So you’ll always know that your function returns an array of the results.
    You’ll also know that if your array’s length is 1 you have one result.

    # April 6, 2012 at 7:18 am

    Ah OK. So count($myarray) will return 1 if it’s a single row and > 1 if it’s an array of arrays?

    I guess, since the rest of the site relies on the way this class operates already, I’ll need to check the array length before looping.

    # April 6, 2012 at 9:39 am

    If you want to keep the ‘single row’ option (and it could be useful), I’d put it into its own method.

    If you can’t you should at least put it before the loop, so you don’t waste the time with it. Additionally, this would allow you to fetch a single row from the set while leaving other rows for later use, and not messing with the pointer position.

    # April 6, 2012 at 10:20 am

    I’m not that hot on OO PHP yet. So, you’re saying this would be better:


    public function processRowSet($rowSet, $singleRow=false)
    {
    $resultArray = array();
    if($singleRow === true)
    return $resultArray[0];
    else
    while($row = mysql_fetch_assoc($rowSet)) {
    array_push($resultArray, $row);
    }
    return $resultArray;
    }
    # April 6, 2012 at 11:22 pm

    that won’t work: $resultArray[0] is not defined yet. more like

    < ?php
    // General Warning:
    // if you're using MySQL 4.1 or greater
    // (and you most probably _are_)
    // you should be using the mysqli extension (or PDO)
    // instead of the -outdated- mysql extension
    // (which is not depreciated _yet_, but is scheduled to be).
    // http://php.net/manual/en/mysqlinfo.api.choosing.php

    public function processRowSet( $rowSet,$singleRow=FALSE ){

    // I'd check that $rowSet (which would be less confusing if it were called "resultSet")
    // _really_is_ a mysql result before trying anything.
    if(
    !is_resource( $rowSet )
    || get_resource_type( $rowSet ) !== 'mysql result'
    ){ return FALSE; }

    // deal with single row option first
    if( $singleRow ){
    return mysql_fetch_assoc( $rowSet );
    }

    // otherwise, the user wants all rows:
    while( $row = mysql_fetch_assoc( $rowSet ) ){
    $resultArray[] = $row;
    }

    return $resultArray;

    }
    # April 9, 2012 at 11:13 am

    Thanks guys :D

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic.

*May or may not contain any actual "CSS" or "Tricks".