comment pls on how i take data from the database..

[shotokan99]

as of now im using this approach to take data:
=====================================
require('conn.php');
$q='select * from '.$dbf_vid.' order by xrank';
$res=mysql_query($q) or die('error: cannot perform query on new');
$num=mysql_numrows($res);
$i=0;

while($i<$num){
$id=mysql_result($res,$i,'xid');
$rank=mysql_result($res,$i,'xrank');
$title=mysql_result($res,$i,'xtitle');
$artist=mysql_result($res,$i,'xartist');
$taglink=mysql_result($res,$i,'xtaglink');
$img=mysql_result($res,$i,'ximg');
$ref=mysql_result($res,$i,'xref');
$swf=mysql_result($res,$i,'xswf');

if($i%2==0){
$color='#BCBCDE';
$i++;
}else{
$color='#FFFFFF';
$i++;
}
echo'<tr>';
echo' <td bgcolor="'.$color.'" width="33"><font face="Verdana"
size="1">'.$id.'</font></td>';
echo' <td bgcolor="'.$color.'" width="28"><font face="Verdana"
size="1">'.$rank.'</font></td>';
echo' <td bgcolor="'.$color.'" width="183"><font face="Verdana"
size="1">'.$title.'</font></td>';
echo' <td bgcolor="'.$color.'" width="213"><font face="Verdana"
size="1">'.$artist.'</font></td>';
echo' <td bgcolor="'.$color.'" width="110"><font face="Verdana"
size="1">'.$ref.'</font></td>';
echo' <td bgcolor="'.$color.'" width="54"><font face="Verdana"
size="1">
<a href="mtvdel.php?id='.$id.'&dbf=mtv_topvid_ph">del ete</
a></font></td>';
echo' <td bgcolor="'.$color.'" width="53"><font face="Verdana"
size="1"><a href="mtvedit.php?
id='.$id.'
&rank='.$rank.'
&title='.$title.'
&by='.$artist.'
&taglink='.$taglink.'
&ref='.$ref.'
&img='.$img.'
&swf='.$swf.'
">edit</a></font></td>';
echo' </tr>';
}//end while

==================================
this works fine with me, but is there much better approach compared to
what im doing?

tnx

[Schraalhans Keukenmeester]

At Mon, 21 May 2007 19:32:41 -0700, shotokan99 let his monkeys type:

as of now im using this approach to take data:
=====================================
require('conn.php');
$q='select * from '.$dbf_vid.' order by xrank';
$res=mysql_query($q) or die('error: cannot perform query on new');
$num=mysql_numrows($res);
$i=0;

while($i<$num){
$id=mysql_result($res,$i,'xid');
$rank=mysql_result($res,$i,'xrank');
$title=mysql_result($res,$i,'xtitle');
$artist=mysql_result($res,$i,'xartist');
$taglink=mysql_result($res,$i,'xtaglink');
$img=mysql_result($res,$i,'ximg');
$ref=mysql_result($res,$i,'xref');
$swf=mysql_result($res,$i,'xswf');

if($i%2==0){
$color='#BCBCDE';
$i++;
}else{
$color='#FFFFFF';
$i++;
}
echo'<tr>';
echo' <td bgcolor="'.$color.'" width="33"><font face="Verdana"
size="1">'.$id.'</font></td>';
echo' <td bgcolor="'.$color.'" width="28"><font face="Verdana"
size="1">'.$rank.'</font></td>';
echo' <td bgcolor="'.$color.'" width="183"><font face="Verdana"
size="1">'.$title.'</font></td>';
echo' <td bgcolor="'.$color.'" width="213"><font face="Verdana"
size="1">'.$artist.'</font></td>';
echo' <td bgcolor="'.$color.'" width="110"><font face="Verdana"
size="1">'.$ref.'</font></td>';
echo' <td bgcolor="'.$color.'" width="54"><font face="Verdana"
size="1">
<a href="mtvdel.php?id='.$id.'&dbf=mtv_topvid_ph">del ete</
a></font></td>';
echo' <td bgcolor="'.$color.'" width="53"><font face="Verdana"
size="1"><a href="mtvedit.php?
id='.$id.'
&rank='.$rank.'
&title='.$title.'
&by='.$artist.'
&taglink='.$taglink.'
&ref='.$ref.'
&img='.$img.'
&swf='.$swf.'
">edit</a></font></td>';
echo' </tr>';
}//end while

==================================
this works fine with me, but is there much better approach compared to
what im doing?

tnx

[...]
if (mysql_numrows($res) === 0) {
// do_something when no rows found
// and exit
}
while ($row = mysql_fetch_assoc($res)) {
// your html here, replace your var names with
// $row['fieldname'] (e.g. $row['title'])
}

All the quotes can make things confusing. Consider using heredoc syntax
instead, then you only have to worry about quotes you actually need in the
html.

echo <<<HTML_END
<td bgcolor="$row['color']" width="33">
<font face="Verdana" size="1">$row['id']</font>
</td>';
(etc etc)
HTML_END; // no spaces before this line, just the heredoc endtag and a ;
HTH
Sh.

[Toby A Inkster]

shotokan99 wrote:

this works fine with me, but is there much better approach compared to
what im doing?

Yes.

<?php
require('conn.php');
$q="SELECT xid,xrank,xtitle,xartist,xref,xtaglink,ximg,xswf
FROM $dbf_vid
ORDER BY xrank;";
$res=mysql_query($q) or die('error: cannot perform query on new');
$i=0;

$shown_fields = array('id', 'rank', 'title', 'artist', 'ref');

while($data = mysql_fetch_assoc($res))
{
$class = (++$i%2==0) ? 'even' : 'odd';

printf('<tr class="%s">', htmlentities($class));

foreach ($shown_fields as $f)
printf('<td class="%s">%s</td>', $f, htmlentities($data['x'.$f]));

printf('<td class="delete"><a href="mtvdel.php?id=%s&amp;dbf=mtv_topvid_ph">dele te</a></td>',
htmlentities(urlencode($data['xid'])));

$editurl = sprintf('id=%s&rank=%s&title=%s&by=%s&taglink=%s&r ef=%s&img=%s&swf=%s',
urlencode($data['xid']),
urlencode($data['xrank']),
urlencode($data['xtitle']),
urlencode($data['xartist']),
urlencode($data['xtaglink']),
urlencode($data['xref']),
urlencode($data['ximg']),
urlencode($data['xswf']));
printf('<td class="edit"><a href="%s">edit</a></td>', htmlentities($editurl));

echo "</tr>\n";
}
?>

Advantages:

* You don't specify which columns exist in your database, so you
may be fetching other columns that you don't need. Hence I've
specified the exact columns to select.

* I've removed all the colours, fonts and so on from the output,
replacing them with class="odd" and class="even" attributes on the
table cells, which can be styled appropriately via CSS.

* I've not specified any widths -- browsers are generally pretty
good at guessing widths themselves. I've added classes to the
table cells so that you can use CSS to specify widths if required.

* I've made sure that all output is properly urlencoded and special
HTML characters are escaped. A mixture of printf(), htmlentities()
and urlencode() makes this fairly easy and very clear.

* I've output the first five columns using a foreach loop, which
reduces repetition in your code.

* I've used mysql_fetch_assoc() which is significantly faster than
repeated calls to mysql_result(). This also eliminates the need
to call mysql_numrows() entirely.

* I've added a line break after "</tr>" to make the resulting HTML
slightly easier to read.

--
Toby A Inkster BSc (Hons) ARCS
http://tobyinkster.co.uk/
Geek of ~ HTML/SQL/Perl/PHP/Python/Apache/Linux