Why is my Second Query not working?

SalientAnimal · February 5, 2014

Hi All,

I have a second part to my registration process which determines the access level a user has to my site. The access level is being updated by the update query, however the insert query does not seem to be inserting.

When I run var dump all the variables are being passed by the query / s.

Below is my code.

<?php

include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();
if (login_check($mysqli) == true) 
	{
    $logged = 'in';
	} 

$error_msg = "";
$username = $_SESSION['username'];
$email = $_SESSION['email'];
$id = $_SESSION['user_id'];

// create string of queries separated by ;
	//var_dump(login_check($mysqli));
	//var_dump($_SESSION); exit; 
	//var_dump($_POST);exit; 


$query  =  	"UPDATE 
						members 
			SET
						level = '$_POST[level]'
			WHERE 
						id = $id
			LIMIT
						1;";
						
						
$query .= 	"INSERT INTO 
						members_info 
			(
						id
			, 			fname
			, 			known_as
			, 			lname
			,			gender
			, 			race
			,			start_date
			, 			department
			, 			level
			, 			msisdn
			, 			dob
			, 			details
			, 			emergency_contact
			, 			emergency_msisdn
			) 
			VALUES 
			(
						'".mysqli_real_escape_string($_POST['user_id'])."'
			, 			'".mysqli_real_escape_string($_POST['fname'])."'
			, 			'".mysqli_real_escape_string($_POST['known_as'])."'
			, 			'".mysqli_real_escape_string($_POST['lname'])."'
			, 			'".mysqli_real_escape_string($_POST['gender'])."'
			, 			'".mysqli_real_escape_string($_POST['race'])."'
			,			'".mysqli_real_escape_string($_POST['start_date'])."'
			, 			'".mysqli_real_escape_string($_POST['department'])."'
			, 			'".mysqli_real_escape_string($_POST['level'])."'
			, 			'".mysqli_real_escape_string($_POST['msisdn'])."'
			, 			'".mysqli_real_escape_string($_POST['dob'])."'
			, 			'".mysqli_real_escape_string($_POST['details]'])."'
			, 			'".mysqli_real_escape_string($_POST['emergency_contact'])."'
			, 			'".mysqli_real_escape_string($_POST['emergency_msisdn'])."'
			);";

// execute query - $result is false if the first query failed
$result = mysqli_multi_query($mysqli, $query);

if ($result) 
{
    do {
        // grab the result of the next query
        if (($result = mysqli_store_result($mysqli)) === false && mysqli_error($mysqli) != '') {
            echo "Query failed: " . mysqli_error($mysqli);
        }
		
		} while (mysqli_more_results($mysqli) && mysqli_next_result($mysqli)); // while there are more results

} else {
    echo "Update Query  Failed..." . mysqli_error($mysqli);
}


		$mysqli->close();

if($failed == false) {
    header('Location: ../index.php');
    exit;
}

mac_gyver · February 5, 2014

that's not how to call mysqli_real_escape_string. i'm pretty sure you will have had it suggested in one of your threads or have read a thread where it has been suggested that you have php's error_reporting set to E_ALL and display_errors set to ON to get php to help you.

SalientAnimal · February 5, 2014

The suggestion I had in a previous thread I posted was mysql_real_escape_string. But that is for MySQL and not mysqli, I assumed that it would just be mysqli instead of MySQL. Will try out the error reporting to see what I get.

mac_gyver · February 5, 2014

in programming, you cannot assume anything. the parameters of the mysqli function calls are different from mysql. a way to avoid the confusion between mysql and msyqli is to use the object notation for mysqli (which actually results in shorter syntax.)

i'm not sure why you had any mysql functions in your code. weren't you using mysqli all along in these series of threads?

SalientAnimal · February 5, 2014

Here is the output I get when enabling the error reporting in php.ini.

Fatal error: Class 'mysqli' not found in C:\htdocs\includes\db_connect.php on line 4

And this is db_connect.php and psl-config.php

