Address list

Muiter

I’ve been trying to build a mod called ‘address list’ this mod can be used to maintain a public address list. The mod is almost finished but I have a few small problems. I hope someone can help me to solve this since I’ve been out of solutions.

There are 2 serious problems left. The first one is that I can’t make state (provincie) and country (land) appear in the list http://www.modelautoforum.nl/addresses.php Probably because the names are in an other database table

The second one is that when you click on a address to view its details a pop-up appears but without the address details. The reason for this is that in the addressbar of you’re browser you see this link http://www.modelautoforum.nl/address_info.php?addr_id= after ?addr_id= should be number. If you enter there a number yourself you will see the address details like http://www.modelautoforum.nl/address_info.php?addr_id=18

You can find the code here: http://www.modelautoforum.nl/addresses.txt

This is my database code:

CREATE TABLE ADDR_adressen (
ADDR_id int(11) unsigned NOT NULL auto_increment,
name varchar(255) NOT NULL default '0',
addr varchar(255) NOT NULL default '0',
pc varchar(7) NOT NULL default '0',
city varchar(255) NOT NULL default '0',
state_id int(11) unsigned NOT NULL default '0',
country_id int(11) unsigned NOT NULL default '0',
www varchar(255) NOT NULL default '-',
mail varchar(255) NOT NULL default '-',
tel varchar(255) NOT NULL default '-',
special text NOT NULL,
form_link varchar(255) NOT NULL default '-',
active enum('N','Y') NOT NULL default 'N',
user_id mediumint(8) unsigned NOT NULL default '2',
date int(11) unsigned NOT NULL default '0',
eshop enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (ADDR_id)
) TYPE=MyISAM AUTO_INCREMENT=101 ;

CREATE TABLE ADDR_countries (
country_id int(11) unsigned NOT NULL auto_increment,
name varchar(255) NOT NULL default '',
PRIMARY KEY (country_id)
) TYPE=MyISAM AUTO_INCREMENT=241 ;

CREATE TABLE ADDR_states (
state_id int(11) unsigned NOT NULL auto_increment,
name varchar(255) NOT NULL default '0',
PRIMARY KEY (state_id)
) TYPE=MyISAM AUTO_INCREMENT=40 ;

bastien

I would simply add a another query or 2 to fetch the country.name and state.name.

That gigantic sql statement which gather the data from all the tables could use some tuning as well...i would suggest that you specifically list which fields you need, using table alias' where needed to get exactly the fields you want. Then if you uncomment the country sql bit, its should work...

Muiter

Thank you Bastien, Ik will try that. Any ideas on the other (and bigger) problem.

bastien

Yeah, sorry was just going over the code. When you are doing the select, I don't see the assignment of an address/store id to the template

Muiter

See line 249:

$address_link = "address_info.{$phpEx}?addr_id=$address_id";

Muiter

I have most of it working. But still the problems with id's. See this page where the id's should be replaced by states and countrys: http://www.modelautoforum.nl/addresses.php

How should I edit my code?

