Solved

MySql query not returning results

Posted on 2014-03-04
48
507 Views
Last Modified: 2014-03-09
I am trying to code a query whereby the query checks for duplicate items in a table and if found sends an error back to jquery. However, what is happening, is that jquery keeps giving me a typeError and I cannot see why. In my posted code, the code that is commented out, /* if ($box == 'DEMO111') works fine but not my query.

All mysql connections are working and I can connect to the db.

I would be grateful if someone could check my code and show me where I have gone wrong. Thanks

<?php 

$array = split('[,]', $_POST['box_add']);
$boxerrortext = 'No duplicate boxes';
?>

<?php

if (isset($_POST['submit'])) 	{
			$error = array();
			foreach ($array as $box) {
			$sql = "SELECT * FROM act WHERE item = '" . $box . "'";  
			$result = runSQL($sql) or die(mysql_error());
			$num_rows = mysql_num_rows($result);
			
			if ($num_rows) {
			//trigger_error('It exists.', E_USER_WARNING);
			
			$error[] = array('boxerror'=>$boxerrortext,
							'box'=>$box);
			$result = json_encode($error);

			echo $result;
			return;
			}
			}

			/* if ($box == 'DEMO111')
			
			{
			//echo 'There was an error somewhere';
			//$box = 'ERROR';

			//$error = array('boxerror'=>$boxerrortext, 'box'=>$box);
			//$output = json_encode($error);

			//echo $output;
			//return;
			} */
			
                       else {
	               
                       $form = array();

                       foreach ($array as $box) {
     
                       $form[] = array('dept'=>$dept, 
                      'company'=>$company,
                      'address'=>$address,
                      'service'=>$service,
                      'box'=>$box,
                      'destroydate'=>$destroydate,
                      'authorised'=>$authorised,
                      'submit'=>$submit);
     
$sql = "INSERT INTO `temp` (service, activity, department, company, address, user, destroydate, date, item, new) VALUES ('$service', '$activity', '$dept', '$company', '$address', '$requested', '$destdate', NOW(), '$box', 1)";
$result = runSQL($sql) or die(mysql_error());
      }
	 }
   } 
          
        $result=json_encode($form);
     
        echo $result;

?>

Open in new window

0
Comment
Question by:peter-cooper
  • 19
  • 14
  • 4
  • +3
48 Comments
 
LVL 58

Expert Comment

by:Gary
Comment Utility
What is runSQL?
What is your jQuery code?
Confused by this
if (isset($_POST['submit']))

...are you using ajax? If so there is no $_POST['submit']
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Please show us the HTML form that submits the request to this script.  The best way to do that would be to post a link to the page so we can see your SSCCE.
0
 

Author Comment

by:peter-cooper
Comment Utility
Here is the db config:

<?php

