concatenate a mysql result

[bschultz]

I need to add a variable to a mysql row...

 

so...below, $position is the variable...then an underscore and then "number"

 

$row[$position_number]

 

 

I've tried this

 

$row[$position'.'_number]

 

and I get this...

 

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting ']'

 

Any ideas on how to concatenate these two?

 

[947740]

$key = $position . "_number";

 

$row[$key];

[bschultz]

This is going inside a function...and I'll need to print out ten different rows.... Is there any way to do it once...or will I have to come up with ten different "keys"?

[947740]

$key will be redefined each time you do it...which means that $row will have ten different keys, along with 10 corresponding values.

 

If you don't like my way, try this:

 

$row[$position."_number"]

[bschultz]

OK...this isn't working as I wanted it to...

 

Here's some background.  I have a database that is filled with football (American football) players information (jersey number, name, height, weight, etc).  For several reasons...ALL INFO IS IN ONE DATABASE TABLE...and it has to stay this way.

 

I have a config file that will list who is playing for a given day...broken down by position.

 

It's format is such:

 

$de1 = "93";

$de2 = "48";

$de3 = "1";

 

The format above is this:  $de1, $de2 and $de3 are positions on the field.  The numbers 93, 48, and 1 are the players jersey numbers that will be playing for the game.

 

I currently have this code to print out the players name, height weight and all other data from the database.

 

<table border="0" cellspacing="0" cellpadding="0">
  <tr>

<td width="290">
<?php  
mysql_select_db('briansch_2009fb',$dbc);
$sql = " SELECT * FROM 
(select number as de1_number, name as de1_name, pos as de1_pos, height as de1_height, weight as de1_weight, year as de1_year, city as de1_city, state as de1_state, tackles as de1_tackles, tfl as de1_tfl, sack as de1_sack, intc as de1_int, fumb as de1_fumb FROM $db_select_own WHERE number = $de1) as de1";
$rs = mysql_query($sql,$dbc);  
$matches = 0; 
while ($row = mysql_fetch_assoc($rs))  {  $matches++;   
echo "<table border='0'><tr><div class='position'>$dend_left</div></tr><tr>";
echo "<td class='number'>$row[de1_number]</td><td><div class='name'>$row[de1_name]</div><div class='height'>$row[de1_height], $row[de1_weight] - $row[de1_year]<br />$row[de1_city] $row[de1_state] </div><div class='stats'><strong>$row[de1_tackles] Tack - $row[de1_tfl] TFL - $row[de1_sack] Sacks <br /> $row[de1_int] INT -  $row[de1_fumb] FUMB</strong></div></td></tr><tr>"; }
if (! $matches) { 
echo "<table border='0'><tr><div class='position'></div></tr><tr>";
echo "<td class='number'></td><td><div class='name'></div><div class='height'></div><div class='stats'><strong></strong></div></td></tr></table>"; }    
?>
<?php  
mysql_select_db('briansch_2009fb',$dbc);
$sql = " SELECT * FROM 
(select number as de2_number, name as de2_name, pos as de2_pos, height as de2_height, weight as de2_weight, year as de2_year, city as de2_city, state as de2_state, tackles as de2_tackles, tfl as de2_tfl, sack as de2_sack, intc as de2_int, fumb as de2_fumb FROM $db_select_own WHERE number = $de2) as de2";
$rs = mysql_query($sql,$dbc);  
$matches = 0; 
while ($row = mysql_fetch_assoc($rs))  {  $matches++;   
echo "<td class='number'>$row[de2_number]</td><td><div class='name'>$row[de2_name]</div><div class='height'>$row[de2_height], $row[de2_weight] - $row[de2_year]<br />$row[de2_city] $row[de2_state] </div><div class='stats'><strong>$row[de2_tackles] Tack - $row[de2_tfl] TFL - $row[de2_sack] Sacks <br /> $row[de2_int] INT -  $row[de2_fumb] FUMB</strong></div></td></tr><tr>"; }
if (! $matches) { 
echo "<table border='0'><tr><div class='position'></div></tr><tr>";
echo "<td class='number'></td><td height='60'><div class='name'></div><div class='height'></div><div class='stats'><strong></strong></div></td></tr></table>"; }    
?>
<?php  
mysql_select_db('briansch_2009fb',$dbc);
$sql = " SELECT * FROM 
(select number as de3_number, name as de3_name, pos as de3_pos, height as de3_height, weight as de3_weight, year as de3_year, city as de3_city, state as de3_state, tackles as de3_tackles, tfl as de3_tfl, sack as de3_sack, intc as de3_int, fumb as de3_fumb FROM $db_select_own WHERE number = $de3) as de3";
$rs = mysql_query($sql,$dbc);  
$matches = 0; 
while ($row = mysql_fetch_assoc($rs))  {  $matches++;   
echo "<td class='number'>$row[de3_number]</td><td><div class='name'>$row[de3_name]</div><div class='height'>$row[de3_height], $row[de3_weight] - $row[de3_year]<br />$row[de3_city] $row[de3_state] </div><div class='stats'><strong>$row[de3_tackles] Tack - $row[de3_tfl] TFL - $row[de3_sack] Sacks <br /> $row[de3_int] INT -  $row[de3_fumb] FUMB</strong></div></td></tr></table>"; }
if (! $matches) { 
echo "<table border='0'><tr><div class='position'></div></tr><tr>";
echo "<td class='number'></td><td><div class='name'></div><div class='height'></div><div class='stats'><strong></strong></div></td></tr></table>"; }    
?>
</td>

 

