9 replies to this topic

[Eric The Red]

I am a total noob at PHP. I have other programming language experience but I have just started to look at PHP (I know, pretty late on the boat).

The issue I have is a site that needed some SQL updates which I did and now the correct results are being returned but no matter what I try, I cannot get it to return more than 2 results. I tried the same SQL calls using another language to display the data and everything is fine there, but in the PHP code written by someone else, I cannot get it to display more than two.

I think I have narrowed it down to the following bit of code:

    $this->setOutput($this->output);
    }
    
    public function getListingrows(page $page, $sql)
    {
        $return = '';
        $rownum = 1;
        while (($a = $page->dba->getarray($sql, $rownum))) {
            $rownum++;
            if (!isset($a['id'])) continue;
            $image = 'p1f' . $a['id'] . '.jpg';
            // Get the first paragraph of the instruct field
            $next = strpos($a['instruct'], '</p>');
            $next--;
            $displaytext = substr($a['instruct'], 0, $next);
            
            if (method_exists($this, 'getAvailability')) {
                $availability = $this->getAvailability($a);                
            } else {
                $availability = '';
            }
            
            $return .= <<<EOF

I think, for whatever reason, this WHILE is only returning 2 results no matter what. Am I correct? And if I am, can you help me change that?

Thanks very much in advance!

e

[Drew]

$rownum = 1;
Change to
$rownum = 0;

If you are using what I believe you are using then it converts it to an array from the database. Arrays are 0 based.

Try that and see if it works.

[Eric The Red]

Yea, I thought the very same thing and had tried that initially with no results. With it equal to 0 no results are returned.

With it set as 2 only one result is returned.

I built a parallel query and checked the results and it is returning row 1 and 3 and skipped 2,4,5,6.

This is quite frustrating :-(

One thing is that there are 2 queries in the WHILE, could they be limited the iterations of the WHILE? Does PHP have that kind of issue where a loop within a loop can cause the outer loop to fail if the internal loop is not returning the correct info for instance in this case it is looking for ID to continue, what if the SQL from the queries inside the loop are not returning ID, would that make the WHILE break?

[Jody LeCompte]

The loop is going to keep going until the condition of the loop is met, regardless of what other structures might be inside it. Now it could literally stop the application completely with a fatal error, but that's not the case.

Can you post the code from your dba abstraction class for the query and getarray and the actual sql statement your using for that function?

[Eric The Red]

SQL query is as follows:

SELECT 
                p.id, p.prop_name, p.instruct, p.location, p.sleeps, p.bedroom_num, 
                p.bathroom_num, p.setting, p.price_on, p.price_off, p.prop_type, 
                l.location AS location_code,
                s.setting AS setting_code,
                t.prop_type AS listing_type
            FROM prop_info p
            LEFT JOIN location_code l ON l.id=p.location
            LEFT JOIN setting_code s ON s.id=p.setting
            LEFT JOIN listing_type t ON t.id=p.prop_type
            WHERE 
               (p.location IN (2,4,9,15,17,22,36,48,79,81)) AND
               (p.book_active = 1) AND 
               (p.listing_type = 2)
             ORDER BY p.bedroom_num

I am not familiar with the term " dba abstraction class for the query and getarray" in that I do not know if I can find that in his code. What he wrote is very dynamic and there are bits and pieces everywhere. I am not sure where to look to get these things.

There is this stuff...

public function loadListing($listing)
    {
        $listing_name = $listing;
        
        $this->dba->query("SELECT l.listid, l.urlname, l.title, l.metadesc, l.metakeywords,
                                  l.submenuname, l.searchfilter, l.locationcode, l.bedrooms,
                                  l.proptype, l.customview, l.locationtext, l.customquery, 
                                  l.additional_submenues, l.usecustomquery, l.showmenu
                          FROM prop_lists l
                          WHERE l.urlname='{$listing_name}'");
        if ($this->dba->numrows() < 1) {
            trigger_error("Could not find a listing to display. Please check the URL.");
            die;
        }
        
        $a = $this->dba->getarray();
        
        if (DEBUGMODE) {
            var_dump($a);
        }
        
        $this->setTitle($a['title']);
        $this->setMetadesc($a['metadesc']);
        $this->setMetakeywords($a['metakeywords']);
        $this->setSubmenuname($a['submenuname']);
        $this->setSearchfilter($a['searchfilter']);
        $this->setLocationcode($a['locationcode']);
        $this->setBedrooms($a['bedrooms']);
        $this->setProptype($a['proptype']);
        $this->setShowmenu($a['showmenu']);
        $this->setCustomview($a['customview']);
        $this->setLocationtext($a['locationtext']);

        $a['additional_submenues'] = str_replace(' ', '', $a['additional_submenues']);
        if ($a['additional_submenues'] != '') {
            $submenues = explode(',', $a['additional_submenues']);
            foreach ($submenues as $menu) {
                $this->loadSubmenu($menu);
            }
        }
        
        if ($a['usecustomquery'] != '') {
            $custom = 'use' . ucfirst($a['usecustomquery']) . 'Query';
            $this->$custom();
        } else {
            $this->setListingquery($this->default_query);
        }
    }
    
    /**
     * get_listings
     *
     * Output the list of properties.
     **/
    public function get_listings() {
        $this->metadesc = $this->getTitle() . " " . $this->getMetadesc();

        include('lib/ui/general_listings.php');
        
        
        $sql = $this->dba->query($this->getListingquery(), FALSE);
                     
        if (trim($this->getCustomview()) != '') {
            include('./lib/ui/' . $this->getCustomview() . '.php');
            $classname = $this->getCustomview();
            $ui = new $classname($this);
        } else {
            $ui = new general_listings;
        }
        $ui->getListings($this, $sql);
        $ui->printPage($this);
    }

Let me know if you need more :-)

[joyo]

If you excute the sql scrip directly, how many rows do you get?

Edited by WingedPanther, 20 June 2010 - 05:38 AM.
Delete spam links

[Eric The Red]

There are 6 results when using the SQL.

[Jody LeCompte]

What I meant by DBA abstraction class is the code behind $dba. It's called an abstraction layer or class because you're not using the standard PHP mysql_* functions but custom functions based on them with(i'm assuming) enhanced options or features. Whenever you're loading multiple rows like the above example, $a = $this->dba->getarray(); would normally be contained in a while() loop to repeat the necessary instructions for each row returned by the database. Without seeing the code for $dba->getarray(), it's impossible to guess because the function could literally work exactly like mysql_fetch_array() or it could loop and build the array inside of the function, which would mean back to the drawing board to look for another issue.

[joyo]

Do you have manual of help about function $dba->getarray(). perhaps it would be helpful.

[Eric The Red]

Thanks very much for the help on this, still not resolved.

Let me know if anything below helps...

There is this stuff:

<?php

class db_adapters_pgsql
{
    var $name = '';
    var $host = '';
    var $user = '';
    var $pass = '';
    var $db_installed = TRUE;
    var $installing = FALSE;
    var $last_error = '';

    var $showqueries = 0;
    var $queries = 0;
    var $querytext = '';
    var $result = '';
    var $error = FALSE;
    var $lasterror = '';
    var $lastquery = '';
    var $p = '';
    var $f = '';

    function __construct($host, $db, $user, $pass, $installing = FALSE)
    {
        $this->host = $host;
        $this->name = $db;
        $this->user = $user;
        $this->pass = $pass;
        $this->installing = $installing;

        if (!function_exists('pg_connect')) return $this->db_installed = FALSE;

        if (!$this->installing) {
            $this->connect();
            $this->select();
        }
        $this->f = "<font size=\"-1\" face=\"verdana\">";
        $this->querytext = "<table width=\"90%\" cellspacing=\"2\" border=\"1\"><tr><td colspan=\"3\" align=\"center\"><font size=\"+1\" face=\"verdana\">SQL Queries </font></td></tr><tr><td align=\"center\">{$this->f}#</td><td align=\"center\">{$this->f}Type</td><td>{$this->f}SQL</td></tr>";
    }

    function connect()
    {
        GLOBAL $dbresource;
        if (!($dbresource = @pg_connect("host={$this->host} dbname={$this->name} user={$this->user} password={$this->pass}"))) {
            if (!$this->installing) {
                echo "Seir Anphin was unable to connect to the database at $this->host. <br><br>Reason: " . pg_last_error();
                exit();
            } else {
                $this->last_error = "Could not connect to the database at $this->host. Reason: " . pg_last_error();
                return FALSE;
            }
        }
        return TRUE;
    }

    function select()
    {
        return TRUE;
    }

    function close()
    {
        return mysql_close();
    }

    // at least systems running php5 will get their table tag closed. it's important, sort of.
    function __destruct()
    {
        $this->querytext .= "</table><br>";
    }

    function show_tables()
    {
        $this->query("SELECT relname FROM pg_stat_user_tables ORDER BY relname");
    }

    function insert($table, $fieldarr, $functions = '')
    {
        if ($functions != '') $functions = explode(',', $functions);

        $fields = '';
        $values = '';
        foreach ($fieldarr as $k=>$v) {
            if (is_array($functions)) {
                foreach ($functions as $funcname) {
                    $v = $funcname($v);
                }
            }
            $fields .= "$k, ";
            $values .= "'$v', ";
        }
        $fields = substr($fields, 0, -2);
        $values = substr($values, 0, -2);
        $this->query("INSERT INTO {$this->p}$table ($fields) VALUES ($values)", FALSE);
    }

    function query($sql, $use_internal_result = TRUE)
    {
        GLOBAL $dbresource;
        if ($use_internal_result) {
            $this->result = pg_query($dbresource, $sql);
        } else {
            $result = pg_query($dbresource, $sql);
        }

        $this->queries++;
        $this->lastquery = $sql;
        if (pg_last_error() != '') {
            $this->error = TRUE;
            $this->lasterror = "Error in query $sql<br />" . pg_last_error();
            $this->querytext.="<tr><td align=\"center\"><b>$this->queries</b></td><td align=\"center\"><font color=\"red\"><i>error</i> </font></td><td>$this->f".nl2br(htmlspecialchars($sql))."<br>MySQL error message: $this->lasterror </font></td></tr>";
            trigger_error($this->lasterror);
            //echo "<br><font size=\"-2\" face=\"verdana\">SQL Error, check the sql queries to find out what went wrong.";
            return FALSE;
        }
        $this->querytext.="<tr><td align=\"center\"><b>$this->queries</b></td><td align=\"center\"><i>query</i></td><td>".nl2br(htmlspecialchars($sql))."</td></tr>";
        if ($use_internal_result) {
            return $this->result;
        } else {
            return $result;
        }
    }

    // Returns FALSE if no results.
    function result($sql)
    {
        $this->queries++;
        $this->querytext.="<tr><td align=\"center\"><b>$this->queries</b></font> </td><td><i>result</i></td><td>$sql</td></tr>";
        $result = pg_query($sql);
        @pg_result_seek($result, 0);
        $r_result = pg_fetch_row($result);
        $r_result = $r_result[0];
        if ($this->numrows($result) < 1) return FALSE;
        if (is_string($result)) return stripslashes($r_result);
        return $r_result;
    }

    function getarray($result = '', $type = PGSQL_ASSOC)
    {
        if (!empty($result)) {
            return @pg_fetch_array($result, null, $type);
        } else {
            return @pg_fetch_array($this->result, null, $type);
        }
    }

    function numrows($result = '')
    {
        if ($result != '') return @pg_num_rows($result);
        return @pg_num_rows($this->result);
    }

    function affected_rows()
    {
        GLOBAL $dbresource;
        return pg_affected_rows($dbresource);
    }

    function _insert_id($sequence) {
        GLOBAL $dbresource;
        $res = pg_query("SELECT currval('{$sequence}') AS currval");
        if ($res) {
            $rec = pg_fetch_assoc($res);
            $insert_id = $rec['currval'];
            return $insert_id;
        } else {
            trigger_error("PostgreSQL did not return the sequence currval, try using the $dba->insert_id(\$table, \$name function instead.");
        }
    }


    function insert_id($table, $idname) {
        #trigger_error("Error: the insert_id method was called when it is no longer supported.");
        $id = $this->result("SELECT $idname FROM $table ORDER BY $idname DESC LIMIT 1");
        return $id;
    }
}

?>

and there is this stuff:

<?php

class baseDAO 
{
    public static function buildSQL(array $pvars, $functions = '', $syntax = 'COL_LIST')
    {
        if ($functions != '') $functions = explode(',', $functions);

        $keys = '';
        $values = '';
        $string = '';
        
        if (is_array($pvars)) {
            foreach ($pvars as $key=>$val) {
                if (is_array($functions)) {
                    foreach ($functions as $funcname) {
                        $val = $funcname($val);
                    }
                }
                
                // Generates the column-list syntax
                if ($syntax == 'COL_LIST') {
                    $keys .= "$key, ";
                    $values .= "'$val', ";
                    
                // Generates a SET query
                } else {
                    $string .= "$key='$val', ";
                }
            }
            if ($syntax == 'COL_LIST') {
                $keys = substr($keys, 0, -2);
                $values = substr($values, 0, -2);
                $string = "($keys) VALUES ($values)";
            } else {
                $string = "SET " . substr($string, 0, -2);                
            }
            return $string;
        } else {
            trigger_error("Error: buildsql() expected an array.", E_ERROR);
            return FALSE;
        }    
    }
}

?>

And this

<?php

/**
 * Data Access Object for prop_lists table
 */

class listingDAO extends baseDAO
{
    public function load(lists $ui)
    {
        GLOBAL $dba;
        $items = array();
        $dba->query("SELECT * FROM prop_lists ORDER BY urlname ASC");
        while (($a = $dba->getarray())) {
            $ui->addRow($a);
        }
        return $items;
    }
    
    public function queryById($id)
    {
        GLOBAL $dba;
        $dba->query("SELECT * FROM prop_lists WHERE listid=$id");
        return $dba->getarray();
    }
    
    public function add($data)
    {
        GLOBAL $dba;
        
        // Change dashes to underscores because submenues are classes
        // and classes can't have dashes in them.
        $data['submenuname'] = str_replace('-', '_', $data['submenuname']);
        
        // Remove any file extensions from urlname
        $data['urlname'] = str_replace('.php', '', $data['urlname']);
        $data['urlname'] = str_replace('.ihtml', '', $data['urlname']);
        
        $dba->query("INSERT INTO prop_lists ". $this->buildSQL($data, 'pg_escape_string'));        
    }
    
    public function saveById($data, $id)
    {
        GLOBAL $dba;
        $dba->query("UPDATE prop_lists ". $this->buildSQL($data, 'pg_escape_string', 'SET') . " WHERE listid=$id");
    }
    
    public function deleteById($id)
    {
        GLOBAL $dba;
        $dba->query("DELETE FROM prop_lists WHERE listid=$id");
    }
}

?>