Why doesn't this join logic works?

[mbarandao]

Hello;

I using the following sql to get information from two tables --through join;

/

/ this join works in sql, but can
$res = "select ad.firstname, ad.lastname, ad.address, ad.city, ad.state, ad.zipcode, ad.dhtmlgoodies_category, ad.dhtmlgoodies_subcategory, ad.caryear, ad.customerid, ad.servicedesc, aj.email,  aj.areacode, aj.phoneprefix, ad.clientID, 
aj.phonesufix, aj.cellareacode, aj.cellprefix, aj.cellsufix, aj.commentonclient from additional_cars as ad, ajax_client as aj where ad.clientID='$clientId' group by ad.clientID";

it works fine when tested in phpmyadmin, but fail we incorporated with the following which passes the value to another form using "formObj". here is the entire code:

function toSafeString($string) {
  $string = str_replace('<br/>', "\n", $string); 
   $string = preg_replace('/[\r\n]/s', '\\n', $string); 
  $string = str_replace(array('<br />','<br/>'),"\n",$string); 
     return $string;
   }
  
if(isset($_GET['getClientId'])){ 
  $clientId = preg_replace("/[^0-9|a-z|A-Z]/si", "", $_GET['getClientId']);

$res = "select ad.firstname, ad.lastname, ad.address, ad.city, ad.state, ad.zipcode, ad.dhtmlgoodies_category, ad.dhtmlgoodies_subcategory, ad.caryear, ad.customerid, ad.servicedesc, aj.email,  aj.areacode, aj.phoneprefix, ad.clientID, 
aj.phonesufix, aj.cellareacode, aj.cellprefix, aj.cellsufix, aj.commentonclient from additional_cars as ad, ajax_client as aj where ad.clientID='$clientId' group by ad.clientID";

  if($inf = mysql_fetch_array($res)){
    echo "formObj.firstname.value = '".toSafeString($inf["firstname"])."';\n";    
    echo "formObj.lastname.value = '".toSafeString($inf["lastname"])."';\n"; 
    echo "formObj.lastname1.value = '".toSafeString($inf["lastname"])."';\n";    
    echo "formObj.address.value = '".toSafeString($inf["address"])."';\n";    
    echo "formObj.zipcode.value = '".toSafeString($inf["zipcode"])."';\n";    
    echo "formObj.city.value = '".toSafeString($inf["city"])."';\n";    
    echo "formObj.state.value = '".toSafeString($inf["state"])."';\n";
    echo "formObj.dhtmlgoodies_category.value = '".toSafeString($inf["dhtmlgoodies_category"])."';\n";
    echo "formObj.dhtmlgoodies_subcategory.value = '".toSafeString($inf["dhtmlgoodies_subcategory"])."';\n";
    echo "formObj.caryear.value = '".toSafeString($inf["caryear"])."';\n";
    echo "formObj.servicedesc.value = '".toSafeString($inf["servicedesc"])."';\n"; 
    //echo "formObj.theDate.value = '".toSafeString($inf["theDate"])."';\n";
    //echo "formObj.areacode.value = '".toSafeString($inf["areacode"])."';\n";
    //echo "formObj.phoneprefix.value = '".toSafeString($inf["phoneprefix"])."';\n";
    //echo "formObj.phonesufix.value = '".toSafeString($inf["phonesufix"])."';\n"; 
    echo "formObj.email.value = '".toSafeString($inf["email"])."';\n"; 
    //echo "formObj.cellareacode.value = '".toSafeString($inf["cellareacode"])."';\n";
    //echo "formObj.cellprefix.value = '".toSafeString($inf["cellprefix"])."';\n";
    //echo "formObj.cellsufix.value = '".toSafeString($inf["cellsufix"])."';\n";
    echo "formObj.currentmileage.value = '".toSafeString($inf["currentmileage"])."';\n";
    //echo "formObj.Balancedue.value ='".toSafeString($inf["Balancedue"])."';\n";
   // echo "formObj.uploadedfile.value = '".$uploaddir.$inf["uploadedfile"]."';\n";//this includes directory and filename
    echo "formObj.servicearea.value = '".toSafeString($inf["servicearea"])."';\n";
    //echo "formObj.commentonclient.value = '".toSafeString($inf["commentonclient"])."';\n";
    echo "formObj.drivenmileage.value = '".toSafeString($inf["drivenmileage"])."';\n"; 
    echo "formObj.clientid.value = '".toSafeString($inf["clientID"])."';\n"; 
    //echo "formObj.clientid2.value = '".toSafeString($inf["clientID"])."';\n"; 
    echo "formObj.clientid3.value = '".toSafeString($inf["clientID"])."';\n"; 
    echo "formObj.displayname.value = '".toSafeString($inf["lastname"]).",".toSafeString($inf["firstname"])."';\n"; 
	echo "formObj.returnedClientId.value = '".toSafeString($inf["clientID"])."';\n";

  }
  else
  {
    echo "formObj.firstname.value = '';\n";    
    echo "formObj.lastname.value = '';\n";    
    echo "formObj.lastname1.value = '';\n";    
    echo "formObj.address.value = '';\n";    
    echo "formObj.zipcode.value = '';\n";    
    echo "formObj.city.value = '';\n";    
    echo "formObj.state.value = '';\n";
    echo "formObj.dhtmlgoodies_category.value = '';\n";
    echo "formObj.dhtmlgoodies_subcategory.value = '';\n";
    echo "formObj.caryear.value = '';\n";
    echo "formObj.servicedesc.value = '';\n";
    //echo "formObj.theDate.value = '';\n";
    //echo "formObj.areacode.value = '';\n";
    //echo "formObj.phoneprefix.value = '';\n";
    //echo "formObj.phonesufix.value = '';\n";
    echo "formObj.email.value = '';\n";
    //echo "formObj.cellareacode.value = '';\n";
    //echo "formObj.cellprefix.value = '';\n";
    //echo "formObj.cellsufix.value = '';\n";
    echo "formObj.currentmileage.value = '';\n";
    //echo "formObj.Balancedue.value = '';\n";
    echo "formObj.servicearea.value = '';\n";
    //echo "formObj.commentonclient.value = '';\n";
    echo "formObj.drivenmileage.value = '';\n";
    echo "formObj.clientid.value = '';\n";
    //echo "formObj.clientid2.value = '';\n";
    echo "formObj.clientid3.value = '';\n";
    echo "formObj.displayname.value = '';\n";
	echo "formObj.returnedClientId.value = '';\n";

        }    

}

