Jump to content

Archived

This topic is now archived and is closed to further replies.

wizzkid

Database Query Display

Recommended Posts

Hi Guys,

I was able to post the content of my database, however I would like to put a maximum number of display, beause I want to put at my left bar of my website, I want to display only the 5 recent articles, then put more articles on the bottom of the left side bar. When I click more article it should show all articles.

Also, How can I make link to the title so it go to the full article? :) eg. because I only want to display:

date:
title

date:
title

date:
title

when I click the title it will go to the full artile which is
date:
title
article:

Here's my script.
[a href=\"http://www.leeph.net/test/view_news.txt\" target=\"_blank\"]http://www.leeph.net/test/view_news.txt[/a]


Hope you guys could help me out :)

Thanks.

Share this post


Link to post
Share on other sites
Change:
[code]$query = 'SELECT * FROM news ORDER BY date DESC';[/code]
To:
[code]$query = 'SELECT * FROM news ORDER BY date DESC LIMIT 5';[/code]

Share this post


Link to post
Share on other sites
To do what you want to do place the following bit of code:
[code]// check whether the 'more' link has not been clicked.
// if it hasn't we'll only show the five most recent news articles
if(!isset($_GET['more']) || $_GET['more'] == 0)
{
    // this tells MySQL to select only 5 results
    $query .= " LIMIT 5";
}[/code]
after this line:
[code]$query = 'SELECT * FROM news ORDER BY date DESC';[/code]

Now to add the link you'll need this code:
[code]<a href="?more=1">View all</a>[/code]
This will need to be placed after you news article is displayed, proabably after the while loop if I'm correct.

So now what will happen is PHP will check 'more' has been set in the url and that it is set to '1'. If more is not set in the url it'll append 'LIMIT 5' to end of the query. This will then only show 5 news articles. But if more is set in the url and is set 1 it'll show all the articles.

Now to linke the title you'll need to do this:
[code]<h3><a href=\"news.php?id={$row['vc_title']}\">{$row['vc_title']}</a></h3>[/code]
Change news.php to the correct file you want the user to go to

Share this post


Link to post
Share on other sites
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Now to add the link you'll need this code:
[code]<a href="?more=1">View all</a>[/code]
This will need to be placed after you news article is displayed, proabably after the while loop if I'm correct.
[/quote]

