HelpSpot Help Desk Software | HelpSpot Blog | HelpSpot Support

How do the Livelookup scripts get the fields from HelpSpot?


#1

Hi,
I’m the process of evaluating HelpSpot and I have a problem/question using Livelookup to retrieve information from a MySQL database.

Basically, I’m using the file “live_lookup_mysql.php” as guideline but what it seems not to be working is the initial “LIVE LOOKUP LOGIC” where the script is supposed to receive the field(s) from HelpSpot. So, I fill in the customer id (1001) in the customer tab and then I hit the LiveLookup tab but the result from Livelookup is always empty (no customers found).

Now, if I just go to the script and hardcode the filter variable like:
$filter = ‘customer_id=1001’

Then, Livelookup works like a charm (of course, always retrieving the info for the user with id 1001)… my point here is just to prove that once the $filter variable is set to a correct value, the script interaction with the remote mysql db retrieving information works fine.

My Livelookup configuration:
Enabled: yes
Automatically run: no (I run it hitting the livelookup tab)
Lookup via: HTTP GET
Path to script: http://… (it’s set correctly since the script works when $filter is hardcoded)
XML char encod: iso-8859-1 (which matches the script)

Regards,
Beltran


#2

Hi Beltran,

It looks like your database uses a different column name than the example script. In the example script near the top you’ll see when the customer ID is passed in it’s doing id=XXXX where the X’s are customer ID. In what you posted you’re using customer_id=XXXX so that’s the issue.

You can either change the database column name to ID (if you’re just playing with it, if it’s a real database you probably don’t want to do that) or in the script change that logic so it uses customer_id= in the filter instead of just id=


#3

Hi Ian,
Thanks for your prompt answer.

Unfortunately I don’t think this is the problem since I changed the script accordingly to match my db columns… .and yet, it looks like the filter value is always ‘1=0’ no matter what I enter in Helpspot.

My db columns are: customer_id, first_name, last_name and email

And this is the scrip I’m using:

<?php /***** DATABASE CONNECTION VARIABLES *****/ $host = "mysql-host.nominum.com"; $user = "root"; $pswd = "ThisIsNotTheRealPassword"; $name = "test"; //database name /***** LIVE LOOKUP LOGIC *****/ if(!empty($_GET['customer_id'])){ $filter = 'customer_id='.mysql_real_escape_string($_GET['customer_id']); }elseif(!empty($_GET['email'])){ $filter = 'email="'.mysql_real_escape_string($_GET['email']).'"'; }elseif(!empty($_GET['last_name'])){ $filter = 'last_name="'.mysql_real_escape_string($_GET['last_name']).'"'; }elseif(!empty($_GET['first_name'])){ $filter = 'first_name="'.mysql_real_escape_string($_GET['first_name']).'"'; }else{ $filter = '1=0'; } /***** HARDCODE FILTER *****/ /* $filter = 'customer_id=1001'; */ /***** QUERY DATABASE *****/ $link=mysql_connect($host,$user,$pswd); if(!is_resource($link)){ echo 'Unable to connect to database'; }else{ mysql_select_db($name, $link); $result = mysql_query("SELECT * FROM contact WHERE ".$filter,$link); } /***** OUTPUT LIVE LOOKUP XML *****/ header('Content-type: text/xml'); echo '<?xml version="1.0" encoding="ISO-8859-1"?>';

?>

<?php while($row = mysql_fetch_assoc($result)): ?>

<customer_id><?php echo $row['customer_id'] ?></customer_id>
<first_name><?php echo $row['first_name'] ?></first_name>
<last_name><?php echo $row['last_name'] ?></last_name>
<?php echo $row['email'] ?>
<?php echo $row['phone'] ?>

<?php endwhile; ?>


#4

Hmm, perhaps we could try isolating this a bit more.

First, don’t test through HelpSpot. Go directly to the script in your browser, this way you can see what XML is output. So go to your_script.php?customer_id=1001 and see what XML that outputs. Perhaps there’s a PHP error or something that’s easily fixed that we’re not seeing right now because HS is just detecting there’s no customer results.

Also try some of the ones besides customer ID in that method and see if those work.


#5

Hi Ian,
I added some debug lines in the code and was able to find the problem. The script was receiving the correct info from helpspot but then the result of the mysql_real_escape_string() function was always empty… not sure why but definitively not a Helpspot issue :slight_smile:

In any case, I just modified the logic of the script assigning all the possible parsed information to variables like:

$cust_id_helpspot = $_GET['customer_id'];
$first_name_helpspot = $_GET['first_name'];
...etc

…and then building the query with those variables:

$my_query = "SELECT * FROM customer_info 
	WHERE CompanyId = ".$cust_id_helpspot.
	"AND FirstName = ".$first_name_helpspot.
	...etc";

$result = mysql_query($my_query);

So now LivesLookup is working like a charm!!

Thanks Ian.

Regards,
Beltran


#6

Ah, great! Yes, that’s odd never seen that not work before.

Well glad you got it working, let us know if there’s anything else we can do!


#7

Hello Ian,
Just as an FYI (closing the loop).

In the script above, mysql_real_escape_string() was always returning FALSE because it was used before the MySQL connection was established.
So, right before the “LIVE LOOKUP LOGIC” lines you should establish the MySQL connection:

    <?php
    /***** DATABASE CONNECTION VARIABLES *****/
    $host = "mysql-host.nominum.com";
    $user = "root";
    $pswd = "ThisIsNotTheRealPassword";
    $name = "test"; //database name

    /***** DATABASE CONNECTION *****/
    $link=mysql_connect($host,$user,$pswd);
    if(!is_resource($link)){
    echo 'Unable to connect to database';
    }else {
    mysql_select_db($name);
    }
    
    /***** LIVE LOOKUP LOGIC *****/
    if(!empty($_GET['customer_id'])){
    $filter = 'customer_id='.mysql_real_escape_string($_GET['customer_id']); 
    }elseif(!empty($_GET['email'])){
    $filter = 'email="'.mysql_real_escape_string($_GET['email']).'"';
    ....
    ....
    ....
    /***** QUERY DATABASE *****/
    $result = mysql_query("SELECT * FROM contact WHERE ".$filter,$link);
   
    /***** OUTPUT LIVE LOOKUP XML *****/
    header('Content-type: text/xml');
    ....
    ....
    ....

Regards,
Beltran


#8

Huh, didn’t realize that was a requirement. Perhaps in certain PHP versions that acts differently. We’ll move it in the example.