MySql query not returning results

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']

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.

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

?>

Select allOpen in new window

Select allOpen 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" );
			 }
		       }
		   });
		  }
			  
		}
			   
	     });
	   }
	}

Select allOpen in new window

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

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

an unusual set of characters to be splitting on.

It is the name of the input field and a user can enter multiple entries seperated by comma. Thanks

So it should be

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

No, the way I have it posted is correct. It handles more than 1 entry.

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

Will that fix my error? Thanks

Probably not but it will prevent a future error from popping up.

I have corrected that Dave. But I need to find a way to correct my original question. Any help would be great. Thanks

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.

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

Select allOpen in new window

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( ...) 

Select allOpen in new window

is going to result in

$error[0] =>
      boxerror => 'Somevalue',
      box => 'Some other value';

Select allOpen in new window

So when you refer to

success: function(data) {
     data.boxerror

Select allOpen in new window

It is going to be a problem because you need to actually be doing

success: function(data) {
     data[0].boxerror

Select allOpen in new window

Or alternatively in your PHP change

if ($num_rows) {
  $error[] = array(
        'boxerror'=>$boxerrortext,
        'box'=>$box
      );
  $result = json_encode($error);

  echo $result;
  return;
}

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

Select allOpen in new window

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.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

@Dave
All variables are available in the form of and are receiving correct values from ajax. Thanks

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

Select allOpen in new window

@peter-cooper - did you see my post re the array reference?

@julianH Sorry, got called out. Yes, I changed to your comment but it still errors with TypeError data is null. Thanks

Is there a link we can look at so we can see the error?

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

What problem are we trying to solve? A type error return on your AJAX call or how to detect duplicates in a database?

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

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?

Sorry Julian, I do not understand. I am fairly new to ajax, so how would I do that with my code. Thanks

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

Select allOpen 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.

This strategy will work if your script expects a GET-method request.

http://myserver/myphpscript.php?parameter1=value1&parameter2=value2

Select allOpen 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']);

Select allOpen 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.

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

@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.

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

Select allOpen in new window

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

Select allOpen 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.

@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.

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.

@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

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

Select allOpen in new window

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.

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.

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.

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

Select allOpen in new window

Result

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

Select allOpen in new window

So it is working there. I then changed my original line:
from

foreach ($array as $box) {

Select allOpen in new window

to

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

Select allOpen in new window

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

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

Select allOpen in new window

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());  */		
    } 		
    }
    }
?>

Select allOpen in new window

And this is the value of log file.

1111

Select allOpen 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

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

Select allOpen in new window

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

Select allOpen 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?

@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.

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

Many thanks once again.