$sql = "SELECT * ";
	$sql .= "FROM  ADDR_states, ADDR_assortiment, ADDR_adres_assortimenten, ADDR_adressen ";
	$sql .= "WHERE ADDR_adressen.state_id = ADDR_states.state_id ";
	//$sql .= "AND countries.country_id = adressen.country_id ";
	$sql .= "AND ADDR_assortiment.assort_id  = ADDR_adres_assortimenten.assort_id ";
	$sql .= "AND ADDR_adres_assortimenten.addr_id = ADDR_adressen.addr_id ";
	$sql .= "AND ADDR_adressen.active = 'Y' ";
	if (!empty($name)) $sql .= "AND ADDR_adressen.name LIKE '%$name%' ";
	if (!empty($city)) $sql .= "AND city LIKE '%$city%' ";
	if (!empty($state)) $sql .= "AND ADDR_states.name LIKE '%$state%' ";
	if ($country_id != "no") $sql .= "AND ADDR_adressen.country_id = '$country_id' ";
	if ($assort_id != "no") $sql .= "AND ADDR_adres_assortimenten.assort_id = '$assort_id' ";
	if (!empty($special)) $sql .= "AND ADDR_adressen.special LIKE '%$special%' ";
	$sql .= "GROUP BY ADDR_adres_assortimenten.addr_id ";
	$sql .= "ORDER BY ADDR_adressen.name";
	// $addresses = mysql_query($sql) or die (mysql_error());
	// echo $sql;


}
else {
	$sql = "SELECT * ";
	$sql .= "FROM  ADDR_states, ADDR_assortiment, ADDR_adres_assortimenten, ADDR_adressen ";
	$sql .= "WHERE ADDR_assortiment.assort_id  = ADDR_adres_assortimenten.assort_id ";
	$sql .= "AND ADDR_adres_assortimenten.addr_id = ADDR_adressen.addr_id ";
	$sql .= "AND ADDR_adressen.active = 'Y' ";
	$sql .= "GROUP BY ADDR_adres_assortimenten.addr_id ";
	if (isset($HTTP_GET_VARS['sname']) || isset($HTTP_GET_VARS['scity']) || isset($HTTP_GET_VARS['stel']) || isset($HTTP_GET_VARS['swww'])) {
	    if (isset($HTTP_GET_VARS['sname'])) $sql .= "ORDER BY ADDR_adressen.name $HTTP_GET_VARS[sname]";

	if (isset($HTTP_GET_VARS['scity'])) $sql .= "ORDER BY ADDR_adressen.city $HTTP_GET_VARS[scity]";

	if (isset($HTTP_GET_VARS['sstate'])) $sql .= "ORDER BY ADDR_adressen.state $HTTP_GET_VARS[sstate]";

	if (isset($HTTP_GET_VARS['scountry'])) $sql .= "ORDER BY ADDR_adressen.country $HTTP_GET_VARS[scountry]";

	if (isset($HTTP_GET_VARS['stel'])) $sql .= "ORDER BY ADDR_adressen.tel $HTTP_GET_VARS[stel]";

    if (isset($HTTP_GET_VARS['swww'])) $sql .= "ORDER BY ADDR_adressen.www $HTTP_GET_VARS[swww]";

}
else $sql .= "ORDER BY ADDR_adressen.name";
}
if( !($result = $db->sql_query($sql)) )
{
	message_die(GENERAL_ERROR, 'Could not query users', '', __LINE__, __FILE__, $sql);
}

Database structure:

CREATE TABLE `ADDR_adressen` ( 
  `ADDR_id` int(11) unsigned NOT NULL auto_increment, 
  `name` varchar(255) NOT NULL default '0', 
  `addr` varchar(255) NOT NULL default '0', 
  `pc` varchar(7) NOT NULL default '0', 
  `city` varchar(255) NOT NULL default '0', 
  `state_id` int(11) unsigned NOT NULL default '0', 
  `country_id` int(11) unsigned NOT NULL default '0', 
  `www` varchar(255) NOT NULL default '-', 
  `mail` varchar(255) NOT NULL default '-', 
  `tel` varchar(255) NOT NULL default '-', 
  `special` text NOT NULL, 
  `form_link` varchar(255) NOT NULL default '-', 
  `active` enum('N','Y') NOT NULL default 'N', 
  `user_id` mediumint(8) unsigned NOT NULL default '2', 
  `date` int(11) unsigned NOT NULL default '0', 
  `eshop` enum('N','Y') NOT NULL default 'N', 
  PRIMARY KEY  (`ADDR_id`) 
) TYPE=MyISAM AUTO_INCREMENT=101 ; 

CREATE TABLE `ADDR_countries` ( 
  `country_id` int(11) unsigned NOT NULL auto_increment, 
  `name` varchar(255) NOT NULL default '', 
  PRIMARY KEY  (`country_id`) 
) TYPE=MyISAM AUTO_INCREMENT=241 ; 

CREATE TABLE `ADDR_states` ( 
  `state_id` int(11) unsigned NOT NULL auto_increment, 
  `name` varchar(255) NOT NULL default '0', 
  PRIMARY KEY  (`state_id`) 
) TYPE=MyISAM AUTO_INCREMENT=40 ;

bastien

If you run the query thru the gui for your db, do you get the country/state names? If so then you just need to reference those columns when parsing the resultset.

Muiter

I have it working now, thank you!!

There are two small questions left. How can I add pagination?
And second why is my search not working as it should? The search itzelf is good but when I want re-order the search results by country or whatever it's re-ordering all the entrys from the database instead of only re-ordering the search results.