db_connect.php
<?php
//includes/db_connect.php 
include_once 'psl-config.php';   // As functions.php is not included
$mysqli = new mysqli(HOST, USER, PASSWORD, DATABASE);

?>


psl-config.php
<?php
// includes/psl-config.php -->

/**
* These are the database login details
*/  
define("HOST", "localhost");     // The host you want to connect to.
define("USER", "username");    // The database username. 
define("PASSWORD", "mypassword");    // The database password. 
define("DATABASE", "mydatababse");    // The database name.
define("CAN_REGISTER", "any");
define("DEFAULT_ROLE", "member");

define("SECURE", FALSE);    // FOR DEVELOPMENT ONLY!!!!

// Root Administration    define("ADMINISTRATION", "system"); -->
// The test user password define("TESTUSER","test"); -->

?>

SalientAnimal · February 5, 2014

in programming, you cannot assume anything. the parameters of the mysqli function calls are different from mysql. a way to avoid the confusion between mysql and msyqli is to use the object notation for mysqli (which actually results in shorter syntax.)

i'm not sure why you had any mysql functions in your code. weren't you using mysqli all along in these series of threads?

Yes I am using mysqli, but I didn't have *_real_escape_string preceeding my $_POST at all. The suggestion given to me was to add it as I did in my code, the only difference was is was mysql_real_escape_string and not the mysqli_real_escape_string as I had put it.

This is the thread where the suggestion was made:

http://forums.phpfreaks.com/topic/285759-where-to-put-redirect-header/

mac_gyver · February 5, 2014

if you are getting a fatal error about the mysqli class, it means that the mysqli extension is not loaded in your php.ini configuration and each page attempting to use mysqli was failing.

SalientAnimal · February 5, 2014

I've come across this regarding mysqli.

If you connection string is:
mysql_connect()

then use:
mysql_real_escape_string($_POST[''])

If it is:
$mysqli = new mysqli();

then use:
$mysqli->real_escape_string($_POST[''])

So with that in mind, does it suggest that my script should actually be:

<?php

include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();
if (login_check($mysqli) == true) 
	{
    $logged = 'in';
	} 

$error_msg = "";
$username = $_SESSION['username'];
$email = $_SESSION['email'];
$id = $_SESSION['user_id'];

// create string of queries separated by ;
	//var_dump(login_check($mysqli));
	//var_dump($_SESSION); exit; 
	//var_dump($_POST);exit; 


$query  =  	"UPDATE 
						members 
			SET
						level = '$_POST[level]'
			WHERE 
						id = $id
			LIMIT
						1;";
						
						
$query .= 	"INSERT INTO 
						members_info 
			(
						id
			, 			fname
			, 			known_as
			, 			lname
			,			gender
			, 			race
			,			start_date
			, 			department
			, 			level
			, 			msisdn
			, 			dob
			, 			details
			, 			emergency_contact
			, 			emergency_msisdn
			) 
			VALUES 
			(

						$mysqli->real_escape_string($_POST['user_id'])
			, 			$mysqli->real_escape_string($_POST['fname'])
			, 			$mysqli->real_escape_string($_POST['known_as'])
			, 			$mysqli->real_escape_string($_POST['lname'])
			, 			$mysqli->real_escape_string($_POST['gender'])
			, 			$mysqli->real_escape_string($_POST['race'])
			,			$mysqli->real_escape_string($_POST['start_date'])
			, 			$mysqli->real_escape_string($_POST['department'])
			, 			$mysqli->real_escape_string($_POST['level'])
			, 			$mysqli->real_escape_string($_POST['msisdn'])
			, 			$mysqli->real_escape_string($_POST['dob'])
			, 			$mysqli->real_escape_string($_POST['details]'])
			, 			$mysqli->real_escape_string($_POST['emergency_contact'])
			, 			$mysqli->real_escape_string($_POST['emergency_msisdn'])
			);";

// execute query - $result is false if the first query failed
$result = mysqli_multi_query($mysqli, $query);

