Link to home
Start Free TrialLog in
Avatar of runnerjp
runnerjp

asked on

allowing users to enter their time and events

   *
    *
          o View Profile
          o Personal Message (Online)

allowing users to enter their time and events
« on: Today at 09:11:53 AM »

    * Reply with quoteQuote
    * Modify messageModify

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..

Code: [Select]
--
-- 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 follows below!

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!

Quote

    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?
<?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>

Open in new window

Avatar of bombozama
bombozama
Flag of Chile image

you have a quote problem. Try this.

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

With that, your php code should look something like this.
<?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>

Open in new window

Avatar of runnerjp
runnerjp

ASKER

ahh it enters it but i still get the error

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

and it only entred a pb of 3 and not 3.35
oh and also its not updating the currect fields if user id and distance exist:S
is your column declaration for pb a double or float? You could post the table structure to see what's going on.

Let me double check the code... I'll get back to you in a couple of hours.
cheers :)


table structe follows
-- 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 ;
 
--
-- Dumping data for table `pb`
--

Open in new window

Avatar of Ionut A. Tudor
Hi,
you need to change the type of pd field into varchar

ALTER TABLE pb CHANGE pb pb VARCHAR(11) NOT NULL DEFAULT '0' 

Open in new window

hey 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?
ASKER CERTIFIED SOLUTION
Avatar of Ionut A. Tudor
Ionut A. Tudor
Flag of Romania image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ha that works great!!!
Glad to help, be sure to set off the error reporting when you put your site online for security reasons. Cheers