[RESOLVED] Adding a zero value

ElphieFabalaFae

My intention is to create a database to keep track of how many times I ride a ride in a theme park on a said day, so that I can query things like: how many rides I've ridden today, how many in total, etc. I have created the database like this:

create table ride (ride_date date,
ride_one int,
ride_two int,
ride_three int,
ride_four int,
ride_five int,
primary key (ride_date));

I have since created a PHP 'add' page, which lists every ride in the database with a text field for the date and for each ride. As long as each text field for the rides has a value of 1 or more, then the update to the database will take place. As soon as any of the ride text fields are left empty/have '0' in them, the database update does not happen.

Since I will not necessarily ride every ride, every day, I need some way of being able to add '0' to the database entry for any given ride on any given ride. I want to be able to add something like this:

Date: 2006-05-21
Ride One: 0
Ride Two: 0
Ride Three: 0
Ride Four: 1
Ride Five: 3

I have tried playing with the NULL and NOT NULL features, but I am not sure they are what I need in order to make this task possible. Any help or tips would be gratefully received!

cgraz

Just set the default value of each ride field to zero when you create your database. If you don't explicitly input a value (meaning you've ridden the ride), it will input the zero for you by default.

ElphieFabalaFae

Okay, I have re-created the database using the following code:

create table ride (ride_date date,
ride_one int default 0,
ride_two int default 0,
ride_three int default 0,
ride_four int default 0,
ride_five int default 0,
primary key (ride_date));

But I am still not able to update the database when there is a 0 value. Have I mis-written the inital code for creating the database?

cgraz

Then the problem is with your php code, not your MySQL setup. For example, this query would work:

INSERT INTO ride( ride_date, ride_one, ride_three, ride_five)
	VALUES( '2007-05-27', 4, 2, 5 );

and would set the following values

ride_date: 2007-05-27
ride_one: 4
ride_two: 0
ride_three: 2
ride_four: 0
ride_five: 5

Mysql wouldn't stop you from doing an INSERT/UPDATE just because the value was zero. Could you post the code that is giving you trouble?

cgraz

By the way, this would be equivalent to the code above (since you mentioned using zero's)

INSERT INTO ride ( ride_date, ride_one, ride_two, ride_three, ride_four, ride_five )
	VALUES( '2007-05-27', 4, 0, 2, 0, 5 );

ElphieFabalaFae

Okay, it's definitely a PHP issue. Why I didn't think to 'hardcode' an entry to the database to check in the first place I don't know! Following your examples aboves, and entering it to the database via the Command Prompt window works perfectly fine. When trying to add from my PHP page, it still does not want to 'play ball'!

I have tried leaving the fields blank, as well as entering 0, but neither way seems to get what I want. I will eventually be adding more rides to the database, so hardcoding it into the database will be extremely difficult and time consuming!!!!

This is how the add_rides.php page stands at the moment:

<html><head>
<title>adding Ride Count</title></head>
<body>

<?php

$self =  $_SERVER['PHP_SELF'];
$rides_date =    $_POST['rides_date'];
$rides_one =    $_POST['rides_one'];
$rides_two =    $_POST['rides_two'];
$rides_three =    $_POST['rides_three'];
$rides_four =    $_POST['rides_four'];
$rides_five =    $_POST['rides_five'];

?>

<form action="<?php echo( $self ); ?>" method="post">
Date (1983-06-16): <input type="text" name="rides_date" size="15"><p>
PARK ONE<br>
Ride One: <input type="text" name="rides_one" size="5"><br>
Ride Two: <input type="text" name="rides_two" size="5"><br>
Ride Three: <input type="text" name="rides_three" size="5"><p>

PARK TWO<br>
Ride Four: <input type="text" name="rides_four" size="5"><br>
Ride Five: <input type="text" name="rides_five" size="5"><p>
<input type="submit" value="Submit">
</form>

<?php

if( $rides_date and $rides_one and $rides_two and $rides_three and $rides_four and $rides_five )
{
  $conn=@mysql_connect( "localhost", "root", "######" ) or die( "Err:Conn" );

 #select the specified database
 $rs = @mysql_select_db( "themepark", $conn) or die( "Err:Db" );

 #create the query
 $sql = "insert into rides ( rides_date, rides_one, rides_two, rides_three, rides_four, rides_five ) values ( \"$rides_date\", \"$rides_one\", \"$rides_two\", \"$rides_three\", \"$rides_four\", \"$rides_five\" )";

 #execute query
 $rs = mysql_query( $sql, $conn );

 if( $rs )
 {
   echo( "Record added:$rides_date, $rides_one, $rides_two, $rides_three, $rides_four, $rides_five" );
 }
}

?>

</body></html>

bradgrafelman

ElphieFabalaFae wrote:
I will eventually be adding more rides to the database

Which means you'll have to alter the table structure, right?

Sounds like your DB setup desperately needs to be normalized.

cgraz

Try adding some more error checking. Here's an example (untested, and requires input validation). You'll need to add name="submit" to your submit button in order for this to work:

<?php

if( isset( $_POST['submit'] ) )
{
	// Do whatever error checking you need here

$rides = array( 'rides_date', 'rides_one', 'rides_two', 'rides_three', 'rides_four', 'rides_five' );

foreach( $rides as $key )
{
	if( $key == 'rides_date' )
	{
		$rides_date = isset( $_POST[$key] ) ? $_POST[$key] : date( 'Y-m-d' );
	}
	else
	{
		$val = (int) $_POST[$key];

		$$key = isset( $val ) ? $val : 0;
	}
}

// Connect to Server
$conn = mysql_connect( 'localhost', 'root', 'pass' ) or die( 'Connection Failed: ' . mysql_error() );

// Select DB
$rs = mysql_select_db( 'themepark', $conn ) or die( 'Select DB Failed: ' . mysql_error() );

// Create the query
$sql = "INSERT INTO rides ( rides_date, rides_one, rides_two, rides_three, rides_four, rides_five ) 
		VALUES( '" . $rides_date . "', '" . $rides_one . "', '" . $rides_two . "', '" . $rides_three . "', '" . $rides_four . "', '" . $rides_five . "' )";

// Execute query
$rs = mysql_query( $sql, $conn ) or die( 'Query Failed: ' . mysql_error() );

// Print Query String
print 'Query String: ' . $sql . '<br /><br />';

if( $rs )
{
	unset( $_POST['submit'] );
	print 'Record Added: ' . print_r( $_POST );
}
}
else
{
	// Display Form
}

?>

If the INSERT doesn't work, you'll be able to see the query you're sending MySQL, and any errors generated. This should help you sort the problem out.

ElphieFabalaFae

Thanks cgraz, your recoding seems to have sorted everything out! I guess adding a 0 to a database is not an easy thing! lol Still not entirely sure what went wrong, but it seems to be fixed now, inserting into the database whether I leave things blank or add a 0! Thank you, it's perfect!

And bradgrafelman, thanks for the tip, but at the moment, this is just the test database. Once I've figured everything out via localhost, I will create the main one on my web hoster, and will create the database from scratch, including everything I need from the get go, so there will be no further additions to this database or the next one. But the tutorial was very interesting to read, so thanks for the link.