Jquery PHP Script - At a loss.

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 499 Views - Last Post: 08 July 2011 - 12:31 AM Rate Topic: -----

#1 E_Geek  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 45
  • View blog
  • Posts: 236
  • Joined: 20-February 11

Jquery PHP Script - At a loss.

Posted 07 July 2011 - 12:43 PM

Hi,

I'm attempting to implement a jquery autocomplete form into an application, but have come up stumps.

For the autocomplete form, I need to my JSON search results as so:
[
    {"id":"1","name":"Sarah"},
    {"id":"24","name":"Gregory"},
    ...
]



I'm doing so by gathering the ID and Name from a DB table. Using the following code:

<?php
	//Includes Ommited, Only included class files.

	//Instaniate classes
	$db = new DbConnection();
	$social = new SocialNet();
	
	//Connect db
	$db->connect();
	
	$name = 'name';
	$q = '%' . $_GET['q'] . '%';
	try{
		//Custom PS to get names containing $param
		$ps = $db->link->prepare("SELECT friend_id, name FROM autocomplete WHERE user_id = :uid AND :name LIKE :query");
		//Bind Values
		$ps->bindParam(':uid', $_SESSION['user_id'], PDO::PARAM_STR);
		$ps->bindParam(':name', $name, PDO::PARAM_STR);
		$ps->bindParam(':query', $q, PDO::PARAM_STR);
	
		//Execute and fetch results
		$ps->execute();
		
	} catch(Exception $e){
		echo $e;
	}
	
	$arr = array();
	
	//Collect the results
	while($friend = $ps->fetch()) {
		$arr[] = array('id' => $friend['friend_id'], 'name' => $friend['name']);
	}
	
	//JSON-encode the response
	$json_response = json_encode($arr);
	
	// Optionally: Wrap the response in a callback function for JSONP cross-domain support
	if($_GET["callback"]) {
		$json_response = $_GET["callback"] . "(" . $json_response . ")";
	}

	// Return the response
	echo $json_response;
	
	//Close db
	$db->disconnect();
	
?>



The JQuery part of the form works, only it doesn't show any results. I tried dumping $arr after hardcoding a $q value i knew worked from running sql in phpmyadmin, and I got an empty array: array(0) { } []

Can anyone see where I'm going wrong?

Is This A Good Question/Topic? 0
  • +

Replies To: Jquery PHP Script - At a loss.

#2 maniacalsounds  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 117
  • View blog
  • Posts: 469
  • Joined: 27-June 10

Re: Jquery PHP Script - At a loss.

Posted 07 July 2011 - 12:45 PM

Perhaps your database connection isn't working? Can you run other queries via $db from the DBConnection class?
Was This Post Helpful? 0
  • +
  • -

#3 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3392
  • View blog
  • Posts: 9,586
  • Joined: 08-June 10

Re: Jquery PHP Script - At a loss.

Posted 07 July 2011 - 12:52 PM

there could be a database error, unless you manually enabled PDOs error reporting (which is disabled by default except for the DB connection).

to enable the errors add directly after the PDO instantiation
// $pdo = new PDO();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


I could imaging line 17 to be a problem.

besides that, if I want an array from PDO, Id use PDOStatement->fetchAll().
Was This Post Helpful? 1
  • +
  • -

#4 E_Geek  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 45
  • View blog
  • Posts: 236
  • Joined: 20-February 11

Re: Jquery PHP Script - At a loss.

Posted 07 July 2011 - 01:14 PM

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);



Sorry, I do have this set on opening the connection.

I've also tried hard coding the id to a known ID value, but still am getting an empty JSON_response. As fetchall returns an array of arrays, I've tried json_encode on the fetchall value, but still to no avail.

The DB connection is in use in multiple different areas and works without error as well.

This post has been edited by E_Geek: 07 July 2011 - 01:15 PM

Was This Post Helpful? 0
  • +
  • -

#5 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3392
  • View blog
  • Posts: 9,586
  • Joined: 08-June 10

Re: Jquery PHP Script - At a loss.

Posted 07 July 2011 - 01:15 PM

View PostE_Geek, on 07 July 2011 - 10:14 PM, said:

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);



Sorry, I do have this set on opening the connection.

