why wont sql statement insert into db?

[runnerjp]

for some reason my sql statements wotn insert into my db ?  i tried to echo the sql statements yet i get no ouput :S

 

can any1 figure out why?

 

<?php 
ob_start("ob_gzhandler");
session_start();
error_reporting(E_ALL); 
require_once 'settings.php';
include "info.php"; // sets username/id ect

//insert any new entry into the database
$uid = $id;
$dist = mysql_real_escape_string($_POST['dist']);
$pb = mysql_real_escape_string($_POST['pb']);
$result = mysql_query("SELECT * FROM pb") or die(mysql_error()); 
insert($uid,$dist,$pb);
/*-------------------------------------------------------------------------------*/
function insert($uid,$dist,$pb)    //insert new or update existing record
{

  if (!$uid) return;    //should validate here
if (!$dist) return;
  if (!$pb) return;
//if(!isset($uid)) return;
//see if already exists
  $sql1 =  "SELECT * FROM pb WHERE uid=$uid AND distance='$dist'";
  $result = mysql_query($sql1) or die(mysql_error());

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

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

    echo 'sql is ' . $sql3 . '<br>';
    $result = mysql_query($sql3) 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 '';
}
echo $sql2;
echo $sql3;
echo 'Your profile has been update again!';

/*-------------------------------------------------------------------------------*/
?> 

[Maq]

Try echoing them in the if else statements to see if you're even getting there.

 

Another thing is that you have this, which is nothing:

 

$uid = $id;

 

then you have this, which will always return because $id is never set to anything:

 

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

[runnerjp]

sorry i should have added this... the file i included (info.php) sets the user id ect

 

<?php
/* user sessions */
$username= get_username($_SESSION['user_id']); // gest username of online user
$id = $_SESSION['user_id']; // gets the online users id ?>

 

i tried to echo it in the statement and nothing sadly...it is echoing 'Your profile has been update again!'  though...

[manny]

you set two functions

1: mget();

2: insert();

 

when are you calling them?

 

also you set

ob_start("ob_gzhandler");

are you calling ob_end_flush(); somewhere else?

 

 

[Maq]

i tried to echo it in the statement and nothing sadly...it is echoing 'Your profile has been update again!'  though...

 

Yeah because that's unconditional...

 

Did you try echoing both queries, in the IF and the ELSE?  One of them will show, and I'm guessing it's updating every time...

 

How about echoing the variables in the function?

[premiso]

Sorry, this was just bugging me:

 

$return = insert($uid,$dist,$pb);
if ($return === true) 
    echo "The database should have been updated.";
else 
    echo "The database was not updated.";

//insert new or update existing record
function insert($uid,$dist,$pb) {
if (!is_numeric($uid) || empty($dist) || empty($pb)) 
	return false;

//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) > 0) {    //already exists
	$sql = "UPDATE pb Set `pb`.`pb` = '$pb' WHERE uid=$uid AND distance='$dist'";   //try updating in case already exists
}else {    //need a new record
	$sql =  "INSERT INTO pb (uid, distance, pb) values ($uid,'$dist','$pb')";
}

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

return (mysql_affected_rows($result) > 0)?true:false;
}

 

That would be a better way to structure that function, reduces reptitive code meaning less prone to errors.

 

As far as what the issue is, why do you have a column name the same as your table? I do not know if this would cause issues, but that is just weird and can make some confusion. See if the above function works or not and let us know.

 

And to answer manny's question, he only calls insert right before the function definition as far as I can tell.

 

[runnerjp]

ermm im not calling mget yet... but i am calling insert(); just below the insert

 