function runSQL($rsql) {
	$hostname = "localhost";
	$username = "root";
	$password = "";
	$dbname   = "temp";
	$connect = mysql_connect($hostname,$username,$password) or die ("Error: could not connect to database");
	$db = mysql_select_db($dbname);
	$result = mysql_query($rsql) or die (mysql_error()); 
	return $result;
	mysql_close($connect);

?>

Open in new window


Open in new window


jQuery

submitHandler: function()   {
                if ($("#USRboxint").valid() === true)  { 
                var data = $("#USRboxint").serialize() + '&submit=true';

                $.ajax({
			type: "POST",
			url: "add.php",
			data: data,
			dataType: "json",
        
		success: function(data) {
         
			if(data.boxerror == 'No duplicate boxes'){
				//$("#USRaddbox").html("<div class='errorMessage'>Sorry duplicates. Try again.</div>");
					
				var $dialog = $('<div id="dialog"></div>').html('<br />Your New Intake of: ' + data.box + ' was NOT SUBMITTED.<br />You must enter a number that is unique<br />Thank you.');
		$dialog.dialog({
		autoOpen: true,
		modal: true,
		title: 'New Entry Unsuccessfull',
		 width: 400,
		height: 200,
		draggable: false,
		resizable: false,
		 buttons: {
			Close: function() {
			$( this ).dialog( "close" );
			}
			}
		 });
			} else {
				//$("#USRaddbox").html("<div class='successMessage'>is a valid e-mail address. Thank you.</div>");
			$("#USRboxint").get(0).reset();
			var $dialog = $('<div id="dialog"></div>').html('<br /><b>Your New Intake of: ' + data.box + ' was successfully submitted.<br />Thank you.</b>');
		$dialog.dialog({
		autoOpen: true,
		modal: true,
		title: 'New Entry successfull',
		width: 400,
		height: 200,
		draggable: false,
		 resizable: false,
		buttons: {
			Close: function() {
			$( this ).dialog( "close" );
			 }
		       }
		   });
		  }
			  
		}
			   
	     });
	   }
	}

Open in new window

0
 

Author Comment

by:peter-cooper
Comment Utility
Ray, all other areas are working apart from the php backend. As I stated in my question, if I substitute my original code back, it works but I have to hardcode a vlaue. I need the value to be dynamic, hence this new code. Thanks
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
What is box_add
$array = split('[,]', $_POST['box_add']);

an unusual set of characters to be splitting on.
0
 

Author Comment

by:peter-cooper
Comment Utility
It is the name of the input field and a user can enter multiple entries seperated by comma. Thanks
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
So it should be

$array = split(',', $_POST['box_add']);
0
 

Author Comment

by:peter-cooper
Comment Utility
No, the way I have it posted is correct. It handles more than 1 entry.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
If you are just using a comma, then you need to use 'explode' instead of 'split' because 'split' is going away.

$array = explode(',', $_POST['box_add']);

http://us1.php.net/manual/en/function.split.php

http://us1.php.net/manual/en/function.explode.php
0
 

Author Comment

by:peter-cooper
Comment Utility
Will that fix my error? Thanks
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
Probably not but it will prevent a future error from popping up.
0
 

Author Comment

by:peter-cooper
Comment Utility
I have corrected that Dave. But I need to find a way to correct my original question. Any help would be great. Thanks
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
In your original code above, you are trying to use 'mysql_error' after the connection has been closed and I think you have to have an open connection to get that error message.  Same for 'mysql_num_rows'.

http://us1.php.net/manual/en/function.mysql-error.php

If you are going to put the mysql operations in a function, you need to run All of them thru that function.  I don't do it that way and most don't.  There is usually an 'include' at the top of the page (that is Not a function) that opens the connection and then you run the queries as needed.  The connection is automatically closed by PHP at the end of the script.  

Note that the only value available to the function is the one you have passed to it in the function call and the only value available from the function is what you 'return' from it.  In your code, '$result' will be available because you 'return' it.  Nothing else associated with the connection will be available outside the function because they are 'local' to the function and not 'global' to the script.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
Also, I don't see where you are getting the variable values in this statement.
                       $form[] = array('dept'=>$dept, 
                      'company'=>$company,
                      'address'=>$address,
                      'service'=>$service,
                      'box'=>$box,
                      'destroydate'=>$destroydate,
                      'authorised'=>$authorised,
                      'submit'=>$submit);

Open in new window

0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
A number of problems

1. mysql library is deprecated - you might want to move off that
2.  In the following code
$error = array(); 
 $error[] = array( ...) 

Open in new window

is going to result in
$error[0] =>
      boxerror => 'Somevalue',
      box => 'Some other value';

Open in new window

So when you refer to
success: function(data) {
     data.boxerror

Open in new window

It is going to be a problem because you need to actually be doing
success: function(data) {
     data[0].boxerror

Open in new window

0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Or alternatively in your PHP change
if ($num_rows) {
  $error[] = array(
        'boxerror'=>$boxerrortext,
        'box'=>$box
      );
  $result = json_encode($error);

  echo $result;
  return;
}

Open in new window

To
if ($num_rows) {
  // SET $error to the array not the first element ($error[])
  $error = array(
        'boxerror'=>$boxerrortext,
        'box'=>$box
      );
  $result = json_encode($error);

  echo $result;
  return;
}

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Some good "getting started" resources are identified in this article.  I think the best advice we can give you is to step back from this level of complexity and deconstruct the problem into smaller problems that can be solved independently of each other.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html
0
 

Author Comment

by:peter-cooper
Comment Utility
@Dave
All variables are available in the form of and are receiving correct values from ajax. Thanks

$company = mysql_real_escape_string($_POST['company']);

Open in new window

0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
@peter-cooper - did you see my post re the array reference?
0
 

Author Comment

by:peter-cooper
Comment Utility
@julianH Sorry, got called out. Yes, I changed to your comment but it still errors with TypeError data is null. Thanks
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Is there a link we can look at so we can see the error?
0
 

Author Comment

by:peter-cooper
Comment Utility
Sorry, I am working localhost.  Without using UNIQUE  at mysql level, what is another good way to check for dupes such as I am trying to do here. Thanks
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
What problem are we trying to solve? A type error return on your AJAX call or how to detect duplicates in a database?
0
 

Author Comment

by:peter-cooper
Comment Utility
One problem led to another.  It is the php that is causing the TypeError that I can see. So if I can solve that, then I think the other will go away. If not, so long as I know that my php is working 100%, I know to look at jquery for problem. Thanks
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
So why don't you call your PHP script directly with URL parameters as you would with the AJAX call - get that working and then go back to the AJAX?
0
 

Author Comment

by:peter-cooper
Comment Utility
Sorry Julian, I do not understand. I am fairly new to ajax, so how would I do that with my code. Thanks
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
This is not ajax - your php script does some work and produces some output. This it does irrespective of how you call it.

So if you suspect that the problem is in the php script then call the php script directly i.e. type in the URL to the script in your browser
http://myserver/myphpscript.php?parameter1=value1&parameter2=value2

Open in new window

And see what the output is.

Once the output is what you expect from the script - i.e. all errors sorted then go back to the AJAX script and call it from there.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
This strategy will work if your script expects a GET-method request.
http://myserver/myphpscript.php?parameter1=value1&parameter2=value2

Open in new window

But right at the top we have this, implying that you're using a POST-method request.  
$array = split('[,]', $_POST['box_add']);

Open in new window

You may be able to temporarily get around this by changing the back-end PHP script.  You could use $_REQUEST or you could just write an assignment statement like this: $_POST = $_GET.  Be aware that these strategies are hacks and must not be left in the deployed system; they create an unacceptable security exposure.

I really think you should deconstruct this problem into smaller bite-sized tasks that can be completed and validated independently of one another.  I can help with one part, since I have a generalized example showing the "Hello World" exercise in jQuery.  Once you have that part working, then you might try adding additional data to the AJAX communication, adding the JSON encoding, etc.  But take it one step at a time.  When you try to get everything working at once, it's very unlikely that you can avoid an extensive and complicated debugging cycle.
0
 
LVL 33

Expert Comment

by:Slick812
Comment Utility
Hello,  this is NO WAY to start out an AJAX resonse -
if (isset($_POST['submit']))       {

I would suggest you make a page that does NOT use ajax, place the same form on it and with a normal select button in form to post to a test PHP page, leave out all the json_encode( );, and do standard html <table> to display your MySql Row data, after you work out the bugs and get a correct error and correct $form (whatever that is) return and display from yout mysql tables, then use the array entries and json_encode( ); to finish your database for ajax, your php code is messed up and disorganized, you need to start anew, and simplify and understand your Mysql code
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
@Ray - for the purposes of testing it is a simple process to change $_POST to $_REQUEST

There is no need to create a separate form.

Just change your $_POST to $_REQUEST and test it with a URL - it is simple, quick and it will show you any errors immediatley.

First get your php script working - test that it is returning the data you want to return and then wire it into the AJAX script.
0
 

Author Comment

by:peter-cooper
Comment Utility
Thanks for all the comments experts. Julian, I tried your suggestion and in my php backed changed $_POST to $_REQUEST but all I get is white page. Should I be seeing some data? I took the values from firebug after submitting form. Thanks

http://localhost/domain/users/boxrtvDB.php?requested=Some+User&activity=Box+Entry&service=Standard&rtv_dept=DEMO&address2=7-9+Some+Street+++London+W1D+000&box_rtv=demo111&status=9&company=DEMO&submit=Submit&submit=true

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
@Slick812: Exactly right!

Simply changing $_POST to $_REQUEST is not going to change the failing program logic, it's just going to point it at a different superglobal array.  The correct approach is to step back from this and deconstruct the problem.  How do you eat an elephant?  One bite at a time.  I would throw the existing code, and all of the confusion it has caused, straight into the rubbish bin and start over with a clean slate and a fresh understanding of the protocols involved.

Here are the parts that need to be tested one at a time.

1. Something (anything) using the background script.  Trigger it from the browser address bar and see the response in the browser window.  Example:
<?php echo "Hello World";

Open in new window

2. A request variable, sent from the browser address bar and returned in the browser window.  This is where $_GET or $_REQUEST might come into play.  For more details on which to choose, you need to understand that GET requests must be idempotent, whereas POST requests may change the data model.  The $_REQUEST array may contain data from both kinds of requests, subject to the variables order directive.

3. An actual working script that does something, such as take a request variable from the browser address bar and use it to query a data base.  The output goes to the browser window.

4. A jQuery request that mimics Step 1.

5. A jQuery request that mimics Step 2.

6. A jQuery request that mimics Step 3.

Etc.  Now gradually add the request data and the response, one bit at a time, using data visualization every step along the way.

None of these steps is likely to take more than a few minutes of testing, and each step builds on a foundation that is created by the antecedent steps.  This is how professional programmers work - stepwise with incremental testing.  It's a more predictable process than just making random changes in the superglobals and wondering why there is a blank screen.  Make sure that every incremental change puts something on the screen to prove the step worked.

It's not rocket science, just plodding along, taking the problem in small enough bites that each bite is digestible.  That's all.
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
@Peter - If I look at the URL you posted  - there is no 'box_add' parameter in that URL. As you are running your foreach on all the values found in the box_add variable I suspect that is where your problem is.

You need to fix your input data.
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Also looking at the original code you posted there seems to be a funny with your if / else.

The if ($box == 'DEM0111') is commented out but it seems to have an else attached to it that is not commented out - either that or the else belongs to the if ($_POST['submit]') - but then you are missing a closing '}' before the else.

Either way your script is a problem. Start with cleaning that up and reposting.
0
 
LVL 33

Expert Comment

by:Slick812
Comment Utility
@peter-cooper, if I thought that you could do a change to $_GET, I may have suggested that, but many times the GET values require changing punctuation to URL safe hex values.

any way you have set the jquery ajax with -  dataType: "json" , , what do you think that does in the interaction for the send and receive of ajax exchange ?

Could that some how be related to your rather uninformed report of "jquery keeps giving me a typeError"

I did not spend any time going through all the PHP code in your question, but  my impression from lookin at it was - there was likely a PHP fatal error in that mess, maybe Mysql, or a missed ending }    etc.

If you had bothered to do the development thing and add error_reporting(E_ALL); so the warnings could be seen also, you could have half a chance to have errors reported and the Exact code Line they are on to debug this mess.
BUT
even if there is a fatal php error OR warning-notice, how could you know and see it in your browser or with the ajax JS you have there?
I do not believe that there can be a change in 3 or 4 lines of code in the PHP or JS to fix this thing, but I could be wrong?
You have several experts here trying to help, but from their comments they do not understand your php code enough to tell you a fix.
To me that would mean a change of debug procedure, since this way is not getting results
0
 

Author Comment

by:peter-cooper
Comment Utility
I now have a working code thanks to the advice of you guys. However, one area where I am stuck in the $sql query in the foreach statement. It is inserting the values but instead of sending success to jauery, it is sending the $boxerror, almost like it is checking the table again after inserting data. How can I correct this. Thanks

<?php


// Connection config
function runSQL($rsql) {
    $hostname = "localhost";
    $username = "root";
    $password = "";
    $dbname   = "sample";
    $connect = mysql_connect($hostname,$username,$password) or die ("Error: could not connect to database");
    $db = mysql_select_db($dbname);
    $result = mysql_query($rsql) or die (mysql_error());
    return $result;
    mysql_close($connect);
    }
?>
<?php
    // test vars from jquery form
    $status = mysql_real_escape_string($_REQUEST['status']);
    $company = mysql_real_escape_string($_REQUEST['company']);
    $requested = mysql_real_escape_string($_REQUEST['requested']);
    $activity = mysql_real_escape_string($_REQUEST['activity']);
    $address = mysql_real_escape_string($_REQUEST['address1']);
    $service = mysql_real_escape_string($_REQUEST['service']);
    $box = mysql_real_escape_string($_REQUEST['box_add']);
    $authorised = mysql_real_escape_string($_SESSION['kt_name_usr']);
    $dept = mysql_real_escape_string($_REQUEST['dept']);
    $boxerrortext = 'Error';
    
    // Split the box if multiples
    $array = explode(',', $_REQUEST['box_add']);
    $error = array();
    // Loop to split if multiple request and check DB for dupe entries
    foreach ($array as $box) {
        $sql = "SELECT item FROM act WHERE item = '$box'";
        $result = runSQL($sql) or die(mysql_error());
        
        // If there are dupe entries, send message to jquery
        if (mysql_num_rows($result)>0) {
            //echo 'Error';
	    $error = array('boxerror'=>$boxerrortext, 'box'=>$box);
	    $output = json_encode($error);

	    echo $output;
            return;
        
    } else {
        
        // If no dupes, then enter values into DB.
        $form = array();
        foreach ($array as $box) {
            $form[] = array('dept'=>$dept,
            'company'=>$company,
            'address'=>$address,
            'service'=>$service,
            'box'=>$box,
            'authorised'=>$requested);
            
	    $sql  = "INSERT INTO `act` (service, activity, department, company, address, user, date, item, new)";
	    $sql .= "VALUES ('$service', '$activity', '$dept', '$company', '$address', '$requested', NOW(), '$box', 1)";
            $result = runSQL($sql) or die(mysql_error()); 
    }
    }
    }
	    $result=json_encode($form);
     
            echo $result;	
	 
	 
    ?>

Open in new window

0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
You have a foreach loop.

My guess it is going around once to enter the data and then going around a second time and setting the error.

Haven't checked in depth though - but that would be my first port of call to find the problem.
0
 

Author Comment

by:peter-cooper
Comment Utility
With 1 box it works fine. But if I enter more than 1 box say 456,457 it goes wrong. The funny thing is that it enters the data in db correctly.
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
As I said - I think it is doing one too many iterations in the loop.

Try putting debug statements to a log file in each of the sections (error, insert) just to see if that is happening.
0
 

Author Comment

by:peter-cooper
Comment Utility
As per your suggestion, I created log file like so:

       
        $array = explode(',', $_REQUEST['box_add']);
	file_put_contents('php_log.txt', print_r($array, true));

Open in new window


Result
Array
(
    [0] => demo111
    [1] => fg91a
)

Open in new window

So it is working there. I then changed my original line:
from
foreach ($array as $box) {

Open in new window

to
foreach ($array as $box => $value) {
file_put_contents('php_log.txt', print_r($value, true));

Open in new window


This only showed the last box: fg91a. Why does $value on show the 1 item. . Thanks
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Change the last parameter of your file_put_contents to FILE_APPEND

The place the file_put_contents
    foreach ($array as $box) {
        // ***** HERE ****
        file_put_contents('php_log.txt',printr_($value), FILE_APPEND);

        $sql = "SELECT item FROM act WHERE item = '$box'";
        $result = runSQL($sql) or die(mysql_error());
        
        // If there are dupe entries, send message to jquery
        if (mysql_num_rows($result)>0) {
            // **** HERE *****
            file_put_contents('php_log.txt',printr_($value), FILE_APPEND);
	    $error = array('boxerror'=>$boxerrortext, 'box'=>$box);

            ....

            return;
        
    } else {
        // **** HERE ****
        file_put_contents('php_log.txt',printr_($value), FILE_APPEND);
        ...
   }

Open in new window

0
 

Author Comment

by:peter-cooper
Comment Utility
I have put your code Julian as you suggested, but it dosen't seem to be adding the right entries from the input. This is the latest code i am using and this is the entry in the log file. Thanks

<?php
    $boxerrortext = 'Error';
    
    // Split the box if multiples
    $array = explode(',', $_REQUEST['box_add']);
    $error = array();
	
    // Loop to split if multiple request and check DB for dupe entries
    foreach ($array as $box => $value) {
	
	file_put_contents('php_log.txt',print_r($value), FILE_APPEND);

        $sql = "SELECT item FROM act WHERE item = '$box'";
        $result = runSQL($sql) or die(mysql_error());
        
        // If there are dupe entries, send message to jquery
  if (mysql_num_rows($result)>0) {
	file_put_contents('php_log.txt',print_r($value), FILE_APPEND);
           
	$error[] = array('boxerror'=>$boxerrortext, 'box'=>$box);
	$output = json_encode($error);
	echo $output;
	return;
    } else {
        file_put_contents('php_log.txt',print_r($value), FILE_APPEND);
		
        // If no dupes, then enter values into DB.
        $form = array();
        foreach ($array as $box) {
            $form[] = array('dept'=>$dept,
            'company'=>$company,
            'address'=>$address,
            'service'=>$service,
            'box'=>$box,
            'authorised'=>$requested);
			
            $result=json_encode($form);
	    echo $result;
	    /* $sql  = "INSERT INTO `temp` (service, activity, department, company, address, user, date, item, new)";
		$sql .= "VALUES ('$service', '$activity', '$dept', '$company', '$address', '$requested', NOW(), '$box', 1)";
            $results = runSQL($sql) or die(mysql_error());  */		
    } 		
    }
    }
?>

Open in new window


And this is the value of log file.
1111

Open in new window


Interestingly, when I click on the file link in firebug console. it shows all the entries as though they had been put in the db. That is commented out. Thanks
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Change your debut statements to
    foreach ($array as $box => $value) {
	
	file_put_contents('php_log.txt','Foreach: ' .  print_r($value, true) . "\n", FILE_APPEND);

        $sql = "SELECT item FROM act WHERE item = '$box'";
        $result = runSQL($sql) or die(mysql_error());
        
        // If there are dupe entries, send message to jquery
  if (mysql_num_rows($result)>0) {
	file_put_contents('php_log.txt','If: ' . print_r($value, true) . "\n", FILE_APPEND);
           
	$error[] = array('boxerror'=>$boxerrortext, 'box'=>$box);
	$output = json_encode($error);
	echo $output;
	return;
    } else {
        file_put_contents('php_log.txt','Else: ' . print_r($value) . "\n", FILE_APPEND);

Open in new window

0
 
LVL 33

Accepted Solution

by:
Slick812 earned 500 total points
Comment Utility
OHH, can't take it any more, here is my suggestion to try and get this to go forward. My PHP code suggestion -
<?php
// you do not need that runSQL( ) function
    $connect = mysql_connect("localhost","root","password") or die ('{"opp":"error","box":"mysql_connect FAILED"}');
	//If this Fails at least place it in a JSON so you can see IT
    $db = mysql_select_db("sample");


    // test vars from jquery form
    $status = mysql_real_escape_string($_REQUEST['status']);
    $company = mysql_real_escape_string($_REQUEST['company']);
    $requested = mysql_real_escape_string($_REQUEST['requested']);
    $activity = mysql_real_escape_string($_REQUEST['activity']);
    $address = mysql_real_escape_string($_REQUEST['address1']);
    $service = mysql_real_escape_string($_REQUEST['service']);
    //$box = mysql_real_escape_string($_REQUEST['box_add']); // never used
    $authorised = mysql_real_escape_string($_SESSION['kt_name_usr']);
    $dept = mysql_real_escape_string($_REQUEST['dept']);
    $boxerrortext = 'Error';
    
    // Split the box if multiples
    $array = explode(',', $_REQUEST['box_add']);
    //$error = array();
	$outString = ''; // You NEED to do your HTML output Formating on SERVER Side
// Only Return a String to browser NOT an array

    // IMPORTANT - you do not understand what your foreach loop is doing! !
    foreach ($array as $box) {
		$box = mysql_real_escape_string($box);
        $sql = "SELECT item FROM act WHERE item = '$box'";
        $result = mysql_query($sql) or die ('{"opp":"error","box":"'.mysql_error().'"}');
        
        // If there are dupe entries, send message to jquery
        if (mysql_num_rows($result)>0) {
// DO NOT create an $error array here it is usless in a for each LOOP
	    //$error = array('boxerror'=>$boxerrortext, 'box'=>$box);
	    //$output = json_encode($error);
        $outString .= $box.','
	    //echo $output;
        //    return; // BAD CODE ! will ONLY ever get ONE number
        
		} 
	} // foreach ($array
	
	if ($outString) { // if there are numbers in this string, then you have error Duplicates
	$error = array('opp'=>"error", 'box'=>$outString);// box will have the TEXT for your Dialog Text
// IMPORTANT, I have changed your JSON output to use an Opperation (opp) to indicate
// in javascript if there was an error OR not
	$output = json_encode($error);
	echo $output;
	exit();
	}

        
// Will Only execute the SUCCESS if $outString is empty (no exit();)
    //$form = array();
    foreach ($array as $box) {
// DO NOT USE ARRAY, use a string
    /*$form[] = array('dept'=>$dept,
            'company'=>$company,
            'address'=>$address,
            'service'=>$service,
            'box'=>$box,
            'authorised'=>$requested);*/
	$outString .= "company = $company, address = $address, service = $service, box = $box <br />";
		
	$box = mysql_real_escape_string($box);           
	    $sql  = "INSERT INTO `act` (service, activity, department, company, address, user, date, item, new)";
	    $sql .= "VALUES ('$service', '$activity', '$dept', '$company', '$address', '$requested', NOW(), '$box', 1)";
	    $result = mysql_query($sql) or die ('{"opp":"error","box":"'.mysql_error().'"}'); 
    }

       $json = array('opp'=>'insert', 'box'=>$outString);
       $result=json_encode($json);
       echo $result;	
?>

Open in new window


Now in your javascript you need to change your -
if(data.boxerror == 'No duplicate boxes') {

to
if(data.opp == 'error') {

= = = = = = =
in javascript data.box will be the Dialog Output STRING text (not an array) used in this -
var $dialog = $('<div id="dialog"></div>').html('<br />Your New Intake of: ' + data.box + ' was NOT SUBMITTED.<br />You must enter a number that is unique<br />Thank you.');

I could not test test code, but I tried to get it right, but may have syntax errors.
Please look it over and try and see the reasons for the changes I have made.
0
 
LVL 33

Expert Comment

by:Slick812
Comment Utility
you may not understand how to use my next suggestion , but if you insist on using the -
dataType: "json"  in ajax, , , so then if there is any thing in the PHP return that Jquery fails to parse to JSON (like a PHP fatal error), then the -
success: function(data) {

is NEVER fired! And the ajax ERROR is called.  ,  then for development to see errors you can add a Jquery ajax error thing like this inside your javascript-
// I always have the ajaxError(function for development
$(document).ajaxError(function( event, request, settings, exc ) {
if (request.status==404) alert("ERROR from Ajax as '404 status' the "+settings.url+" page was NOT on Server, \nCan NOT recover from this ERROR, This operation is NOT available!"); 
else {
alert("ERROR from Ajax POST= Server-Status: "+request.status+", post-URL: "+settings.url+", post-Data: "+settings.data+", error because: "+exc);
if (request.responseText) {
  $('#debug1').html(request.responseText); 
  }else $('#debug1').html("ajaxError responseText is empty");
}
});

Open in new window


I rarely see anyone using the Jquery Error thing, I guess because they made it so complicated (jquery is suppose to be easy right?)

in the error I have -
$('#debug1').html(request.responseText);
which shows me the RETURN TEXT (json or not) inside of a DIV with an ID of debug1, so you can add a div for development somewhere on form page like -
<div id="debug1" style "border:2px solid #00d; min-height:2em;">No DEBUG</div>

Once you get it working, you Remove the <div> from the page, and the  $(document).ajaxError(function(    from the javascript

Maybe this will help someone?
0
 

Author Comment

by:peter-cooper
Comment Utility
@Slick

OHH, can't take it any more
Reminds me of a scene in Airplane where people commit suicide rather than listen to Ted Straker :-) Anyway, on with the plot. I am getting error in line 42 of: Parse error: syntax error, unexpected '}'. Thanks ever so much for all your effort. Once we get this error sorted, I think we can move on.
0
 

Author Comment

by:peter-cooper
Comment Utility
It was just a missing semi colon. Thanks to you, I finally have it working and I am only sorry I cannot treat you to a pint for all the help and patience you have shown me. I would also like to thank the other experts who pitched in with there comments. Many thanks
0
 

Author Closing Comment

by:peter-cooper
Comment Utility
Many thanks once again.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now