I tried to add it, but when I point the link, its pointed to [a href=\"http://localhost/php/tnr/index.php?more=1\" target=\"_blank\"]http://localhost/php/tnr/index.php?more=1[/a] is that correct? when I click it, nothing happens.

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
Now to linke the title you'll need to do this:
[code]<h3><a href=\"news.php?id={$row['vc_title']}\">{$row['vc_title']}</a></h3>[/code]
Change news.php to the correct file you want the user to go to
[/quote]

I changed the id={$row['vc_title']}\">{$row['vc_title']} to id={$row['int_id']}\">{$row['vc_title']}
so when I point the link it points to [a href=\"http://localhost/php/tnr/news/news.php?id=37\" target=\"_blank\"]http://localhost/php/tnr/news/news.php?id=37[/a] however, I dont know what syntax to put in news.php, can you guys instruct me? based on [a href=\"http://www.leeph.net/test/view_news.txt\" target=\"_blank\"]http://www.leeph.net/test/view_news.txt[/a]


Thanks and hope you guys could teach me, sorry im newbie to php.

Share this post


Link to post
Share on other sites
Sorry, yesterday I didn't get to know what you trying to say, maybe I'm kinda tired..

From the index page where you show your news,
point yur title url to $row['int_id'].

Below is the title on your index page:
[code]<a href=\"news.php?id={$row['int_id']}\">$row['vc_title']</a>[/code]


Create a [b]news.php[/b], and put this code in.
[code]<?php
if (!isset($_GET['news'] || $_GET['news'] == 0)) {
    echo "You didn't select any article.";
    } else {
        $_GET['news'] = $news;
        $query = "SELECT * FROM news WHERE int_id ='$news'";
        $r = mysql_query($query) or die("Query failed: " . mysql_error());
        $row = mysql_fetch_assoc($r);
        echo $row['date']; // Remember to change date, i'm not sure of your table structure..
        echo "<p><h3>".$row['vc_title']."</h3>";
        echo "".$row['vc_article']."<br /></p>";
      }
?>[/code]

Tell me if there's any more problem, ps.. I'm still in a sleepy mode.. lolz. :s

Share this post


Link to post
Share on other sites
[!--quoteo(post=387340:date=Jun 23 2006, 08:34 PM:name=hackerkts)--][div class=\'quotetop\']QUOTE(hackerkts @ Jun 23 2006, 08:34 PM) [snapback]387340[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Sorry, yesterday I didn't get to know what you trying to say, maybe I'm kinda tired..

From the index page where you show your news,
point yur title url to $row['int_id'].

Below is the title on your index page:
[code]<a href=\"news.php?id={$row['int_id']}\">$row['vc_title']</a>[/code]
Create a [b]news.php[/b], and put this code in.
[code]<?php
if (!isset($_GET['news'] || $_GET['news'] == 0)) {
    echo "You didn't select any article.";
    } else {
        $_GET['news'] = $news;
        $query = "SELECT * FROM news WHERE int_id ='$news'";
        $r = mysql_query($query) or die("Query failed: " . mysql_error());
        $row = mysql_fetch_assoc($r);
        echo $row['date']; // Remember to change date, i'm not sure of your table structure..
        echo "<p><h3>".$row['vc_title']."</h3>";
        echo "".$row['vc_article']."<br /></p>";
      }
?>[/code]

Tell me if there's any more problem, ps.. I'm still in a sleepy mode.. lolz. :s
[/quote]

Hi hackerkts,

Thanks, I will try this :)

Regards,
Lee

Share this post


Link to post
Share on other sites
Hi! I tried to put this code to /news/news.php

[code]
<?php
require_once(mysql_connect.php);

ini_set ('display_errors', 1);

error_reporting (E_ALL & ~E_NOTICE);
    
if (!isset($_GET['news']) || $_GET['news'] == 0) {
    echo "You didn't select any article.";
    } else {
        $_GET['news'] = $news;
        $query = "SELECT * FROM news WHERE int_id ='$news'";
        $r = mysql_query($query) or die("Query failed: " . mysql_error());
        $row = mysql_fetch_array($r);
        echo $row['date'];
        echo "<p><h3>".$row['vc_title']."</h3>";
        echo "".$row['vc_article']."<br /></p>";
      }
?>
[/code]

but I its just showed my "You didnt select any article"

I used this code from index.php
[code]
<a href=\"news/news.php?id={$row['int_id']}\">{$row['vc_title']}</a>
[/code]

Any comments? :)

thanks and really appreciated your help!

Share this post


Link to post
Share on other sites
When you clicked on the title, what link it show ?

[b]Edit[/b]:
Ok I know why, it's my mistake. Morning just woke up not long and write out that script.

Now change:
[code]if (!isset($_GET['news']) || $_GET['news'] == 0) {[/code]
To:
[code]if (!isset($_GET['id']) || $_GET['id'] == 0) {[/code]

[b]Edit2[/b]:
If you still having problem with it, try checking the scripts below.

Database:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]CREATE TABLE `news` (
`id` tinyint(10) NOT NULL auto_increment,
`date` text NOT NULL,
`title` text NOT NULL,
`article` text NOT NULL,
PRIMARY KEY (`id`)
) [!--sql2--][/div][!--sql3--]

config.php (remember to change the config, I just setup it using my local computer)
[code]<?php
$host = "localhost";
$username = "root";
$password = "";
$db = "help";

mysql_connect($host,$username,$password) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
mysql_close;
?>[/code]

index.php
[code]<?php

require_once('config.php');
$query = "SELECT * FROM `news` ORDER BY id DESC LIMIT 5";
$result = mysql_query($query) or die("Query failed: " . mysql_error());
while($row = mysql_fetch_assoc($result)) {
    echo "".$row['date']."<br>";
    echo "<a href='news.php?id=".$row['id']."'>".$row['title']."</a><br><br>";
}

?>[/code]

news.php
[code]<?php

require_once('config.php');
$news = $_GET['id'];
$query = "SELECT * FROM `news` WHERE id ='$news'";
$result = mysql_query($query) or die("Query failed: " . mysql_error());
$row = mysql_fetch_assoc($result);
if (!isset($news) || $news == 0) {
    echo "You didn't select any news.";
    } else {
        echo "".$row['date']."<br>";
        echo "".$row['title']."<br>";
        echo $row['article'];
        }

?>[/code]

Share this post


Link to post
Share on other sites
Hi hackerkts,

Thank you so much for your help! :) I will try this out. hope all get well. :)