if ($result) 
{
    do {
        // grab the result of the next query
        if (($result = mysqli_store_result($mysqli)) === false && mysqli_error($mysqli) != '') {
            echo "Query failed: " . mysqli_error($mysqli);
        }
		
		} while (mysqli_more_results($mysqli) && mysqli_next_result($mysqli)); // while there are more results

} else {
    echo "Update Query  Failed..." . mysqli_error($mysqli);
}


		$mysqli->close();

if($failed == false) {
    header('Location: ../index.php');
    exit;
}

SalientAnimal · February 5, 2014

Ok, now I'm rather confused. in my PHP.ini file the MySQLi setting is as follows:

mysqli.allow_local_infile=On

and is uncommented. Now I really don't know what I am missing.

mac_gyver · February 5, 2014

mysql and mysqli are two different library's of database functions. you need to pick one and use it throughout your code. the mysql extension is depreciated starting in php5.5 and should not be used when writing new code as you will need to rewrite it in the future when the mysql extension is completely eliminated.

the php.ini line you posted has nothing to do with enabling the mysqli extension. it is a setting that enables a load local infile query.

assuming you are using windows, the line to enable the msyqli extension would be - extension=php_mysqli.dll

SalientAnimal · February 5, 2014

Do I need to install anything additional to be using this? Or is it just a matter of commenting it in the php.ini, as it is already commented in.

What doesn't make sense though, is that the db_connect files uses mysqli to establish the connection to my database and this all seems to work as my other forms on my site work. Its just this particular page that for some reason only updates, but doesn't insert. I have another page that updates and inserts as well and it is working, I have even looked at duplicating that code and just changing the required fields.

I installed my Apache, MySQL, and PHP using XAMP. And yes I'm running on Windows Server 2013. Not my first choice, but it is what was given to me by the company to work with.

mac_gyver · February 5, 2014

i'm wondering what environment you got the fatal error in post #5 in?

syntax errors in the php.ini will prevent the remainder of the php.ini from being parsed. i suspect that when you changed the error_reporting/display_errors settings you introduced a syntax error in the php.ini and none of your enabled extensions are being loaded.

edit: in this case, the web server's error log should (untested) contain a php error message about a problem with the php.ini.

Edited February 5, 2014 by mac_gyver

SalientAnimal · February 5, 2014

Ok, let me go look if I see something.

When changing the php.ini file to enable editing earlier, I only uncommented the two error reporting fields, and nothing else.

The error occurred when I tried to submit a file, but this was because I was already on a preloaded page. I then closed my browser completely, and was then not even able to access my index.php page. At this point it returned the same error.

mac_gyver · February 5, 2014

there should have been no need to uncomment anything, just change values in settings. it's likely you uncommented part of the description of what the setting does, not the actual setting.

you should end up with two lines somewhere that look like -

error_reporting = E_ALL

display_errors = On

SalientAnimal · February 5, 2014

Yip, those two line were as follows:

; error_reporting = E_ALL

; display_errors = On

And I then changed them to:

error_reporting = E_ALL

display_errors = On

When checking the error log, I wasn't able to actually find the correct error in the logs, as the time set in my log seems to be in the incorrect zone.

I will replicate the error and post any messages that I get in the error log.

mac_gyver · February 5, 2014

another possibility is when you edited the php.ini file, it got saved as php.ini.txt and is no longer being used by php (all the default settings would be in effect.)

what does the phpinfo(); output show for the Loaded Configuration File value. it should be the php.ini that you edited.

SalientAnimal · February 5, 2014

C:\auxilium\php\php.ini

That's the loaded config file from phpinfo();

SalientAnimal · February 6, 2014

I have looked at my error logs, and no errors that should be preventing the form from inserting into the table are visible in the logs. I intentionally forced an error to ensure that the log is updating, and this is still working.

mac_gyver · February 6, 2014

is your php installation with the mysqli extension working now or are you still getting a fatal error at the mysqli functions?

if mysqli is working, what is your current code?

SalientAnimal · February 7, 2014

The mysqli works on all my other forms. And I have multiple queries running on at least two other forms. It is only the register.addinfo.php file that is only updating, but not inserting into the table.

