Jump to content

completeamateur

Members
 View Profile  See their activity

  • Posts

    69

  • Joined

  • Last visited

    Never

 Content Type 

Posts posted by completeamateur

    Database Layout (Product Catalogue)

    in Application Design

    Posted

    Hi guys,

     

    I'm setting up a database to help control my web site's product catalogue and stock levels.

     

    I'm thinking of having 2 tables, one that describes each product (common information), as well as one that contains possible options (unique information).

     

    I wanted to hear people's suggestions regarding the layout I have come up with...

     

    CREATE TABLE IF NOT EXISTS `products` (
  `productID` smallint(6) unsigned NOT NULL auto_increment,
  `team` text NOT NULL,
  `description` text NOT NULL,
  `HA3` tinyint(1) NOT NULL,
  `gk` tinyint(1) NOT NULL,
  `start` year(4) NOT NULL,
  `end` year(4) NOT NULL,
  PRIMARY KEY  (`productID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `stock` (
  `stockID` smallint(5) unsigned NOT NULL auto_increment,
  `size` tinytext NOT NULL,
  `price` float NOT NULL,
  `condition` tinyint(1) NOT NULL,
  `qty` smallint(3) NOT NULL,
  `productID` smallint(5) unsigned NOT NULL,
  PRIMARY KEY  (`stockID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

     

    FYI, each product could have up to 50 different options (approx. 10 size variations and 5 condition variations).  Is this the best approach or would you approach it differently?  It seems a little cumbersome but my brain isn't capable of anything better!

     

    Thanks for your help.

    [SOLVED] Complex Query

    in Frameworks

    Posted

    I have no finger nails or hair, but after what has felt like several years, I've managed to sort it!!

     

    		$sql = $this->select()
		->from('category AS node')
		->from('category AS parent', '')
		->from('category AS sub_parent', '(COUNT(parent.name) - (sub_tree.depth + 1)) AS depth')
		->joinInner(
			array('sub_tree' =>
				$this->select()
					->from('category AS node', 'name')
					->from('category AS parent', '(COUNT(parent.name) - 1) AS depth')
					->where('node.lft BETWEEN parent.lft AND parent.rgt')
					->where('node.lft = ' . $lft)
					->group('node.name')
					->order('node.lft')
			), 'sub_parent.name = sub_tree.name', array()
		)
		->where('node.lft BETWEEN parent.lft AND parent.rgt')
		->where('node.lft BETWEEN sub_parent.lft AND sub_parent.rgt')
		->group('node.name')
		->having('depth = 1')
		->order('node.lft');

    [SOLVED] ZF: Je suis tres confused!

    in Frameworks

    Posted

    Daniel,

     

    I've made the modifications you have suggested and it looks as if we're half way there.

     

    I seem to have some permissions problems as it is throwing the following errors...

     

    Warning: move_uploaded_file(/Library/WebServer/Documents/bbusl/public/upload/catalogue/images/European Cup.jpg) [function.move-uploaded-file]: failed to open stream: Permission denied in /Library/WebServer/Documents/library/Zend/File/Transfer/Adapter/Http.php on line 102

     

    Warning: move_uploaded_file() [function.move-uploaded-file]: Unable to move '/private/var/tmp/phpdDRdh9' to '/Library/WebServer/Documents/bbusl/public/upload/catalogue/images/European Cup.jpg' in /Library/WebServer/Documents/library/Zend/File/Transfer/Adapter/Http.php on line 102

     

    Warning: move_uploaded_file(/Library/WebServer/Documents/bbusl/public/upload/catalogue/images/European Cup.jpg) [function.move-uploaded-file]: failed to open stream: Permission denied in /Library/WebServer/Documents/library/Zend/File/Transfer/Adapter/Http.php on line 102

     

    Warning: move_uploaded_file() [function.move-uploaded-file]: Unable to move '/private/var/tmp/phpdDRdh9' to '/Library/WebServer/Documents/bbusl/public/upload/catalogue/images/European Cup.jpg' in /Library/WebServer/Documents/library/Zend/File/Transfer/Adapter/Http.php on line 102

     

    I'm running Mac OS X, PHP 5.2.5, Apache 2.0.  Any suggestions?

     

    Thanks in advance.

    [SOLVED] Complex Query

    in Frameworks

    Posted

    It's all running from my local machine so there shouldn't be a problem.

     

    The query is supposed to resemble what is suggested in http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

     

    I'm still not having any joy though.  This is my latest (failed) attempt...

     

    		$sql = $this->select()
		->from('category AS node')
		->from('category AS parent')
		->from('category AS sub_parent')
		->from(
			array('sub_tree' =>
				$this->select()
					->from('category AS node')
					->from('category AS parent', '(COUNT(parent.name) - 1) AS depth')
					->where('node.lft BETWEEN parent.lft AND parent.rgt')
					->where('node.lft = 1')
					->group('node.name')
					->order('node.lft')
			), '(COUNT(parent.name) - (sub_tree.depth + 1)) AS depth'
		)
		->where('node.lft BETWEEN parent.lft AND parent.rgt')
		->where('node.lft BETWEEN sub_parent.lft AND sub_parent.rgt')
		->where('sub_parent.name = sub_tree.name')
		->group('node.name')
		->having('depth = 1')
		->order('node.lft');

     

     

    [SOLVED] Simple UPDATE query

    in MySQL Help

    Posted

    I'm afraid this just returns...

     

    Notice: Undefined property: Catalogue::$update in /Library/WebServer/Documents/bbusl/application/models/Catalogue.php on line 80

     

    Fatal error: Call to a member function __toString() on a non-object in /Library/WebServer/Documents/bbusl/application/models/Catalogue.php on line 80

    [SOLVED] Simple UPDATE query

    in MySQL Help

    Posted

    I'm working with ZF so the code I use to generate the query is a little abstract...

     

    $this->update(array('lft'=>'lft + 2'), 'lft > ' . $lft);

     

    I've tried all variations of apostrophes and quotes but to no avail.  Interestingly enough, I've found that...

     

    $this->update(array('lft'=>'2'), 'lft > ' . $lft);

     

    ...works fine, but...

     

    $this->update(array('lft'=>'lft'), 'lft > ' . $lft);

     

    ...doesn't.  I presume this is because it is trying to enter the string 'lft'?

    [SOLVED] ZF: Je suis tres confused!

    in Frameworks

    Posted

    Why is the ZF so baffling?  I'm trying to provide a facility to upload files.  I have created an element within the form...

     

    			$image = new Zend_Form_Element_File('image');
		$image->setLabel('Photo')
		->setAttrib('enctype', 'multipart/form-data')
		->setRequired(false)
		->setDestination('/upload/catalogue/images')
		->addValidator('Count', false, 1)					// ensure only 1 file
		->addValidator('Size', false, 1024000)				// limit to 1MB
		->addValidator('Extension', false, 'jpg,png,gif');	// only JPEG, PNG, and GIFs

     

    ...and the controller consists of...

     

    		if ($this->_request->isPost()) {

	$formData = $this->_request->getPost();

		Zend_Debug::dump($_FILES);
		exit;

     

    ...but this just returns...

     

    array(0) {

    }

     

    i.e. no file is uploaded!  I don't understand what I'm doing wrong.  Any suggestions?

    [SOLVED] Simple UPDATE query

    in MySQL Help

    Posted

    Hi guys,

     

    I'm trying to increment the current value of a column by using an update query.

     

    UPDATE category SET lft = lft + 2 WHERE lft > $lft;

     

    Unfortunately, because I'm using Zend Framework, it interprets 'lft + 2' incorrectly (don't ask me why!).  Is there another way to increment the existing value, e.g. lft++

     

    [SOLVED] Simple UPDATE query

    in Frameworks

    Posted

    Hi guys,

     

    I'm trying to create a simple query to update some records.

     

    $this->update(array('lft'=>'lft + 2'), 'lft > ' . $lft);

     

    This works fine, except it does not write the correct value in the lft column, it just writes '0', where as it should be 'lft + 2' i.e. the original value + 2.  I'm not quite sure what I'm doing wrong.

     

    TIA

    [SOLVED] Complex Query

    in Frameworks

    Posted

    I'm trying to construct a fairly complex query...

     

    I suppose firstly I should point out that I don't understand the difference between inserting the query as a string and constructing it in Zend (although what I would interpret as the same query gives differing ouputs)?

     

    This is the query I'm trying to run;

     

    		$sql = '
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM category AS node,
category AS parent,
category AS sub_parent,
(
	SELECT node.name, (COUNT(parent.name) - 1) AS depth
	FROM category AS node,
	category AS parent
	WHERE node.lft BETWEEN parent.lft AND parent.rgt
	AND node.lft = 1
	GROUP BY node.name
	ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth = 1
ORDER BY node.lft';

	$result = $this->fetchAll($sql);
	return $result;

     

    ...doesn't work (although I can run the query fine in mysql query browser),

     

    		$sub = $this->select()
		->from(
			array('node' => 'category'),
			array('name', '(COUNT(parent.name) - 1) AS depth')
		)
		->from(
			array('parent' => 'category')
		)
		->where('node.lft BETWEEN parent.lft AND parent.rgt')
		->where('node.lft = 1')
		->group('node.name')
		->order('node.lft');

	$sql = $this->select()
		->from(
			array('node' => 'category'),
			array('name', '(COUNT(parent.name) - (sub_tree.depth + 1)) AS depth')
		)
		->from(
			array('parent' => 'category')
		)
		->from(
			array('sub_parent' => 'category')
		)
		->from(
			array('sub_tree' => new Zend_Db_Expr('(' . $sub . ')'))
		)
		->where('node.lft BETWEEN parent.lft')
		->where('parent.rgt AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt')
		->where('sub_parent.name = sub_tree.name')
		->group('node.name')
		->having('depth = 1')
		->order('node.lft');

	$result = $this->fetchAll($sql);
	return $result;

     

    ...doesn't work.  I'm a bit stumped.

     

    Any pointers much appreciated.

    I hate IE

    in CSS Help

    Posted

    Strange, that shouldn't be an issue, but i've put a fix in...

     

    html { background: #000;
body { background: url(/images/DSC00294.JPG) repeat fixed center;

     

    Still snookered by the form rendering, can anyone else confirm if it is an issue? perhaps IE6 or 5.5?

    I hate IE

    in CSS Help

    Posted

    Steam is coming out of my ears...

     

    Does anybody know why the form on my site is rendering so poorly in IE?  www.footieclassics.com/contact

     

    Also the navigation list is displaying a bit strangely...

    #books li { color:#FF7200; float:left; list-style:lower-roman inside; margin-right:0.4em; }

     

    Thanks.

    Zend Form - Hidden Elements produce unwanted tags?

    in Frameworks

    Posted

    Hi there,

     

    I have been redesigning my websites using the Zend Framework (namely www.footieclassics.com), but have come across a little annoyance.

     

    Using hidden elements in Zend Form seems to generate unwanted <dd>/<dt> tags which then cause some horrible white space.  Is there any way to eliminate this?

     

    <dd>
<input type="hidden" name="cmd" value="_xclick" id="cmd">
</dd>
<dt> </dt>

     

    TIA

     

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.