table of lies

Maljonic

Can't get enough of FH
Joined
Dec 22, 2003
Messages
1,297
Okay, I have this table on this page: http://www.maljonicsdreams.com/love_answers.htm with some dating sites on it. At the top it says 'Dating Site of the Month' and claims that the site shown is the most clicked, but it's all lies at the moment - I just paste a different site in now and again.

So can someone help me out and instruct me on how to make it a table of truths, so the data is live and the cell at the top displayed the most clicked site? Like a simple MYSQL database thingy... :)
 

Funkybunny

Banned
Joined
Jan 21, 2004
Messages
1,291
ROFLMAO!!! bestest EVER!!!

"scientificly based on me copy pasting a link from a notepad document called "lovelinks.txt"
 

Maljonic

Can't get enough of FH
Joined
Dec 22, 2003
Messages
1,297
Indeed, that's why I want it done properly, because it's stupid the way it is - I can introduce stats based on people signing up too but all I want is a way to show the most popular by visits at the moment, the scientificly based part as you say is down to the individual dating sites themselves. :)
 

Jonty

Fledgling Freddie
Joined
Dec 22, 2003
Messages
1,411
Hi Maljonic

  1. Login to Cpanel on your website and click on the 'MySQL Databases' option
  2. If necessary, create a new database, then click on the 'PHPMyAdmin' option
  3. Select the relevant database in the left-hand frame
  4. Click on the 'SQL' tab in the right-hand frame
  5. Paste the following code into the textbox and click 'Go'
    Code:
    CREATE TABLE `dating_services` (
    `id` SMALLINT NOT NULL AUTO_INCREMENT,
    `address` TINYTEXT NOT NULL,
    `description` TEXT NOT NULL,
    `popularity` SMALLINT NOT NULL DEFAULT '1',
    `title` TINYTEXT NOT NULL,
    PRIMARY KEY (`id`)
    ) COMMENT = 'Dating Services';
  6. Click on 'dating_services' in the left-hand frame
  7. Click on the 'Insert' tab in the right-hand frame
  8. Leave the 'id' value empty (important); insert the dating service website address in the 'address' field (e.g. http://www.datingservice.com/); insert a description of the website in the 'description' field (e.g. This is a dating service site); leave the 'popularity' field with '1' in it; and finally, insert the website title into the 'title' field (e.g. Dating Services Example). Click 'Go' and repeat for each service as required.
  9. In the webpage which will list the dating services add the following code where you want the list, save and upload. The file must be capable of executing PHP (e.g. have the '.php' file extension).
    Code:
    <?php
    // Connect to the database
    $database = mysql_connect("INSERT_HOST", "INSERT_USERNAME", "INSERT_PASSWORD");
    mysql_select_db("INSERT_DATABASE_NAME", $database);
    
    // Retrieve the dating services information from the database
    $query = mysql_query("SELECT * FROM `dating_services` ORDER BY `popularity` DESC LIMIT 10", $database);
    
    // Produce the most popular service listing
    $popular = mysql_fetch_assoc($query);
    printf('<h1>Most Popular Service</h1> <p>The most popular dating service is <a href="update.php?id=%s">%s</a>.</p> <p>%s</p> <p>This service has been viewed %s times.</p>'."\n\n", $popular["id"], $popular["title"], $popular["description"], $popular["popularity"]);
    
    // Produce the table detailing the dating services in order of popularity
    echo '<table id="dating-services">'."\n";
    echo "  <tr>\n";
    echo "    <th>Service</th>\n";
    echo "    <th>Description</th>\n";
    echo "    <th>Views</th>\n";
    echo "  </tr>\n";
    
    while ($row = mysql_fetch_assoc($query))
    {
      echo "  <tr>\n";
      echo '    <td><a href="update.php?id='.$row['id'].'">'.$row['title'].'</a>'."\n";
      echo "    <td>".$row['description']."</td>\n";
      echo "    <td>".$row['popularity']."</td>\n";
      echo "  </tr>\n";
    }
    
    echo "</table>\n";
    ?>
  10. Create a new file called 'update.php' and insert the following code, save and upload to the same directory as the file listing the services.
    Code:
    <?php
    // Connect to the database
    $database = mysql_connect("INSERT_HOST", "INSERT_USERNAME", "INSERT_PASSWORD");
    mysql_select_db("INSERT_DATABASE_NAME", $database);
    
    // Retrieve the site id from the query string
    $service = $_GET["id"];
    
    // Update the popularity of the service by one
    $update = mysql_query("UPDATE `dating_services` SET `popularity`=`popularity`+1 WHERE `id`='$service'");
    
    // Retrieve the service address
    $query = mysql_query("SELECT `address` FROM `dating_services` WHERE `id`='$service'");
    $address = mysql_result($query, 0);
    
    // Redirect the user
    header("Location: $address");
    exit();
    ?>
That's it :) Basically whenever someone clicks a dating service link it calls the update page, increments the popularity, then redirects the user to that website.

Please note this code is very basic. It lacks any error checking, for example, which really should be added (e.g. if no 'id' is provided for the update page, if the database queries are unsuccessful etc.). Note also you'll have to update the two database connection fields with your database host address, username, password and database name.

Obviously you can customise it to your needs, just ask if you're unsure of anything.

Kind Regards

Jonty

P.S. If you find this useful, there's no need to credit me on your site given the very basic nature of the example provided, just feel free to bump my reputation :)
 

Maljonic

Can't get enough of FH
Joined
Dec 22, 2003
Messages
1,297
Cheers Jonty, this is great stuff; didn't even know I could do all that through my control panel... anyway I've sort of got it working, here is my test page so far: http://www.maljonicsdreams.com/love_dating/ but it isn't displaying everything properly, there should be 10 showing all together but there's only 5, and the addresses are mixed up - is it to do with setting the amount of results to display, I vaguely remember learning that somewhere but I can't see where to change it? :)
 

Jonty

Fledgling Freddie
Joined
Dec 22, 2003
Messages
1,411
Hi Maljonic

My mistake, we are missing the second quote off the <a href="... so that's messing up the links :) Just find the list code and replace the existing line with the one below:

Code:
echo '    <td><a href="update.php?id='.$row['id'].'">'.$row['title'].'</a>'."\n";
That should be alright :) As for the number of items, try replacing the existing line with the one below and see if it helps:
Code:
$query = mysql_query("SELECT * FROM `dating_services` ORDER BY `popularity` DESC LIMIT 10", $database);
Kind Regards
 