This is the code from that particular page:

FORM

<!doctype html>
<meta http-equiv="X-UA-Compatible" content="IE=Edge">

<!-- INCLUDING REQUIRED AUTHENTICATION FILES, DATABASE CONNECTIONS, FUNCTIONS. -->



<?php
include_once 'includes/db_connect.php';
include_once 'includes/functions.php';
include_once 'includes/formatting.php';

sec_session_start();
if (login_check($mysqli) == true) 
	{
    $logged = 'in';
	} 
else 
	{
    $logged = 'out';
	header('location:index.php');
	echo 'You are required to login';
	exit;
	}
	
// CHECKS IF THERE ARE ANY ERRORS WITH CONNECTING TO THE DATABASE	
if (mysqli_connect_errno()) 
	{
		printf("Connect failed: %s\n", mysqli_connect_error());
		exit();
	}
$error_msg = "";
$username = $_SESSION['username'];
$email = $_SESSION['email'];
$level = $_SESSION['level'];
$id = $_SESSION['user_id'];
?>

<!--
	Copyright 2014 TechDesignLab
	CRM TRACKING UTILITY
-->







<!-- HEADER CONTENT OF PAGE - THIS CONTAINS ALL SCRIPT, CSS, AND META TAG RELATED INFORMATION FOR THE WEBPAGE -->

<head>
  	<title>Altech Autopage - Registration</title>
</head>




<!-- BODY CONTENT OF THE WEBPAGE - THIS IS HOW THE PAGE WILL BE DISPLAYED TO THE USER -->

<body>

<!-- LOGIN / DROP DOWN PANEL START -->

<!-- LEFT SECTION OF THE LOGIN PANEL IS DEFINED HERE. THE SECTION BELOW IS WHAT WILL BE SEEN BY USERS WHO ARE LOGGED IN-->



<!-- INCLUDING THE NAVIGATION MENU -->


<?php
// INCLUDING THE TOP LOGIN / LOGOUT PANEL
include 'includes/panel.php';

// INCLUDING THE NAVIGATION MENU
include '/nav/menu.html';
 
