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/connect-to-mysql-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.

[llorax]

Yeah it's still giving me trouble.


<?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";
$info=mysql_query($query);

$num=mysql_numrows($info);

$sql = "SELECT date FROM showdates where date > (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();
?>

and here is the exact error message:


Warning: mysql_result() [function.mysql-result]: venue not found in MySQL result index 4 in /home/kc/public_html/shows2.php on line 75

Warning: mysql_result() [function.mysql-result]: location not found in MySQL result index 4 in /home/kc/public_html/shows2.php on line 76

Warning: mysql_result() [function.mysql-result]: information not found in MySQL result index 4 in /home/kc/public_html/shows2.php on line 77

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home/kc/public_html/shows2.php on line 78

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 1 on MySQL result index 4 in /home/kc/public_html/shows2.php on line 74

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 1 on MySQL result index 4 in /home/kc/public_html/shows2.php on line 75

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 1 on MySQL result index 4 in /home/kc/public_html/shows2.php on line 76

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 1 on MySQL result index 4 in /home/kc/public_html/shows2.php on line 77

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home/kc/public_html/shows2.php on line 78

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 2 on MySQL result index 4 in /home/kc/public_html/shows2.php on line 74

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 2 on MySQL result index 4 in /home/kc/public_html/shows2.php on line 75

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 2 on MySQL result index 4 in /home/kc/public_html/shows2.php on line 76

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 2 on MySQL result index 4 in /home/kc/public_html/shows2.php on line 77

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home/kc/public_html/shows2.php on line 78

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 3 on MySQL result index 4 in /home/kc/public_html/shows2.php on line 74

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 3 on MySQL result index 4 in /home/kc/public_html/shows2.php on line 75

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 3 on MySQL result index 4 in /home/kc/public_html/shows2.php on line 76

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 3 on MySQL result index 4 in /home/kc/public_html/shows2.php on line 77

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home/kc/public_html/shows2.php on line 78

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 4 on MySQL result index 4 in /home/kc/public_html/shows2.php on line 74

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 4 on MySQL result index 4 in /home/kc/public_html/shows2.php on line 75

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 4 on MySQL result index 4 in /home/kc/public_html/shows2.php on line 76

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 4 on MySQL result index 4 in /home/kc/public_html/shows2.php on line 77

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home/kc/public_html/shows2.php on line 78

[thetestingsite]

Ok, try this in your query:



$sql = "SELECT * FROM showdates where date > (select curdate())";


Hope this helps.

//Added Later: Also, be sure to change the names in this part (the ones in red) to match your database:



$date=mysql_result($result,$i,"date");
$venue=mysql_result($result,$i,"venue");
$location=mysql_result($result,$i,"location");
$information=mysql_result($result,$i,"information");

[llorax]

I moved some things around and did your suggestions which got rid of all those error messages except now theres a new one:



Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home/kc/public_html/shows2.php on line 75


<?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";
$info=mysql_query($query);

$num=mysql_numrows($info);

$sql = "SELECT * FROM showdates where date > (select curdate())";

$result = mysql_query($sql);

$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))
{

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

echo "<tr><td>$date</td><td>$venue</td><td>$location</td><td>$information</td></tr>";
}
$i++;
}

mysql_close();
?>

[thetestingsite]

That is due to this line:



while($record = mysql_fetch_object($res))


The part in red should be $result

Hope this helps.

[llorax]

is there something in the code that tells it to repeat? for some reason i wrote a test entry and it only showed that future entry but it repeated it hundreds of times and actually froze the browser.

[thetestingsite]

That would be due to a bad (endless) loop. The only thing that I see that could cause this would be this line:



$num=mysql_numrows($info);


It should be:



$num=mysql_num_rows($info);


Other than that, the script should work fine. Sorry I didn't notice that before.
Anyways, hope this helps.

[mburt]

The while loop in this case, never ends, considering it fetches data from the server continuosly. So you can call the variables inside the loop when you want.
If you tried echo inside the loop, naturally, it would freeze up.
mysql_fetch_obj never returns true so the loop is infinite.

[thetestingsite]

Instead of fumbling through this script to make it work, I decided to rewrite the script. Try the following to see if it works for you:



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

mysql_connect(localhost,$username,$password);
mysql_select_db($database) or die( "Unable to select database");

$sql = "SELECT * FROM showdates where date > (select curdate())";

$result = mysql_query($sql);

echo '<table>';

while ($q = mysql_fetch_array($result)) {

echo "<tr><td>".$q['date']."</td><td>".$q['venue']."</td><td>".$q['location']."</td><td>".$q['information']."</td></tr>";
}

echo '</table>';

mysql_close();
?>


Hope this helps.