I need a little code help

[Jessie K. Jepsen]

I have this snippet of code, I need some help with.

 

/*

* Returns a list of customers for an autofill box

*

* @access public

* @param string $query - A search query to search the database for

*

* @return void

*/

public function ajaxCustomerList($query)

{

if ( ! $query || $query == '' || strlen($query) < 1)

die();

 

$sql = '

SELECT `id_customer` ,

`firstname` ,

`lastname` ,

`email`

FROM `' . _DB_PREFIX_ . 'customer`

WHERE (

`firstname` LIKE "%' . pSQL($query) . '%"

OR `lastname` LIKE "%' . pSQL($query) . '%"

OR CONCAT(`firstname`, " ", `lastname`) LIKE "%' . pSQL($query) . '%"

OR `email` LIKE "%' . pSQL($query) . '%"

)';

 

$customers = Db::getInstance()->ExecuteS($sql);

 

if ($customers && sizeof($customers))

foreach ($customers as $customer)

printf("%s %s (%s) | %d\n", $customer['firstname'], $customer['lastname'], $customer['email'], (int)$customer['id_customer']);

}

 

-----------------------------------------------------------------------------------------------------------------

 

As you can see, it pulls the customer based on a search for the customers first name, lastname, or email from the CUSTOMER table in the database. I am in need of making this code so I can also search for the customer by postcode "zip code" under the ADDRESS table in the database as well.

 

I have been trying for days now to figure out how to make it search both tables instead of just the one, but cant seem to figure it out.

 

If you know how please edit my code and re-post it to me and I will try it, thank you so much in advance!

 

-K

[CartExpert.net]

Hi.

 

You need to LEFT JOIN the address table in the SQL query.

 

Regards.

 

Robin.

 

The CartExpert Team

[Jessie K. Jepsen]

Ok, Thank you.

 

I will google how to do that.

 

I appreciate your help!

[Jessie K. Jepsen]

Sorry, I am having some trouble figuring this out as my skills are limited and I am still learning..

 

Do you think you could post me a sample using my code above so I can get a visual idea of what I need to do.. ?

 

I would greatly appreciate it..

[Jessie K. Jepsen]

Ok, This is what I have come up with.. Does this look right to you?

 

-------------------------------------------------------------------------------------------------------------------------------------------

/*

* Returns a list of customers for an autofill box

*

* @access public

* @param string $query - A search query to search the database for

*

* @return void

*/

public function ajaxCustomerList($query)

{

if ( ! $query || $query == '' || strlen($query) < 1)

die();

 

$sql = '

SELECT p.`postcode` ,

pl.`firstname` ,

pl.`lastname` ,

p.`address1` ,

p.`city`

FROM `' . _DB_PREFIX_ . 'address` p

JOIN LEFT `' . _DB_PREFIX_ . 'customer` pl

WHERE (

pl.`firstname` LIKE "%' . pSQL($query) . '%"

OR pl.`lastname` LIKE "%' . pSQL($query) . '%"

OR CONCAT(`pl.firstname`, " ", `pl.lastname`) LIKE "%' . pSQL($query) . '%"

OR pl.`address1` LIKE "%' . pSQL($query) . '%"

OR p.`postcode` LIKE "%' . pSQL($query) . '%"

OR p.`city` LIKE "%' . pSQL($query) . '%"

)';

 

$customers = Db::getInstance()->ExecuteS($sql);

 

if ($customers && sizeof($customers))

foreach ($customers as $customer)

printf("%s %s [%s, %s] | %d\n", $customer['firstname'], $customer['lastname'], $customer['address1'], $customer['city'], (int)$customer['postcode']);

}

[CartExpert.net]

Ok, This is what I have come up with.. Does this look right to you?

 

-------------------------------------------------------------------------------------------------------------------------------------------

/*

* Returns a list of customers for an autofill box

*

* @access public

* @param string $query - A search query to search the database for

*

* @return void

*/

public function ajaxCustomerList($query)

{

if ( ! $query || $query == '' || strlen($query) < 1)

die();

 

$sql = '

SELECT p.`postcode` ,

pl.`firstname` ,

pl.`lastname` ,

p.`address1` ,

p.`city`

FROM `' . _DB_PREFIX_ . 'address` p

JOIN LEFT `' . _DB_PREFIX_ . 'customer` pl

WHERE (

pl.`firstname` LIKE "%' . pSQL($query) . '%"

OR pl.`lastname` LIKE "%' . pSQL($query) . '%"

OR CONCAT(`pl.firstname`, " ", `pl.lastname`) LIKE "%' . pSQL($query) . '%"

OR pl.`address1` LIKE "%' . pSQL($query) . '%"

OR p.`postcode` LIKE "%' . pSQL($query) . '%"

OR p.`city` LIKE "%' . pSQL($query) . '%"

)';

 

$customers = Db::getInstance()->ExecuteS($sql);

 

if ($customers && sizeof($customers))

foreach ($customers as $customer)

printf("%s %s [%s, %s] | %d\n", $customer['firstname'], $customer['lastname'], $customer['address1'], $customer['city'], (int)$customer['postcode']);

}

 

That's wrong.

 

Correct example:

 

SELECT c.id_customer, c.firstname, c.lastname, c.email, a.postcode FROM ps_customer c LEFT JOIN ps_address a ON (c.id_customer = a.id_customer) WHERE (c.firstname LIKE "%75000%" OR c.lastname LIKE "%75000%" OR CONCAT(c.firstname, " ", c.lastname) LIKE "%75000%" OR c.email LIKE  "%75000%" OR a.postcode LIKE "%75000%")

 

Regards.

 

Robin.

 

The CartExpert Team

[Jessie K. Jepsen]

Thank you for your help,

 

I tried that code but it still seems to be not working..

I changed it around a little based on some code sites I found on google and still cant get it working..

 

Now whats happening when I paste in the code you gave me is " When I type in the search box for the zip code or even the person lastname or firstname or email, nothing comes up" strange.

 

I must still be doing something wrong..

 

/*

* Returns a list of customers for an autofill box

*

* @access public

* @param string $query - A search query to search the database for

*

* @return void

*/

public function ajaxCustomerList($query)

{

if ( ! $query || $query == '' || strlen($query) < 1)

die();

 

$sql = '

SELECT

x.`firstname` ,

x.`lastname` ,

x.`phone` ,

xl.`postcode`

FROM `' . _DB_PREFIX_ . 'customer` x

LEFT JOIN `' . _DB_PREFIX_ . 'address` xl

ON (

xl.postcode = x.postcode

)

WHERE (xl.postcode LIKE "%' . pSQL($query) . '%" OR x.firstname LIKE "%' . pSQL($query) . '%" x.lastname LIKE "%' . pSQL($query) . '%" x.phone LIKE "%' . pSQL($query) . '%")

AND xl.id_lang = ' . (int)($id_lang);

 

$customers = Db::getInstance()->ExecuteS($sql);

 

if ($customers && sizeof($customers))

foreach ($customers as $customer)

printf("%s %s (%s) | %d\n", $customer['lastname'], $customer['firstname'], $customer['phone'], (int)$customer['postcode']);

}

Edited October 5, 2012 by EMPeiTQ (see edit history)