Executing a Query 1

[PCHomepage]

I created what I thought was a basic and simple function to help in quick database functionality. The connection is working as I can select single or multiple records but I've apparently missed something in executing a function for updating or inserting. There are no errors but $mysqli->query($Query); seems to do nothing. What have I forgotten? Here is the whole function with the area in question in red:

function DBConnect($Query, $ActionType) {
	if (!$Query) {
		exit();
	}
	
	include "config.php";
	$mysqli = new mysqli($dbhost, $dbusername, $dbpass, $dbname);

	if (mysqli_connect_errno()) {
		printf("Connect failed: %s\n", mysqli_connect_error());
		exit();
	}
	
	if ($ActionType == "Select") {
		$result =  $mysqli->query($Query);
		$row = $result->fetch_assoc();
		return $row;
	[COLOR=red]} elseif ($ActionType == "Update" || $ActionType == "Insert" || $ActionType == "Delete") {
		$mysqli->query($Query);[/color]
	} elseif ($ActionType == "Multiple") {
		if ($result = $mysqli->query($Query)) {
			$numrowsCat = $result->num_rows;;
				if ($numrowsCat > 1) {
					$result = $mysqli->query($Query);
					while($row = $result->fetch_array()) {
						$results_array[] = $row;
					}
				   return $results_array;
				}
		}
	} elseif ($ActionType == "Count") {
		if ($result = $mysqli->query($Query)) {
			return $result->num_rows;
		}
	}

	$_SESSION['ActionSubmitted'] = $ActionType;
	
	if ($ActionType && isset($result)) {
		$_SESSION['ActionType'] = "$ActionType Successful";
		$result->close();
	}
	
	$mysqli->close();
}

If I echo the query to the screen, which confirms that it is passing into the proper portion of the conditional, and run it manually in HeidiSQL it works but not in the function. Any help is appreciated.

 

[jpadie]

check the capitalisation of $Actiontype

change this

if (mysqli_connect_errno()) {
		printf("Connect failed: %s\n", mysqli_connect_error());
		exit();
	}

as shown for consistency

if ($mysqli->connect_errno() != 0 ):
		printf("Connect failed: %s\n", $mysqli->connect_error());
		exit();
endif;

 

[PCHomepage]

Thank you. I missed that one when I converted it to object oriented but it now crashes with an error:

Fatal error: Call to undefined method mysqli::connect_errno()

 

[jpadie]

lose the brackets. it's a property not a method. my mistake

 

[PCHomepage]

Thank you, it fixed the crash but now do you have any idea what's happening per the original question?

 

[jpadie]

as per above, my advice was "check the capitalisation of $Actiontype".

that's the only obvious point of failure that you have not explicitly covered.

for example add this at the bottom of your if...elseif

else {
  echo "invalid actiontype: $Actiontype";
}

you might consider using the switch construct instead of piles of if ... elseif. No particular performance enhancement but it's a lot easier to read.

 

[PCHomepage]

I can't spot any inconsistencies with $ActionType (uppercase A and T are the way I wrote it throughout as far as I can tell) but even if there were, the code is finding its way into the $ActionType=Update portion of the conditional yet it's not updating and this is the area where I need some help. I can see nothing wrong to make it not update so clearly I've missed something that other eyes might be able to spot.

 

[jpadie]

nope.
my thought was that the incoming variable was wrong - not the typing! hence the added else at the end.

but if you know it is in the right conditional then have you tried adding some intelligence to the results?

$result = $mysqli->query($Query);
if ($result === false):
 echo $mysqli->error . "<br/>";
else:
 $mysqli->close();
 return true;
endif;

i see also that your code does not take responsibility for escaping and enquoting. so whatever code you use to create the query must do that. This is important as a query might not fail but could still be (dangerously) wrong if not escaped. Likewise pasting a query into heidisql might well work as I believe that app disassembles the query and sorts out the escaping etc before submitting it to the engine.

 

[PCHomepage]

Yes, I've gone though all that and even echoed the query itself to the screen to be sure it was being build properly. It is as though there is nothing there to actually execute the query even though the ones used for selecting data are working. It is in saving it that I am having the problem.

All data is properly quoted and no escaping is needed. This is a simple, very basic Intranet application with only a single user so no problems with odd data being entered and most data is from select boxes and check boxes anyway.

 

[jpadie]

if
1. you know it gets to the right place in the conditional
2. no errors are being thrown.
3. it exits correctly

then the query is being executed correctly.

to test this

$result = $mysqli->query($Query);
if ($result === false):
 echo $mysqli->error . "<br/>";
else:
 echo "Query executed correctly.  {$mysqli->affected_rows} rows were affected<br/>";
 $mysqli->close();
 return true;
endif;

 

[PCHomepage]

I couldn't quite do it the way you suggested as the function is in an external script so I rather than echo, I used return, then disabled the form's redirection, then echoed the value from the function on the form. In doing so, I finally got the clue as to what is wrong as it gave this message:

UPDATE command denied to user

It never occurred to me that it might simply be a user error as I'm so used to always using existing users and rarely create a new one as I did here! I tested everything else but not that and that was it so after providing proper credentials, it now gives.

Query executed correctly. 1 rows were affected

Thank you!