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,

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 :'(

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!

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

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

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
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...
however if i change
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.