Using a preset variable to pull a column name, then use it within a query

Question

I am trying to create a pricing system for a bookings company, I have a DataBase table full of prices. A typical row looks like this:

priceID  |  manufac  |  model  |  janDay  | janWeek  |  febDay  |  febWeek  |  etc etc
--------------------------------------------------------------------------------------
1        |Chevrolelt |  Matiz  |  40.00   |  133.00  |  40.00   |  133.00   |  etc etc

Basically I have a variable:

$startmonth

This variable is set by my code finiding out the month, and depending on its number assigning it a value. In this instance lets assume that it has gone through that and obtained the value:

$startmonth = 'febWeek';

I then have another query that finds out the model of a car, I have tested this and it returns the model, so we know that:

$model = 'Matiz';

Then I execute another query to gain the price:

$getprice = mysql_query("SELECT '".$startmonth."' FROM prices WHERE model = '".$model."' ");
while($ppdrow = mysql_fetch_assoc($getprice)) {

   $priceperday = $ppdrow;

}

I have tried this query numerous ways and I always end up with errors or an undesirable result. When it should return 133.00.

What it actually returns:

( [2] => 2 )

I am proobably doing something really stupid, but can anyone tell me what please?

Sid · Answer 1 · 2013-02-27T09:35:51.023

3

You can try this

$getprice = mysql_query("SELECT ".$startmonth." FROM prices WHERE model ='".$model."'   ");
while($ppdrow = mysql_fetch_assoc($getprice)) {

 $priceperday = $ppdrow[$startmonth];

}

edited Feb 27 '13 at 09:35

answered Feb 27 '13 at 09:30

Sid

854
9
23

score 2 · Accepted Answer · edited May 23 '17 at 10:25

2

Try this query instead:

$getprice = mysql_query("SELECT `".$startmonth."` FROM prices WHERE model = '".$model."' ");

Note the backticks (around the column name) in the place of the single quotes.

And as for the while loop, you are trying to set $priceperday equal to $ppdrow, which is an array. You want to set it to the correct value (column) within the array:

while($ppdrow = mysql_fetch_assoc($getprice)) {
   $priceperday = $ppdrow[$startmonth];
}

Please also read about why not to use mysql_*

edited May 23 '17 at 10:25

Community

1
1

answered Feb 27 '13 at 09:28

George

36,413
9
66
103

backtick does what exactly? just so I know. :) Thank you for the answer. – Bohdi Feb 27 '13 at 09:47
A delimiter isn't actually neccessary in an SQL query, UNLESS the column/table name is the same as a [reserved word](http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html). In which case, the backticks tell MySQL *this is a name of a column/table, not a reserved word* – George Feb 27 '13 at 09:49

Using a preset variable to pull a column name, then use it within a query

2 Answers2