Welcome to WebmasterWorld Guest from 23.20.215.116

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)

Moderator from US 

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

joined:Dec 10, 2005
posts:5616
votes: 44


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)

Senior Member

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

joined:Aug 7, 2003
posts:4783
votes: 0


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)

Moderator from US 

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

joined:Dec 10, 2005
posts:5616
votes: 44


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)

Senior Member

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

joined:Aug 7, 2003
posts:4783
votes: 0


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)

Moderator from US 

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

joined:Dec 10, 2005
posts:5616
votes: 44


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)

Senior Member

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

joined:Aug 7, 2003
posts:4783
votes: 0


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)

Senior Member

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

joined:Aug 7, 2003
posts:4783
votes: 0


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