Welcome to WebmasterWorld Guest from 54.158.36.59

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

getting/assigning variables from a DB

not sure how to do in PHP

     
2:15 pm on May 27, 2013 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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!
3:11 pm on May 27, 2013 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
3:20 pm on May 27, 2013 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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.
7:01 pm on May 27, 2013 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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;
7:40 pm on May 27, 2013 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



Thanks- that looks like what I need.

Now, if I can just get the MSSQL connection working...
8:28 am on May 28, 2013 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.)
8:37 am on May 28, 2013 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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...]
 

Featured Threads

Hot Threads This Week

Hot Threads This Month