Page 2 of 2

Posted: Sun Jul 09, 2006 7:15 pm
by pilsy2001

Code: Select all

sql ="SELECT a.entry_postcode, a.customers_id, f.store_postcode, f.store_email
        FROM address_book a
        INNER JOIN franchises f
        ON a.entry_postcode = f.store_postcode
        WHERE a.customers_id = '$customerid'"
This code here works perfectly!!

Now i just need to figure out the code on how to get my $customerid variable out of this array (code posted below)...

Code: Select all

$this->customer = array('id' => $order['customers_id'],
                              'name' => $order['customers_name'],
                              'company' => $order['customers_company'],
                              'street_address' => $order['customers_street_address'],
                              'suburb' => $order['customers_suburb'],
                              'city' => $order['customers_city'],
                              'postcode' => $order['customers_postcode'],
                              'state' => $order['customers_state'],
                              'country' => $order['customers_country'],
                              'format_id' => $order['customers_address_format_id'],
                              'telephone' => $order['customers_telephone'],
                              'email_address' => $order['customers_email_address']);
will this work?
$customerid = $order['customers_id']

Thanks for all you help!

Posted: Sun Jul 09, 2006 8:29 pm
by dull1554
that should work or you could do:

Code: Select all

$customerid = $customer['id'];//$customer is the array you are talking about pulling the data out of i think.

Posted: Sun Jul 09, 2006 8:52 pm
by pilsy2001
done, done and done :)

Edited: im an idiot
Thanks again, :lol:

Posted: Mon Jul 10, 2006 7:36 pm
by pilsy2001

Code: Select all

// *****************************************************//
// Modification for Post Code Script for Franchises BOF //
//******************************************************//
$query_postcode = "SELECT a.entry_postcode, a.customers_id, f.store_postcode, f.store_email
        		   FROM address_book a
       			   INNER JOIN franchises f
        		   ON a.entry_postcode = f.store_postcode
                   WHERE a.customers_id = '$customer_id'"

// If Statement controling the actual function as to when the $query_postcode string is executed and what action is taken
$check_postcode = "true";

if ( $check_postcode == 'true' && $customer_id !=' ')
{
tep_db_query($query_postcode);
$storeemail = "$query_postcode['store_email']()"
$message =  “Query for Postcode Sucessfully Executed!
echo $message”
}
else ( $customer_id == ' ' )
{
$message = “Query for Postcode Recignition Failed!!”;
echo $message
}