works fine if I'm querying one table, but problem comes about when joining the tables. any thoughts as to why?

Thanks

[ddymacek]

your code:

$res = "select ad.firstname, ad.lastname, ad.address, ad.city, ad.state, ad.zipcode, ad.dhtmlgoodies_category, ad.dhtmlgoodies_subcategory, ad.caryear, ad.customerid, ad.servicedesc, aj.email, aj.areacode, aj.phoneprefix, ad.clientID,
aj.phonesufix, aj.cellareacode, aj.cellprefix, aj.cellsufix, aj.commentonclient from additional_cars as ad, ajax_client as aj where ad.clientID='$clientId' group by ad.clientID";
 
if($inf = mysql_fetch_array($res)){ ...

2 questions what is $inf, and where are you actually executing your query?

$result = mysql_query($res);

// this will never be true according to the above code
if($inf = mysql_fetch_array($res))

[mbarandao]

your code:

$res = "select ad.firstname, ad.lastname, ad.address, ad.city, ad.state, ad.zipcode, ad.dhtmlgoodies_category, ad.dhtmlgoodies_subcategory, ad.caryear, ad.customerid, ad.servicedesc, aj.email, aj.areacode, aj.phoneprefix, ad.clientID,
aj.phonesufix, aj.cellareacode, aj.cellprefix, aj.cellsufix, aj.commentonclient from additional_cars as ad, ajax_client as aj where ad.clientID='$clientId' group by ad.clientID";
 
if($inf = mysql_fetch_array($res)){ ...

2 questions what is $inf, and where are you actually executing your query?

$result = mysql_query($res);

// this will never be true according to the above code
if($inf = mysql_fetch_array($res))

Thanks for the reply, the query is executed on an associated form through the the following jquery script:

var currentClientID = false;
function getClientData() {
	var clientId = $('#clientID').val().replace(/[^0-9|a-z|A-Z]/g,'');//here to incorporate the client id with numbers and letters
	if(clientId.length >0 && clientId!=currentClientID){//here to change the client id length
		$.ajax({
			url: 'getclient.php?getClientId=' + clientId,
			type: "GET",
			success: function showClientData(response) {
				var formObj = document.forms["clientForm"];
				eval(response);
				currentClientID = clientId;
			}
		});
	}
}

the values are passed to a form barring the same name and id of the values in the query.

So essentially:

<input name="lastname" id="lastname" size="20" maxlength="255" disabled="true"></td>
 receives value from
 echo "formObj.firstname.value = '".toSafeString($inf["firstname"])."';\n";

The concept is based on the work I found on DTHMLGoodies.com and escalated the concept a bit further.

[ddymacek]

is there an error in the query? can you call

query or die (mysql_error());

can you echo out your sql before submitting to db. is it correct?

I'm very curious as to what your $res is holding, if anything at all.
I see the clientId coming from your form using as request, I understand all of that.
But I never see your actual query to the database that should look something like this.

$result = mysql_query($res);
// then your $inf line would be 
if($inf = mysql_fetch_array($result)){  // and you should have data

[mbarandao]

There is no error. When echoed the sql is not printed. But the sql by itself in phpmyadmin works as expected

[ddymacek]

ok. We're not quite seeing eye to eye here.
question: is the 81 lines of php everything that is in the getclient.php code?
Are you connecting to a db at all? you don't have to show any connection code, but I am just curious.
you have to log in to your db.
you then set your sql syntax.
you then execute your sql.
you then 'normally' do something with results.
So while it all may be in your code, I am not seeing the following things:
For me this is my normal 'connection' code. I think the main part of your code looks fine... except I see no db connections in your code.

/*  Define your db params prior to their use */
	define(DB_HOST,'localhost');
	define(DB_USER,'username');
	define(DB_PASS,'password');
	define(DB_BASE,'database_name');

	// placeholder for dblink
	$dblink = null;
	try	{
		// connect to your database as defined above, or die spewing error message.
		$dblink = mysql_connect(DB_HOST,DB_USER,DB_PASS) or die(mysql_error());
		// change to your selected database that you want to use.
		mysql_select_db(DB_BASE,$dblink);
	} catch(Exception $ex) {
		echo "Could not connect to " . DB_HOST . ":" . DB_BASE . "\n";
		echo "Error: " . $ex->message;
		exit;
	}
	// define your sql syntax (change the query to be your "$res" statement)		
	$sql =  "SELECT * FROM x WHERE y = '".$this->slashes($id)."'";

	/****    note ***/
	  this is where I meant to echo out your sql.  you said "When echoed the sql is not printed"
	  I'm guessing you mean your result, which I don't think you are ever even hitting your database.
	  what I meant was echo out your "$res" statement, and see if $clientID was properly set.
	  so: to 'echo sql'  echo "MY SQL = " . $sql . "<br />";
	/**** end note ***/
	   echo "MY SQL = " . $sql . "<br />";
	// actually execute your query to the db, or die spewing error message.
	$res = mysql_query($sql,$dblink) or die(mysql_error());
	// also calling query this way with die will send out an error message if there is something wrong with your syntax or connection.

	/************** 
	  now you should have some returned results...  
	**************/

	if ($inf = mysql_fetch_array($res)) {	
	} else {
		// set your blank vars
	}
	finally close the database connection.
	if(is_resource($dblink)) {		
		mysql_close($dblink);
	}

Does this make sense. I don't think you are ever connecting to your database, as opposed to there being an error in your syntax.

[mbarandao]

ok. We're not quite seeing eye to eye here.
question: is the 81 lines of php everything that is in the getclient.php code?
Are you connecting to a db at all? you don't have to show any connection code, but I am just curious.
you have to log in to your db.
you then set your sql syntax.
you then execute your sql.
you then 'normally' do something with results.
So while it all may be in your code, I am not seeing the following things:
For me this is my normal 'connection' code. I think the main part of your code looks fine... except I see no db connections in your code.

/*  Define your db params prior to their use */
	define(DB_HOST,'localhost');
	define(DB_USER,'username');
	define(DB_PASS,'password');
	define(DB_BASE,'database_name');

	// placeholder for dblink
	$dblink = null;
	try	{
		// connect to your database as defined above, or die spewing error message.
		$dblink = mysql_connect(DB_HOST,DB_USER,DB_PASS) or die(mysql_error());
		// change to your selected database that you want to use.
		mysql_select_db(DB_BASE,$dblink);
	} catch(Exception $ex) {
		echo "Could not connect to " . DB_HOST . ":" . DB_BASE . "\n";
		echo "Error: " . $ex->message;
		exit;
	}
	// define your sql syntax (change the query to be your "$res" statement)		
	$sql =  "SELECT * FROM x WHERE y = '".$this->slashes($id)."'";

	/****    note ***/
	  this is where I meant to echo out your sql.  you said "When echoed the sql is not printed"
	  I'm guessing you mean your result, which I don't think you are ever even hitting your database.
	  what I meant was echo out your "$res" statement, and see if $clientID was properly set.
	  so: to 'echo sql'  echo "MY SQL = " . $sql . "<br />";
	/**** end note ***/
	   echo "MY SQL = " . $sql . "<br />";
	// actually execute your query to the db, or die spewing error message.
	$res = mysql_query($sql,$dblink) or die(mysql_error());
	// also calling query this way with die will send out an error message if there is something wrong with your syntax or connection.

	/************** 
	  now you should have some returned results...  
	**************/

	if ($inf = mysql_fetch_array($res)) {	
	} else {
		// set your blank vars
	}
	finally close the database connection.
	if(is_resource($dblink)) {		
		mysql_close($dblink);
	}

Does this make sense. I don't think you are ever connecting to your database, as opposed to there being an error in your syntax.

My apology, I do have an include database connection file within the file. I'm able to connect to db and process the request without problem when I'm only querying one table.

Though my connection is note as elabarate as yours, but the following is included

$connection = mysql_connect("host","username","password");  
mysql_select_db("databaseName",$connection);

I'll give your re-write a try and advise shortly.

Thanks, I appreciate your time on this.

Mossa

[mbarandao]

I have modified according to the your suggestion, unfortunately, same outcome as my initial code. No data is pulled, nor I'm getting any errors. Also, unable to echo as instructed. Any thoughts!

Here is the complete code:

<?php
 function toSafeString($string) {
  $string = str_replace('<br/>', "\n", $string); 
   $string = preg_replace('/[\r\n]/s', '\\n', $string); 
  $string = str_replace(array('<br />','<br/>'),"\n",$string); 
     return $string;
   }
if(isset($_GET['getClientId'])){ 
  $clientId = preg_replace("/[^0-9|a-z|A-Z]/si", "", $_GET['getClientId']);

/*  Define your db params prior to their use */
	define(DB_HOST,'******');
	define(DB_USER,'****');
	define(DB_PASS,'*****');
	define(DB_BASE,'*******');

	// placeholder for dblink
	$dblink = null;
	try	{
		// connect to your database as defined above, or die spewing error message.
		$dblink = mysql_connect(DB_HOST,DB_USER,DB_PASS) or die(mysql_error());
		// change to your selected database that you want to use.
		mysql_select_db(DB_BASE,$dblink);
	} catch(Exception $ex) {
		echo "Could not connect to " . DB_HOST . ":" . DB_BASE . "\n";
		echo "Error: " . $ex->message;
		exit;
	}

	$sql ="SELECT ad.firstname, ad.lastname, ad.address, ad.city, ad.state, ad.zipcode, ad.dhtmlgoodies_category, ad.dhtmlgoodies_subcategory, ad.caryear, ad.customerid, ad.servicedesc, aj.email,  aj.areacode, aj.phoneprefix, ad.clientID, 
	aj.phonesufix, aj.cellareacode, aj.cellprefix, aj.cellsufix, aj.commentonclient FROM additional_cars as ad, ajax_client as aj WHERE ad.clientID='$clientId' group by ad.clientID"; //y = '".$this->slashes($id)."'";

	 echo "MY SQL = " . $sql . "<br />";
	// actually execute your query to the db, or die spewing error message.
	$res = mysql_query($sql,$dblink) or die(mysql_error());
	// also calling query this way with die will send out an error message if there is something wrong with your syntax or connection.

	/************** 
	  now you should have some returned results...  
	**************/

	if ($inf = mysql_fetch_array($res)) 
	{	
	echo "formObj.firstname.value = '".toSafeString($inf["firstname"])."';\n";    
    echo "formObj.lastname.value = '".toSafeString($inf["lastname"])."';\n"; 
    echo "formObj.lastname1.value = '".toSafeString($inf["lastname"])."';\n";    
    echo "formObj.address.value = '".toSafeString($inf["address"])."';\n";    
    echo "formObj.zipcode.value = '".toSafeString($inf["zipcode"])."';\n";    
    echo "formObj.city.value = '".toSafeString($inf["city"])."';\n";    
    echo "formObj.state.value = '".toSafeString($inf["state"])."';\n";
    echo "formObj.dhtmlgoodies_category.value = '".toSafeString($inf["dhtmlgoodies_category"])."';\n";
    echo "formObj.dhtmlgoodies_subcategory.value = '".toSafeString($inf["dhtmlgoodies_subcategory"])."';\n";
    echo "formObj.caryear.value = '".toSafeString($inf["caryear"])."';\n";
    echo "formObj.servicedesc.value = '".toSafeString($inf["servicedesc"])."';\n"; 
    //echo "formObj.theDate.value = '".toSafeString($inf["theDate"])."';\n";
    //echo "formObj.areacode.value = '".toSafeString($inf["areacode"])."';\n";
    //echo "formObj.phoneprefix.value = '".toSafeString($inf["phoneprefix"])."';\n";
    //echo "formObj.phonesufix.value = '".toSafeString($inf["phonesufix"])."';\n"; 
    echo "formObj.email.value = '".toSafeString($inf["email"])."';\n"; 
    //echo "formObj.cellareacode.value = '".toSafeString($inf["cellareacode"])."';\n";
    //echo "formObj.cellprefix.value = '".toSafeString($inf["cellprefix"])."';\n";
    //echo "formObj.cellsufix.value = '".toSafeString($inf["cellsufix"])."';\n";
    echo "formObj.currentmileage.value = '".toSafeString($inf["currentmileage"])."';\n";
    //echo "formObj.Balancedue.value ='".toSafeString($inf["Balancedue"])."';\n";
   // echo "formObj.uploadedfile.value = '".$uploaddir.$inf["uploadedfile"]."';\n";//this includes directory and filename
    echo "formObj.servicearea.value = '".toSafeString($inf["servicearea"])."';\n";
    //echo "formObj.commentonclient.value = '".toSafeString($inf["commentonclient"])."';\n";
    echo "formObj.drivenmileage.value = '".toSafeString($inf["drivenmileage"])."';\n"; 
    echo "formObj.clientid.value = '".toSafeString($inf["clientID"])."';\n"; 
    //echo "formObj.clientid2.value = '".toSafeString($inf["clientID"])."';\n"; 
    echo "formObj.clientid3.value = '".toSafeString($inf["clientID"])."';\n"; 
    echo "formObj.displayname.value = '".toSafeString($inf["lastname"]).",".toSafeString($inf["firstname"])."';\n"; 
	echo "formObj.returnedClientId.value = '".toSafeString($inf["clientID"])."';\n";

	} 
	else {
		echo "formObj.firstname.value = '';\n";    
    echo "formObj.lastname.value = '';\n";    
    echo "formObj.lastname1.value = '';\n";    
    echo "formObj.address.value = '';\n";    
    echo "formObj.zipcode.value = '';\n";    
    echo "formObj.city.value = '';\n";    
    echo "formObj.state.value = '';\n";
    echo "formObj.dhtmlgoodies_category.value = '';\n";
    echo "formObj.dhtmlgoodies_subcategory.value = '';\n";
    echo "formObj.caryear.value = '';\n";
    echo "formObj.servicedesc.value = '';\n";
    //echo "formObj.theDate.value = '';\n";
    //echo "formObj.areacode.value = '';\n";
    //echo "formObj.phoneprefix.value = '';\n";
    //echo "formObj.phonesufix.value = '';\n";
    echo "formObj.email.value = '';\n";
    //echo "formObj.cellareacode.value = '';\n";
    //echo "formObj.cellprefix.value = '';\n";
    //echo "formObj.cellsufix.value = '';\n";
    echo "formObj.currentmileage.value = '';\n";
    //echo "formObj.Balancedue.value = '';\n";
    echo "formObj.servicearea.value = '';\n";
    //echo "formObj.commentonclient.value = '';\n";
    echo "formObj.drivenmileage.value = '';\n";
    echo "formObj.clientid.value = '';\n";
    //echo "formObj.clientid2.value = '';\n";
    echo "formObj.clientid3.value = '';\n";
    echo "formObj.displayname.value = '';\n";
	echo "formObj.returnedClientId.value = '';\n";
	}
	//finally close the database connection.
	if(is_resource($dblink)) {		
		mysql_close($dblink);
	}
	}
?>

[urtrivedi]

after running page view souce and search for MY SQL =
I think you should find real query text around it in source. then run that query in myadmin.

[mbarandao]

after running page view souce and search for MY SQL =
I think you should find real query text around it in source. then run that query in myadmin.

urtrivedi, thanks for your post. I'm afraid I'm not following your suggestion. Can you please clarify?

I have tested the sql inside phpmyadmin. It works fine!

Mossa--

[urtrivedi]

when you run this page in browser, then, if you right click on broswer, it will show VIEW SOURCE OPTION (THAT WILL SHOW HTML CODE). depending on browser one or another way you will find such menu.

when you loook at html code in search text type MY SQL =

then following you will find query, copy it then run it in phpymadmin
this is to check whether your php page is builing query properly or not

[urtrivedi]

I have tested this code, its running fine here
make sure you pass getClientId and your post method is get, Be sure about case sensitivity of variable name C and I should be capital and rest letters in small

when I open link

http://server/folder/page.php?getClientId=DG9642

It gives following output

MY SQL = SELECT ad.firstname, ad.lastname, ad.address, ad.city, ad.state, ad.zipcode, ad.dhtmlgoodies_category, ad.dhtmlgoodies_subcategory, ad.caryear, ad.customerid, ad.servicedesc, aj.email, aj.areacode, aj.phoneprefix, ad.clientID, aj.phonesufix, aj.cellareacode, aj.cellprefix, aj.cellsufix, aj.commentonclient FROM additional_cars as ad, ajax_client as aj WHERE ad.clientID='DG9642' group by ad.clientID
formObj.firstname.value = 'Mossa'; formObj.lastname.value = 'Barandao'; formObj.lastname1.value = 'Barandao'; formObj.address.value = '6572 Park Fifth Ave'; formObj.zipcode.value = '22384'; formObj.city.value = 'New York'; formObj.state.value = 'NY'; formObj.dhtmlgoodies_category.value = 'JAGUAR'; formObj.dhtmlgoodies_subcategory.value = 'X-TYPE'; formObj.caryear.value = '1988'; formObj.servicedesc.value = '\nOn June 30, 2011, 9:55 am\nWe performed the following:\nServiced Area: Exhaust \nService Details:\n\nexshaust\n\nMileage @ Service: 4334\n\n----End of Service Report----\n\nOn June 24, 2011, 2:48 pm\nWe performed the following:\nServiced Area: Driveshaft & Axle \nService Details:\n\ntest\n\nMileage @ Service: 5634\n\n----End of Service Report----\n\nOn June 22, 2011, 4:39 pm\nWe performed the following:\nServiced Area: Clutch , Driveshaft & Axle , Suspension \nService Details:\n\ntest\n\nMileage @ Service: 6377\n\n----End of Service Report----\n\nOn June 21, 2011, 12:31 pm\nWe performed the following:\nServiced Area: Air Intake , Brake , Oil Change , Engine Mechanical , Steering , Transmission \nService Details:\n\ntest\n\nMileage @ Service: 4343\n\n----End of Service Report----\n\nOn June 20, 2011, 11:05 am\nWe performed the following:\nServiced Area: Clutch , Engine Mechanical , Steering \nService Details:\n\ntest\n\nMileage @ Service: 4343\n\n----End of Service Report----\n\nOn April 18, 2011, 9:43 am\nWe performed the following:\nServiced Area: Driveshaft & Axle , Engine Mechanical \nService Details:\n\ntest\n\nMileage @ Service: 54\n\n----End of Service Report----\n\nOn April 16, 2011, 7:08 pm\nWe performed the following:\nServiced Area: Air Intake , Brake , Driveshaft & Axle , Engine Mechanical \nService Details:\n\nTest\n\nMileage @ Service: 657\n\n----End of Service Report----\n\nOn April 10, 2011, 6:04 pm\nWe performed the following:\nServiced Area: Exhaust \nService Details:\n\ntest\n\nMileage @ Service: 5433\n\n----End of Service Report----\n\nOn April 10, 2011, 5:00 pm\nWe performed the following:\nServiced Area: Cooling System , Engine Mechanical \nService Details:\n\ntest\n\nMileage @ Service: 54545\n\n----End of Service Report----\n\nOn April 10, 2011, 4:59 pm\nWe performed the following:\nServiced Area: Cooling System , Engine Mechanical \nService Details:\n\ntest\n\nMileage @ Service: 54545\n\n----End of Service Report----\n\nOn April 10, 2011, 4:51 pm\nWe performed the following:\n\nAdded Vehicle Profile to this account\n\n----End of Service Report----\n'; formObj.email.value = '[e-mail]'; formObj.currentmileage.value = ''; formObj.servicearea.value = ''; formObj.drivenmileage.value = ''; formObj.clientid.value = 'DG9642'; formObj.clientid3.value = 'DG9642'; formObj.displayname.value = 'Barandao,Mossa'; formObj.returnedClientId.value = 'DG9642';

[ddymacek]

can you drop a

print_r($res); or var_dump($res);

on line 37 and tell us results.
is there anything in the error message?
I know you've said before, but can you run a straight simple
select * from x
and see if you have anything in $res.
I'm trolling the web looking for similar issues. This one I have not dealt with before, and the lack of error messages and data is confusing.

[mbarandao]

can you drop a

print_r($res); or var_dump($res);

on line 37 and tell us results.
is there anything in the error message?
I know you've said before, but can you run a straight simple
select * from x
and see if you have anything in $res.
I'm trolling the web looking for similar issues. This one I have not dealt with before, and the lack of error messages and data is confusing.

Thanks for the reply; I have added

print_r($res);

, still the same outcome. Also, I'm not successful either with just a straight "select * from x".

Testing the code as suggested by urtrivedi, through a link ie:

page.php?getClientId=D932DG

gives this output:

MY SQL = SELECT ad.firstname, ad.lastname, ad.address, ad.city, ad.state, ad.zipcode, ad.dhtmlgoodies_category, ad.dhtmlgoodies_subcategory, ad.caryear, ad.customerid, ad.servicedesc, aj.email, aj.areacode, aj.phoneprefix, ad.clientID, aj.phonesufix, aj.cellareacode, aj.cellprefix, aj.cellsufix, aj.commentonclient FROM additional_cars as ad, ajax_client as aj WHERE ad.clientID='9932dg' and ad.customerid=aj.customerid group by ad.clientID
resource(2) of type (mysql result) formObj.firstname.value = 'Paul'; formObj.lastname.value = 'Johnson'; formObj.lastname1.value = 'Johnson'; formObj.address.value = '2040 W Virginia Avenue NE'; formObj.zipcode.value = '23873'; formObj.city.value = 'Washington'; formObj.state.value = 'DC'; formObj.dhtmlgoodies_category.value = 'FORD'; formObj.dhtmlgoodies_subcategory.value = 'E150 CLUB WAGON'; formObj.caryear.value = '2006'; formObj.servicedesc.value = '\nOn July 23, 2011, 7:19 am\nWe performed the following:\nServiced Area: Oil Change \nService Details:\n\ntest\n\nMileage @ Service: 4453\n\n----End of Service Report----\n\nOn July 22, 2011, 9:38 pm\nWe performed the following:\nServiced Area: Oil Change \nService Details:\n\noil change\n\nMileage @ Service: 2345\n\n----End of Service Report----\n\nOn July 7, 2011, 11:22 am\nWe performed the following:\n\nAdded Vehicle Profile to this account\n\n----End of Service Report----\n'; formObj.theDate.value = ''; formObj.email.value = '[e-mail]'; formObj.currentmileage.value = ''; formObj.servicearea.value = ''; formObj.drivenmileage.value = ''; formObj.clientid.value = '9932DG'; formObj.clientid3.value = '9932DG'; formObj.displayname.value = 'Johnson,Paul'; formObj.returnedClientId.value = '9932DG';

This indicates that the sql and the db connection is working fine; but the main issue is: I'm not able to get these variables into the receiving form --on which the ajax function is coded.

Again, here is the ajax:

var currentClientID = false;
function getClientData() {
	var clientId = $('#clientID').val().replace(/[^0-9|a-z|A-Z]/g,'');//here to incorporate the client id with numbers and letters
	if(clientId.length >0 && clientId!=currentClientID){//here to change the client id length
		$.ajax({
			url: 'getclient.php?getClientId=' + clientId,
			type: "GET",
			success: function showClientData(response) {
				var formObj = document.forms["clientForm"];
				eval(response);
				currentClientID = clientId;
			}
		});
	}
}

I'm suppose to get the variables in form field like:

<input name="firstname" id="firstname" size="20" maxlength="255" disabled="true">

Your thoughts!
Mossa

[urtrivedi]

Now you know that your script is giving code, so now stop echoing sql and other variables only you should echo javascript statement, because when u eval(response), it will expect javascript code not any other. so comment unwanted things.

also you upload your whole form page that is having ajax funtion here, I will give a try.

[mbarandao]

Thanks for the follow-up. the form page is a bit extensive --about 700 lines. I'll attach.

thanks...

[urtrivedi]

I am not able to trace, but I think you must separate clientdata calling from other part.
call it separately.

Also eval() is also not working. so you must find another way of setting html elements other than eval.
I have never used jquery -ajax so I am not able to debug it,
For ajax i use XAJAX libraries

[mbarandao]

A valiant effort; thank you! I have always had issue with this concept of performing a client-look up. I think it is time to construct a much more efficient and less problematic process.

So I'm moving away from it.

Again, I appreciate the many suggestions.

Mossa

[ddymacek]

Mossa, have you ever considered using classes. They are efficient and very useful. When I get a moment I will try to construct you a 'client' class, which you could use within you php scripts. I'm a big fan of classes and separating php logic code, from html code. I'll get back to you in a couple of hours.

[mbarandao]

Mossa, have you ever considered using classes. They are efficient and very useful. When I get a moment I will try to construct you a 'client' class, which you could use within you php scripts. I'm a big fan of classes and separating php logic code, from html code. I'll get back to you in a couple of hours.

Very kind of you , dymacek! I have not considered classes and my knowledge of it is somewhat lacking. I appreciate some guidance. I'll certainly begin looking into it as well.

Again, thank you for your continued assistance!
Mossa--

[ddymacek]

mbarandao I've figured out my ajax call.
What exactly are you trying to do?
What are you wanting to do with client data when looked up.
Are we supposed to automatically fill in all of the relevant form fields with client data?
you said....
"I'm suppose to get the variables in form field like:

PHP Syntax (Toggle Plain Text)

<input name="firstname" id="firstname" size="20" maxlength="255" disabled="true">

"
is the return client data supposed to be a new form, or are you wanting to populate your 'clientForm' form on client_retrieval_Mossa from data retrieved from getclient.php?

[mbarandao]

mbarandao I've figured out my ajax call.
What exactly are you trying to do?
What are you wanting to do with client data when looked up.
Are we supposed to automatically fill in all of the relevant form fields with client data?
you said....
"I'm suppose to get the variables in form field like:

PHP Syntax (Toggle Plain Text)

<input name="firstname" id="firstname" size="20" maxlength="255" disabled="true">

"
is the return client data supposed to be a new form, or are you wanting to populate your 'clientForm' form on client_retrieval_Mossa from data retrieved from getclient.php?

Excellent! At the moment, I simply want to display the data. This is currently what I'm able to do with data from a single table. I want to pull other information from different tables and simply display it on the client_retrieval_Mossa form.

I hope this answers your questions.

Mossa--

[leakbali]

Please trying this, update your code in line 30, 31:

$sql ="SELECT ad.firstname, ad.lastname, ad.address, ad.city, ad.state, ad.zipcode, ad.dhtmlgoodies_category, ad.dhtmlgoodies_subcategory, ad.caryear, ad.customerid, ad.servicedesc, aj.email, aj.areacode, aj.phoneprefix, ad.clientID,
aj.phonesufix, aj.cellareacode, aj.cellprefix, aj.cellsufix, aj.commentonclient FROM additional_cars as ad JOIN ajax_client as aj ON ad.xxx = aj.xxx WHERE ad.clientID='".$clientId."' group by ad.clientID";

Please change the code in red marked, according to your table structure. what field has relation from ajax_client table to addiotional_cars. Example, id_client.
So the code look like:

$sql ="SELECT ad.firstname, ad.lastname, ad.address, ad.city, ad.state, ad.zipcode, ad.dhtmlgoodies_category, ad.dhtmlgoodies_subcategory, ad.caryear, ad.customerid, ad.servicedesc, aj.email, aj.areacode, aj.phoneprefix, ad.clientID,
aj.phonesufix, aj.cellareacode, aj.cellprefix, aj.cellsufix, aj.commentonclient FROM additional_cars as ad JOIN ajax_client as aj ON ad.clientID = aj.clientID WHERE ad.clientID='".$clientId."' group by ad.clientID";

Good luck

[ddymacek]

I found this to work for me so far. I'm not sure exactly how you want to handle the data coming back from getclient.php so I am posting 2 solutions on how you can use the data.

<script type="text/javascript">
    var currentClientID = false;
    function getClientData() {
		alert('get client data  called!');
		var clientId = $('#clientID').val().replace(/[^0-9|a-z|A-Z]/g,'');//here to incorporate the client id with numbers and letters
		alert('get client data for ' + clientId + ' called!');
		if(clientId.length >0 && clientId!=currentClientID){//here to change the client id length	
			//alert("do get ");
			/*  original code...
			$.ajax({
				url: 'getclient.php?getClientId=' + clientId,
				type: "GET",
				success: function showClientData(response) {
					var formObj = document.forms["clientForm"];
					eval(response);
					currentClientID = clientId;
				}
			});
			*/
			// You can take out the alert(msg) call from this part...
			bodyContent = $.ajax({
				  url: "getclient.php",
				  global: false,
				  type: "POST",
				  //data: ({clientID : this.getAttribute('id')}),
				  data: ({clientID : clientId}),
				  dataType: "html",
				  async:false,
				  success: function(msg){
					 alert(msg);
				  }
			   }
			).responseText;		
			//document.getElementById("form_div").innerHTML=bodyContent;
			/********************************
			// this is where you have options and can decide exactly what output your getclient.php needs to return.
			// you could wrap it all up in preformatted html and then display stuff on the page by 
			// appending a div   ---- (i've named 'form_div' on the page...)
			// or in this case, I have added back in the eval() statement to populate existing form
			// fields on clientForm below.
			******** if you are returning javascript use eval, and you can comment out the add_div stuff below.
			******** if you are returning plaintext / table structure value use add_div function below and remove eval code.
			******** you can decide best how you want to handle the data.
			**********************************/
			var add_div = jQuery(document.createElement('div')).attr('id',clientId).text(bodyContent);	
			//alert("add to div = ");
			add_div.appendTo("#form_div"); 			
			//alert("call eval");
			eval(bodyContent);
			//alert('done eval');
		
		}
		alert('end content function');
    }
</script>




<form name="clientForm" id="clientForm" action="#" method="post" onsubmit="javascript:getClientData(); return false;"><!--ajax-client_lookup.html-->
<input type="text" name="clientID" id="clientID" value=""><br>
<table>
<td><label for="firstname">First name:</label></td>
<td><input name="firstname" id="firstname" size="20" maxlength="255" disabled="true"></td>
<td>&nbsp;</td>
</tr>
<tr>
<td><label for="lastname">Last name:</label></td>
<td>
<input name="lastname" id="lastname" size="20" maxlength="255" disabled="true"></td>
<input type="hidden" name="displayname" id="displayname" size="20" maxlength="255">
<input type="hidden" name="lastname1" id="lastname1" size="20" maxlength="255"></td>
<td>&nbsp;</td>
</tr>
<tr>
<td><label for="address">Address:</label></td>
<td><input name="address" id="address" size="20" maxlength="255" disabled="true"></td>
<td>&nbsp;</td>
</tr>
</table>
<input type="submit" name="submit" value="Submit">
</form>

<!--  this is where your returned data will display if you use the 'add_div' code from above.   -->
<div id="form_div"></div>

I had a strange occurrence when testing, once the response was complete, the page would reload because of the form onsubmit functionality. to work around this I added return false; to the call:

<form name="clientForm" id="clientForm" action="#" method="post" onsubmit="javascript:getClientData(); return false;">

getclient.php

<?php
/***********************
case 1, fill in current form data on client_retrieval page...
use the eval()
************************/
include_once('client.class.php');
$id = $_POST['clientID'];
//echo "id = " . $id . "<BR>";
$c = new client();
$client = $c->select_by_id($id);
//$client->print_to_screen();
// right now, I am returning javascript but not in js tags or anything.  you are 'already' in javascript when calling and retrieving data
// so you just have to have a 'plain text' javascript reference like below.  This return statement will fill in the firstname, lastname and address 
// on your clientForm.
// the following will be returned as 'html' or a plain text string.
?>
document.clientForm.firstname.value = "<?php echo $client->get_firstname(); ?>";
document.clientForm.lastname.value = "<?php echo $client->get_lastname(); ?>";
document.clientForm.address.value = "<?php echo $client->get_address(); ?>";
<?php 
// exit page to return response.
exit;
?>

one last note:

An alternative to setting js variables, you could build like an html table or something else to be returned to the page.
If that is the case do not use eval().  eval() is to evaluate javascript logic so if you have a table or other data that you want
return something like:
<?php
/***********************
case 2, return formatted html
************************/

include_once('client.class.php');
$id = $_POST['clientID'];
//echo "id = " . $id . "<BR>";
$c = new client();
$client = $c->select_by_id($id);
?>
<table>
<tr><td>firstname</td><td>lastname</td><td>address</td></tr>
<tr><td><?php echo $client->get_firstname(); ?></td><td><?php echo $client->get_lastname(); ?></td><td><?php echo $client->get_address(); ?></td></tr>
</table>
<?php 
// exit page to return response.
exit;
?>
And then use the append_div() call as opposed to eval()

[ddymacek]

I left an extra alert() or two in the code I tried to comment most of them out. Let me know what you think Mossa. It is functional and tested.