Sorting data display

[llorax]

I have made a table which displays upcoming events by date. I have a few modifications I want to make on pages and wanted help.

-is there a code that turns my db information to hash to help protect it?
-is there a way i can set the table to only display the events that have not happened yet?
-for a separate page is there a way to say only display the next 5 events?

Thanks!

[codeexploiter]

-is there a way i can set the table to only display the events that have not happened yet?

You can do this by using a database and a server-side script like ASP or PHP. Whenever the page is requested by a user the web server will execute the server-side script which communicates with the database table and retrieve only those event records which is going to be held in the future.

-is there a code that turns my db information to hash to help protect it?

If you keep your information in a secure database package either in the same machine where your web server is running or on a dedicated database server, it would be very difficult for the people to directly access them and since your server-side script is responsible for communicating with the database table there is no question of revealing your key information about your database to your users.

-for a separate page is there a way to say only display the next 5 events?

Yes it is possible. You can do it either in your SQL statement or using the programming logic through your server-side script you can achieve this.

Since you haven't mentioned anything about your development environment it is very difficult to provide a specific comments.

Last but not least you could've used a better heading for your question (personal feeling).

[llorax]

I should have been more clear in my first post but I have created a php/mysql database. I have it retrieving all the dates but I need to code it so it only retrieves future dates and I'm not sure how to do so.

Same with the only viewing 5 or so of the data. I'm just not sure how I would specify those two things in the code.

[codeexploiter]

The following PHP program demonstrates retrieving only the dates which are greater than the current date (future date). You need to modify only the following items in the below mentioned code

(1) change the database name to your database name.
(2) change the database user username and password into your database username and password.
(3) change the sql statement mainly the fieldlist (what are the fields you need to retrieve and mostly you don't have to change the where clause.
(4) change the table definition according to your requirement if necessary.

<?php
class database
{
    private $db_handle;
    private $user_name;
    private $password;
    private $data_base;
    private $host_name;
    private $sql;
    private $results;

    function __construct($host="localhost",$user,$passwd)
    {
        $this->db_handle = mysql_connect($host,$user,$passwd);
    }

    function dbSelect($db)
    {
        $this->data_base = $db;
        if(!mysql_select_db($this->data_base, $this->db_handle))
        {
            error_log(mysql_error(), 3, "/phplog.err");
            die("Error connecting to Database");
        }
    }
    
    function executeSql($sql_stmt)
    {
        $this->sql = $sql_stmt;
        $this->result = mysql_query($this->sql);
    }
    
    function transferResult()
    {
        return $this->result;
    }
}

$user = "root";
$passwd = "";
$db = "testdb";

$sql = "select dateofevent from test where dateofevent > (select curdate())";

$dbObject = new database($host,$user,$passwd);
$dbObject->dbSelect($db);
$dbObject->executeSql($sql);
$res = $dbObject->transferResult();

$html = "<table cellspacing='0' cellpadding='0' width='200' border='1'>";
echo $html;
while($record = mysql_fetch_object($res))
{
    echo "<tr><td width='200'>$record->dateofevent</td></tr>";
}
echo "</table>";
?>

2. You can limit your database record retrieval using the LIMIT clause allowed in MySQL

SELECT employee_id, first_name, last_name from employees ORDER BY employee_id LIMIT 5;

The above sql query will return only the first 5 records from table employees sorted in an ascending order based on employee id the result consists of employee_id, first_name and last_name fields.

Let me know if I can offer any more help if it is possible.

[tomyknoker]

Hi codeexploiter I was interested in the following that you posted:

<?php
class database
{
    private $db_handle;
    private $user_name;
    private $password;
    private $data_base;
    private $host_name;
    private $sql;
    private $results;

    function __construct($host="localhost",$user,$passwd)
    {
        $this->db_handle = mysql_connect($host,$user,$passwd);
    }

    function dbSelect($db)
    {
        $this->data_base = $db;
        if(!mysql_select_db($this->data_base, $this->db_handle))
        {
            error_log(mysql_error(), 3, "/phplog.err");
            die("Error connecting to Database");
        }
    }
    
    function executeSql($sql_stmt)
    {
        $this->sql = $sql_stmt;
        $this->result = mysql_query($this->sql);
    }
    
    function transferResult()
    {
        return $this->result;
    }
}

Is this a better way to connect to the database? I mean I only have been using information from this simple tutorial http://www.php-mysql-tutorial.com/co...-using-php.php, but it doesn't show what you did so if you could elaborate on why it's better/what it does that'd be tops!

[llorax]

I tried to merge my code and yours and I don't think I succeeded at all:/ What did I do wrong? Please note, I do have the usernames and passwords but I took them out to post on the board.

<?
$username="";
$password="";
$database="";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM showdates ORDER BY date DESC LIMIT 5";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

$sql = "select dateofevent from test where dateofevent > (select curdate())";

$dbObject = new database($host,$user,$passwd);
$dbObject->dbSelect($db);
$dbObject->executeSql($sql);
$res = $dbObject->transferResult();

$i=0;
while ($i < $num) {

$date=mysql_result($result,$i,"date");
$venue=mysql_result($result,$i,"venue");
$location=mysql_result($result,$i,"location");
$information=mysql_result($result,$i,"information");
while($record = mysql_fetch_object($res))
{
echo "<tr><td>$date</td><td>$venue</td><td>$location</td><td>$information</td></tr>";
}
$i++;
}

?>

[thetestingsite]

Sorry for the late response, but try this:

<?php
$username="";
$password="";
$database="";

mysql_connect(localhost,$username,$password);
mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM showdates ORDER BY date DESC LIMIT 5";
$info=mysql_query($query);

$num=mysql_numrows($info);

$sql = "select dateofevent from test where dateofevent > (select curdate())";

$result = mysql_query($sql);

$i=0;
while ($i < $num) {

$date=mysql_result($result,$i,"date");
$venue=mysql_result($result,$i,"venue");
$location=mysql_result($result,$i,"location");
$information=mysql_result($result,$i,"information");
while($record = mysql_fetch_object($res))
{
echo "<tr><td>$date</td><td>$venue</td><td>$location</td><td>$information</td></tr>";
}
$i++;
}

mysql_close();
?>

Not tested, but should work. Hope this helps.

As for the code you posted above, this section can be removed:

$dbObject = new database($host,$user,$passwd);
$dbObject->dbSelect($db);
$dbObject->executeSql($sql);
$res = $dbObject->transferResult();

because you did not reference the class that code-ex posted. Also, you had already made a connection to the database at the beginning of your code.

[djr33]

Also, to protect data using a hash, realize that it works by creating a generated result that cannot be undone, so you can't return it to the original value, but, rather, just verify, for example, a password by running it through the same algorithm.

Look into md5() and sha1() on PHP.net

[llorax]

I changed the code to the one you gave me and it says 'Warning: mysql_result(): supplied argument is not a valid MySQL result resource' for the following

$date=mysql_result($result,$i,"date");
$venue=mysql_result($result,$i,"venue");
$location=mysql_result($result,$i,"location");
$information=mysql_result($result,$i,"information");
while($record = mysql_fetch_object($res)) 

[thetestingsite]

In this query:

select dateofevent from test where dateofevent > (select curdate())

Did you change the parts in red to match your database table?
This is the only thing that I could see that could be causing that error.
Hope this helps.