yes. if you like, you can also pass it as option to the PDO object.
Was This Post Helpful? 1
  • +
  • -

#6 E_Geek  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 45
  • View blog
  • Posts: 236
  • Joined: 20-February 11

Re: Jquery PHP Script - At a loss.

Posted 07 July 2011 - 01:24 PM

Not reporting any errors.

So you know, the DB structure and row i'm working with is as so:

id - user_id - friend_id - name

1 - 8 - 8 - Adam Test
Was This Post Helpful? 0
  • +
  • -

#7 codeprada  Icon User is offline

  • Changed Man With Different Priorities
  • member icon

Reputation: 943
  • View blog
  • Posts: 2,353
  • Joined: 15-February 11

Re: Jquery PHP Script - At a loss.

Posted 07 July 2011 - 01:24 PM

Is your user id really of type string?
Was This Post Helpful? 1
  • +
  • -

#8 E_Geek  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 45
  • View blog
  • Posts: 236
  • Joined: 20-February 11

Re: Jquery PHP Script - At a loss.

Posted 07 July 2011 - 01:26 PM

You mean stored in the DB? It's stord as Int(11)
Was This Post Helpful? 0
  • +
  • -

#9 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 5954
  • View blog
  • Posts: 23,215
  • Joined: 23-August 08

Re: Jquery PHP Script - At a loss.

Posted 07 July 2011 - 01:29 PM

Well then why this?
$ps->bindParam(':uid', $_SESSION['user_id'], PDO::PARAM_STR);
?
Was This Post Helpful? 1
  • +
  • -

#10 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3392
  • View blog
  • Posts: 9,586
  • Joined: 08-June 10

Re: Jquery PHP Script - At a loss.

Posted 07 July 2011 - 01:29 PM

that could pose a problem when you try to pass it as (string) "8" instead of (int) 8.
Was This Post Helpful? 1
  • +
  • -

#11 E_Geek  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 45
  • View blog
  • Posts: 236
  • Joined: 20-February 11

Re: Jquery PHP Script - At a loss.

Posted 07 July 2011 - 01:35 PM

The stupid wonders of copy and paste :S I've changed this now :)

Although I'm still not getting any results returned.
Was This Post Helpful? 0
  • +
  • -

#12 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 5954
  • View blog
  • Posts: 23,215
  • Joined: 23-August 08

Re: Jquery PHP Script - At a loss.

Posted 07 July 2011 - 01:51 PM

I don't believe you can substitute a table name via a prepared statement.
Was This Post Helpful? 0
  • +
  • -

#13 E_Geek  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 45
  • View blog
  • Posts: 236
  • Joined: 20-February 11

Re: Jquery PHP Script - At a loss.

Posted 07 July 2011 - 01:59 PM

$ps = $db->link->prepare("SELECT friend_id, name FROM autocomplete WHERE user_id = :uid AND :name REGEXP :query");


I'm placing the table name directly into the query, not biding a value to it, if that's what you meant.

If I run:
SELECT friend_id, name FROM autocomplete WHERE user_id = 8 AND name REGEXP 'adam';



Via phpmyadmin, it pulls my result without any errors.
Was This Post Helpful? 0
  • +
  • -

#14 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 5954
  • View blog
  • Posts: 23,215
  • Joined: 23-August 08

Re: Jquery PHP Script - At a loss.

Posted 07 July 2011 - 02:23 PM

Quote

I'm placing the table name directly into the query, not biding a value to it, if that's what you meant.

Huh?

$ps = $db->link->prepare("SELECT friend_id, name FROM autocomplete WHERE user_id = :uid AND :name LIKE :query");
...
$ps->bindParam(':name', $name, PDO::PARAM_STR);


Sure looks like you're binding.

But I'm not a PDO expert, so I may be all wet here.
Was This Post Helpful? 1
  • +
  • -

#15 codeprada  Icon User is offline

  • Changed Man With Different Priorities
  • member icon

Reputation: 943
  • View blog
  • Posts: 2,353
  • Joined: 15-February 11

Re: Jquery PHP Script - At a loss.

Posted 07 July 2011 - 05:14 PM

JackOfAllTrades is saying you can't bind column names.

If you're placing it directly in there it should be $name and not :name
Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2