Really appreacited it a lot :)

Regards,
Lee

Share this post


Link to post
Share on other sites
No problem, I'm always glad to help out.
If you had any problem with it, feel free to reply or drop me a msg in msn.

Share this post


Link to post
Share on other sites
[!--quoteo(post=387452:date=Jun 24 2006, 09:50 AM:name=hackerkts)--][div class=\'quotetop\']QUOTE(hackerkts @ Jun 24 2006, 09:50 AM) [snapback]387452[/snapback][/div][div class=\'quotemain\'][!--quotec--]
No problem, I'm always glad to help out.
If you had any problem with it, feel free to reply or drop me a msg in msn.
[/quote]

Okay,, thanks again :)

Share this post


Link to post
Share on other sites
Still not working for on me :(

The link on index.php is [a href=\"http://localhost/php/tnr/news/news.php?id=12\" target=\"_blank\"]http://localhost/php/tnr/news/news.php?id=12[/a]


Index.php (works)
[code]
<?php

ini_set ('display_errors', 1);

error_reporting (E_ALL & ~E_NOTICE);



if(!isset($_GET['more']) || $_GET['more'] == 0)    
$query = 'SELECT * FROM `news` ORDER BY date DESC LIMIT 5';



if ($r = mysql_query ($query)) {
    

    while($row = mysql_fetch_assoc($r)) {

     echo "".$row['date']."<br>";
    echo "<a href='news/news.php?id=".$row['int_id']."'>".$row['vc_title']."</a><br><br>";

    }

    

    } else {

    die ('<p>Could create the table because: <b>' . mysql_error() . "</b>. The query was $query.</p>");

}


mysql_close(); // Close the database connection.



?>
[/code]

Here's my news/news.php. I encounter error and it doesnt display the query.
[code]
<?php

$news = $_GET['int_id'];
$query = "SELECT * FROM `news` WHERE int_id ='$news'";
$result = mysql_query($query) or die("Query failed: " . mysql_error());
$row = mysql_fetch_assoc($r);
if (!isset($_GET['int_id']) || $_GET['int_id'] == 0) {
    echo "You didn't select any news.";
    } else {
        echo "".$row['date']."<br>";
        echo "".$row['vc_title']."<br>";
        echo $row['vc_article'];
        }
?>
[/code]
error i encounter is:
an error occurred in script /media/data/projects/WebApps/php/tnr/include/config.inc on line 14: Undefined index: int_idAn error occurred in script /media/data/projects/WebApps/php/tnr/include/config.inc on line 14: Undefined variable: rAn error occurred in script /media/data/projects/WebApps/php/tnr/include/config.inc on line 14: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resourceYou didn't select any news.

Here's my config.inc
[code]
<?php

error_reporting (0); // Production level
error_reporting (E_ALL); // Development level


function my_error_handler ($e_number, $e_message) {

    $message = 'An error occurred in script ' . __FILE__ . ' on line ' . __LINE__ . ": $e_message";
    echo '<font color="red" size="+1">', $message, '</font>'; // Development (print the error in red)
}
set_error_handler('my_error_handler');
?>
[/code]

My Database is
int_id = tinyint(10)
vc_title = text
vc_article = text
date = date

Thanks again and again for the help! :)

Share this post


Link to post
Share on other sites
Your news.php, you need to make a [b]connection to MySQL[/b].
It would be better if you put the setting in config.php, so next time you wanna connect to MySQL you just require/include that file.

As for the error, I'm not very sure about it.
Try to set a connect in news.php and see if the error still there.

Share this post


Link to post
Share on other sites
[!--quoteo(post=387490:date=Jun 24 2006, 11:18 AM:name=hackerkts)--][div class=\'quotetop\']QUOTE(hackerkts @ Jun 24 2006, 11:18 AM) [snapback]387490[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Your news.php, you need to make a [b]connection to MySQL[/b].
It would be better if you put the setting in config.php, so next time you wanna connect to MySQL you just require/include that file.

As for the error, I'm not very sure about it.
Try to set a connect in news.php and see if the error still there.
[/quote]

Yes, I have included require_once('mysql_connect.php'); i just didnt include it on my post, sorry.

on news.php i have:
require_once('include/mysql_connect.php');
require_once('include/getnews.php')

where getnews.php contains
[code]
<?php

$news = $_GET['int_id'];
$query = "SELECT * FROM `news` WHERE int_id ='$news'";
$result = mysql_query($query) or die("Query failed: " . mysql_error());
$row = mysql_fetch_assoc($r);
if (!isset($_GET['int_id']) || $_GET['int_id'] == 0) {
    echo "You didn't select any news.";
    } else {
        echo "".$row['date']."<br>";
        echo "".$row['vc_title']."<br>";
        echo $row['vc_article'];
        }
?>
[/code]


any other suggestion? :)

Share this post


Link to post
Share on other sites
try know
[code]
<?php

$news = $_GET['int_id'];
$query = "SELECT * FROM `news` WHERE int_id ='$news'";
$r= mysql_query($query) or die("Query failed: " . mysql_error());
$row = mysql_fetch_assoc($r);
if (!isset($_GET['int_id']) || $_GET['int_id'] == 0) {
    echo "You didn't select any news.";
    } else {
        echo "".$row['date']."<br>";
        echo "".$row['vc_title']."<br>";
        echo $row['vc_article'];
        }
?>
[/code]

Share this post


Link to post
Share on other sites
Hi I just fixed the script and it worked..

Here's the working code:
[code]
<?php
$news = $_GET['id'];
$query = "SELECT * FROM news WHERE int_id = '$news'";
$r = mysql_query($query) or die("Query failed: " . mysql_error());
$row = mysql_fetch_assoc($r);
if (!isset($_GET['id']) || $_GET['id'] == 0) {
    echo "You didn't select any news.";
    } else {
        echo "".$row['date']."<br>";
        echo "".($row['vc_title'])."<br>";
        echo nl2br($row['vc_article']);
    }
?>
[/code]


Thanks a lot for the hellp guys :)

