PHP and mySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

mademokid
Forum Commoner
Posts: 25
Joined: Tue Jul 10, 2007 11:10 am

PHP and mySQL

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What do yo have so far?
mademokid
Forum Commoner
Posts: 25
Joined: Tue Jul 10, 2007 11:10 am

Post by mademokid »

Erm... not a lot!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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)?
mademokid
Forum Commoner
Posts: 25
Joined: Tue Jul 10, 2007 11:10 am

Post by mademokid »

I think i might have it! I'll tell you if it works!
User avatar
robshanks
Forum Commoner
Posts: 32
Joined: Sun Aug 05, 2007 9:27 pm
Location: Hull, UK

Post 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.
mademokid
Forum Commoner
Posts: 25
Joined: Tue Jul 10, 2007 11:10 am

Post by mademokid »

I also need a code that deletes the match if the date has passed.
User avatar
robshanks
Forum Commoner
Posts: 32
Joined: Sun Aug 05, 2007 9:27 pm
Location: Hull, UK

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
mademokid
Forum Commoner
Posts: 25
Joined: Tue Jul 10, 2007 11:10 am

Post 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?
User avatar
robshanks
Forum Commoner
Posts: 32
Joined: Sun Aug 05, 2007 9:27 pm
Location: Hull, UK

Post by robshanks »

'YYYY-MM-DD'
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
mademokid
Forum Commoner
Posts: 25
Joined: Tue Jul 10, 2007 11:10 am

Post by mademokid »

So how would I set the date field?
User avatar
robshanks
Forum Commoner
Posts: 32
Joined: Sun Aug 05, 2007 9:27 pm
Location: Hull, UK

Post 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'
mademokid
Forum Commoner
Posts: 25
Joined: Tue Jul 10, 2007 11:10 am

Post by mademokid »

So how could I use this in php?
Post Reply