Page 1 of 1

Looping through MySQL fields newbie question

Posted: Sat Dec 13, 2003 9:15 am
by lazersam
Hi all

I have a database where I have named the fields flag1, flag2 etc..

I now want to retreive them to work with their data. I am currently doing it like this;

Code: Select all

$row = mysql_fetch_assoc($rs);
	
	$flag1 = $row ['flag1']; $flag11 = $row ['flag11'];
	$flag2 = $row ['flag2']; $flag12 = $row ['flag12'];
	$flag3 = $row ['flag3']; $flag13 = $row ['flag13'];
	$flag4 = $row ['flag4']; $flag14 = $row ['flag14'];
	$flag5 = $row ['flag5']; $flag15 = $row ['flag15'];
	$flag6 = $row ['flag6']; $flag16 = $row ['flag16'];
	$flag7 = $row ['flag7']; $flag17 = $row ['flag17'];
	$flag8 = $row ['flag8']; $flag18 = $row ['flag18'];
	$flag9 = $row ['flag9']; $flag19 = $row ['flag19'];
	$flag10 = $row ['flag10']; $flag20 = $row ['flag20'];
I know there must be a better way. Does anyone know how I might loop through these field names?

Regards

Lawrence.

Posted: Sat Dec 13, 2003 9:26 am
by Gen-ik
There are two ways to do it. You could use either mysql_fetch_array or mysql_fetch_object. Here are a couple of examples..

Code: Select all

<?php

$result = mysql_query("SELECT * FROM `Peoples`");

while($arr = mysql_fetch_array($result))
{
     echo $arr["Name"];
     echo $arr["Age"];
}


$result = mysql_query("SELECT * FROM `Peoples`");

while($obj = mysql_fetch_object($result))
{
     echo $obj->Name;
     echo $obj->Age;
}

?>
Hope that helps.

Posted: Sat Dec 13, 2003 9:28 am
by igoy
if you are using $flag1 or $flagwhatever number, to output somewhere.... you can create and use an array.

Code: Select all

$row = mysql_fetch_assoc($rs));

$i = 0;
do {
    $i++;
    $flag[$i] =$row['flag'.$i];
} while ($row = mysql_fetch_assoc($rs));
then you can use $flag array to output like this

Code: Select all

echo $flag[1];

// will output whatever value is in database for field [b]$flag1[/b]

Posted: Sat Dec 13, 2003 1:19 pm
by jason

Code: Select all

<?php 

echo "<table>\n";
while ( $row = mysql_fetch_assoc($rs) ) {
    echo "<tr>\n";
    foreach ( $row as $value ) {
        echo "<td>$value</td>\n";
    }
    echo "</tr>\n";
}
echo "</table>\n";

?>
Obviously, you can modify this to your hearts content. But this should give you an idea of how to easily output information from a database into a nice HTML table. From here, you can also modify the above to simply get all the values and put them in values, etc.

Posted: Sun Dec 14, 2003 2:22 am
by lazersam
Thanks guys :) - I have modified my code now.

On the same theme, I am creating a query using the variable names flag1, flag2 etc. But I have substiuted the numerical part with a variable called $search.

My query looks like this;

Code: Select all

$sql = "select * from flags1 where flag$search = "$s"  ";
This works fine - I was just wondering whether it was dangerous to place a variable ($search) along side the begining of the field name? I have tried various alternatives such as flag[$search] but this doesnt work.

As it seems to work OK should I just leave it alone?

Regards


Lawrence.

Posted: Sun Dec 14, 2003 2:43 am
by infolock

Code: Select all

$flag=array();
while($row = mysql_fetch_assoc($rs)) 
{   
   foreach($row as $flags)
   {
      $flag[]=$flags;
   }
}

echo '<table>';
for($i=0; $i<=count($flag); $i++)
{
   echo '<tr><td>'.$flag[$i].'</td></tr>';
}

I'm not sure why you would want to do this as it's gonna end up querying your table a lot more than is actually needed...

i'm not sure if this is correct or not, but are you suggesting querying the table for specific flags only? like, you just want to show the one you need, and don't have plans looping this query for each individual flag as well? because if you are just wanting to write this for one flag, then sure this is fine.

and just to answer your question, there is nothing wrong with putting variables beside your fieldnames or as your field names. i just recommend exiting the string when you want to call those variables. helps you when you need to do trouble shooting and don't have to search close for those variables when you can just see where you exited the query.

Code: Select all

$sql = "select * from flags1 where flag" .$search. " = '" .$s. "'";