JingleBells

FH is my second home
Joined
Mar 25, 2004
Messages
2,224
Quick pointer from a users POV, I think you only want the table headers to appear once, so I would change the lines that read:
Code:
while ($row = mysql_fetch_assoc($query))
{
  echo "  <tr>\n";
  echo "    <th>Service</th>\n";
  echo "    <th>Description</th>\n";
  echo "    <th>Views</th>\n";
  echo "  </tr>\n";

  echo "  <tr>\n";
  echo '    <td><a href="update.php?id='.$row['id'].'">'.$row['title'].'</a>'."\n";
  echo "    <td>".$row['description']."</td>\n";
  echo "    <td>".$row['popularity']."</td>\n";
  echo "  </tr>\n";
}

to read:

Code:
  echo "  <tr>\n";
  echo "    <th>Service</th>\n";
  echo "    <th>Description</th>\n";
  echo "    <th>Views</th>\n";
  echo "  </tr>\n";

while ($row = mysql_fetch_assoc($query))
{
  echo "  <tr>\n";
  echo '    <td><a href="update.php?id='.$row['id'].'" target="_blank">'.$row['title'].'</a>'."\n";
  echo "    <td>".$row['description']."</td>\n";
  echo "    <td>".$row['popularity']."</td>\n";
  echo "  </tr>\n";
}

BTW - nice piece of work Jonty
 

Jonty

Fledgling Freddie
Joined
Dec 22, 2003
Messages
1,411
Thanks JingleBells, this is why I hate rushing out code :) Thanks for picking that up, you're a star :D

Maljonic, replace your existing code to use JingleBell's sample of code (I'll update my original post accordingly if that's easier).

Kind Regards
 

Maljonic

Can't get enough of FH
Joined
Dec 22, 2003
Messages
1,297
Thanks both of you, I already updated it. I really must get a book on php, looks pretty cool. Just two more tiny bits, how do I get the links to open in a new page? And can a put a space between each of the results so they aren't bunched up so close, or pur them in idividual table cells? :)

P.S. It's quite late you know? :)
 

Jonty

Fledgling Freddie
Joined
Dec 22, 2003
Messages
1,411
Hi Maljonic

Replace the existing code with this line to make the links open up in new windows:

Code:
  echo '    <td><a href="update.php?id='.$row['id'].'" target="_blank">'.$row['title'].'</a>'."\n";
As for the spacing, we can solve that with some CSS :) But in the morning, hehe.

Kind Regards
 

Jonty

Fledgling Freddie
Joined
Dec 22, 2003
Messages
1,411
Hi Maljonic

Just one more fix which will allow easy styling of the table, find this code:
Code:
echo "<table>\n";
... and replace with ...
Code:
echo '<table id="dating-services">'."\n";
This allows us to easily style the table to achieve the desired layout (I've updated my original post accordingly). For example, the following CSS code in your stylesheet:
Code:
#dating-services tr
{
  margin-bottom:20px;
}
... would create a twenty pixel margin between each row.

Naturally you don't have to use tables. The example provided is just that, an example, feel free to display the data how you want :)

Kind Regards
 

Maljonic

Can't get enough of FH
Joined
Dec 22, 2003
Messages
1,297
I'm sure I tried putting the target="_blank" bit in, must have made a mistake with it being so late, it works now... the table id doesn't work though, causes errors, must be a punctuation mark too many, or missing. I've changed the html so it looks like it will when it's on the site: http://www.maljonicsdreams.com/love_dating/ it's just a bit difficult to tell where one description ends and another one begins. This is all good though, I'm enjoying learning all this new stuff. :)
 

Jonty

Fledgling Freddie
Joined
Dec 22, 2003
Messages
1,411
Hi Maljonic

Try this, there was a '"' missing :)
Code:
echo '<table id="dating-services">'."\n";
All above code snippets have been amended for consistency.

