Using Variable in MySQL query

[nita]

Hi everone.

 

Having a problem with getting the values from a database.

 

What im trying to do is:

I use variable 'varname' from packaging_items table (values are coressponding to the names of columns in packaging table ... pack01, pack02 .. and so on).

But in query result1 instead of getting the value of (pack01, pack02 ..) i get the names of columns (pack01, pack02 ..)

Here is my short code: (ofcourse there is more to it, but this bit is most important)

$result = mysql_query("SELECT * FROM packaging_items") or die(mysql_error());  
while($row = mysql_fetch_array($result)) {  
   $data1 = $row['varname']; 
   $name = $row['name']; 
   $price = $row['price']; [/background][/size][/font][/color]


[color=#464646][font='Helvetica Neue', Helvetica, Arial, sans-serif][size=3][background=rgb(244, 244, 244)]$result1=mysql_query("SELECT `$data1` FROM packaging WHERE orderno='$orderno' LIMIT 1") or die(mysql_error()); 
while($row1 = mysql_fetch_array( $result1 )) { 
   if ( $data1 == '' ) {} else { 
   echo" <tr><td>$name</td><td>$data1</td><td>£$price</td><tr>"; } 
} 
}  

 

Im stuck here, tried some other options .. and only get worst..

 

What do i wrong .. if someone can help will be nice.

 

Thank you in advance!

[nita]

for example when i hardcode the query, results are ok and that is what i want to achive here

$result1=mysql_query("SELECT pack01 FROM packaging WHERE orderno='$orderno' LIMIT 1") or die(mysql_error());
while($row1 = mysql_fetch_array( $result1 )) {
$pack01 = $row1['pack01'];
echo "$pack01";
}

[Christian F.]

For what you want to do you'll need to use a JOIN. Running SQL queries inside loops is not only extremely wasteful, but also a sign of you doing something rather unnecessary.

 

By using a JOIN you'll fetch all of the necessary data from the database with only one query, which you then loop over to process/output as you like. Lots of tutorials on how to do this, and thread on this forum dealing with this exact problem.

[nita]

got it fixed ...

 

$result1=mysql_query("SELECT ".$data1." as data1 FROM packaging WHERE orderno='$orderno' LIMIT 1") or die(mysql_error());

while($row1 = mysql_fetch_array( $result1 )) {

$data = $row1['data1'];

if ( $data == '' ) {} else { echo" <tr><td>$name</td><td>$data</td><td>£$price</td><tr>"; }

 

}

[Christian F.]

Ehmm... Looking at that "fix", I think you might have some issues with your database structure as well. As such I recommend posting the results of SHOW CREATE {table} in the MySQL section of this forum, asking for feedback and tips on how you can improve it.

If I'm right, then it will help make things a lot easier for you. Not only right now, but especially so in the future.

[nita]

Certainly will take your advice in mind, will look into JOIN mysql, could you give me some link with basic example that i can look into and learn, that is what i need ! Thank you

[Christian F.]

Searching for "mysql join" on this forum gave lots of results, amongst those I found this:

http://forums.phpfreaks.com/topic/272567-groupingcategorizing-results/page__hl__+mysql%20+join#entry1402518