Jump to content

Why is my Second Query not working?


SalientAnimal
Go to solution Solved by SalientAnimal,

Recommended Posts

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;
}



Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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"); -->

?>
Link to comment
Share on other sites

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/ 

Link to comment
Share on other sites

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;
}


Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by mac_gyver
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by SalientAnimal
Link to comment
Share on other sites

  • Solution

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.