?>





    <div id="container">
		<div id="content" style="margin-top:-45px;">
		<img src="images/logo.png" alt="Altech Autopage"></img>
			<h1>Auxilium</h1>
			<!-- <h2>Sliding login panel with jQuery - Demo</h2>	 -->
		
		<div id="stylized" class="form">
			<form id="form" name="form" method="post" action="process/register.addinfo.php">
			<h1 style="color:red; width:600px; margin-left:90px;">Complete Registration</h1>
			<p style="color:red; width:600px; ;">You are required to complete the remainder of the registration to continue using Auxilium.</p>

			<h1 style="width:600px; margin-left:90px;">Access Details</h1>			
			
			<!-- DISPLAY THE DETAILS OF THE LOGGED IN USER -->
			<label>User Logged In :
			<span class="small">You are logged in as</span>
			</label>
			<input type="text" name="username" id="username" value="<?php echo htmlentities($_SESSION['username']);?>" readonly style="background-color: #C9C9C9">
			
			
			<label>E-Mail :
			<span class="small">Your Altech Autopage e-mail cannot be edited here</span>
			</label>
			<input type="text" name="email" id="email" value="<?php echo htmlentities($_SESSION['email']);?>" readonly style="background-color: #C9C9C9"/>

			<label>ID :
			<span class="small">ID used to identify your user in the database</span>
			</label>
			<input type="text" name="user_id" id="user_id" value="<?php echo htmlentities($_SESSION['user_id']);?>" readonly style="background-color: #C9C9C9"/>			
			

			<!-- FIELD FOR CAPUTRING THE CUSTOMER'S FIRST NAME -->
			<label>First Name :
			<span class="small">Your legal name</span>
			</label>
			<input type="text" name="fname" id="fname"/>
			
			<!-- FIELD FOR CAPUTRING THE CUSTOMER'S FIRST NAME -->
			<label>Known As :
			<span class="small">What do you want us to call you</span>
			</label>
			<input type="text" name="known_as" id="known_as"/>			

			
			<!-- FIELD FOR CAPUTRING THE CUSTOMER'S SURNAME -->
			<label>Surname :
			<span class="small">Your family name</span>
			</label>
			<input type="text" name="lname" id="lname"/>

			<label>Gender :
			<span class="small">Your gender</span>
			</label>
			<select id="qender" name="gender">
				<option value=""> -- Select your gender --</option>
				<option value="Female">Female</option>
				<option value="Male">Male</option>
			</select>
			
			
			<label>Race :
			<span class="small">Your racial group</span>
			</label>
			<select id="race" name="race">
				<option value=""> -- Select your race --</option>
				<option value="African">African</option>
				<option value="Asian">Asian</option>					
				<option value="Caucasian">Caucasian</option>
				<option value="Coloured">Coloured</option>					
			</select>			

						<!-- FIELD FOR CAPUTRING THE CUSTOMER'S MSISDN / CONTACT NUMBER -->
			<label>Start Date : 
			<span class="small">Your official starting date at Altech Autopage</span>
			</label>
			<input id="start_date" name='start_date' type="text">	
			
			
			<label>Department :
			<span class="small">Area you work in</span>
			</label>
			<select id="department" name="department">
				<option value=""> -- Select your department --</option>
				<option value="3rd Party">3rd Party</option>
				<option value="Cashier">Cashier</option>
				<option value="Client Liaison Consultant">Client Liaison Consultant</option>
				<option value="Credit Control Consultant">Credit Control Consultant</option>
				<option value="Insurance Consultant">Insurance Consultant</option>
				<option value="Manager">Manager</option>
				<option value="Meeting/Interview">Meeting/Interview</option>
				<option value="Premier Client Consultant">Premier Client Consultant</option>
				<option value="Retail Shop Consultant">Retail Shop Consultant</option>
				<option value="Retention Collection">Retention Collection</option>
				<option value="Tech Deck Consultant">Tech Deck Consultant</option>
			</select>
			
			
			<label>Title :
			<span class="small">What best describes your job role</span>
			</label>
			<select id="level" name="level">
				<option value=""> -- Select your reporting level --</option>
				<option value="5">1st Level Manager</option>
				<option value="4">2nd Level Manager</option>
				<option value="4">Workforce Planner</option>
				<option value="4">Report Administrator</option>	
				<option value="4">Team Manager</option>							
				<option value="3">Call Centre Agent</option>												
				<option value="3">Retail Agent</option>	
				<option value="3">Reception Hostess</option>		

			</select>			


			<!-- FIELD FOR CAPUTRING THE CUSTOMER'S MSISDN / CONTACT NUMBER -->
			<label>Contact Number :
			<span class="small">Your contact number</span>
			</label>
			<input type="text" name="msisdn" id="msisdn"/>			
			

			<!-- FIELD FOR CAPUTRING THE CUSTOMER'S MSISDN / CONTACT NUMBER -->
			<label>Date of Birth : 
			<span class="small">Your birth date</span>
			</label>
			<input id="dob" name='dob' type="text">						
	

			<label>About Me :
			<span class="small">Tell us a few fun facts about yourself. You may use up to a maximum of 5,000 characters.</span>
			</label>			

			<textarea name="details" rows="3"></textarea>
	
	
			<!-- FIELD FOR CAPUTRING THE CUSTOMER'S MSISDN / CONTACT NUMBER -->
			<label>Emergency Contact :
			<span class="small">Who should we contact in an emergency?</span>
			</label>
			<input type="text" name="emergency_contact" id="emergency_contact" />		
			
			
			<!-- FIELD FOR CAPUTRING THE CUSTOMER'S MSISDN / CONTACT NUMBER -->
			<label>Emergency Contact Number :
			<span class="small">What is your emergency contact's number?</span>
			</label>
			<input type="text" name="emergency_msisdn" id="emergency_msisdn" />				
			
			
			
			<input type="button" value="Submit" class="bt_login" onClick="form.submit()"/> 
			</form></div>
			
		<br><br><br><br><br><br><br><br><br><br><br><br><br>
		<br><br><br><br><br><br><br><br><br><br><br><br><br>
		<br><br><br><br><br><br><br><br><br><br><br><br>
		</div><!-- / content -->		
	</div><!-- / container -->
