Postcode Script - Help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

pilsy2001
Forum Newbie
Posts: 16
Joined: Tue Jun 20, 2006 8:00 pm

Post 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!
User avatar
dull1554
Forum Regular
Posts: 680
Joined: Sat Nov 22, 2003 11:26 am
Location: 42:21:35.359N, 76:02:20.688W

Post 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.
pilsy2001
Forum Newbie
Posts: 16
Joined: Tue Jun 20, 2006 8:00 pm

Post by pilsy2001 »

done, done and done :)

Edited: im an idiot
Thanks again, :lol:
Last edited by pilsy2001 on Mon Jul 10, 2006 7:37 pm, edited 1 time in total.
pilsy2001
Forum Newbie
Posts: 16
Joined: Tue Jun 20, 2006 8:00 pm

Post 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:
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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 //
//******************************************************//
?>
pilsy2001
Forum Newbie
Posts: 16
Joined: Tue Jun 20, 2006 8:00 pm

Post by pilsy2001 »

That worked perfectly, thanks everah! :D
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Glad I could be of help.
pilsy2001
Forum Newbie
Posts: 16
Joined: Tue Jun 20, 2006 8:00 pm

Post 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:
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
pilsy2001
Forum Newbie
Posts: 16
Joined: Tue Jun 20, 2006 8:00 pm

Post 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! ?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Normalization would dictate separation of the values into their own records.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

LIKE expects a string I think. You are probably going to need to change that comparison.
pilsy2001
Forum Newbie
Posts: 16
Joined: Tue Jun 20, 2006 8:00 pm

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Yes, try that.
Post Reply