Page 1 of 1
List MySQL Table Items
Posted: Fri Aug 19, 2005 2:15 am
by iBloB
Hello.. i am exteremly new to MySQL..
What i am looking for is how I would post the contents of a table onto a webpage. This database is a list of games that I have. I have the following fields:
title = Game Title
cat = Game Category
host = Hosted By
creater = Created By
url = Game URL
I would like it to be listed like the following:
<a href="url" target="_blank">Title</a> | Cat | Host | Creater
<a href="url" target="_blank">Title</a> | Cat | Host | Creater
<a href="url" target="_blank">Title</a> | Cat | Host | Creater
<a href="url" target="_blank">Title</a> | Cat | Host | Creater
I also would like it to be possible for the viewers of the site to be able to sort the list by title, category, host, or creater.
I have no clue where to even start setting this up. I would really like it if someone could show me how to do this.
Posted: Fri Aug 19, 2005 5:51 am
by Subfusion
Try the following code, I done it really quick as I'm at work; I done the first part of it, where it displays the results.
Code: Select all
<?php
//Connect to MySQL Database
mysql_connect("localhost","user","password");
mysql_select_db("database");
$result = mysql_query("select * from x"); // Change x to your table name.
while($r=mysql_fetch_array($result)) {
$title = $r["title"];
$cat = $r["cat"];
$host = $r["host"];
$creater = $r["creater"];
$url = $r["url"];
//Display the data in HTML
echo '<a href="$url" target="_blank">$title</a> | $cat | $host | $creater';
}
?>
If you want to filter the results just change the $result variable.
Code: Select all
$result = mysql_query("select * from news order by title");
The above code would filter your results by the title.
Posted: Fri Aug 19, 2005 8:17 am
by feyd
for sorting by column, read a thread mentioned in the Useful Posts thread. A link to it can be found in my signature.
Posted: Fri Aug 19, 2005 2:15 pm
by iBloB
Okay I have used some of the things you guys gave me... just a couple bits of editing i need (thankfully there isnt an error i just need editing)
Heres what i got right now
http://iblob.info/games2.php
Notice how it shows the URL on the right side of the list.. Is there a way i can make it so instead of showing it there it makes the game title a link and have the url showin the link so it would be something like:
<a href="url_db_info" target="_blank">Game Title</a>
Heres the code i have:
Code: Select all
<?php
//make the database connection
$conn = mysql_connect("********", "******", "******");
mysql_select_db('********', $conn);
//organize data
$sql = 'SELECT * FROM games';
// add recognized names in here..
$recognized = array(
'title'=>array('title',0,1),
'cat'=>array('cat',0,1),
'host'=>array('host',0,1),
'creater'=>array('creater',0,1),
'url'=>array('url',0,1)
);
//DESC/ASC order
$addon = '';
foreach($_GET as $k => $v)
{
if(isset($recognized[$k]))
$addon .= (empty($addon) ? ' ORDER BY ' : ', ') .
($recognized[$k][0]) .
($recognized[$k][1 + (int)(bool)($v)] ? ' ASC' : ' DESC');
}
$sql .= $addon;
//create a query
$result = mysql_query($sql, $conn) or die(mysql_error());
print "<table id='games' align='center'>\n";
print "<tr bgcolor='#fdf8f0'>
<td colspan='7' align='center' valign='middle'>
<h1 id='toptitle'>Games List</h1>
</td>
</tr>\n";
//get field names
//$kv = rand(0,1);
print "<tr id=releases_header>\n";
//while ($field = mysql_fetch_field($result)){
print "
<th width='140' align='left'><a href='games2.php?title=1'>Game Title</a></th>
<th width='120' align='left'><a href='games2.php?cat=1'>Category</a></th>
<th width='140' align='left'><a href='games2.php?host=1'>Hosted By</a></th>
<th width='140' align='left'><a href='games2.php?creater=1'>Created By</a></th>\n";
//end while
print "</tr>\n\n";
//get row data as an associative array
$counter = 1;
while ($row = mysql_fetch_assoc($result)){
if ($counter++ % 2){
print "<tr class=releases_rowstyle_dark>\n";
} else {
print "<tr class=releases_rowstyle_light>\n";
} // end if;
//look at each field
foreach ($row as $col=>$val){
if ($col !='id')
print " <td>$val</td>\n";
} //end foreach
print "</tr>\n\n";
}//end while
print "</table>\n";
?>
Posted: Fri Aug 19, 2005 4:10 pm
by Jean-Yves
Code: Select all
echo "<a href='$url'>$title</a>\n";
Is that what you mean?
Posted: Fri Aug 19, 2005 4:13 pm
by iBloB
Well sort of im just not sure where i would add that at.
I also need it to NOT show the url where it is now.
Posted: Fri Aug 19, 2005 8:15 pm
by iBloB
Okay I got my firend to help me with it.
Heres the link to the finished product (inlcuding a nice search):
http://iblob.info/?id=games.php
Now i will list my finished code:
Code: Select all
<form action=index.php method=GET>
<div align="center"><input type=hidden name=id value='games.php'>
<input size="55" type=text name=search style='background-color:#EEEEEE'>
<input type=submit value='Search Games' style='background-color:#EEEEEE'>
</div></form>
<!--START GAMES LIST TABLE-->
<?php
//make the database connection
$conn = mysql_connect("******", "*******", "******");
mysql_select_db('*******', $conn);
//organize data
$sql = 'SELECT * FROM games';
// add recognized names in here..
$recognized = array(
'title'=>array('title',0,1),
'cat'=>array('cat',0,1),
'host'=>array('host',0,1),
'creater'=>array('creater',0,1),
'url'=>array('url',0,1)
);
//DESC/ASC order
$addon = '';
foreach($_GET as $k => $v)
{
if(isset($recognized[$k]))
$addon .= (empty($addon) ? ' ORDER BY ' : ', ') .
($recognized[$k][0]) .
($recognized[$k][1 + (int)(bool)($v)] ? ' ASC' : ' DESC');
}
if(!$_GET['title'] && !$_GET['cat'] && !$_GET['host'] && !$_GET['creater']) {
$k = 'title';
$addon .= (empty($addon) ? ' ORDER BY ' : ', ') .
($recognized[$k][0]) .
($recognized[$k][1 + (int)(bool)($v)] ? ' ASC' : ' DESC');
}
$sql .= $addon;
if($_GET['search']) {
$sql = "SELECT * FROM games WHERE title LIKE '%".$search."%' ORDER BY title ASC";
}
//create a query
$result = mysql_query($sql, $conn) or die(mysql_error());
print "<table width='540' cellpadding='0' cellspacing='0' id='games' align='center'>\n";
//get field names
//$kv = rand(0,1);
print "<tr id=releases_header>\n";
//while ($field = mysql_fetch_field($result)){
print "
<th width='180' align='left'><a href='?id=games.php&title=1'>Game Title</a></th>
<th width='120' align='left'><a href='?id=games.php&cat=1'>Category</a></th>
<th width='100' align='left'><a href='?id=games.php&host=1'>Hosted By</a></th>
<th width='140' align='left'><a href='?id=games.php&creater=1'>Created By</a></th>\n";
//end while
print "</tr>\n\n";
//get row data as an associative array
$counter = 1;
while ($row = mysql_fetch_assoc($result)){
if ($counter++ % 2){
print "<tr class='releases_rowstyle_light' onmouseover=\"this.className='hoveron_light';\" onmouseout=\"this.className='hoveroff_light';\">\n";
} else {
print "<tr class='releases_rowstyle_dark' onmouseover=\"this.className='hoveron_dark';\" onmouseout=\"this.className='hoveroff_dark';\">\n";
} // end if;
//What To Say For Link
if($row['host'] == 'http://www.iblob.info') { $linkshow = 'iBlob'; } else { $linkshow = 'Other'; }
//look at each field
echo"<td width='170' align='left'><a href='{$row[url]}' target='_blank'>{$row[title]}</a></td>
<td width='120' align='left'>{$row[cat]}</td>
<td width='90' align='left'><a href='{$row[host]}'>{$linkshow}</a></td>
<td width='140' align='left'>{$row[creater]}</td>\n";
print "</tr>\n\n";
}//end while
print "</table>\n";
?>