|
 |
|
|
02-11-2009, 06:32 AM
|
mysql help needed
|
Posts: 14
Name: R Pearson
|
Hi all
I have a mysql table that requires the insertion of plain text.
What I have at present is below, and below that is what I require.
Can some advise on how I carry this out
Code:
--
-- Table structure for table `pmr_locations`
--
CREATE TABLE IF NOT EXISTS `pmr_locations` (
`name` varchar(50) DEFAULT NULL,
`name2` varchar(50) DEFAULT NULL,
`name3` varchar(50) DEFAULT NULL,
`name4` varchar(50) DEFAULT NULL,
`name5` varchar(50) DEFAULT NULL,
`name6` varchar(50) DEFAULT NULL,
`id` int(5) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1503 ;
--
-- Dumping data for table `pmr_locations`
--
INSERT INTO `pmr_locations` (`name`, `name2`, `name3`, `name4`, `name5`, `name6`, `id`) VALUES
('Abbots Langley', NULL, NULL, NULL, NULL, NULL, 1),
('Aberaeron', NULL, NULL, NULL, NULL, NULL, 2),
('Aberdare', NULL, NULL, NULL, NULL, NULL, 3),
('Aberdeen', NULL, NULL, NULL, NULL, NULL, 4),
('Aberdovey', NULL, NULL, NULL, NULL, NULL, 5),
('Aberfeldy', NULL, NULL, NULL, NULL, NULL, 6),
('Abergavenny', NULL, NULL, NULL, NULL, NULL, 7),
('Abergele', NULL, NULL, NULL, NULL, NULL, 8),
('Aberlour', NULL, NULL, NULL, NULL, NULL, 9),
('Abertillery', NULL, NULL, NULL, NULL, NULL, 10),
('Aberystwyth', NULL, NULL, NULL, NULL, NULL, 11),
('Abingdon', NULL, NULL, NULL, NULL, NULL, 12),
('Aboyne', NULL, NULL, NULL, NULL, NULL, 13),
('Accrington', NULL, NULL, NULL, NULL, NULL, 14),
('Acharacle', NULL, NULL, NULL, NULL, NULL, 15),
('Achnasheen', NULL, NULL, NULL, NULL, NULL, 16),
('Addlestone', NULL, NULL, NULL, NULL, NULL, 17),
('Airdrie', NULL, NULL, NULL, NULL, NULL, 18),
('Alcester', NULL, NULL, NULL, NULL, NULL, 19),
('Aldeburgh', NULL, NULL, NULL, NULL, NULL, 20),
('Alderley Edge', NULL, NULL, NULL, NULL, NULL, 21),
('Aldershot', NULL, NULL, NULL, NULL, NULL, 22),
('Alexandria', NULL, NULL, NULL, NULL, NULL, 23),
('Alford', NULL, NULL, NULL, NULL, NULL, 24),
('Alford', NULL, NULL, NULL, NULL, NULL, 25),
('Alfreton', NULL, NULL, NULL, NULL, NULL, 26),
('Alloa', NULL, NULL, NULL, NULL, NULL, 27),
('Alness', NULL, NULL, NULL, NULL, NULL, 28),
('Alnwick', NULL, NULL, NULL, NULL, NULL, 29),
('Alresford', NULL, NULL, NULL, NULL, NULL, 30),
('Alston', NULL, NULL, NULL, NULL, NULL, 31),
('Alton', NULL, NULL, NULL, NULL, NULL, 32),
('Altrincham', NULL, NULL, NULL, NULL, NULL, 33),
('Alva', NULL, NULL, NULL, NULL, NULL, 34),
('Ambleside', NULL, NULL, NULL, NULL, NULL, 35),
('Ambleside', NULL, NULL, NULL, NULL, NULL, 36),
('Amersham', NULL, NULL, NULL, NULL, NULL, 37),
('Amlwch', NULL, NULL, NULL, NULL, NULL, 38),
('Ammanford', NULL, NULL, NULL, NULL, NULL, 39),
('Andover', NULL, NULL, NULL, NULL, NULL, 40),
('Annan', NULL, NULL, NULL, NULL, NULL, 41),
('Anstruther', NULL, NULL, NULL, NULL, NULL, 42),
('Antrim', NULL, NULL, NULL, NULL, NULL, 43),
('Appin', NULL, NULL, NULL, NULL, NULL, 44),
('Appleby-in-Westmorland', NULL, NULL, NULL, NULL, NULL, 45),
('Arbroath', NULL, NULL, NULL, NULL, NULL, 46),
('Ardgay', NULL, NULL, NULL, NULL, NULL, 47),
('Ardrossan', NULL, NULL, NULL, NULL, NULL, 48),
('Arisaig', NULL, NULL, NULL, NULL, NULL, 49),
('Arlesey', NULL, NULL, NULL, NULL, NULL, 50),
('Armagh', NULL, NULL, NULL, NULL, NULL, 51),
Change too
Code:
--
-- Table structure for table `pmr_locations`
--
CREATE TABLE IF NOT EXISTS `pmr_locations` (
`name` varchar(50) DEFAULT NULL,
`name2` varchar(50) DEFAULT NULL,
`name3` varchar(50) DEFAULT NULL,
`name4` varchar(50) DEFAULT NULL,
`name5` varchar(50) DEFAULT NULL,
`name6` varchar(50) DEFAULT NULL,
`id` int(5) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1503 ;
--
-- Dumping data for table `pmr_locations`
--
INSERT INTO `pmr_locations` (`name`, `name2`, `name3`, `name4`, `name5`, `name6`, `id`) VALUES
('Abbots Langley', NULL, NULL, NULL, NULL, NULL, 1),
('Aberaeron', NULL, NULL, NULL, NULL, NULL, 2),
('Aberdare', NULL, NULL, NULL, NULL, NULL, 3),
('Aberdeen', NULL, NULL, NULL, NULL, NULL, 4),
('Aberdovey', NULL, NULL, NULL, NULL, NULL, 5),
('Aberfeldy', NULL, NULL, NULL, NULL, NULL, 6),
('Abergavenny', NULL, NULL, NULL, NULL, NULL, 7),
('Abergele', NULL, NULL, NULL, NULL, NULL, 8),
('Aberlour', NULL, NULL, NULL, NULL, NULL, 9),
('Abertillery', NULL, NULL, NULL, NULL, NULL, 10),
('Aberystwyth', NULL, NULL, NULL, NULL, NULL, 11),
THIS AREA IS IN WALES
('Abingdon', NULL, NULL, NULL, NULL, NULL, 12),
('Aboyne', NULL, NULL, NULL, NULL, NULL, 13),
('Accrington', NULL, NULL, NULL, NULL, NULL, 14),
('Acharacle', NULL, NULL, NULL, NULL, NULL, 15),
('Achnasheen', NULL, NULL, NULL, NULL, NULL, 16),
('Addlestone', NULL, NULL, NULL, NULL, NULL, 17),
('Airdrie', NULL, NULL, NULL, NULL, NULL, 18),
THIS AREA IS IN ENGLAND
('Alcester', NULL, NULL, NULL, NULL, NULL, 19),
('Aldeburgh', NULL, NULL, NULL, NULL, NULL, 20),
('Alderley Edge', NULL, NULL, NULL, NULL, NULL, 21),
('Aldershot', NULL, NULL, NULL, NULL, NULL, 22),
('Alexandria', NULL, NULL, NULL, NULL, NULL, 23),
('Alford', NULL, NULL, NULL, NULL, NULL, 24),
('Alford', NULL, NULL, NULL, NULL, NULL, 25),
THIS AREA IS IN SCOTLAND
('Alfreton', NULL, NULL, NULL, NULL, NULL, 26),
('Alloa', NULL, NULL, NULL, NULL, NULL, 27),
('Alness', NULL, NULL, NULL, NULL, NULL, 28),
('Alnwick', NULL, NULL, NULL, NULL, NULL, 29),
('Alresford', NULL, NULL, NULL, NULL, NULL, 30),
('Alston', NULL, NULL, NULL, NULL, NULL, 31),
('Alton', NULL, NULL, NULL, NULL, NULL, 32),
('Altrincham', NULL, NULL, NULL, NULL, NULL, 33),
('Alva', NULL, NULL, NULL, NULL, NULL, 34),
('Ambleside', NULL, NULL, NULL, NULL, NULL, 35),
('Ambleside', NULL, NULL, NULL, NULL, NULL, 36),
('Amersham', NULL, NULL, NULL, NULL, NULL, 37),
('Amlwch', NULL, NULL, NULL, NULL, NULL, 38),
('Ammanford', NULL, NULL, NULL, NULL, NULL, 39),
('Andover', NULL, NULL, NULL, NULL, NULL, 40),
('Annan', NULL, NULL, NULL, NULL, NULL, 41),
('Anstruther', NULL, NULL, NULL, NULL, NULL, 42),
('Antrim', NULL, NULL, NULL, NULL, NULL, 43),
('Appin', NULL, NULL, NULL, NULL, NULL, 44),
('Appleby-in-Westmorland', NULL, NULL, NULL, NULL, NULL, 45),
('Arbroath', NULL, NULL, NULL, NULL, NULL, 46),
('Ardgay', NULL, NULL, NULL, NULL, NULL, 47),
('Ardrossan', NULL, NULL, NULL, NULL, NULL, 48),
('Arisaig', NULL, NULL, NULL, NULL, NULL, 49),
('Arlesey', NULL, NULL, NULL, NULL, NULL, 50),
('Armagh', NULL, NULL, NULL, NULL, NULL, 51),
Thanks in advanve if you can help. |
|
|
|
|
02-11-2009, 07:15 AM
|
Re: mysql help needed
|
Posts: 3,621
Name: Thierry
Location: I'm the uber Spaminator !
|
I post this here too, in case someone wonders ( http://www.sitepoint.com/forums/show...=1#post4146674)...
You must add a relation to a region:
Code:
--
-- Table structure for table `pmr_locations`
--
CREATE TABLE IF NOT EXISTS `pmr_locations` (
`name` varchar(50) DEFAULT NULL,
`name2` varchar(50) DEFAULT NULL,
`name3` varchar(50) DEFAULT NULL,
`name4` varchar(50) DEFAULT NULL,
`name5` varchar(50) DEFAULT NULL,
`name6` varchar(50) DEFAULT NULL,
`id` int(5) unsigned NOT NULL AUTO_INCREMENT,
`regionId` int null,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1503 ;
create table if not exists region(
regionId int not null,
descr varchar(255) not null,
primary key (regionId)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
insert into region(regionId, descr) values (1,'Wales');
insert into region(regionId, descr) values (2,'England');
insert into region(regionId, descr) values (3,'Scotland');
INSERT INTO `pmr_locations` (`name`, `name2`, `name3`, `name4`, `name5`, `name6`, `id`, regionId) VALUES
('Abbots Langley', NULL, NULL, NULL, NULL, NULL, 1, null),
('Aberaeron', NULL, NULL, NULL, NULL, NULL, 2, null),
('Aberdare', NULL, NULL, NULL, NULL, NULL, 3, null),
('Aberdeen', NULL, NULL, NULL, NULL, NULL, 4, null),
('Aberdovey', NULL, NULL, NULL, NULL, NULL, 5, null),
('Aberfeldy', NULL, NULL, NULL, NULL, NULL, 6, null),
('Abergavenny', NULL, NULL, NULL, NULL, NULL, 7, null),
('Abergele', NULL, NULL, NULL, NULL, NULL, 8, null),
('Aberlour', NULL, NULL, NULL, NULL, NULL, 9, null),
('Abertillery', NULL, NULL, NULL, NULL, NULL, 10, null),
('Aberystwyth', NULL, NULL, NULL, NULL, NULL, 11, null),
--THIS AREA IS IN WALES
('Abingdon', NULL, NULL, NULL, NULL, NULL, 12, 1),
('Aboyne', NULL, NULL, NULL, NULL, NULL, 13, 1),
('Accrington', NULL, NULL, NULL, NULL, NULL, 14, 1),
('Acharacle', NULL, NULL, NULL, NULL, NULL, 15, 1),
('Achnasheen', NULL, NULL, NULL, NULL, NULL, 16, 1),
('Addlestone', NULL, NULL, NULL, NULL, NULL, 17, 1),
('Airdrie', NULL, NULL, NULL, NULL, NULL, 18, 1),
--THIS AREA IS IN ENGLAND
('Alcester', NULL, NULL, NULL, NULL, NULL, 19, 2),
('Aldeburgh', NULL, NULL, NULL, NULL, NULL, 20, 2),
('Alderley Edge', NULL, NULL, NULL, NULL, NULL, 21, 2),
('Aldershot', NULL, NULL, NULL, NULL, NULL, 22, 2),
('Alexandria', NULL, NULL, NULL, NULL, NULL, 23, 2),
('Alford', NULL, NULL, NULL, NULL, NULL, 24, 2),
('Alford', NULL, NULL, NULL, NULL, NULL, 25, 2),
--THIS AREA IS IN SCOTLAND
('Alfreton', NULL, NULL, NULL, NULL, NULL, 26, 3),
('Alloa', NULL, NULL, NULL, NULL, NULL, 27, 3),
('Alness', NULL, NULL, NULL, NULL, NULL, 28, 3),
('Alnwick', NULL, NULL, NULL, NULL, NULL, 29, 3),
('Alresford', NULL, NULL, NULL, NULL, NULL, 30, 3),
('Alston', NULL, NULL, NULL, NULL, NULL, 31, 3),
('Alton', NULL, NULL, NULL, NULL, NULL, 32, 3),
('Altrincham', NULL, NULL, NULL, NULL, NULL, 33, 3),
('Alva', NULL, NULL, NULL, NULL, NULL, 34, 3),
('Ambleside', NULL, NULL, NULL, NULL, NULL, 35, 3),
('Ambleside', NULL, NULL, NULL, NULL, NULL, 36, 3),
('Amersham', NULL, NULL, NULL, NULL, NULL, 37, 3),
('Amlwch', NULL, NULL, NULL, NULL, NULL, 38, 3),
('Ammanford', NULL, NULL, NULL, NULL, NULL, 39, 3),
('Andover', NULL, NULL, NULL, NULL, NULL, 40, 3),
('Annan', NULL, NULL, NULL, NULL, NULL, 41, 3),
('Anstruther', NULL, NULL, NULL, NULL, NULL, 42, 3),
('Antrim', NULL, NULL, NULL, NULL, NULL, 43, 3),
('Appin', NULL, NULL, NULL, NULL, NULL, 44, 3),
('Appleby-in-Westmorland', NULL, NULL, NULL, NULL, NULL, 45, 3),
('Arbroath', NULL, NULL, NULL, NULL, NULL, 46, 3),
('Ardgay', NULL, NULL, NULL, NULL, NULL, 47, 3),
('Ardrossan', NULL, NULL, NULL, NULL, NULL, 48, 3),
('Arisaig', NULL, NULL, NULL, NULL, NULL, 49, 3),
('Arlesey', NULL, NULL, NULL, NULL, NULL, 50, 3),
('Armagh', NULL, NULL, NULL, NULL, NULL, 51, 3),
__________________
Only a biker knows why a dog sticks his head out the window.
|
|
|
|
|
02-11-2009, 07:49 AM
|
Re: mysql help needed
|
Posts: 16
Name: Richard
|
Hi
I am scratching my head somewhat, what are you suggesting?
|
|
|
|
|
02-11-2009, 08:06 AM
|
Re: mysql help needed
|
Posts: 3,621
Name: Thierry
Location: I'm the uber Spaminator !
|
1) you create a table that will hold the regions:
Code:
create table if not exists region(
regionId int not null,
descr varchar(255) not null,
primary key (regionId)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
insert into region(regionId, descr) values (1,'Wales');
insert into region(regionId, descr) values (2,'England');
insert into region(regionId, descr) values (3,'Scotland');
2) you extend your main table to have a reference to the region
Code:
CREATE TABLE IF NOT EXISTS `pmr_locations` (
`name` varchar(50) DEFAULT NULL,
`name2` varchar(50) DEFAULT NULL,
`name3` varchar(50) DEFAULT NULL,
`name4` varchar(50) DEFAULT NULL,
`name5` varchar(50) DEFAULT NULL,
`name6` varchar(50) DEFAULT NULL,
`id` int(5) unsigned NOT NULL AUTO_INCREMENT,
`regionId` int null,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1503 ;
3) You add the information in the inserts
Code:
INSERT INTO `pmr_locations` (`name`, `name2`, `name3`, `name4`, `name5`, `name6`, `id`, regionId) VALUES
('Abbots Langley', NULL, NULL, NULL, NULL, NULL, 1, null), --null means "no region associated"
...
('Ardrossan', NULL, NULL, NULL, NULL, NULL, 48, 3), --3 means "scotland"
The code I posted contains the complete modifications.
__________________
Only a biker knows why a dog sticks his head out the window.
|
|
|
|
|
02-11-2009, 08:09 AM
|
Re: mysql help needed
|
Posts: 16
Name: Richard
|
You are a star, can i thank you for your time and effort in helping me.
|
|
|
|
|
02-11-2009, 08:11 AM
|
Re: mysql help needed
|
Posts: 16
Name: Richard
|
Oh just one thing, will the region show (say England) on the customer front end dropdown box with all the areas below it?
|
|
|
|
|
02-11-2009, 08:33 AM
|
Re: mysql help needed
|
Posts: 3,621
Name: Thierry
Location: I'm the uber Spaminator !
|
Quote:
|
will the region show (say England) on the customer front end dropdown box with all the areas below it?
|
It's up to you...
You have to get that information, and output it into the html.
It won't get done by itself.
__________________
Only a biker knows why a dog sticks his head out the window.
|
|
|
|
|
02-11-2009, 08:37 AM
|
Re: mysql help needed
|
Posts: 16
Name: Richard
|
Hii
I have an error
Code:
Error
SQL query:
INSERT INTO `pmr_locations` ( `name` , `name2` , `name3` , `name4` , `name5` , `name6` , `id` , regionId )
VALUES ('Abbots Langley', NULL , NULL , NULL , NULL , NULL , 1, NULL
), (
'Aberaeron', NULL , NULL , NULL , NULL , NULL , 2, NULL
), (
'Aberdare', NULL , NULL , NULL , NULL , NULL , 3, NULL
), (
'Aberdeen', NULL , NULL , NULL , NULL , NULL , 4, NULL
), (
'Aberdovey', NULL , NULL , NULL , NULL , NULL , 5, NULL
), (
'Aberfeldy', NULL , NULL , NULL , NULL , NULL , 6, NULL
), (
'Abergavenny', NULL , NULL , NULL , NULL , NULL , 7, NULL
), (
'Abergele', NULL , NULL , NULL , NULL , NULL , 8, NULL
), (
'Aberlour', NULL , NULL , NULL , NULL , NULL , 9, NULL
), (
'Abertillery', NULL , NULL , NULL , NULL , NULL , 10, NULL
), (
'Aberystwyth', NULL , NULL , NULL , NULL , NULL , 11, NULL
),
MySQL said: 
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 12
|
|
|
|
|
02-11-2009, 09:38 AM
|
Re: mysql help needed
|
Posts: 3,621
Name: Thierry
Location: I'm the uber Spaminator !
|
It's the comments that are messing with the insert query.
remove the lines
Code:
--THIS AREA IS IN SCOTLAND
and such.
What you need to take care too, is that you just posted a part of the full query, where you insert them into pmr_locations.
Either you must extend the modifications to all rows, or you should replace the last "," with a ";"
__________________
Only a biker knows why a dog sticks his head out the window.
|
|
|
|
|
02-11-2009, 09:51 AM
|
Re: mysql help needed
|
Posts: 14
Name: R Pearson
|
Hi
This was the error ";"
Thanks again
|
|
|
|
|
02-12-2009, 12:51 PM
|
Re: mysql help needed
|
Posts: 14
Name: R Pearson
|
Following on from this I am still having a few issues, namely I wish to arrange the 'Location' dropdown box in my 'Submit Agent ' so it does not show alphabetically. I would prefer it to default to 'regionId' in the database tables under '_location'
What I have done so far is in submit.php I have changed
PHP Code:
generate_options_list(LOCATIONS_TABLE, $form['realtor_location'])
TO
PHP Code:
generate_options_list(LOCATIONS_TABLE, $form['realtor_location'],true)
and then change includes/functions.php you have to add/change three lines
PHP Code:
//line 1 changed
function generate_options_list ( $name, $selected = '', $defaultorderby = false )
{
global $db;
global $cookie_language;
if ($cookie_language == "english") $in = 'name';
if ($cookie_language == "french") $in = 'name2';
if ($cookie_language == "german") $in = 'name3';
if ($cookie_language == "italian") $in = 'name4';
if ($cookie_language == "russian") $in = 'name5';
if ($cookie_language == "spanish") $in = 'name6';
//line 2 added
$orderBy = ($defaultorderby != true) ? ' ORDER BY ' . $in : '';
//line 3 changed
$sql = 'SELECT id, ' . $in . ' FROM ' . $name . $orderBy;
$r = $db->query ($sql) or error ('Critical Error', mysql_error () );
$output = '';
while ($f = $db->fetcharray ($r) )
{
if ($f['id'] == $selected)
$output.= '<option value="'. $f['id'] . '" SELECTED>' . $f[1] . '</option>';
else
$output.= '<option value="'. $f['id'] . '">' . $f[1] . '</option>';
}
return $output;
}
However I have just added a new table where I link certain places (via their ID) to England/Scotland etc. So I add a relation to a region as follows
PHP Code:
CREATE TABLE IF NOT EXISTS `pmr_locations` (
`name` varchar(50) DEFAULT NULL,
`name2` varchar(50) DEFAULT NULL,
`name3` varchar(50) DEFAULT NULL,
`name4` varchar(50) DEFAULT NULL,
`name5` varchar(50) DEFAULT NULL,
`name6` varchar(50) DEFAULT NULL,
`id` int(5) unsigned NOT NULL AUTO_INCREMENT,
`regionId` int null,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1503 ;
create table if not exists region(
regionId int not null,
descr varchar(255) not null,
primary key (regionId)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
insert into region(regionId, descr) values (1,'Wales');
insert into region(regionId, descr) values (2,'England');
insert into region(regionId, descr) values (3,'Scotland');
INSERT INTO `pmr_locations` (`name`, `name2`, `name3`, `name4`, `name5`, `name6`, `id`, regionId) VALUES
('Abbots Langley', NULL, NULL, NULL, NULL, NULL, 1, null),
How will this change you last post and what changes will I need to make.
Thanks for your continued help by the way.
Last edited by PWS; 02-12-2009 at 12:53 PM..
|
|
|
|
|
|
« Reply to mysql help needed
|
|
|
| Thread Tools |
Search this Thread |
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
