[SOLVED] populating select box with database values

[AdRock]

I have a function that is supposed to populate a select list with 2 database tables joined using a union.

 

I am getting this error for every row that is in the query so the query must be executed but not displayed

 

Warning: mysql_result(): supplied argument is not a valid MySQL result resource

 

I pass the 2 tables as a parameter

 

function selectArticle ($table1,$table2) {
global $host,$dbUser,$dbPass,$dbName;

require_once("../php/database/connection.php");
require_once("../php/database/MySQL.php");

// Connect to the database and grab the email
$db = & new MySQL($host,$dbUser,$dbPass,$dbName);

    $sql = "SELECT id,title FROM $table1 UNION SELECT id, title FROM $table2";

$results = $db->query($sql);

$numrows = $results->size();

    if ($numrows > 0){
	$x=0;
	while ($x < $numrows) {
		$id = mysql_result($results,$x,'id');
		$articletitle = mysql_result($results,$x,'title');
		echo "<option value=\"$id\">$articletitle</option>\n";
		$x++;
	}
    }
}

[suma237]

check the database connection .Find out it using die(mysql_error()) statement

[AdRock]

Definitely got a database connection as it would tell me if there wasn't one.

 

It counting the number of rows which should be 8 and I get 16 errors, 8 for each of the mysql_results so the problem lies there.

[suma237]

use simple statement instead of function.check this sample code

$sql = "SELECT * FROM state";

   $results = mysql_query($sql);
   
   $numrows = mysql_numrows($results);
   
    if ($numrows > 0){
      $x=0;
      while ($x < $numrows) {
         $id = mysql_result($results,$x,'state_id');
         $articletitle = mysql_result($results,$x,'state_name');
         echo "<option value=\"$id\">$articletitle</option>\n";
         $x++;
      }
    }
[code]

[AdRock]

I know that works but i have a MySQL class i use

 

I added some code to my database class so i can use the mysql_result()

function fetchresult () {
return mysql_result($this->query, $offset, $field);
}

 

It does output something in the select list but all the values are '10'.

 

I don't know where 10 is coming from as there's 8 rows in the 2 tables

 

Here is the updated function

function selectArticle ($table1,$table2) {
global $host,$dbUser,$dbPass,$dbName;

require_once("../php/database/connection.php");
require_once("../php/database/MySQL.php");

// Connect to the database and grab the email
$db = & new MySQL($host,$dbUser,$dbPass,$dbName);

    $sql = "SELECT id,title FROM $table1 UNION SELECT id, title FROM $table2";

$results = $db->query($sql);

$numrows = $results->size();

    if ($numrows > 0){
$x=0;
while ($x < $numrows) {
	$id = $results->fetchresult($results,$x,'id');
	$articletitle = $results->fetchresult($results,$x,'title');
	echo "<option value=\"$id\">$articletitle</option>\n";
	$x++;
}
    }
}

 

Here is my database class if it helps

<?php
/**
* @package SPLIB
* @version $Id: MySQL.php,v 1.1 2003/12/12 08:06:07 kevin Exp $
*/
/**
* MySQL Database Connection Class
* @access public
* @package SPLIB
*/
class MySQL {
    /**
    * MySQL server hostname
    * @access private
    * @var string
    */
    var $host;

    /**
    * MySQL username
    * @access private
    * @var string
    */
    var $dbUser;

    /**
    * MySQL user's password
    * @access private
    * @var string
    */
    var $dbPass;

    /**
    * Name of database to use
    * @access private
    * @var string
    */
    var $dbName;

    /**
    * MySQL Resource link identifier stored here
    * @access private
    * @var string
    */
    var $dbConn;

    /**
    * Stores error messages for connection errors
    * @access private
    * @var string
    */
    var $connectError;

    /**
    * MySQL constructor
    * @param string host (MySQL server hostname)
    * @param string dbUser (MySQL User Name)
    * @param string dbPass (MySQL User Password)
    * @param string dbName (Database to select)
    * @access public
    */
    function MySQL ($host,$dbUser,$dbPass,$dbName) {
        $this->host=$host;
        $this->dbUser=$dbUser;
        $this->dbPass=$dbPass;
        $this->dbName=$dbName;
        $this->connectToDb();
    }

    /**
    * Establishes connection to MySQL and selects a database
    * @return void
    * @access private
    */
    function connectToDb () {
        // Make connection to MySQL server
        if (!$this->dbConn = @mysql_connect($this->host,
                                      $this->dbUser,
                                      $this->dbPass)) {
            trigger_error('Could not connect to server');
            $this->connectError=true;
        // Select database
        } else if ( !@mysql_select_db($this->dbName,$this->dbConn) ) {
            trigger_error('Could not select database');
            $this->connectError=true;
        }
    }

    /**
    * Checks for MySQL errors
    * @return boolean
    * @access public
    */
    function isError () {
        if ( $this->connectError )
            return true;
        $error=mysql_error ($this->dbConn);
        if ( empty ($error) )
            return false;
        else
            return true;
    }

    /**
    * Returns an instance of MySQLResult to fetch rows with
    * @param $sql string the database query to run
    * @return MySQLResult
    * @access public
    */
    function & query($sql) {
        if (!$queryResource=mysql_query($sql,$this->dbConn))
            trigger_error ('Query failed: '.mysql_error($this->dbConn).
                           ' SQL: '.$sql);
        return new MySQLResult($this,$queryResource);
    }
}

/**
* MySQLResult Data Fetching Class
* @access public
* @package SPLIB
*/
class MySQLResult {
    /**
    * Instance of MySQL providing database connection
    * @access private
    * @var MySQL
    */
    var $mysql;

    /**
    * Query resource
    * @access private
    * @var resource
    */
    var $query;

    /**
    * MySQLResult constructor
    * @param object mysql   (instance of MySQL class)
    * @param resource query (MySQL query resource)
    * @access public
    */
    function MySQLResult(& $mysql,$query) {
        $this->mysql=& $mysql;
        $this->query=$query;
    }

    /**
    * Fetches a row from the result
    * @return array
    * @access public
    */
    function fetch () {
        if ( $row=mysql_fetch_array($this->query,MYSQL_ASSOC) ) {
            return $row;
        } else if ( $this->size() > 0 ) {
            mysql_data_seek($this->query,0);
            return false;
        } else {
            return false;
        }
    }

    /**
    * Returns the number of rows selected
    * @return int
    * @access public
    */
    function size () {
        return mysql_num_rows($this->query);
    }

function fetchrow () {
	return mysql_fetch_row($this->query);
}

function fetchresult () {
	return mysql_result($this->query, $offset, $field);
}

    /**
    * Returns the ID of the last row inserted
    * @return int
    * @access public
    */
    function insertID () {
        return mysql_insert_id($this->mysql->dbConn);
    }
    
    /**
    * Checks for MySQL errors
    * @return boolean
    * @access public
    */
    function isError () {
        return $this->mysql->isError();
    }
}
?>