Query works local server but does not work www server

[phprock]

	$q=addslashes($q);
	$search = '';
	$the_array = explode(' ', $q);
	$i = 0;

foreach( $the_array AS $t ){
	if( $i == 0 ){
	$search .= " '%$t%' ";
	$i = 1;
	}else{
	$search .= " OR org_name LIKE '%$t%' ";
	}
 }



if($searchin==1){
		$cond3=" OR org_desc LIKE $search";
}

$c="INNER JOIN ".$prefix."_city ON (".$prefix."_organization.phyadd_city = nuke_city.city_id)";
$c.=" INNER JOIN ".$prefix."_volunteer_opor   ON(org_id=op_contact_org)";

$query = "SELECT org_name, city_name, op_id,op_duty";
$query.=" FROM  ".$prefix."_organization  $c   WHERE (org_name  like $search $cond3)";

$resultc=$db->sql_query("$query");
$num_record=$db->sql_numrows($resultc);

if i remove $c it works in the www server but gives me duplicate records.

MY question is there anything to do with JOIN Query. why it works fine my local server but does not work on www server. So i think something with the join Query..

Any help would be appreciated.

[rachel2004]

What versions of PHP and MySQL (if that's the db that you're using) are running locally and on the remote server?

[phprock]

www server
MySQL 4.0.18

local server
MySQL 4.1.0-alpha-max-nt

[rachel2004]

This may not answer your question, but is there a reason why you're not using the dot operator (in MySQL) as follows:

tableName.columnName

for ALL portions of your query?

Your second ON clause in $c is stating only the column name which can be problematic in multiple table joins.

Post back with your results after you fix that.

[phprock]

Again it worked fine in my local machine but does not work in the www server.

	$q=addslashes($q);
	$search = '';
	$the_array = explode(' ', $q);
	$i = 0;

foreach( $the_array AS $t ){
	if( $i == 0 ){
	$search .= " '%$t%' ";
	$i = 1;
	}else{
	$search .= " OR ".$prefix."_organizatio.org_name LIKE '%$t%' ";
	}
 }



if($searchin==1){
		$cond3=" OR ".$prefix."_organizatio.org_desc LIKE $search";
}

$c="INNER JOIN ".$prefix."_city ON (".$prefix."_organization.phyadd_city = nuke_city.city_id)";
$c.=" INNER JOIN ".$prefix."_volunteer_opor   ON(".$prefix."_organization.org_id=".$prefix."_volunteer_opor.op_contact_org)";

$query = "SELECT ".$prefix."_organization.org_name, ".$prefix."_city.city_name, ".$prefix."_volunteer_opor.op_id,".$prefix."_volunteer_opor.op_duty";
$query.=" FROM  ".$prefix."_organization  $c   WHERE (".$prefix."_organization.org_name  like $search $cond3)";
$resultc=$db->sql_query("$query");

[phprock]

When i search for word niagra.
My query output is like this

SELECT nuke_organization.org_name, nuke_city.city_name, nuke_volunteer_opor.op_id,nuke_volunteer_opor.op_duty 
FROM nuke_organization 
INNER JOIN nuke_city
ON (nuke_organization.phyadd_city = nuke_city.city_id) 
INNER JOIN 
nuke_volunteer_opor 
ON(nuke_organization.org_id=nuke_volunteer_opor.op_contact_org) 
WHERE 
(nuke_organization.org_name like '%Niagara%' OR nuke_organization.org_desc LIKE '%Niagara%' )

[nemonoman]

You can always try this the old fashioned way:

SELECT nuke_organization.org_name, nuke_city.city_name, nuke_volunteer_opor.op_id,nuke_volunteer_opor.op_duty
FROM nuke_organization , nuke_city, nuke_volunteer_opor
WHERE nuke_organization.phyadd_city = nuke_city.city_id
AND nuke_organization.org_id=nuke_volunteer_opor.op_contact_org
AND
(nuke_organization.org_name like '%Niagara%'
OR nuke_organization.org_desc LIKE '%Niagara%' )

PS: IMPORTANT: What does 'worked' mean...as in this 'work'ed on my local server but didn't 'work' on www?

If the query is executing without a mysql_error(), but you are sad because no rows are being returned, then the problem is with your data on the www machine.

If you've verified that the query executes,
you'll have explore the rows output on both machines for variance.

Compare:

Query 1
SELECT nuke_organization.org_name, nuke_organization.phyadd_city , nuke_organization.org_id
FROM nuke_organization
WHERE (nuke_organization.org_name like '%Niagara%'
OR nuke_organization.org_desc LIKE '%Niagara%' )

that should return 1 or more org_ids

SELECT nuke_volunteer_opor.op_id,nuke_volunteer_opor.op_duty , nuke_volunteer_opor.op_contact_org
FROM nuke_volunteer_opor
WHERE nuke_volunteer_opor.op_contact_org IN ($ReturnedOrgID1, $ReturnedOrgID2...)

Query 1 should also return 1 or more phyadd_city

SELECT nuke_city.city_name
FROM nuke_city
WHERE nuke_city.city_id IN ($ReturnedCITYID1, $ReturnedCITYID2...)

My guess is that this is a DATA problem, not a query problem