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'>
&nbsp;&nbsp;<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";
?>