I'd like to simplify this by creating a function...and then just calling the function once per position on the field.

 

I tried this:

 

<?php

function football_table($postion){ 

$pos_number = $position . "_number";
$pos_name = $position . "_name";
$pos_pos = $position . "_pos";
$pos_height = $position . "_height";
$pos_weight = $position . "_weight";
$pos_year = $position . "_year";
$pos_city = $position . "_city";
$pos_state = $position . "_state";
$pos_tackles = $position . "_tackles";
$pos_tfl = $position . "_tfl";
$pos_sack = $position . "_sack";
$pos_int = $position . "_int";
$pos_fumb = $position . "_fumb";


$dbc = mysql_connect('localhost', 'xxx', 'xxx'); 
mysql_select_db('briansch_2009fb',$dbc);
$sql = " SELECT * FROM 
(select number as $pos_number, name as $pos_name, pos as $pos_pos, height as $pos_height, weight as $pos_weight, year as $pos_year, city as $pos_city, state as $pos_state, tackles as $pos_tackles, tfl as $pos_tfl, sack as $pos_sack, intc as $pos_int, fumb as $pos_fumb FROM $db_select_own WHERE number = $position) as $position";


$rs = mysql_query($sql,$dbc);  
$matches = 0; 
while ($row = mysql_fetch_assoc($rs))  {  $matches++;   
print "<table border='0'><tr><div class='position'>$dend_left</div></tr><tr>";
print "<td class='number'>$row[$pos_number]</td><td><div class='name'>$row[$pos_name]</div><div class='height'>$row[$pos_height], $row[$pos_weight] - $row[$pos_year]<br />$row[$pos_city] $row[$pos_state] </div><div class='stats'><strong>$row[$pos_tackles] Tack - $row[$pos_tfl] TFL - $row[$pos_sack] Sacks <br /> $row[$pos_int] INT -  $row[$pos_fumb] FUMB</strong></div></td></tr><tr>"; }
if (! $matches) { 
print "<table border='0'><tr><div class='position'></div></tr><tr>";
print "<td class='number'></td><td><div class='name'></div><div class='height'></div><div class='stats'><strong></strong></div></td></tr></table>"; }    


}
echo football_table(de1);
echo football_table(de2);
echo football_table(de3);
?>

 

The problems that I'm having are:

 

- If I echo $sql, the position de1 isn't being included in the echo...so I'm obviously doing something wrong in my defining of the variable. 

