2
Greeding from Thailand. Need help for my news script. I am trying to display news which keywords match the current page keywords. I am using Dreamweaver 8 and PhpMyAdmin to manage MySQL. May I explain you what I have done. My database and data in that look like this.
//* Data Base
Expand|Select|Wrap|Line Numbers
- -- phpMyAdmin SQL Dump
- -- version 2.6.2-pl1
- -- http://www.phpmyadmin.net
- --
- -- Host: localhost
- -- Generation Time: Nov 29, 2006 at 03:45 AM
- -- Server version: 4.1.14
- -- PHP Version: 5.0.5
- --
- -- Database: `news`
- --
- -- --------------------------------------------------------
- --
- -- Table structure for table `news`
- --
- CREATE TABLE `news` (
- `news_id` int(6) unsigned NOT NULL auto_increment,
- `cat` int(6) unsigned NOT NULL default '0',
- `keywords` text collate utf8_bin NOT NULL,
- `title` text collate utf8_bin NOT NULL,
- `brief` text collate utf8_bin NOT NULL,
- `story` text collate utf8_bin NOT NULL,
- `created` datetime NOT NULL default '0000-00-00 00:00:00',
- `published` datetime default NULL,
- PRIMARY KEY (`news_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=9 ;
- --
- -- Dumping data for table `news`
- --
- INSERT INTO `news` VALUES (1, 11, 0x4a6f686e, 0x4a6f686e2076697369742047617264656e, 0x4a6f686e2076697369742047617264656e206f6e2053756e6461792e, 0x4a6f686e2076697369742047617264656e206f6e2053756e64617920776974682068697320667269656e64732e20, '2006-11-29 01:30:20', '2006-11-29 01:30:20');
- INSERT INTO `news` VALUES (2, 11, 0x4a6f686e, 0x4a6f686e206c696b657320466f6f7462616c6c, 0x4a6f686e206973204d616e2d552066616e2e20, 0x4a6f686e206c696b657320466f6f7462616c6c20616e642061204d616e2d552066616e2e20, '2006-11-29 01:48:12', '2006-11-29 01:48:12');
- INSERT INTO `news` VALUES (3, 12, 0x4d617279, 0x4d617279, 0x4d61727920697320612073747564656e742e, 0x4d61727920697320612073747564656e7420616e64207072657474792e20, '2006-11-29 02:54:45', '2006-11-29 02:54:45');
- INSERT INTO `news` VALUES (4, 12, 0x4d617279, 0x4d61727920627579206375702e, 0x4d617920627579206120726564206375702e, 0x4d617920627579206120726564206375702e20, '2006-11-29 02:56:26', '2006-11-29 02:56:26');
- INSERT INTO `news` VALUES (5, 11, 0x4368726973, 0x4368726973, 0x4368726973206973206120626f792e, 0x4368726973206c696b6520706c6179696e672067616d65732e20, '2006-11-29 03:02:40', '2006-11-29 03:02:40');
- INSERT INTO `news` VALUES (6, 11, 0x546f6d, 0x546f6d, 0x546f6d206861766520616e206170706c652e20, 0x546f6d20697320656174696e6720616e206170706c652e20, '2006-11-29 03:03:39', '2006-11-29 03:03:39');
- INSERT INTO `news` VALUES (7, 12, 0x4a656e6e79, 0x4a656e6e79, 0x4a656e6e79206973206120746561636865722e, 0x4a656e6e79207465616368657320456e676c69736820746f2073747564656e74732e, '2006-11-29 03:13:21', '2006-11-29 03:13:21');
- INSERT INTO `news` VALUES (8, 12, 0x4c697a, 0x4c697a, 0x4c697a20697320612073696e6765722e20, 0x4c697a2073696e6773206120706f7020736f6e672e, '2006-11-29 03:14:33', '2006-11-29 03:14:33');
This is my index.php page to show all published news.
------
Expand|Select|Wrap|Line Numbers
- <?php require_once('Connections/db.php'); ?>
- <?php
- $maxRows_news = 10;
- $pageNum_news = 0;
- if (isset($_GET['pageNum_news'])) {
- $pageNum_news = $_GET['pageNum_news'];
- }
- $startRow_news = $pageNum_news * $maxRows_news;
- mysql_select_db($database_news, $news);
- $query_news = "SELECT * FROM news WHERE published is not null ORDER BY news.published DESC";
- $query_limit_news = sprintf("%s LIMIT %d, %d", $query_news, $startRow_news, $maxRows_news);
- $news = mysql_query($query_limit_news, $news) or die(mysql_error());
- $row_news = mysql_fetch_assoc($news);
- if (isset($_GET['totalRows_news'])) {
- $totalRows_news = $_GET['totalRows_news'];
- } else {
- $all_news = mysql_query($query_news);
- $totalRows_news = mysql_num_rows($all_news);
- }
- $totalPages_news = ceil($totalRows_news/$maxRows_news)-1;
- ?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
- <html xmlns="http://www.w3.org/1999/xhtml">
- <head>
- <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
- <title>News</title>
- </head>
- <body>
- <h2>INDEX PAGE</h2>
- <h3>Latest News</h3>
- <?php do { ?>
- <strong>Title: </strong><?php echo $row_news['title']; ?><br />
- <strong>Date: </strong><?php echo $row_news['created']; ?><br />
- <strong>Brief: </strong><?php echo $row_news['brief']; ?><br />
- <a href="view.php?news_id=<?php echo $row_news['news_id']; ?>&cat=<?php echo $row_news['cat']; ?>">Detail</a><br /><br />
- <?php } while ($row_news = mysql_fetch_assoc($news)); ?>
- </body>
- </html>
- <?php
- mysql_free_result($news);
- ?>
This is the view.php code to display News' detail, latest news and related news.
-----------
Expand|Select|Wrap|Line Numbers
- <?php require_once('Connections/db.php'); ?>
- <?php
- mysql_select_db($database_news, $news);
- $query_regional = "SELECT * FROM news WHERE news_id=$news_id AND cat=$cat ORDER BY news.published";
- $regional = mysql_query($query_regional, $news) or die(mysql_error());
- $row_regional = mysql_fetch_assoc($regional);
- $totalRows_regional = mysql_num_rows($regional);
- $maxRows_head = 3;
- $pageNum_head = 0;
- if (isset($_GET['pageNum_head'])) {
- $pageNum_head = $_GET['pageNum_head'];
- }
- $startRow_head = $pageNum_head * $maxRows_head;
- mysql_select_db($database_news, $news);
- $query_head = "SELECT * FROM news WHERE news_id!=$news_id ORDER BY news.published DESC";
- $query_limit_head = sprintf("%s LIMIT %d, %d", $query_head, $startRow_head, $maxRows_head);
- $head = mysql_query($query_limit_head, $news) or die(mysql_error());
- $row_head = mysql_fetch_assoc($head);
- if (isset($_GET['totalRows_head'])) {
- $totalRows_head = $_GET['totalRows_head'];
- } else {
- $all_head = mysql_query($query_head);
- $totalRows_head = mysql_num_rows($all_head);
- }
- $totalPages_head = ceil($totalRows_head/$maxRows_head)-1;
- $maxRows_kw = 3;
- $pageNum_kw = 0;
- if (isset($_GET['pageNum_kw'])) {
- $pageNum_kw = $_GET['pageNum_kw'];
- }
- $startRow_kw = $pageNum_kw * $maxRows_kw;
- mysql_select_db($database_news, $news);
- $query_kw = "SELECT * FROM `news` WHERE news_id!=$news_id AND cat=$cat AND keywords LIKE '%$kw%' ORDER BY news.published DESC";
- $query_limit_kw = sprintf("%s LIMIT %d, %d", $query_kw, $startRow_kw, $maxRows_kw);
- $kw = mysql_query($query_limit_kw, $news) or die(mysql_error());
- $row_kw = mysql_fetch_assoc($kw);
- if (isset($_GET['totalRows_kw'])) {
- $totalRows_kw = $_GET['totalRows_kw'];
- } else {
- $all_kw = mysql_query($query_kw);
- $totalRows_kw = mysql_num_rows($all_kw);
- }
- $totalPages_kw = ceil($totalRows_kw/$maxRows_kw)-1;
- ?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
- <html xmlns="http://www.w3.org/1999/xhtml">
- <head>
- <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
- <title>News</title>
- </head>
- <body>
- <h2>NEWS DETAIL PAGE </h2>
- <h3>News</h3>
- <strong>Title: </strong><?php echo $row_regional['title']; ?><br />
- <strong>Date: </strong><?php echo $row_regional['created']; ?><br />
- <strong>Story: </strong><?php echo $row_regional['story']; ?><br />
- <h4>Other latest News </h4>
- <?php do { ?>
- <a href="view.php?news_id=<?php echo $row_head['news_id']; ?>&cat=<?php echo $row_head['cat']; ?>"><?php echo $row_head['title']; ?></a><br />
- <?php } while ($row_head = mysql_fetch_assoc($head)); ?><br /><br />
- <h4>Related News</h4>
- <?php do { ?>
- <a href="view.php?news_id=<?php echo $row_kw['news_id']; ?>&cat=<?php echo $row_kw['cat']; ?>"><?php echo $row_kw['title']; ?></a><br />
- <?php } while ($row_kw = mysql_fetch_assoc($kw)); ?> <br /><br />
- <br />
- <a href="index.php">HOME
- </a>
- </body>
- </html>
- <?php
- mysql_free_result($regional);
- mysql_free_result($head);
- mysql_free_result($kw);
- ?>
What I am trying to do is that If I am viewing a page that contain John, at the related news I just want the other Title that contain the keywords John to be shown. But in my case that appear all news which cat is the same.
Please have a look at my code and correct. Do I write query wrong or Do I build my database table for keywords wrong or How do I do????????????
Your help is much appreciate
Thanks in advance
Gawn