</body>



<div id="container">
<div id="footer" style="margin-top:10px;">

<footer style="background:#E5E5E5; height:20px">
  <p>Copyright © Altech Autopage 2014 | <a href="mailto:lclaassen@autopage.altech.co.za">Contact Us</a>.</p>
  <img src="images/altron-footer-logo.png" alt="Altron Footer Logo" align="left"></img>
  <img src="images/altech_bbbee.png" alt="Altech Level 2 BBEE" align="left"></img>
</footer>
</div>	
</div>
</html>

Submit File:

<?php




include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();
if (login_check($mysqli) == true) 
	{
    $logged = 'in';
	} 

$error_msg = "";
$username = $_SESSION['username'];
$email = $_SESSION['email'];
$id = $_SESSION['user_id'];

// create string of queries separated by ;
	//var_dump(login_check($mysqli));
	//var_dump($_SESSION); exit; 
	//var_dump($_POST);exit; 


$query  =  	"UPDATE 
						members 
			SET
						level = '$_POST[level]'
			WHERE 
						id = $id
			LIMIT
						1;";
						
						
$query .= 	"INSERT INTO 
						members_info 
			(
						id
			, 			fname
			, 			known_as
			, 			lname
			,			gender
			, 			race
			,			start_date
			, 			department
			, 			level
			, 			msisdn
			, 			dob
			, 			details
			, 			emergency_contact
			, 			emergency_msisdn
			) 
			VALUES 
			(
						'($_POST['user_id'])'
			, 			'($_POST['fname'])'
			, 			'($_POST['known_as'])'
			, 			'($_POST['lname'])'
			, 			'($_POST['gender'])'
			, 			'($_POST['race'])'
			,			'($_POST['start_date'])'
			, 			'($_POST['department'])'
			, 			'($_POST['level'])'
			, 			'($_POST['msisdn'])'
			, 			'($_POST['dob'])'
			, 			'($_POST['details]'])'
			, 			'($_POST['emergency_contact'])'
			, 			'($_POST['emergency_msisdn'])'
			);";

// execute query - $result is false if the first query failed
$result = mysqli_multi_query($mysqli, $query);

if ($result) 
{
    do {
        // grab the result of the next query
        if (($result = mysqli_store_result($mysqli)) === false && mysqli_error($mysqli) != '') {
            echo "Query failed: " . mysqli_error($mysqli);
        }
		
		} while (mysqli_more_results($mysqli) && mysqli_next_result($mysqli)); // while there are more results

} else {
    echo "Update Query  Failed..." . mysqli_error($mysqli);
}


		$mysqli->close();

if($failed == false) {
    header('Location: ../index.php');
    exit;
}

for now I removed the .mysqli_real_escape_string from the POST as I have not yet figured out exactly how to use this, but I am busy researching it.

However I still get the fatal error message when I turn error reporting on.

Edited February 7, 2014 by SalientAnimal

SalientAnimal · February 7, 2014

Ok, I am ashamed to have to supply a solution to this, and have to say sorry for wasting time. I had originally had the column named level in both my members table and my members_info table. However, when doing some of the structural changes I removed the level column from my members_info table, but never removed it from my insert query.

I slowly went throught he query and added the columns one by one, and realised this (I fee like an idiot), and now it works. The only thing I don't get is why didn't I get an error message from the server saying that the column doesn't exist or something of that sort?

Thanks for your help on this though mac_gyver

Barand · February 7, 2014

Out of curiosity,if you run the multiquery as two separate queries do you get the expected "unknown column" error message ffrom mysqli_error?

SalientAnimal · February 7, 2014

Nope, that's why I am totally confused.And couldn't figure out what the error could be

Sign In

Why is my Second Query not working?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information