Kind Regards
 

Maljonic

Can't get enough of FH
Joined
Dec 22, 2003
Messages
1,297
I changed the code a bit to put background colors in and aligned the titles left and top, and the popularities in the middle, it doesn't look so bad now. :)
 

Maljonic

Can't get enough of FH
Joined
Dec 22, 2003
Messages
1,297
I quite like the look of it now; I had a javascript before to put the current month in but I guess there must be a way to include that in this code, so the bit at the top says, Top Dating Site for 'current month + year'? :)
 

JingleBells

FH is my second home
Joined
Mar 25, 2004
Messages
2,224
You could get it to do that, and it would be easier if you did it using the inbuily PHP Date functions, as this means its based on the servers time, not the clients (which might be wrong)

What you want is something like:
Code:
echo '<h1>Top Dating sites for '.date("F Y").'</h1>';
which would output:
Top Dating sites for March 2005

For more info on the date() function, check out the PHP manual entry.
 

Maljonic

Can't get enough of FH
Joined
Dec 22, 2003
Messages
1,297
Thanks Jonty and JingleBells, it's all just perfect now; even looks a lot better than I had it before, as well as doing what it says. :)
 

Jonty

Fledgling Freddie
Joined
Dec 22, 2003
Messages
1,411
Hi Maljonic

Try adding <div> tags before and after the PHP list code, e.g.
Code:
<div>
<?php
...
?>
</div>
This is very much a hack, but it should solve the validation problem.

Kind Regards
 

Maljonic

Can't get enough of FH
Joined
Dec 22, 2003
Messages
1,297
That kind of works, now there's only one error; but it says I can't have a <div> tag there. :)
 

Jonty

Fledgling Freddie
Joined
Dec 22, 2003
Messages
1,411
Hi Maljonic

Unfortunately the problem occurs because you're trying to include a table (the list) in a table (the design). The only way to solve it is to stop the layout table, then have your list, then start the layout table again is needs be.

Kind Regards
 

wyrd_fish

Fledgling Freddie
Joined
Dec 27, 2003
Messages
537
Maljonic, you really should look into css based layout, they can be a bugger at first, but once you get teh basics they're quite easy to expand on

www.w3schools.com
 

wyrd_fish

Fledgling Freddie
Joined
Dec 27, 2003
Messages
537
(why can we only edit for 10mins???)

you site, maljonicsdreams.com, would need around 4 or 5 for the layout

a top bar
a left bar
a main bit
a right bar

teh html would look somthing like this...
Code:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>my title</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<link rel="stylesheet" type="text/css" href="mycssfile.css">
</head>
<body>
<div id="topbar"><img src="logo.ext" alt="my logo, ain't it pretty" /></div>
<div id="main">
<div id="left">yo' links go here</div>
yo' content goes here
<div id="right"></div>
</div>
</body>
</html>

and the CSS a little like this...
Code:
topbar, main{
	width: 90%;
	margin-left: auto;
	margin-right: auto;
}
right{
	float: right;
}
left{
	float: left;
}

then you can put tabled in the main bit without destroying your validation

EDIT in adition to this you could replace all that JS image swapping, in the menu, with 1 background image and CSS to change the text on top of it
 

wyrd_fish

Fledgling Freddie
Joined
Dec 27, 2003
Messages
537
(this is really annoying)

html:
Code:
<a href="#">latest dreams</a>

css:
Code:
left a:link{
	width: whateverpx;
	height: whateverpx;
	background: url(lingbg.ext);
	weight: normal;
	text-decoration: none;
}
left a:visited{
	width: whateverpx;
	height: whateverpx;
	background: url(lingbg.ext);
	weight: normal;
	text-decoration: none;
}
left a:hover{
	width: whateverpx;
	height: whateverpx;
	background: url(lingbg.ext);
	weight: bold;
	text-decoration: none;
}
left a:active{
	width: whateverpx;
	height: whateverpx;
	background: url(lingbg.ext);
	weight: normal;
	text-decoration: none;
}
 

Jonty

Fledgling Freddie
Joined
Dec 22, 2003
Messages
1,411
Great advice, wyrd_fish :) Maljonic, if you ever have time, modern standards based design really can be worth the effort. It makes maintaining your site so much easier, improves load times, makes your site more accessible, saves bandwidth ... the list goes on :)

Kind Regards
 

wyrd_fish

Fledgling Freddie
Joined
Dec 27, 2003
Messages
537
right, i made you a mock up of your site, it's far from perfect but still has a lot less errors than the other examples :/
 

Attachments

  • index.zip
    32.1 KB · Views: 6

Maljonic

Can't get enough of FH
Joined
Dec 22, 2003
Messages
1,297
I can't get that folder to open, says it's corrupt. I do use CSS a bit, my header is a style sheet so I can change my logo on the whole site straight away if I want, but I'm very new to it I must admit. :)
 

Users who are viewing this thread

Top Bottom