Use MySql field as source for form select field

CutAndPaste · #1 (**permalink**) 03-14-05, 10:11 AM

I'm trying to get some code to populate a select field on a form with the contents of a field in a MySql database. I've got as far as the code below (with some help) BUT it only returns the first row in the table. Any ideas as to how I can get the full listing to display?

insert_items.php

Quote:

<?
error_reporting( E_ERROR | E_PARSE );

require_once( 'inc/config.inc.php' );
require_once( 'inc/mysql.class.php' );

//-------------------------------------

$oDb = new MySQL( $aCfg['db_host'], $aCfg['db_name'], $aCfg['db_user'], $aCfg['db_pass'] );

$sList = '';

if ( $oDb->Connect() )
{
$hQRes = $oDb->ExecQuery( "SELECT {$aCfg['db_field']} AS fld_alias FROM {$aCfg['db_table']} " );
if ( $aRes = $oDb->FetchArray($hQRes) )
{
$sTmp = addslashes( $aRes['fld_alias'] );
$sList .= "<option value='$sTmp'>$sTmp</option>\n";
}
echo $sList;
}
else
{
// print_r( $oDb->aErrors[count($oDb->aErrors)-1] );
}

?>

config.inc

Quote:

<?
// Configuration
$aCfg['db_host'] = 'localhost';
$aCfg['db_name'] = 'MyDatabase';
$aCfg['db_user'] = 'user';
$aCfg['db_pass'] = 'pwd';

$aCfg['db_table'] = 'MyTable';
$aCfg['db_field'] = 'MyField';

?>

mysql.class.php

Quote:

<?

class MySQL
{
var $sDBHost = '';
var $sDBName = '';
var $sDBUser = '';
var $sDBPass = '';
var $nPort = '';
var $hLink = '';
var $aErrors = array();

// Constructor
function MySQL( $sDBHost, $sDBName, $sDBUser, $sDBPass, $nPort = 3306 )
{
$this->sDBHost = $sDBHost;
$this->sDBName = $sDBName;
$this->sDBUser = $sDBUser;
$this->sDBPass = $sDBPass;
$this->nPort = $nPort;
}

function Connect()
{
$bResult = false;
$this->hLink = @mysql_connect( "{$this->sDBHost}:{$this->nPort}",
$this->sDBUser, $this->sDBPass );

if ( $this->hLink != false )
{
if ( @mysql_select_db( $this->sDBName, $this->hLink ) )
$bResult = true;
}
else
{
$this->aErrors[] = mysql_errno().':'.mysql_error();
}

return $bResult;
}

function Close()
{
@mysql_close( $this->hLink );
}

function ExecQuery( $sQuery )
{
$hResult = @mysql_query( $sQuery, $this->hLink );
return $hResult;
}

function FetchArray( $hQRes )
{
return @mysql_fetch_array( $hQRes );
}

function FetchRow( $hQRes )
{
return @mysql_fetch_row( $hQRes );
}

function NumRows( $hQRes )
{
return @mysql_num_rows( $hQRes );
}
}

?>

The Code is called from the form field like this:

Quote:

Many thanks,

Simon

moronovich · #2 (**permalink**) 03-14-05, 01:29 PM

snip...

change this:

PHP Code:


			
if ( $aRes = $oDb->FetchArray($hQRes) )

{

$sTmp = addslashes( $aRes['fld_alias'] );

$sList .= "<option value='$sTmp'>$sTmp</option>\n";

}

echo $sList;

}

else 

{

// print_r( $oDb->aErrors[count($oDb->aErrors)-1] );

}

into this

PHP Code:


			
if($oDb->NumRows($hQRes)) {

    while ( $aRes = $oDb->FetchArray($hQRes) ){

          $sTmp = addslashes( $aRes['fld_alias'] );

          $sList .= "<option value='$sTmp'>$sTmp</option>\n";

     }

     echo $sList;

}

else {

     print 'No row returned';

     // print_r( $oDb->aErrors[count($oDb->aErrors)-1] );

}

ps:
1. indentation *in most case* will help to understand the code more.
2. your mysql wrapper class isn't functional, you're reinventing the wheel. if you want to do so, maybe you can write the interface class first and then implement them using your mysql class.
3. this is just a suggestion from somebody unknown. you don't even have to listen to..

pps:
of course, i don't guarantee the code to work..

CutAndPaste · #3 (**permalink**) 03-14-05, 04:45 PM

moronovich, tx for the reply...

Quote:

2. your mysql wrapper class isn't functional, you're reinventing the wheel. if you want to do so, maybe you can write the interface class first and then implement them using your mysql class.

Does this mean the mysql.class.php needs to be rewritten? I was given the code and this is starting to get way over my head!

Has anyone any other suggestions as to how I can get the data from a field in the database to populate a form select field?

Thanks,

Simon

moronovich · #4 (**permalink**) 03-14-05, 10:35 PM

uhm, maybe functional is not a proper word to call it. at a glance, your wrapper class should work because it's only redefining native php functions into class methods. it's like adding cosmetics to something which actually can stay in its best form without any add-ons. if you're making a wrapper class (in this case the mysql class), first you should think why you need it, whether it's only some syntactic sugar or it's really needed to satisfy your system concept and design (maybe the mvc).

moving from structural programming to object oriented programming sometimes brings confusion on dealing with the new aspect of oo programming. if one considers oo programming is just another way to list your functions and variables in a more fashioned way, he should never realize the power of oo programming and the reason why its development is rapidly increasing nowadays. from your mysql class, we can take this as example:

PHP Code:


			
//...deleted

function ExecQuery( $sQuery )

{

$hResult = @mysql_query( $sQuery, $this->hLink );

return $hResult;

}



function FetchArray( $hQRes )

{

return @mysql_fetch_array( $hQRes );

}



function FetchRow( $hQRes )

{

return @mysql_fetch_row( $hQRes );

}

//...deleted

from the code above, it seems that all functions do the same thing with its original. if someone says, why must we rewrite them while they purely do the same with their ancestors? i won't answer the question here, because i'm not the code author

. anyway, if we change above class above, maybe we can answer the question easily:

PHP Code:


			
class MySQL

{

var $sDBHost = '';

var $sDBName = '';

var $sDBUser = '';

var $sDBPass = '';

var $nPort = '';

var $hLink = '';

var $curRes = '';

var $aErrors = array();



/*

 *@access private

 *@singleton

 */

function getInstance($sDBHost,$sDBName,$sDBUser,$sDBPass, $nPort = 3306) {

    static $instance;

    if(!isset($instance)) {

        $link = mysql_connect("$sDBHost:$nPort",$sDBUser,$sDBPass) or die('Construction failed: '.mysql_error());

        mysql_select_db($sDBName) or die('Construction failed: '.mysql_error());

        $this->hLink = $link;

        $this->sDBHost = $sDBHost;

        $this->sDBName = $sDBName;

        $this->sDBUser = $sDBUser;

        $this->sDBPass = $sDBPass;

        $this->nPort = $nPort;

        $instance = new __CLASS__;

    }

    return $instance;

}





function Close()

{

mysql_close( $this->hLink );

}



function ExecQuery( $sQuery )

{

$hResult = mysql_query( $sQuery, $this->hLink );

$this->curRes = $hResult;

return $hResult;

}



function FetchArray( $hQRes = '')

{

if($hQRes == '') $hQRes = $this->curRes;

return mysql_fetch_array( $hQRes );

}



function FetchRow( $hQRes = '')

{

if($hQRes == '') $hQRes = $this->curRes;

return mysql_fetch_row( $hQRes);

}



function NumRows( $hQRes = '')

{

if($hQRes == '') $hQRes = $this->curRes;

return mysql_num_rows( $hQRes );

}

}



//Usage example

$conn = MySQL::getInstance('localhost','mydb','myuser','mypass');

//your query

$sql = 'select foo from bar';

if(!$conn->ExecQuery($sql) && !$conn->NumRows()) {

    //throw exception here

}

while($conn->FetchArray()) {

   //print the options here

}

here, you have your singleton class so that it only has one instance (it's important to not burdening your mysql server). if you take a look at the class methods, you'll notice that some can accept empty param which if it happens, the method will call from the internal property. this will simplify your code a bit by not supplying parameters to each class function and the most important thing, your oo code shows its power.

just a note, php 4 oo is sometimes criticized by the oo purists. it's quite natural since php 4 is not designed to serve oo. if you know how to program oo in php, you must realize that there are not private and public functions and properties. php 4 polymorphism is also a bit tricky since you have to manipulate your extended class to behave as if it were implementing the interface.

common issue in oo programming is how to make oo code not bloated. don't worry, we have the answer here. php is share-nothing architecture. even bad codes can run fast in php environment (but please don't take this as excuse). by lots of exercise you'll then be able to optimize your oo code and then realize how helping the methodoloy is when you're working on big projects.

ps: as usual, i don't guarantee the code to work, and you don't even have to pay attention to this meaningless response

.