homepage Welcome to WebmasterWorld Guest from 54.197.111.87
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
getting/assigning variables from a DB
not sure how to do in PHP
LifeinAsia




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

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




msg:4578222
 3:11 pm on May 27, 2013 (gmt 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: https://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull

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

LifeinAsia




msg:4578224
 3:20 pm on May 27, 2013 (gmt 0)

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




msg:4578304
 7:01 pm on May 27, 2013 (gmt 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;
LifeinAsia




msg:4578314
 7:40 pm on May 27, 2013 (gmt 0)

Thanks- that looks like what I need.

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

swa66




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

swa66




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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved