MySQL queries run but don't update the database.

None of the queries actually update the database please help. I

[eflaherty81]

When running this code I get get the message telling me that "New User Successfully Added" or "Existing User Data Successfully Updated" but nothing changes in the database. Please help!


<?php
error_reporting (E_ALL ^ E_NOTICE);
$a = session_id();
if(empty($a)) session_start();

include "MySQLConnector.txt";
$LocalCustomerID = 0;
$WhatAction = "";

// Get the data from the form:
$firstname = trim($_REQUEST['firstname']);
$middleinitial = trim($_REQUEST['middleinitial']);
$lastname = trim($_REQUEST['lastname']);
$address = trim($_REQUEST['address']);
$address2 = trim($_REQUEST['address2']);
$city = trim($_REQUEST['city']);
$state = trim($_REQUEST['state']);
$zipcode = trim($_REQUEST['zipcode']);
$username = trim($_REQUEST['username']);
$password = trim($_REQUEST['password']);
$email = trim($_REQUEST['email']);
$phone = trim($_REQUEST['phone']);
$birthdate = trim($_REQUEST['birthdate']);
// $CustomerID2Update = $_REQUEST['CustomerID2Update'];// Hidden

//Check HERE to see if Username already exists.

switch ($_SESSION['SecurityID']) {
case 1:// Existing User UPDATING their own data.
$UsernameQuery = "SELECT COUNT(*) AS UsernameCount FROM customers
WHERE UserName = '" . $username . "' AND CustomerID != " . $_SESSION['CustomerID'] ;// Build the query
$rs = @mysqli_query ($dbc, $UsernameQuery); // Return the Result Set
WHILE ($row = mysqli_fetch_array($rs, MYSQLI_ASSOC)) {// Fetch the data
$UsernameCount = $row['UsernameCount'];
}
if($UsernameCount > 0) {// Username Already Exists...
$_SESSION['Message'] = "Sorry, the User Name: <i><font color=red>" . $Username . "</font></i> is already in use.<br><br>Please try again. <br>";
mysqli_close($dbc);
header("Location: ../index.php");// Bail Out
} else {
$WhatAction = "Existing User";// Good to Go
}
break;

case 2:// Security ID=2. Administrator UPDATING Existing User Data. Check to see if Username Exists anywhere in the database...
$UsernameQuery = "SELECT COUNT(*) AS UsernameCount FROM customers
WHERE UserName = '" . $username . "' AND CustomerID != " . $CustomerID2Update ;// Build the query
$rs = @mysqli_query ($dbc, $UsernameQuery); // Return the Result Set
WHILE ($row = mysqli_fetch_array($rs, MYSQLI_ASSOC)) {// Fetch the data
$UsernameCount = $row['UsernameCount'];
}
if($UsernameCount > 0) {// Username Already Exists...
$_SESSION['Message'] = "Note: The User Name: <i><font color=red>" . $Username . "</font></i> is already in use.<br><br>Please try again. <br>";
mysqli_close($dbc);
header("Location: ../index.php");// Bail Out
} else {
$WhatAction = "Administrator";// Good to Go
}
break;

default:// case else: Security ID=0. NEW USER. Check to See if Username Exists anywhere in the database...
$UsernameQuery = "SELECT COUNT(*) AS UsernameCount FROM customers
WHERE UserName = '" . $username . "'" ;// Build the query
$rs = @mysqli_query ($dbc, $UsernameQuery); // Return the Result Set
WHILE ($row = mysqli_fetch_array($rs, MYSQLI_ASSOC)) {// Fetch the data
$UsernameCount = $row['UsernameCount'];
}

if($UsernameCount > 0) {// Username Already Exists...
$_SESSION['Message'] = "Sorry, the User Name: <i><font color=red>" . $Username . "</font></i> is already in use.<br><br>Please try again. <br>";
mysqli_close($dbc);
header("Location: ../index.php");// Bail Out
} else {
$WhatAction = "New User";// Good to Go
}
break;
}

switch ($WhatAction) {
case "New User":// INSERT New Db Record


$query = "INSERT INTO customers (CustomerID,FirstName,MiddleInitial,LastName,
Address,Address2,City,State,Zip,UserName,Password,Email,Phone,DateOfBirth)
VALUES (NULL, '" . $firstname . "', '" . $middleinitial . "', '" . $lastname . "', '" . $address .
"', '" . $address2 . "', '" . $city . "', '" . $state . "', '" . $zipcode . "', '" .
$username . "', '" . $password . "', '" . $email . "', '" . $phone . "','" . $birthdate . "', NULL , NULL)" ;

case "Existing User":// UPDATE Existing Db Record
$query = "UPDATE customers
SET FirstName = '" . $firstname . "',
MiddleInitial = '" . $middleinitial . "',
LastName = '" . $lastname . "',
Address = '" . $address . "',
Address2 = '" . $address2 . "',
City = '" . $city . "',
State = '" . $state . "',
Zip = '" . $zipcode . "',
UserName = '" . $username . "',
Password = '" . $password . "',
Email = '" . $email . "',
Phone = '" . $phone . "',
DateOfBirth = '" . $birthdate . "',
WHERE CustomerID = '" . $_SESSION['CustomerID'] ;
break;

case "Administrator":// UPDATE Existing Db Record as Edited by Administrator
$query = "UPDATE customers
SET FirstName = '" . $firstname . "',
MiddleInitial = '" . $middleinitial . "',
LastName = '" . $lastname . "',
Address = '" . $address . "',
Address2 = '" . $address2 . "',
City = '" . $city . "',
State = '" . $state . "',
Zip = '" . $zipcode . "',
UserName = '" . $username . "',
Password = '" . $password . "',
Email = '" . $email . "',
Phone = '" . $phone . "',
DateOfBirth = '" . $birthdate . "',
WHERE CustomerID = " . $CustomerID2Update ;
break;
}


mysqli_query($dbc,$query);

$newid = mysqli_insert_id($dbc);
if ($newid!=0 AND $WhatAction = "New User") {
$_SESSION['CustomerID'] = $newid;
$_SESSION['FirstName'] = $firstname;
$_SESSION['SecurityID'] = 1;
}

mysqli_close($dbc);

switch ($WhatAction) {
case "New User":
$_SESSION['Message'] = "New User Successfully Added. <br>";
header("Location: index.php");
print($query);
print("<br>");
break;

case "Existing User":
$_SESSION['Message'] = "Existing User Data Successfully Updated. <br>";
header("Location: index.php");
break;

case "Administrator":
header("Location: index.php?menukey=6");//Maintenance Page
break;
}

?>

[penders]

include "MySQLConnector.txt";

Assuming you are successfully connecting to your DB...

mysqli_query($dbc,$query); 
$newid = mysqli_insert_id($dbc);  
if ($newid!=0 AND $WhatAction = "New User") {  
  $_SESSION['CustomerID'] = $newid;  
  $_SESSION['FirstName'] = $firstname;  
  $_SESSION['SecurityID'] = 1;  
}

You don't appear to be checking for (or reporting) the fail state. So if the query did fail (which seems to be the case) you are going to get a success message anyway. I think

$WhatAction = "New User"

(assignment) should be

==

(comparison) - since if the query is successfull it will always be a "New User"? Which I think is another clue... the query is never successful, which is why you still get "Existing User Data Successfully Updated".

You could try this to see what error you are getting...

$newid = mysqli_insert_id($dbc); 
if ($newid == 0) { 
  die (mysqli_error($dbc)); 
}

[eflaherty81]

I inserted your suggested code and I thought I had figured it out but I get this error now.

"Parse error: parse error in D:\WebShare\Students\Term\5Summer\PHP\5335-40\FlahertyE\exam1\insertcustomer.php on line 109"

I have put the line numbers below. It doesn't seem to me like I should be getting the same error because I get it no matter whether I updating a record, creating a new user or, logged in as the administrator.


108 $query = "UPDATE customers
109 SET FirstName = '" . $firstname . "',
110 MiddleInitial = '" . $middleinitial . "',
111 LastName = '" . $lastname . "',
112 Address = '" . $address . "',
113 Address2 = '" . $address2 . "',
114 City = '" . $city . "',
115 State = '" . $state . "',
116 Zip = '" . $zipcode . "',
117 UserName = '" . $username . "',
118 Password = '" . $password . "',
119 Email = '" . $email . "',
120 Phone = '" . $phone . "',
121 DateOfBirth = '" . $birthdate . "',
122 WHERE CustomerID = " . $CustomerID2Update ;
123 break;
124 }

[penders]

Is that the full error? A parse error implies a fundamental error with the PHP source code. There does not appear to be an error in the code you have posted?

[eflaherty81]

That is the full error message. Any ideas as to what might cause this?

[penders]

Presumably you get this error as soon as "insertcustomer.php" is requested?

I don't think the contents of the variables $firstname, etc. could cause this?!

My only thought is that you have some strange characters in your source code at the EOL, other than CR / LF?! Can you 'show all characters' in your code editor? Or retype those lines of code?!

[penders]

include "MySQLConnector.txt";

Aside... what does this contain? If it contains DB connection info, like username and password then this is potentially a big security issue! Since .txt files won't be parsed by PHP if directly requested.