Looping through MySQL fields newbie question

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

Moderator: General Moderators

Post Reply
User avatar
lazersam
Forum Contributor
Posts: 105
Joined: Sat Nov 15, 2003 4:07 am
Location: Hertfordshire, UK

Looping through MySQL fields newbie question

Post 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.
Gen-ik
DevNet Resident
Posts: 1059
Joined: Mon Aug 12, 2002 7:08 pm
Location: London. UK.

Post 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.
Last edited by Gen-ik on Sat Dec 13, 2003 2:06 pm, edited 1 time in total.
User avatar
igoy
Forum Contributor
Posts: 203
Joined: Fri May 02, 2003 11:57 pm
Location: India
Contact:

Post 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]
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post 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.
User avatar
lazersam
Forum Contributor
Posts: 105
Joined: Sat Nov 15, 2003 4:07 am
Location: Hertfordshire, UK

Post 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.
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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. "'";
Post Reply