getting/assigning variables from a DB

not sure how to do in PHP

[LifeinAsia]

I can do the following in ColdFusion, but I'm a PHP noob. I have the following table:

VarName | DefaultValue | AltValueA | AltValueB
'var1' | 'Default value 1' | 'Alt Value 1' | NULL
'var2' | 'Default value 2' | NULL | 'Alt Value 2'

What I want to do is:
1) Pass a parameter of "a" or "b" to the page (assume appropriate SQL injection protection).
2) Get the appropriate values from the table.
3) Assign the value to the variable.
4) Display the VarName and its value.

Using ColdFusion and MSSQL, I would do something like the following:
<cfquery name="TheQuery" datasource="TheDS">
SELECT VarName, IsNull(AltValue#ThisParam#,DefaultValue) AS TheValue
FROM TheTable
</cfquery>
<cfloop query="TheQuery">
<cfset "#TheQuery.VarName#"=TheQuery.TheValue>
</cfloop>
<cfoutput query="TheQuery">
#TheQuery.VarName#: #Evaluate("#TheQuery.VarName#")#>
</cfoutput>

So, if I pass in "a" I would get:
var1: Alt Value 1
var2: Default value 2

So, if I pass in "b" I would get:
var1: Default value 1
var2: Alt Value 2

How to do steps 2 & 3 in PHP? Thanks!

[swa66]

You could do it all in mysql ...

IFNULL(expr1,expr2) returns expr1 with expr1 is not null and expr2 when expr1 is null.
for the rest it's just creating a sql query

Ref: [dev.mysql.com...]

Or you could just query the 2 colums you need and do the test in php.

[LifeinAsia]

By doing it in MySQL, I don't see how that actually assigns the resulting values to var1 and var2.

Also, how do you identify the fieldname when it's based on a variable? The example I gave only has 2 options- a or b, but the actual table will have more than 5 to start, with others added as time goes on. So the fieldnames have to be designated dynamically.

[swa66]

First of all, assigning the variables directly feels just freaks me out.
It can be done, but it would be right up there among "not a good idea".

IMHO the proper way to do it would be to store the key/values in an array.

Since you're begiing with this:
- forget the mysql interface (it's obsolete)
- use mysqli instead
- use prepared statements where you can (doesn't help here), but in general it's FAR more robust against injection.

Eg.: (untested)

  $col='A';
// $col='B';

  $sql='SELECT `VarName`, IFNULL(`AltValue'.$col.'`,`DefaultValue `) FROM TheTable;';
  if( !$stmt=$mysqli->prepare($sql) ) {
   die('ERROR: database error, giving up.');
  }
  if(!$stmt->execute()) {
   die('ERROR: database error, giving up.');
  }
  $stmt->bind_result($key,$val);
  while ($stmt->fetch()) {
    $vars[$key]=$val;
  }
  $stmt->close();

This would create an array with the variables in them.


BTW: if you insist on using the variable with unpredictable names:
[php.net...]

And change the line

    $vars[$key]=$val;

above with

$$key=$val;

[LifeinAsia]

Thanks- that looks like what I need.

Now, if I can just get the MSSQL connection working...

[swa66]

Part of a connect function I use, modified for removing dependencies to other functions:

 $server = '127.0.0.1';
 $user = 'user';
 $pass = 'password';
 $db = 'database';
 
 $mysqli = new mysqli($server, $user, $pass, $db);
 
 if ($mysqli->connect_errno) {
  die('mysqli connect: '.$mysqli->connect_error.' in '. __FILE__ .' on line '. __LINE__);
 }
 
 $mysqli->set_charset("utf8");

The last line makes php and mysql use UTF-8 to talk to one another (I use UTF-8 everywhere (in the html, between php and mysql and in the tables.)

[swa66]

Oh, wait, are you using MSSQL (SQL server from MSFT) ?
Or MySQL (the Open Source Oracle stuff) ?

All I wrote was about MySQL.

MSSQL: I've never touched that.
But to get you started: [php.net...]