Help with MySQL Options Table

[joshmac]

I have an options table setup like so:

option_id	  option_name	    option_value
1	          sitetitle	            SimpleCollab
2	          siteurl	            http://example.com/
3	          sitepath	            /home/user/MAMP/htdocs/pm
4	          enable_registration	 1
5	          enable_query	 1

Then my php code is as follows:

function get_pm_option( $option ) {
	global $pmdb;

		$result = $pmdb->query( "SELECT * FROM " . DB . "options WHERE option_name IN ('$option')" );
			
			while($r = $result->fetch_object()) {
				
				return $r->$option;
			}
	}

Then my html is like so:

<tr><td>Site Title:</td> <td><input class="forminput" name="sitetitle" size="40" value="<?php echo get_pm_option('sitetitle'); ?>" maxlength="255"></td></tr>

Can you see what might be wrong with any of my code above? The site title is not being printed from the database.

echo "SELECT * FROM " . DB . "options WHERE option_name IN ('$option')";
$result = $pmdb->query( "SELECT * FROM " . DB . "options WHERE option_name IN ('$option')" );

See what that gives you on the screen. If it looks OK, copy it off the screen and paste it into the SQL window in phpmyadmin

[joshmac]

@ardav, yes, now if it would yield the results I am looking for. I wonder if it has something to do with the function itself? Just to clarify, I am looking for the value of site title to print on the screen.

So the query string copied to SQL window does work? So what are you using mysqli?

[joshmac]

@ardav, that is correct.

Is the $pmdb variable a valid object? I know you've made a global var, but perhaps you need to check that it's valid. Although I think you'd get an error if it wasn't. Sorry I can't see anything obvious wrong here.

[joshmac]

@ardav, yes it is valid. That's ok. I needed another pair of eyes as usual to make sure that I am at least on the right track. The answer will show itself eventually. Thank you for your help as always.

[joshmac]

Ok, so I redid my statement because using IN wasn't what I wanted or needed. So, below is what I've changed, but my results are still the same which is nothing.

In my database class I have:

public function get_row($sql) {
        if ( !$results = $this->query($sql . " LIMIT 1") )
            return false;
 
        return $results->fetch_object();
    }

My PHP function was updated to this:

function get_pm_option( $option ) {
	global $pmdb;

		$results = $pmdb->get_row( "SELECT option_value FROM " . DB . "options WHERE option_name = '$option'" );

}

In the options page, I've updated it to this to pull the info from the database:

<tr><td>Site Title:</td> <td><input class="forminput" name="sitetitle" size="40" value="<?php echo get_pm_option($results->sitetitle); ?>" maxlength="255"></td></tr>

After those changes, I still get nothing. I am missing something but can't figure out what, but I know the recent changes are what I need and not what I was doing previously.

$results = $pmdb->get_row( "SELECT option_value FROM " . DB . "options WHERE option_name = '$option'" ) or die('flop');

[joshmac]

Yep, it just dies with no other type of error message.

WHat about this?

<?php echo get_pm_option('sitetitle'); ?>

So let's recap

When you do an echo inside the function, it appears on the screen. So the function is actually being run.

You say that the global variable is valid.

How about you run the query outside the function:

//$pmdb is already set outside functions I take it. DB is a defined CONSTANT, so it will be available from everywhere.

$results = $pmdb->get_row( "SELECT option_value FROM " . DB . " options WHERE option_name = 'siteconfig" ) or die('flop');
...etc...


//DB be a tablename - the name DB suggests that it is a DB name.

[joshmac]

@ardav, yes I've already tried what you suggested and it will not bring back a value from the table. I've tried the same with a different table and it works because that table is structured differently. I am trying to avoid having one long row of options (which is what I have now) and instead break up the options table into several rows especially since the application I am working on also has a plugin system.

$results = $pmdb->get_row( "SELECT option_value FROM " . DB . " options WHERE option_name = 'sitetitle" );

option_id  | option_name | option_value
1          | sitetitle   | Sample Site
2          | siteurl     | http://example.com

OK I think I see an issue : DB

"SELECT option_value FROM " . DB . " options WHERE option_name = 'sitetitle'"

This will give a table name like mydb options (illegal). Is that right?
Or did you intend something like mydb.options or mydboptions ?

(I made the mistake of placing a space before options in the SQL above :(

[joshmac]

@ardav, did catch that and I deleted the space.

OK, try a vanilla mysql connection and query, see if that works:

So outside of any functions:

$query = "SELECT option_value FROM " . DB . "options WHERE option_name = 'sitetitle'";

echo "Query: $query<br /><br />";

$result = mysql_query($query);

echo "Record Count: " . mysql_num_rows($result) . "<br /><br />";

$output = "";
if(mysql_num_rows($result)){
   $output = "<strong>RECORD DATA:</strong><br />";
   while($data = mysql_fetch_array($result)){
      $output .= "{$data['option_value']}<br />";
   }
}
echo $output;

[joshmac]

@ardav, thanks. I will try that query tomorrow and post the results.

[joshmac]

I figured it out. I knew that there was something wrong with my query but couldn't see it until now.

This is what I started with:

function get_pm_option( $option ) {
	global $pmdb;

		$result = $pmdb->query( "SELECT option_value FROM " . DB . "options WHERE option_name = '$option'" );
			
			while($r = $result->fetch_object()) {
				
				return $r->$option;
			}
	}

After thinking it through I saw that the return part is wrong. I need it to bring back the value of $option and not $option itself. So I changed it to this and it works:

function get_pm_option( $option ) {
	global $pmdb;

		$result = $pmdb->query( "SELECT option_value FROM " . DB . "options WHERE option_name = '$option'" );
			
			while($r = $result->fetch_object()) {
				
				return $r->option_value;
			}
	}

Ok. Mark it solved.