allowing users to enter their time and events

[runnerjp]

what im tryin to do is have it so users on my webpage can enter the personal bests for the distance they ran (its a running website)

 

i have my db set out like so..

 

--
-- Table structure for table `pb`
--

CREATE TABLE IF NOT EXISTS `pb` (
  `id` int(11) NOT NULL auto_increment,
  `uid` int(11) NOT NULL default '0',
  `pb` int(11) NOT NULL default '0',
  `distance` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

 

and my code is as follows

 

<?php 
include '../settings.php';


$result = mysql_query("SELECT * FROM pb");       //make the table if needed



//insert any new entry into the database
$uid =  mget('uid',$_POST);
$dist = mget('dist',$_POST);
$pb =  mget('pb',$_POST);


insert($uid,$dist,$pb);

//now display all data

$result = mysql_query("SELECT * FROM pb Order by uid,distance");

if (!$result) {
  echo("<P>Error reading table: " .       mysql_error() . "</P>");
  exit();
  }

echo "Current values:<br>";

echo "<table border=1><tr><td>Uid</td><td>Distance</td><td>Pb</td></tr>";
while ( $row = mysql_fetch_array($result) ) {
  echo "<tr><td>" . $row["uid"] . "</td><td>" . $row["distance"] . "</td><td>" . $row["pb"] . "</td></tr>";
  }
echo "</table>";


/*-------------------------------------------------------------------------------*/
function insert($uid,$dist,$pb)    //insert new or update existing record
{

  if (!$uid) return;    //should validate here
  if (!$dist) return;
  if (!$pb) return;

//see if already exists
  $sql =  "SELECT * FROM pbs WHERE uid=$uid AND distance=$dist";
  $result = mysql_query($sql);

  if (mysql_num_rows($result) >=1) {    //already exists

    $sql =  "UPDATE pb Set pb = $pb WHERE uid=$uid AND distance=$dist";   //try updating in case already exists
    $result = mysql_query($sql);
    }
  else {    //need a new record
    $sql =  "INSERT INTO pb SET uid=$uid,distance=$dist,pb=$pb";

    echo 'sql is ' . $sql . '<br>';
    $result = mysql_query($sql);
    }
  if (!$result) {
    echo("<P>Error saving result into table: " .       mysql_error() . "<br>" . $sql . "</P>");
    }

}
/*-------------------------------------------------------------------------------*/
//when I want to get a key that may not exist without php warnings
function mget($mynam,$myarray)
{
if (is_array($myarray)|| is_object($myarray)) {
if (array_key_exists($mynam, $myarray)) {
  return htmlentities($myarray[$mynam]);    //remove any unsafe html in case of malicious user
  }
}
return '';
}
/*-------------------------------------------------------------------------------*/
?>

<form method="POST" action="<?php "$_SERVER[php_SELF]" ?>">
<br>Add a new personal best:<table><tr>
<td>Uid: <input type=text id=uid name=uid></td><td>Distance: <input type=text id=dist name=dist></td><td>Pb: <input type=text id=pb name=pb></td>
</tr></table>

<input type=submit value=Submit>
</form>

</body>
</html>

 

so basicly a user can enter the distance and personal best... it checks to see if the distance has allready been entred , if so updates it, if not enters it.

 

thing is it doesent work!

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/runningp/public_html/members/test.php on line 47

sql is INSERT INTO pb SET uid=1,distance=100m,pb=3.35

 

Error saving result into table: Unknown column '100m' in 'field list'

INSERT INTO pb SET uid=1,distance=100m,pb=3.35

 

this is the errors i keep getting!

 

i would also like to add once i get it working im going to use drop down menus for distance to narrow down choice! is there anything else you guys could also suggest?

[allworknoplay]

Your selecting a table that doesn't exist!!

 

$sql =  "SELECT * FROM pbs WHERE uid=$uid AND distance=$dist";

 

Use:

 

$sql =  "SELECT * FROM pb WHERE uid=$uid AND distance=$dist";

[runnerjp]

hey thnaks but

 

i still get

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/runningp/public_html/members/test.php on line 47

 

and it wont update fields that are allready there?

[allworknoplay]

well you should look at the variables you are using then to see if they are being populated.

 

 

SELECT * FROM pb WHERE uid=$uid AND distance=$dist

 

 

$uid and $dist

 

If they aren't being filled, then your select statement will fail. The issue is in your query...

 

Or so it seems...

 

[runnerjp]

with this code

<?php
error_reporting(0);
ini_set("display_errors", "On");


include '../settings.php';


$result = mysql_query("SELECT * FROM pb") or die(mysql_error());



//insert any new entry into the database
$uid =  mget('uid',$_POST);
$dist = mget('dist',$_POST);
$pb =  mget('pb',$_POST);


insert($uid,$dist,$pb);

//now display all data

$result = mysql_query("SELECT * FROM pb Order by uid,distance") or die(mysql_error());

echo "Current values:<br>";

echo "<table border=1><tr><td>Uid</td><td>Distance</td><td>Pb</td></tr>";
while ( $row = mysql_fetch_array($result) ) {
  echo "<tr><td>" . $row["uid"] . "</td><td>" . $row["distance"] . "</td><td>" . $row["pb"] . "</td></tr>";
  }
echo "</table>";


/*-------------------------------------------------------------------------------*/
function insert($uid,$dist,$pb)    //insert new or update existing record
{

  if (!$uid) return;    //should validate here
  if (!$dist) return;
  if (!$pb) return;

//see if already exists
  $sql =  "SELECT * FROM pb WHERE uid=$uid AND distance=$dist";
  $result = mysql_query($sql) or die(mysql_error());

  if (mysql_num_rows($result) >=1) {    //already exists

    $sql =  "UPDATE pb Set pb = $pb WHERE uid=$uid AND distance=$dist";   //try updating in case already exists
    $result = mysql_query($sql) or die(mysql_error());
    }
  else {    //need a new record
    $sql =  "INSERT INTO pb SET uid=$uid,distance=$dist,pb=$pb";

    echo 'sql is ' . $sql . '<br>';
    $result = mysql_query($sql) or die(mysql_error());
    }

}
/*-------------------------------------------------------------------------------*/
//when I want to get a key that may not exist without php warnings
function mget($mynam,$myarray)
{
if (is_array($myarray)|| is_object($myarray)) {
if (array_key_exists($mynam, $myarray)) {
  return htmlentities($myarray[$mynam]);    //remove any unsafe html in case of malicious user
  }
}
return '';
}
/*-------------------------------------------------------------------------------*/
?>

<form method="POST" action="<?php "$_SERVER[php_SELF]" ?>">
<br>Add a new personal best:<table><tr>
<td>Uid: <input type=text id=uid name=uid></td><td>Distance: 
  <?php $lines = file('runningevents.txt');
echo '<select class="inputedit" id="dist" name="dist">';
foreach($lines as $line) {
echo '<option>'.$line.'</option>';

}
echo "</select>";
?></td>
<td>Pb: <input type=text id=pb name=pb></td>
</tr></table>

<input type=submit value=Submit>
</form>
</body>
</html>

 

Unknown column '800m' in 'where clause'

 

yet i can enter just 800?