Share this post


Link to post
Share on other sites
Now, I am trying to echo something like. "sorry no data has found on the database" of the database is empty. I want to put it on this code

[code]
<?php
ini_set ('display_errors', 1);

error_reporting (E_ALL & ~E_NOTICE);


$query = 'SELECT * FROM news ORDER BY date DESC';

if ($r = mysql_query ($query)) { // Run the query.


while ($row = mysql_fetch_array ($r)) {
    echo "<b>Date:</b> ".$row['date']."<br>";
    echo "<a href='news.php?id=".$row['int_id']."'><font size = 3>".$row['vc_title']."</font></a><br><br>";
    echo "<font size = 2>Reference Number: " .$row['int_id']."</font><br><hr>";


    }



} else { // Query didn't run.

    die ('<p>Could create the table because: <b>' . mysql_error() . "</b>. The query was $query.</p>");

} // End of query IF.



mysql_close(); // Close the database connection.



?>
[/code]

can somebody tell me how? :)

thanks

Share this post


Link to post
Share on other sites
Do you mean when your news is totally empty then it display the words ?

Try this, and I have modify abit of your script.
[code]<?php
ini_set ('display_errors', 1);

error_reporting (E_ALL & ~E_NOTICE);


$query = 'SELECT * FROM news ORDER BY date DESC';

$r = mysql_query ($query) or die('<p>Could create the table because: <b>' . mysql_error() . "</b>. The query was $query.</p>");

$num_rows = mysql_num_rows($r);
if (!$num_rows || $num_rows == 0) {
    echo "sorry no data has found on the database";
    } else {


while ($row = mysql_fetch_array ($r)) {
    echo "<b>Date:</b> ".$row['date']."<br>";
    echo "<a href='news.php?id=".$row['int_id']."'><font size = 3>".$row['vc_title']."</font></a><br><br>";
    echo "<font size = 2>Reference Number: " .$row['int_id']."</font><br><hr>";
    }
} // End if statements

