www.webdeveloper.com
Results 1 to 3 of 3

Thread: SQL brings up incorrect results on AJAX call

  1. #1
    Join Date
    Jul 2008
    Location
    Bristol
    Posts
    76

    SQL brings up incorrect results on AJAX call

    Hi everyone

    I'm getting a bit confused with a SQL call which pulls up results for a search on a client CMS - basically the client has a list of products which can either be "live" or "recycled" - recycled being temporary deleted and should NOT show up in search results.

    I have a function which triggers when the search form is posted, which happens upon keyup. My SQL call in this function is as follows ('kw' being the keyword typed in the search by the client):

    Code:
    $data_sql = "
    	SELECT 
    	p.id,
    	p.name, 
    	p.last_modified,
    	CONCAT(u.first_name, ' ', u.last_name) AS user
    	FROM products AS p
    	INNER JOIN users AS u ON p.modified_by=u.id  
    	WHERE p.status='live' AND p.status NOT IN ('recycled') ";
    	
    	if($kw) {
    		$data_sql .= " AND (p.name LIKE '%" . mysql_real_escape_string($kw) . "%') ";
    	}	
    	
    	if($categories_id != 0) {
    		$data_sql .= " AND p.id IN (SELECT products_id FROM products_categories_pivot WHERE categories_id = " . intval($categories_id) . ")";
    	}
    	
    	$data_sql .= " ORDER BY p.position ASC";
    		
    	$data_rs = mysql_query($data_sql);
    If the client hits return the results come back fine, but the AJAX call upon keyup returns all products, live or recycled. I even printed out the SQL to the screen on keyup and ran it direct in the database - it returns the correct results.

    Can anyone shed any light on this? It's doing my head in because I cannot see why the AJAX method of posting would pull up different results....

    Unfortunately I cannot give a link to the CMS as it is password protected for the client only, but I'm hoping you get the idea.

    Thanks in advance
    Michael

  2. #2
    Join Date
    Sep 2010
    Posts
    160
    Hmm, very odd yes.

    This line is redundant though, doubt it would be the one causing any problems but still:

    Code:
    WHERE p.status='live' AND p.status NOT IN ('recycled') ";
    If a row can either have a status of 'live' or 'recycled' then you can remove the AND p.status NOT IN ('recycled') as it will not do anything extra to your query (also one less thing to go wrong).

    Archie

  3. #3
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    Try having the ajax return the query it builds.

    Copy and paste that query into a terminal with mysql open... What results does it return?

    Try fixing the query up.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles