Page 1 of 2
Postcode Script - Help
Posted: Mon Jun 26, 2006 12:51 am
by pilsy2001
Pimptastic | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Hi guys,
im having a few problems in getting a script to work, bascially what i am trying to do is.
We have more than 6+ stores, the website must know where to send the online order to (email address).
so basically the code would look something like this,
To select which store to send the email to, we want to use postcodes.
each store has their own set areas for sales based on postcodes, for instance 1 store would have 4512, 4533 and many more stored in the franchise table in the database.
-----
Example:
i am placing an order for 1x Audio Device at a total cost of $299.90 including shipping,
my postcode equals 4512 ($customerpostcode)
The script should automatically know that i am in zone b, therefore the $selectedstore equals the store that has that postcode assosiated with it in the database.
Code: Select all
// MYSQL Connection Script BOF //
$dbcnx = mysql_connect('localhost', 'root', 'mypasswd');
$dbcnx = @mysql_connect('localhost', 'root', 'mypasswd');
if (!$dbcnx) {
echo '<p>Unable to connect to the ' .
'database server at this time.</p>' );
exit();
}
$mysql_select_db('database1', $dbcnx);
if (!@mysql_select_db('database1')) {
exit('<p>Unable to locate the' .
'database at this time.</p>');
}
// Connection Script EOF //
$result = @mysql_query('SELECT entry_postcode FROM address_book WHERE entry_postcode == $franchisepostcode);
if (!$result) {
exit('<p>Error performing query: ' . mysql_error() .
'</p>');
}
My question relates to the bottom part of the code, the select statement.
What is the syntx for the following select command,
Code: Select all
SELECT entry_postcode FROM address_book WHERE entry_postcode is equal to (question, can i use a variable in this select statement like below???)
Code: Select all
SELECT entry_postcode FROM address_book WHERE entry_postcode == "$storepostcode" ???
Or should it be something like below??
Code: Select all
SELECT entry_postcode FROM address_book WHERE entry_postcode = ". ' && ' ."
anyone who can help me please do so

im stuck
Pimptastic | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Posted: Mon Jun 26, 2006 6:37 pm
by RobertGonzalez
Yes, you can use variables in SQL in PHP. Remember, SQL commands in PHP are just strings. It is only when they query the DB that they actually mean something to a computer.
Code: Select all
<?php
$sql = "SELECT entry_postcode FROM address_book WHERE entry_postcode = '$storepostcode'";
?>
Alternatively, you can use wildcards...
Code: Select all
<?php
$sql = "SELECT entry_postcode FROM address_book WHERE entry_postcode LIKE '%$storepostcode%'";
?>
Bear in mind that like usually works best with string values. And if your variable is an integer, leave off the single quotes around it.
Posted: Mon Jul 03, 2006 11:03 pm
by pilsy2001
Thanks Everah your a legend
Can i ask you one other question?
I have 2 tables inside the database at this point in time,
Code: Select all
$check_post_code_customer = "SELECT entry_postcode FROM address_book WHERE entry_postcode ='$customerpostcode'"
Code: Select all
$check_post_code_store = "SELECT postcode, storeemail FROM franchises WHERE postcode ='$customerpostcode'"
Code: Select all
$storesemail = "SELECT storeemail FROM franchises WHERE postcode ='$customerpostcode'"
I also need a string that will pull the storesemail from the database (above string might work?) and then stores that email address as $storesemail.
Once i have the information stored in $storesemail i will then be able to modify the mail form and it will be done.
I have an array that holds the customers postcode, the array code is as follows
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']);
So i need a SQL statement that grabs the customers postcode from that array and stores it in a variable named $customerspostcode, how do i do that?
My main two other sql statements are posted above. (they should work yeah?)
What i need to accomplish is,
Our online shopping cart automatically sends out an email to the customer and the administrator, trick is we have so many stores spread out from each other that we want it to send an email directly to the store it is meant for automatically. (based on postcode)
im wondering should i use a Switch Statement or an If statement?
Any help will be very much appreciated...
Thanks Guys,
Posted: Tue Jul 04, 2006 10:57 am
by RobertGonzalez
Any chance you can post your database tables that you are using in these queries? Maybe do a phpMyAdmin table dump (structure only) and post the table structure here. What you want to do, I think, is a simple join to get the data you want from both tables at once. It is very easy to do, I just need to see the tables and felds to help draw out the appropriate query.
Posted: Thu Jul 06, 2006 12:01 am
by pilsy2001
Below is a MYSQL DUMP (structure only) from my database,
Code: Select all
-- phpMyAdmin SQL Dump
-- version 2.6.4-pl3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 06, 2006 at 02:56 PM
-- Server version: 4.1.14
-- PHP Version: 4.4.0
--
-- Database: `catalog`
--
-- --------------------------------------------------------
--
-- Table structure for table `address_book`
--
CREATE TABLE `address_book` (
`address_book_id` int(11) NOT NULL auto_increment,
`customers_id` int(11) NOT NULL default '0',
`entry_gender` char(1) NOT NULL default '',
`entry_company` varchar(32) default NULL,
`entry_company_tax_id` varchar(32) default NULL,
`entry_firstname` varchar(32) NOT NULL default '',
`entry_lastname` varchar(32) NOT NULL default '',
`entry_street_address` varchar(64) NOT NULL default '',
`entry_suburb` varchar(32) default NULL,
`entry_postcode` varchar(10) NOT NULL default '',
`entry_city` varchar(32) NOT NULL default '',
`entry_state` varchar(32) default NULL,
`entry_country_id` int(11) NOT NULL default '0',
`entry_zone_id` int(11) NOT NULL default '0',
PRIMARY KEY (`address_book_id`),
KEY `idx_address_book_customers_id` (`customers_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
-- --------------------------------------------------------
--
-- Table structure for table `franchises`
--
CREATE TABLE `franchises` (
`key_id` int(10) unsigned NOT NULL auto_increment,
`store_name` varchar(45) NOT NULL default '',
`store_email` varchar(96) NOT NULL default '',
`store_postcode` decimal(10,0) NOT NULL default '0',
`store_street` varchar(255) NOT NULL default '',
`store_state` varchar(45) NOT NULL default '',
PRIMARY KEY (`key_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='// Used for the Postcode Recignition Script' AUTO_INCREMENT=1 ;
--
-- Table structure for table `orders`
--
CREATE TABLE `orders` (
`orders_id` int(11) NOT NULL auto_increment,
`customers_id` int(11) NOT NULL default '0',
`customers_name` varchar(64) NOT NULL default '',
`customers_company` varchar(32) default NULL,
`customers_street_address` varchar(64) NOT NULL default '',
`customers_suburb` varchar(32) default NULL,
`customers_city` varchar(32) NOT NULL default '',
`customers_postcode` varchar(10) NOT NULL default '',
`customers_state` varchar(32) default NULL,
`customers_country` varchar(32) NOT NULL default '',
`customers_telephone` varchar(32) NOT NULL default '',
`customers_email_address` varchar(96) NOT NULL default '',
`customers_address_format_id` int(5) NOT NULL default '0',
`delivery_name` varchar(64) NOT NULL default '',
`delivery_company` varchar(32) default NULL,
`delivery_street_address` varchar(64) NOT NULL default '',
`delivery_suburb` varchar(32) default NULL,
`delivery_city` varchar(32) NOT NULL default '',
`delivery_postcode` varchar(10) NOT NULL default '',
`delivery_state` varchar(32) default NULL,
`delivery_country` varchar(32) NOT NULL default '',
`delivery_address_format_id` int(5) NOT NULL default '0',
`billing_name` varchar(64) NOT NULL default '',
`billing_company` varchar(32) default NULL,
`billing_street_address` varchar(64) NOT NULL default '',
`billing_suburb` varchar(32) default NULL,
`billing_city` varchar(32) NOT NULL default '',
`billing_postcode` varchar(10) NOT NULL default '',
`billing_state` varchar(32) default NULL,
`billing_country` varchar(32) NOT NULL default '',
`billing_address_format_id` int(5) NOT NULL default '0',
`payment_method` varchar(32) NOT NULL default '',
`cc_type` varchar(20) default NULL,
`cc_owner` varchar(64) default NULL,
`cc_number` varchar(32) default NULL,
`cc_expires` varchar(4) default NULL,
`last_modified` datetime default NULL,
`date_purchased` datetime default NULL,
`orders_status` int(5) NOT NULL default '0',
`orders_date_finished` datetime default NULL,
`currency` char(3) default NULL,
`currency_value` decimal(14,6) default NULL,
PRIMARY KEY (`orders_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
Does this help at all Everah??
Posted: Thu Jul 06, 2006 12:26 am
by AshrakTheWhite
your DB structure is bad, use seperate tables for orders and clients for starters, you dont want the same client name to be writen multyple times instead you prefer having it writen once and then get linked by ID from orders

Posted: Thu Jul 06, 2006 1:13 am
by RobertGonzalez
What I would do is clean the tables first. You want to keep information related to one specific group in that groups table. You reference that group by the group id in the other tables as needed.
Then, use a join query to get what you want in one array...
Code: Select all
<?php
$sql = "SELECT a.entry_postcode, f.postcode, f.storeemail
FROM address_book a
INNER JOIN franchises s
ON a.entry_postcode = f.postcode
WHERE a.entry_postcode ='$customerpostcode'";
?>
This will return a single result array that houses the information that you wanted in the three queries you posted.
Posted: Thu Jul 06, 2006 2:10 am
by technofreak
As i looked at the first question, I felt something to ask about it. There are only 6 stores to search for a post_cide, so why don't save the details ie the post_code of each store in arrays, and check whether the post code is within which array and fetch the address depending on the store name. I just want to know whether doing like this is better/faster than implementing a databse query-fetch ??
I am also learning python, is there a dictionary implementation in PHP which will help in having a list of [keys:values], where the values for a particular key can be searched to find in which key the 'value' exists ??
Posted: Thu Jul 06, 2006 2:25 am
by RobertGonzalez
First of all, storing the data in a database is the best thing to do. What if his store base grew to 10,000 stores? Would you want all that in an array? No. Makes sense to plan for it now. Also, there is less code involved in managing store data (ie, open the database connection, edit the data, repost) as opposed to managing array data, which either be a code rewrite or a file system admin tool creation. Not to mention that PHP is a processing language, not a data storage application.
As for a list function, there is
the PHP list() function, which works really well when coupled with
each().
Posted: Thu Jul 06, 2006 8:54 pm
by pilsy2001
Everah wrote:What I would do is clean the tables first. You want to keep information related to one specific group in that groups table. You reference that group by the group id in the other tables as needed.
Then, use a join query to get what you want in one array...
Code: Select all
<?php
$sql = "SELECT a.entry_postcode, f.postcode, f.storeemail
FROM address_book a
INNER JOIN franchises s
ON a.entry_postcode = f.postcode
WHERE a.entry_postcode ='$customerpostcode'";
?>
This will return a single result array that houses the information that you wanted in the three queries you posted.
Question Everah,
Im not sure that is entirely correct...
what it is supposed to do is, (dont know if it is?)
basically it needs to select the stores email address from the table franchises (storeemail) where the entry_postcode in address_book is equal to the postcode stored in the franchises table (postcode)
If it finds a postcode in the list of franchises equal to the entry_postcode in the table address_book then the $emailselected = "whatever store has that postcode on it"
else
$emailselected = "
email@email.com"
how do i get the code you have written to function like this? (and do you know of any tutorials that will help me out with this?
Thanks,
Posted: Thu Jul 06, 2006 9:21 pm
by RobertGonzalez
Run that query in phpMyAdmin (or whatever MySQL management utility you use) and see what you get in the result set.
Posted: Thu Jul 06, 2006 9:55 pm
by pilsy2001
Unknown table 'f' in field list
Database name "catalog"
Franchise Table: franchises
Addressbook table: address_book
Sorry about this, not so great at php at the moment
Posted: Thu Jul 06, 2006 10:01 pm
by RobertGonzalez
No apologies. It was my stupid error. Try this one and see...
Code: Select all
<?php
$sql = "SELECT a.entry_postcode, f.postcode, f.storeemail
FROM address_book a
INNER JOIN franchises f
ON a.entry_postcode = f.postcode
WHERE a.entry_postcode ='$customerpostcode'";
?>
Posted: Thu Jul 06, 2006 10:35 pm
by pilsy2001
Unknown column 'postcode' in 'field list'
That is a no go

Posted: Thu Jul 06, 2006 10:43 pm
by RobertGonzalez
Change f.postcode to f.store_postcode.