~ HELP- my php select script !!~

Hi all

I am having a problem with the following script, what i am trying to do is to embed this php code into html, which selects the latest courses avaliable with in the dept., all works fine but i want to only select the courses that are going to be run, how can i do this, at the moment the script lists all of the courses from the ingres database.

My php script is listed below, please feel free to modify it...thanks in advance..

I only want to show the courses that are going to be run with the dates that have NOT expired how can i do this ???...

mycode:
<?
function Error_Handler( $msg, $cnx )
{

echo "$msg n";
// in case of persistent connexion, it is important to close it before exiting.
odbc_close( $cnx);
exit();
}

// create an ODBC connection, returned in $cnx
$cnx = odbc_connect( 'dbname' , 'username', 'password' );

if( ! $cnx ) {
Error_handler( "Error in odbc_connect" , $cnx );
}

// send a simple odbc query . returns an odbc cursor
$cur= odbc_exec( $cnx, "select crse_cd, start_date, start_time, end_time from tablename");

if( ! $cur ) {
Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx );
}

echo "<table border=1><tr><tr>
<th><strong>Course Title:</strong></th>
<th><strong>Course Date:</strong</th><th><strong>Start Time:</strong</th>
<th><strong>End Time:</strong</th>n";
$nbrow=0;

// fetch the succesive result rows
while( odbc_fetch_row( $cur) )
{
$nbrow++;
// get the field "course title"
$id= odbc_result( $cur, 1 );

// get the field "start date"
$nom2= odbc_result( $cur, 2 );

// get the field "start time"
$nom3= odbc_result( $cur, 3 );

// get the field "end time"
$nom4= odbc_result( $cur, 4 );

// output the results extracted from Ingres DB.
echo "<tr><td>$id</td><td>$nom2</td><td>$nom3</td><td>$nom4</td>n";
}

echo "<tr><td colspan=2><strong>The following $nbrow Entries have been Extracted</strong>
</td></tr></table>";

// close the connection. important if persistent connection are "On"
odbc_close( $cnx);

?>

Use the 'WHERE' clause. For example...

"select crse_cd, start_date, start_time, end_time from tablename WHERE start_date >= expiry_date"

What the WHERE clause is saying here is that only records that have a start date that is greater than or equal to the "expiry_date". (this would be either a hard coded date or you could use the php date() function to use todays date). Remember though that the date formats must match in order for this to work. refer to the php manual for more info on the date() function. I am not quite sure what db you are using, but consult that manual for date functions that you can use.

hi there

thanks for the info. but i am still having problems trying to get the latest entries from the database, the select and where clause is giving me the following error

Function 'date' specified with incorrect number of parameters.


$cur= odbc_exec( $cnx, "select crse_cd, start_date, start_time, end_time from tablename
where start_date>=expiry_date");


thanks again, feel free to modify my script, thanks again...

I'm not sure what db you are using - but are both the start_date and expiry_date fields a date type in your database structure?

is the query you listed in your last post the actual query? If it is, you need to replace 'expiry_date'with an actual date or as I previously suggested, use the PHP date() funtcion.

[highlight=php]

$cur= odbc_exec( $cnx, "select crse_cd, start_date, start_time, end_time from tablename
where start_date>='".date("m/d/Y"),"'");

The date function at the end of the query should return todays date in the following format: 06/19/2003

hi jester

thanks for the reply...i am using ingres, the table structure looks like the following

create table tablename (
crse_cd varchar(16) not null not default,
start_date date not null not default,
start_time date not null not default,
end_time date not null not default,
month integer1 not null not default,
year integer2 not null not default,
expiry_date datetime not null not default
);
pg


below is my full php script...

function Error_Handler( $msg, $cnx )
{

echo "$msg n";
// in case of persistent connexion, it is important to close it before exiting.
odbc_close( $cnx);
exit();
}

// create an ODBC connection, returned in $cnx
$cnx = odbc_connect( 'dbname' , 'userid', 'password' );

if( ! $cnx ) {
Error_handler( "Error in odbc_connect" , $cnx );
}

// send a simple odbc query . returns an odbc cursor
$cur= odbc_exec( $cnx, "select crse_cd, start_date, start_time, end_time from w100v_biqcrse");

if( ! $cur ) {
Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx );
}

echo "<table border=2><strong>June 2003</strong>
<th><strong>Course Title:</strong></th>
<th><strong>Course Date:</strong</th><th><strong>Start Time:</strong</th>
<th><strong>End Time:</strong</th>n";
$nbrow=0;

// fetch the succesive result rows
while( odbc_fetch_row( $cur) )
{
$nbrow++;
// get the field "course title"
$id= odbc_result( $cur, 1 );

// get the field "start date"
$nom2= odbc_result( $cur, 2 );

// get the field "start time"
$nom3= odbc_result( $cur, 3 );

// get the field "end time"
$nom4= odbc_result( $cur, 4 );

// output the results extracted from Ingres DB.
echo "<tr><td>$id</td><td>$nom2</td><td>$nom3</td><td>$nom4</td>n";
}

//echo "<tr><td colspan=2><strong>The following $nbrow Entries have been Extracted</strong>
// </td></tr></table>";

// close the connection. important if persistent connection are "On"
odbc_close( $cnx);

?>
</center><center>
<?
include ("include/footer.php");
?>


the script works okay but lists all of the courses,i only want to list the courses that are still to done...i want to select * from table name where (expiry_date<'$today')";

could u please tell me what i need to modify at the databse end e.g. expiry_date field etc etc ...feel free to modify my code above...thanks again...

Indy,

change your query to look like this.

[highlight=php]

$cur= odbc_exec( $cnx, "select crse_cd, start_date, start_time, end_time from tablename
where expiry_date<'".date("m/d/Y")."'");

that should do it!!!

Hi php gurus,


Great , managed to get this expired date script working okay, thanks all the guys gave me ideas and code snippets...e.g blind eddie and other...thanks again...keep up the good work...great user group, learnt more searching through this group than books...thanks...indy