<?php function insert($uid,$dist,$pb)    //insert new or update existing record
{

echo "<h1>Got in</h1>";


  if (!$uid) return;    //should validate here
echo "<h1>1</h1>";

if (!$dist) return;
echo "<h1>2</h1>";

  if (!$pb) return;

echo "<h1>Got to here</h1>";

//if(!isset($uid)) return;
//see if already exists
  $sql1 =  "SELECT * FROM pb WHERE uid=$uid AND distance='$dist'";
  $result = mysql_query($sql1) or die(mysql_error());
  
  if (mysql_num_rows($result) >=1) {    //already exists

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

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

}
insert($uid,$dist,$pb);?>

 

 

i got rid of ob_start("ob_gzhandler");...

 

i have also tried to find an error in my code so i did this

 

<?php function insert($uid,$dist,$pb)    //insert new or update existing record
{

echo "<h1>Got in</h1>";


  if (!$uid) return;    //should validate here
echo "<h1>1</h1>";

if (!$dist) return;
echo "<h1>2</h1>";

  if (!$pb) return;

echo "<h1>Got to here</h1>";

//if(!isset($uid)) return;
//see if already exists
  $sql1 =  "SELECT * FROM pb WHERE uid=$uid AND distance='$dist'";
  $result = mysql_query($sql1) or die(mysql_error());?>

 

and all i get is 'Got in 1'

 

 

its not updating though as im checking my db also. i used your code premiso and i got the db was not updated sadly

[Maq]

Why are you echoing h1 tags, just echo the variable itself to see if it has the correct value.......

 

{
echo "uid: $uid   dist: $dist   pb: $pb";

 

Also put this at the top of your script:

 

ini_set ("display_errors", "1");
error_reporting(E_ALL);

[premiso]

So your script is not finding the record to update in the table. Simple as that. What is returned from the "sql is " statement ?

 

For debugging instead of just echoing out 1 2 etc, try this: (I would also stick to my function, it is way less error proned.)

 

$return = insert($uid,$dist,$pb);
if ($return === true) 
    echo "The database should have been updated.";
else 
    echo "The database was not updated.<br />pb was {$pb}<br />dist was {$dist}<br />uid was {$uid}.";

 

See what gets returned. Your function is never getting passed the if statements cause you are passing in bad data.

[runnerjp]

ok getting sumwhere !!!

 

this is what was outputted

sql is INSERT INTO pb (uid, distance, pb) values (1,'withoutMinutes|100m','10.1')

Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource in /home/runningp/public_html/do5.php on line 93
The database was not updated.
pb was 10.1
dist was withoutMinutes|100m
uid was 1.sql is INSERT INTO pb (uid, distance, pb) values (1,'withoutMinutes|100m','10.1')

Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource in /home/runningp/public_html/do5.php on line 93
Your profile has been update again! 

 

now it says withoutMinutes|100m but im using javascript that should change this

function validateTime(nDistance) {
  var useDistance = nDistance.split("|");
  var valid = true;
  var nTimeField = document.forms[0]['userDistance'];
  var nTime = document.forms[0]['userDistance'].value;
  
  if (useDistance[0] == "withHours")
  {
    if (nTime == "")
    {
      return false; 
    }				
    if (!/^\d{1,2}[:\-]\d{1,2}[:\-]\d{1,2}$/.test(nTime)
      &&  !/^\d{1,2}[:\-]\d{1,2}[:\-]\d{1,2}\.\d{1,2}$/.test(nTime))
    {
      alert('Hours, Minutes and Seconds must be separated\n' +
      'by a : (colon) or, - (hyphen) \n' +
      'Seconds may be expressed in tenths or hundreths:\n' +
      'ss.ss or, s.ss or, s.s or, ss.s');	
      return false;						 						
    }
    
    nTime = nTime.replace(/[:\-]/g, "|").split("|");				 
    nTime[0] > 23 ? valid = false : 
    nTime[1] > 59 ? valid = false : 
    nTime[2] > 59.99 ? valid = false : null;
    					 
    if (!valid) {
      alert('Invalid time');
      nTimeField.value = "";
      nTimeField.focus();					 					 
    }
  }
		if (useDistance[0] == "withoutHours")
			{	
			 if (nTime == "")
				{
				 return false; 
				}				
			 if (!/^\d{1,2}[:\-]\d{1,2}$/.test(nTime)
				 &&  !/^\d{1,2}[:\-]\d{1,2}\.\d{1,2}$/.test(nTime))
				{
				 alert('Minutes and Seconds must be separated\n' +
					'by a : (colon) or, - (hyphen) \n' +
					'Seconds may be expressed in tenths or hundreths:\n' +
					'ss.ss or, s.ss or, s.s or, ss.s');	
				 return false;						 						
				}
			 nTime = nTime.replace(/[:\-]/g, "|").split("|");
			 nTime[0] > 59 ? valid = false : 
			 nTime[1] > 59.99 ? valid = false : null; 					 
			 if (!valid)
				{
				 alert('Invalid time');
				 nTimeField.value = "";
				 nTimeField.focus();					 
				}
			}				
		if (useDistance[0] == "withoutMinutes")
			{					 	
			 if (nTime == "")
				{
				 return false; 
				}				
			 if (!/^\d{1,2}$/.test(nTime)
				 &&  !/^\d{1,2}\.\d{1,2}$/.test(nTime))
				{
				 alert('Seconds may be expressed in tenths or hundreths:\n' +
					'ss.ss or, s.ss or, s.s or, ss.s');	
				 return false;						 						
				}
			 nTime = nTime.replace(/[:\-]/g, "|").split("|");
			 nTime[0] > 59.99 ? valid = false : null; 					 
			 if (!valid)
				{
				 alert('Invalid time');
				 nTimeField.value = "";
				 nTimeField.focus();					 
				}
			}
			return true;	
}


function setDistance(nDistance){

	var useDistance = nDistance.split("|");
	var nTimeField = document.forms[0]['userDistance'];
	nTimeField.value = "";
	document.forms[0]['userDistance'].parentNode.firstChild.data = useDistance[1] + ": ";		
	if (useDistance[0] == "#")
		{		
		 nTimeField.readOnly = true;			 
		 return;
		}
	nTimeField.readOnly = false; 					
}

function init(){

	document.forms[0]['userDistance'].onblur = function()
		{
		 validateTime(document.forms[0]['distance'].value);
		}		
}

navigator.appName == "Microsoft Internet Explorer" ? attachEvent('onload', init, false) : addEventListener('load', init, false);

  could this be the problem?