Select $variable

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

gfirman
Forum Commoner
Posts: 30
Joined: Tue Nov 21, 2006 10:41 am

Select $variable

Post by gfirman »

Hi,

I'm doing an sql select statement and need to select a particular column from a table. The column name is stored in a variable. Is this possible.
This is what I'm trying to do:

SELECT $drug FROM userdrug WHERE id='$drugid'


Does anyone know if this is possible or is there a way around it?
Last edited by gfirman on Sun Apr 01, 2007 2:32 pm, edited 1 time in total.
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post by Oren »

Yes it is possible.

P.S Instead of asking, couldn't you just try it by yourself?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Next time, try it out first before asking.

Now, to answer your problem, then answer is yes. Any form of string manipulation is fine.. you just need to make sure your building a valid query string.
gfirman
Forum Commoner
Posts: 30
Joined: Tue Nov 21, 2006 10:41 am

Post by gfirman »

I have tried out lots of different stuff and searched the net, but i just can't get it to work
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

echo out the query string to see exactly what the outcome is, and post it here.

You might also want to consider that the variable is indeed a valid column name.. Perhaps have an array of valid column names and check the variable using in_array(). Another thing, you might want to wrap your column names in backticks to avoid reserved name error.

Also, what does mysql_error() tell you? (If you don't know how to use mysql_error -- mysql_query() or die(mysql_error()))
Last edited by John Cartwright on Sun Apr 01, 2007 2:37 pm, edited 2 times in total.
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post by Oren »

Show us a piece of your code (a relevant piece of course :P).
gfirman
Forum Commoner
Posts: 30
Joined: Tue Nov 21, 2006 10:41 am

Post by gfirman »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Ok I know what the problem is. I'v had this problem before and I don't know how to get around it. Can you guys help.

This piece of code works only because when i'm assigning the value to a variable, the name of the variable must be the column name which is selected. crystalmeth is a column name, but there are many others. How do i assign a value to a different variable name?

Code: Select all

//Selecting from drug table
		$sqldrugs = "SELECT $labdrug FROM userdrugs WHERE id='$drugid'";
		$resdrugs = mysql_query($sqldrugs,$db)
		or die("Couldn't execute query1.");
		while($rowdrugs = mysql_fetch_array($resdrugs))
		{
		extract($rowdrugs);
		$crystalmeth;
		}
		echo "lab drug quantity: $crystalmeth";

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post by Oren »

First, please use the PHP tags when you post your code.
Second, I don't see why you use extract and I also don't get what the second line in your while loop is for?!?! :?

By the way, what is the output of this code when you try to run it? Do you see on the screen: Couldn't execute query1. or something else?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

You didn't address anything in my last post :?
gfirman
Forum Commoner
Posts: 30
Joined: Tue Nov 21, 2006 10:41 am

Post by gfirman »

Have you read my last post?

But anyway. What else could I use instead of extract? This is just what I use the whole time. I have never knew there was another way to do it. And as for the while loop, the same thing again. Its just what I saw other people using so I use it now as well.
Is there a better way to do it?
gfirman
Forum Commoner
Posts: 30
Joined: Tue Nov 21, 2006 10:41 am

Post by gfirman »

Sorry jcart,

Yes it is a valid column name
But where should I put the brackets
- in the select statement
or
-where the value is being assigned to the variable

What type of brackets do I use
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

I'm not going to guess why your things arn't working.. so if you can't provide the details I asked I won't be able to help you.

As for the backticks.. they are `

Code: Select all

SELECT `column` FROM `table` 
Also, instead of extract simply reference the column by using the array indice.

Code: Select all

$rowdrugs['column_name_goes_here']
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Post by JellyFish »

Jcart wrote:I'm not going to guess why your things arn't working.. so if you can't provide the details I asked I won't be able to help you.

As for the backticks.. they are `

Code: Select all

SELECT `column` FROM `table` 
Also, instead of extract simply reference the column by using the array indice.

Code: Select all

$rowdrugs['column_name_goes_here']
Wait a minute. What are backticks used for MySQL syntax? I never known about them...
gfirman
Forum Commoner
Posts: 30
Joined: Tue Nov 21, 2006 10:41 am

Post by gfirman »

Em, I don't understand. I told you that there is no problem with the column name in the $labdrug variable. What else were you asking?

Anyway I'v been trying assign the value to a variable the way you showed me, but can't get it to work. Could you show me an example of how it works.

I have also used the tickbacks and they didn't do anything.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Post by JellyFish »

What value are you trying to assign to a variable exactly?
Post Reply