- If I add in the de1 manually, I still get an error

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

 

Any ideas?

 

Thanks!!!!!

[Cagecrawler]

If this is the actual code you're using you've spelt $position wrong when defining it as an argument.

 

function football_table($postion){

should be

function football_table($position){

 

[bschultz]

OK...that was dumb.  Now if I echo $sql, the query is as it should be...

 

But, I still get this:

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

[Cagecrawler]

Try debugging with this just after your mysql_query call.

if (!$rs) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($rs) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

[bschultz]

It threw this error...

 

Could not successfully run query ( SELECT * FROM (select number as de1_number, name as de1_name, pos as de1_pos, height as de1_height, weight as de1_weight, year as de1_year, city as de1_city, state as de1_state, tackles as de1_tackles, tfl as de1_tfl, sack as de1_sack, intc as de1_int, fumb as de1_fumb FROM WHERE number = de1) as de1) from DB: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE number = de1) as de1' at line 2

[bschultz]

OK...upon looking more closely at the error, there was no db table in the query.  Obviously, it's not pulling that from an include earlier in this script.

 

So, can I use an include of a config file (for DB connection info) inside a function?

 

If I manually enter the table name, I get this error:

 

Unknown column 'de1_number' in 'where clause'

 

de1_number was defined in the select...what's going on here?

[bschultz]

Upon further testing...I think I've made this harder than it needs to be.

 

This code works...sort of...

 

<?php

function football_table($position){ 
$dbc = mysql_connect('localhost', 'xxx', 'xxx'); 
mysql_select_db('briansch_2009fb',$dbc);
$sql = " SELECT * FROM bschultz_own WHERE number = '99'";
$rs = mysql_query($sql,$dbc); 
if (!$rs) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}
if (mysql_num_rows($rs) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
} 
$matches = 0; 
while ($row = mysql_fetch_assoc($rs))  {  $matches++;   
print "<table border='0'><tr><div class='position'>$dend_left</div></tr><tr>";
print "<td class='number'>$row[number]</td><td><div class='name'>$row[name]</div><div class='height'>$row[height], $row[weight] - $row[year]<br />$row[city] $row[state] </div><div class='stats'><strong>$row[tackles] Tack - $row[tfl] TFL - $row[sack] Sacks <br /> $row[int] INT -  $row[fumb] FUMB</strong></div></td></tr><tr>"; }
if (! $matches) { 
print "<table border='0'><tr><div class='position'></div></tr><tr>";
print "<td class='number'></td><td><div class='name'></div><div class='height'></div><div class='stats'><strong></strong></div></td></tr></table><br /><br />"; } 
}


echo "this goes before the player<br />";
echo football_table(de1);
echo "this goes after the player";
?>

 

The function part of this works...other than at the bottom...

 

echo "this goes before the player<br />";

echo "this goes after the player";

 

Both of these show up BEFORE the database results.

 

Also, if I try to change the WHERE clause to use the variable defined in the function...

 

WHERE number = $position";

 

I still get the Unknown column 'de1' in 'where clause' error.

 

Any ideas?

 

Thanks!

[Pikachu2000]

echo "this goes before the player<br />";

echo "this goes after the player";

 

Both of these show up BEFORE the database results.

 

They will, because they are being echoed outside of the proper table structure. View the page HTML source to see where they are being echoed.

 

Also, if I try to change the WHERE clause to use the variable defined in the function...

 

WHERE number = $position";

 

I still get the Unknown column 'de1' in 'where clause' error.

 

Any ideas?

 

Thanks!

 

Double check to be sure you have a column by that name in the bschultz_own table.

[bschultz]

There is no column de1...that variable is defined in a "config" file that is "required" earlier in the page.  It's defined as "99"...the jersey number of the player playing that position.  In the database, the column "number" will match "99"...as the select statement says.

[Pikachu2000]

You are passing the string literal 'de1' to the function in the - echo football_table(de1); statement. I believe it should be: echo football_table($de1);

[bschultz]

That did it...thanks to everyone!