Page 1 of 2

PHP and mySQL

Posted: Mon Aug 06, 2007 2:59 pm
by mademokid
I want to create a script.

It has to go to my database and select all the data then display it it the date that will come first. Eg. One event on 1st augyst will be shown above one on the 2nd of august.

I also need a script to add data the the database using the following fields:
match_name
match_date (this is what the most recent can be displayed on)
match_time
match_venue

Any help appreciated!

Posted: Mon Aug 06, 2007 3:17 pm
by feyd
What do yo have so far?

Posted: Mon Aug 06, 2007 3:31 pm
by mademokid
Erm... not a lot!

Posted: Mon Aug 06, 2007 3:35 pm
by feyd
Okay, what do you understand about your needed code? What do you need help with understanding? Have you come up with a plan for how the logic will work in the page(s)?

Posted: Mon Aug 06, 2007 3:42 pm
by mademokid
I think i might have it! I'll tell you if it works!

Posted: Mon Aug 06, 2007 3:45 pm
by robshanks
Is it a mySql database? Select all the data in the database or just in a specific table?

If it is mysql try:

Code: Select all

//connect to server and select database
  $con = mysql_connect($servername, $username, $password);
  if (!$con)
  {
    die('Could not connect :' . mysql_error());
  }
  //select database  
  mysql_select_db($database, $con);
  //create and execute query
  $sql="SELECT * FROM ".$table." ORDER BY match_date ASC";  //ASC so oldest are at the top
  $result=mysql_query($sql);
  //iterate through resulting rows
  while($row=mysql_fetch_array($result))
  {
    echo $row['match_name']." ".$row['match_date']." ".$row['match_time']." ".$row['match_venue']."<br>";
  }
You need to set $servername, $username, $password, $database, and $table as appropriate.

Posted: Mon Aug 06, 2007 3:56 pm
by mademokid
I also need a code that deletes the match if the date has passed.

Posted: Mon Aug 06, 2007 4:03 pm
by robshanks
Try:

Code: Select all

//connect to server and select database
  $con = mysql_connect($servername, $username, $password);
  if (!$con)
  {
    die('Could not connect :' . mysql_error());
  }
  //select database 
  mysql_select_db($database, $con);
  //create and execute query
  $sql="DELETE FROM ".$table." WHERE match_date<CURDATE()";
  $result=mysql_query($sql);
match_date will have to be stored in a field of type 'DATE' for this to work.

Posted: Mon Aug 06, 2007 4:05 pm
by feyd
Unless there's a "good reason" for actually deleting the records, there's generally no need to do so. The logic of the query should be able to handle the exclusion of such a trivial matter.

Posted: Mon Aug 06, 2007 4:57 pm
by mademokid
I dont want the match to be displayed after that date it is on though.

And also if i use roboshanks code what format does the input have to be?

Posted: Mon Aug 06, 2007 5:04 pm
by robshanks
'YYYY-MM-DD'

Posted: Mon Aug 06, 2007 5:04 pm
by feyd
mademokid wrote:I dont want the match to be displayed after that date it is on though.
So you don't want to be able to see them ever, from any interface in the future? Your users don't want to see what has happened in the past?
mademokid wrote:And also if i use roboshanks code what format does the input have to be?
There are no inputs required by robshanks' code if I read it correctly.. and proper coding practices are used.

Posted: Mon Aug 06, 2007 5:47 pm
by mademokid
So how would I set the date field?

Posted: Mon Aug 06, 2007 5:55 pm
by robshanks
You just need to store the 'match_date' correctly when you put the data in the database.

CURDATE() is a mySql function which generates today's date in the 'DATE' format e.g. today is '2007-08-06'

Posted: Mon Aug 06, 2007 5:57 pm
by mademokid
So how could I use this in php?