// send email to Franchise
if (SEND_EXTRA_ORDER_EMAILS_TO = '') { 
// BEGIN - little EMAIL modification, ASKMURPHY 19-aug-2005 (also needed in paypal module PayPal_Shopping_Cart_IPN -> order.class.php)
//  tep_mail('', SEND_EXTRA_ORDER_EMAILS_TO, EMAIL_TEXT_SUBJECT, $email_order, STORE_OWNER, $storeemail);
  $newsubject .= EMAIL_TEXT_SUBJECT . ' (' . $order->customer['firstname'] . ' ' . $order->customer['lastname'] . ')';
  tep_mail('', $store_email, $newsubject, $email_order, STORE_OWNER, STORE_OWNER_EMAIL_ADDRESS);
// END - little EMAIL modification, ASKMURPHY 19-aug-2005 

// *****************************************************//
// Modification for Post Code Script for Franchises EOF //
//******************************************************//

Im trying to get the final section of the script working now and i cannot seem to get it working either :(
the if statement needs to perform the following,

Code: Select all

"If a.entry_postcode and f.store_postcode are equal then f.store_email is selected and stored in the varaible $storeemail"

Code: Select all

"if a.entry_postcode and f.store_postcode are 'not' equal then the $storeemail variable is defined with the default email pilsy2001@yahoo.com"
else, if the script did not accomplish either task echo $message (this section i can do)

Code: Select all

elseif $query_postcode does "not" find a matching postcode in the two tables then the default email is selected and stored in $storeemail (pilsy2001@yahoo.com)
Any help would be much appreciated, its getting to a point where i am willing to pay someone to finish this off if i cant get it finished soon :'( :cry:

Posted: Mon Jul 10, 2006 8:05 pm
by RobertGonzalez
Can I offer a small bit of suggestion?

Code: Select all

<?php
// *****************************************************//
// Modification for Post Code Script for Franchises BOF //
//******************************************************//
$customer_id = 0;

/*
 * TASK FOR YOU - process setting the $customer_id to what was sent
 */

// Now use the customer id value to see if there is a query to run
if ($customer_id)
{
	$store_email = 'pilsy2001@yahoo.com';
	
	$sql = "SELECT a.entry_postcode, a.customers_id, f.store_postcode, f.store_email
			FROM address_book a
			INNER JOIN franchises f
			ON a.entry_postcode = f.store_postcode
			WHERE a.customers_id = '$customer_id'";
	if (!$result = mysql_query($sql))
	{
		die('Could not get the address and franchise information: ' . mysql_error());
	}
	
	while ($row = mysql_fetch_array($result))
	{
		if ($row['entry_postcode'] == $row['store_postcode'])
		{
			$store_email = $row['store_email'];
		}
	}

	// send email to Franchise
	if (SEND_EXTRA_ORDER_EMAILS_TO = '') 
	{ 
		$mailsubject = EMAIL_TEXT_SUBJECT . ' (' . $order->customer['firstname'] . ' ' . $order->customer['lastname'] . ')';
		tep_mail('', $store_email, $mailsubject, $email_order, STORE_OWNER, STORE_OWNER_EMAIL_ADDRESS);
	}
}
// *****************************************************//
// Modification for Post Code Script for Franchises EOF //
//******************************************************//
?>

Posted: Mon Jul 10, 2006 10:35 pm
by pilsy2001
That worked perfectly, thanks everah! :D

Posted: Mon Jul 10, 2006 11:57 pm
by RobertGonzalez
Glad I could be of help.

Posted: Thu Jul 20, 2006 12:02 am
by pilsy2001
:roll: hey everah, i have stumbled onto another problem...

I store all the postcodes in one row and coloum on the database for each store, for instance it would read

Storename | postcodes
--------------------------
Store1 | 4432, 4353, 3423, 2623, 2363, 2352 ...


I store all the postcodes in that database in that format, so im thinking i might need to use the like statement?
If i do could you provide me with an example?

Thanks :wink:

Posted: Thu Jul 20, 2006 1:35 am
by RobertGonzalez
You can use like, but I would seriously consider changing your table layout to take advantage of the relationship capabilites. For a simple like query, you would do something like this...

Code: Select all

SELECT * FROM `mytable` WHERE `myfield` LIKE '%searchstring%';
Just change the table, field(s) and searchstring to what you need them to be.

Posted: Wed Aug 23, 2006 10:58 pm
by pilsy2001
Everah wrote:You can use like, but I would seriously consider changing your table layout to take advantage of the relationship capabilites....
How would you suggest improving the database, there needs to be one row in the database that holds all the information for each store... the only other way of getting around this (to my understanding) is to create a new row for each postcode a store has assigned to them... which would mean if 1 store had 50 postcodes in its range i would need 50 rows!?

then again i could be wrong :roll: :oops:

at the moment i am having trouble with this SQL Statement,

Code: Select all

$sql = "SELECT a.entry_postcode, a.customers_id, f.store_postcode, f.store_email
                        FROM address_book a
                        INNER JOIN franchises f
                        ON a.customers_id = '$customers_id'
                        WHERE f.store_postcode LIKE '%a.entry_postcode%'"
it will not return any results at all, just an empty resultset... :evil:

however if i change

Code: Select all

'%a.entry_postcode%'
to an actual postcode stored in the database it works! ?

Posted: Wed Aug 23, 2006 11:02 pm
by feyd
Normalization would dictate separation of the values into their own records.

Posted: Thu Aug 24, 2006 12:24 am
by RobertGonzalez
LIKE expects a string I think. You are probably going to need to change that comparison.

Posted: Thu Aug 24, 2006 8:00 pm
by pilsy2001
Everah wrote:LIKE expects a string I think. You are probably going to need to change that comparison.
so i have to create a variable with a Search String in it an then link it to the query like '%$variable%' ??
What would the search string have to roughly look like?

Thanks

Posted: Thu Aug 24, 2006 11:42 pm
by RobertGonzalez
Yes, try that.