mysql_close(); // Close the database connection.



?>[/code]

Share this post


Link to post
Share on other sites
[!--quoteo(post=387650:date=Jun 24 2006, 10:43 PM:name=hackerkts)--][div class=\'quotetop\']QUOTE(hackerkts @ Jun 24 2006, 10:43 PM) [snapback]387650[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Do you mean when your news is totally empty then it display the words ?

Try this, and I have modify abit of your script.
[/quote]

Yes, Thanks again for the help! :)

how about for this code: (with totally empty as well)
[code]
<?php
$news = $_GET['id'];
$query = "SELECT * FROM news WHERE int_id = '$news'";
$r = mysql_query($query) or die("Query failed: " . mysql_error());
$row = mysql_fetch_assoc($r);
if (!isset($_GET['id']) || $_GET['id'] == 0) {
    echo "You didn't select any news.";
    } else {
        echo "".$row['date']."<br>";
        echo "".($row['vc_title'])."<br>";
        echo nl2br($row['vc_article']);
    }
?>
[/code]

Thanks a million! :)

Share this post


Link to post
Share on other sites
>.<! It couldn't be totally empty, is that the full script in your news.php ?

Share this post


Link to post
Share on other sites
[!--quoteo(post=387731:date=Jun 25 2006, 06:49 AM:name=hackerkts)--][div class=\'quotetop\']QUOTE(hackerkts @ Jun 25 2006, 06:49 AM) [snapback]387731[/snapback][/div][div class=\'quotemain\'][!--quotec--]
>.<! It couldn't be totally empty, is that the full script in your news.php ?
[/quote]

which one? which >.<? no its not the full script.
what I did was to use
include_once($path."mysql_connect.php");
include_once($path."getNews.php"); // <-- content of this is the script, where known as news.php.

Now I am looking a way to echo "sorry database empty" if the database is totally empty. otherwise, the script was working :)

Also I am looking for script to protect my add_news.php and delete_news.php and edit_news.php, you might have a suggestion for this? I am looking the one with user database so the user could add account to other users which is authorized to add news to the site, however, the simply authenticaion method will do as of yet.

Thanks again in advance. :)

Share this post


Link to post
Share on other sites
Hmm.. Let me get this right first..
Post #19 is the news.php ? or is it index page ?

And I don't think you should include the getNews.php it will make a mess in your script. As for add, del and edit news try session.

Don't ask me about session, I'm still learning it. :s

Hmm.. Do you have a live demo site ? I'm getting lost.. Not sure which is which.

Post#18 it should be the index page, I have added "sorry database empty".
And the only way to view the full article from getNews.php is go to
[a href=\"http://domain/getNews.php?id=1\" target=\"_blank\"]http://domain/getNews.php?id=1[/a]
1 and above.

Share this post


Link to post
Share on other sites
Hi Hackerkts,

Thanks a lot for the help! :) you should not worry about add edit delete, I was able to do so, I am looking right now is how to protect my add edit delete page using authentication.

My add edit delete page was working, i just need to have an authentication methid to protect them.

As far as live demo site, I still havent upload the files to my server, its still in my local computer. :)

once again thanks you :)

Share this post


Link to post
Share on other sites
Already, hmm sorry I don't think I could help out on time.
School open, and my stupid teacher giving me tons of homework. >.<

I don't think I really have the time to play lolz. :s
If you had any problem feel free to ask others. :D

Share this post


Link to post
Share on other sites
[!--quoteo(post=387962:date=Jun 26 2006, 03:16 AM:name=hackerkts)--][div class=\'quotetop\']QUOTE(hackerkts @ Jun 26 2006, 03:16 AM) [snapback]387962[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Already, hmm sorry I don't think I could help out on time.
School open, and my stupid teacher giving me tons of homework. >.<

I don't think I really have the time to play lolz. :s
If you had any problem feel free to ask others. :D
[/quote]

Hi Hackerkts,

Thank you so much for your help! I was able to secure my pages :) it work using session :)

Thanks again!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.