PHP/SQL: Increment Integer Value in Database via Form

[shawntc]

The page in question is http://shawntc.x10hosting.com/picard-page.php

I'm learning to use PHP/SQL. Pretty much what I'm trying to do is grab an integer value from a database, then when the user clicks a button the value is incremented and stored. The PHP on the server that deals with the SQL:

<?php

// add-picard-starships.php

$a=$_POST["a"];
$a++;
$con=mysql_connect("localhost","shawntc_username","wouldntyouliketoknow");
if(!$con){
  die("Connect failed".mysql_error());
}
mysql_select_db("shawntc_test",$con);
mysql_query("UPDATE StarshipTest SET StarshipsOwned='$aString' WHERE Captain='Picard'");
$result=mysql_query("SELECT StarshipsOwned FROM StarshipTest WHERE Captain='Picard');
$row=mysql_fetch_array($result);
echo $row['StarshipsOwned'];
mysql_close($con);
?>

But when I press Add to Picard, this is what is returned:
Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/shawntc/public_html/add-picard-starships.php on line 15

Grrr it makes no sense to me.

[leafypiggy]

In your mysql update query, chain $aString to just $a.

[shawntc]

Tried that, both with and without the single quotes around it. Error still persists.

[leafypiggy]

Okay. I'll take look when I get on my laptop. iPod isn't he best place for debugging.

[lemon-tree]

Your error is on the line it states, you forgot to close the double-quotes:

$result=mysql_query("SELECT StarshipsOwned FROM StarshipTest WHERE Captain='Picard'");

[misson]

There are a few other issues beyond the error.

Note: use [php] rather than [code] for PHP code, and it gets colorized. [html] does the same for HTML code.

$con=mysql_connect("localhost","shawntc_username","wouldntyouliketoknow"); 

The old mysql driver has been obsoleted twice over, first by mysqli, then PDO. For a tutorial on the latter, read "Writing MySQL Scripts with PHP and PDO".

  die("Connect failed".mysql_error()); 

Don't use die when outputting HTML.

Outputting mysql_error to every user reveals too much information.

mysql_query("UPDATE StarshipTest SET StarshipsOwned='$aString' WHERE Captain='Picard'"); 

This is open to SQL injection. Normally, I'd say to use prepared statements, but here you can use a different query:

UPDATE StarshipTest SET StarshipsOwned=StarshipsOwned+1 WHERE Captain='Picard'

This also fixes the problem with tho other query that if multiple users simultaneously access the page and update the table, the last update will clobber the others.

[shawntc]

Wow. I need an IDE bad.

[bdistler]

Here is something to start with...
I have NOT thrown it at a database

<?php

// so we can use the current session ID
session_start();

// put ==> include "my_lib.php"; <== here after you make it

//variables for MySQL database connection S/B in your "my_lib.php" lib file
//used as "$dbConn = mysql_connect($serverName, $userName, $password)"
$serverName = "localhost";
$userName = "shawntc_username";
$password = "wouldntyouliketoknow";

// used as "mysql_select_db($dbName, $dbConn)"
$dbName = "shawntc_test";

// These functions S/B in your php lib file

function connectToData()
  {
    // set in "my_lib.php"
    global $serverName, $userName, $password, $dbName;

    // get "MySQL link identifier" in $dbConn or die
    $dbConn = mysql_connect($serverName, $userName, $password) or die ("<h3>Server unreachable</h3><br />\n");

    //connect to MySQL database or die
    mysql_select_db($dbName, $dbConn) or die ("<h3>Database non existent</h3><br />\n");

    //return chl# = "MySQL link identifier"
    return $dbConn;
  } // end function connectToData()

function stripslashes_nested($bugger)
  {
    if (is_array($bugger))
      {
        return array_map('stripslashes_nested', $bugger);
      } else
        {
          return stripslashes($bugger);
        }
  } // end function stripslashes_nested($bugger)

function full_mysql_escape($escapeit)
  {
    $escapeit = trim($escapeit);
    $escapeit = mysql_real_escape_string($escapeit);
    $escapeit = str_replace("`","\`",$escapeit);
    return $escapeit;
  } //end function full_mysql_escape($escapeit)

// ***************** end of functions *****************

$debugPrint = FALSE;
// rem out next line to stop debug print
$debugPrint = TRUE;

$dbTable = "StarshipTest";
$dbField = "StarshipsOwned";
$dbKey = "Captain = 'Picard'";

// NEED TO TEST FOR JUNK HERE
// data is passed to use via $_POST
if (get_magic_quotes_gpc())
  {
    // if PHP "Magic Quotes" is set on
    // undo the effects of "Magic Quotes" on $_POST
    $_POST = stripslashes_nested($_POST);
  }

// pevent SQL Injection Attack with data sent to MySQL database
// test $value  ** \x00 * \n * \r * \ * ' *` * " * \x1a ** for mysql_real_escape_string($value)
// mysql_real_escape_string($value) will NOT backslashes "`" (next to far let "1" key on USA key borads
// so after "mysql_real_escape_string($value)" use "$value = str_replace("`","\`",$value);"

$a = $_POST["a"];
$a = full_mysql_escape($a);

// I would pass the count as  $_SESSION['count'] also
//
// here you need to test the value of $a like:
// if (($a < $min) || ($a > $max) || ($a != $_SESSION['count'])) do something about the error

// I would pass session ID as $_SESSION['star'] also
//
// $theID = $_SESSION["star"];
// will not put $theID into MySQL database no need to use ==> full_mysql_escape() <==
//
// test for same session
// if ($theID !== session_id()) do something about NOT same session

// assume good to work database
global $dbConn;
$dbConn = connectToData();

// assume $a is incremented BEFORE passed to us
$query = "UPDATE $dbTable SET $dbField = ' $a ' WHERE $dbKey";

$result = mysql_query($query, $dbConn);

if (!$result)
  {
    if ($debugPrint) print "Could not successfully run update query (" . $query . ") <br />  mySQL error: " . mysql_error() . "<br />\n";
    print "Finished with error<br />\n";
    exit;
  }

if (mysql_affected_rows($dbConn) == 0)
  {
    if ($debugPrint) print "No record updated with query (" . $query . ") <br />\n";
  }
  elseif (mysql_affected_rows($dbConn) > 1)
    {
      if ($debugPrint) print "More then one record updated with query (" . $query . ") <br />\n";
      print "Finished with error<br />\n";
      exit;
    }

// assume only one $dbKey
$query = "SELECT $dbField FROM $dbTable WHERE $dbKey";

$result=mysql_query($query, $dbConn);

if (!$result)
  {
    if ($debugPrint) print "Could not successfully run read-back query (" . $query . ") <br />  mySQL error: " . mysql_error() . "<br />\n";
    print "Finished with error<br />\n";
    exit;
  }

if (mysql_num_rows($result) < 1)
  {
    if ($debugPrint) print "No record found at read-back";
    print "Finished with error<br />\n";
    exit;
  }
  elseif (mysql_num_rows($result) > 1)
    {
      if ($debugPrint) print "More then one record found";
      print "Finished with error<br />\n";
      exit;
    }

$row = mysql_fetch_assoc($result);
print "You are the owner of $row[$dbField] Starships<br />\n";

mysql_close($con);
?>