New to PHP

[kwmlr439]

I have created some tables on phpmyadmin but not sure if I did it right regarding varchar and the 255 value. If anyone is familiar with this and can correct it that would be great. Thanks. Here are the tables:

 

http://oi44.tinypic.com/1z6v2j5.jpg

 

 

[Ch0cu3r]

More of a MySQL question than a PHP one.

You set all fields to be varchar with a  255 character limit. Which is lazy database design.

 

The data type you set the field to should represent the type of data you are storing in that field. Such as the

• zipcode, bedrooms and sqft would be numbers so an int datatype would be more suitable.
• price should be float or decimal datatype.

You should rethink how you are setting the table up.

 

http://dev.mysql.com/doc/refman/5.0/en/data-types.html

[QuickOldCar]

The larger you set varchar the bigger the data will be, even if you insert a value that contains 12 characters, it will take up the space of 255 in the database.

After mysql version 5.03 ...you can set this all the way up to 65,535, although I don't know anyone that would want or need to.

http://dev.mysql.com/doc/refman/5.7/en/char.html

 

You can try to set these for something that can match your data, or a hair larger to be sure, such as a name only needing around 30 or 40 characters.

 

Numbers can be int, those also have different character amounts but work on display.

http://dev.mysql.com/doc/refman/5.5/en/numeric-type-attributes.html

 

If any of those are going to be optional and may contain a blank value, you can set them to NULL so your query can still insert.

[kwmlr439]

I think I may create all the tables I need using the same format and structure and pay someone to change the value of each one of them. I have a bigger problem than I thought but I love to learn actually! It's a fascianting experience for me.

 

John

[Barand]

• Ask for advice
• Ignore it
• Pay someone else to do it

 

That's a great approach to learning you have there!

[kwmlr439]

Guru, I apologies! I actually started doing what Ch0cu3r suggested and I am creating all the tables I need right now using the advices here and reading the URL I was provided. But some of the fields are complex and I don't think I will be able to figure them out. That's why I said that I could create all the tables which will speed up the process than figure out a way to make appropriate change or find someone to help me.

 

My intentions to this forum are harmless and I'm indeed looking forward to your answers and learn more about PHP tables.

 

Regards,

 

John

Edited December 1, 2013 by kwmlr439

[jcbones]

You are talking about MySQL tables, which are not a part of PHP.  PHP is a server side scripting language, and MySQL is a database.

[kwmlr439]

Yes MYSQL tables but I can't move this topic to a different section can I?

 

John

[kicken]

The larger you set varchar the bigger the data will be, even if you insert a value that contains 12 characters, it will take up the space of 255 in the database.

That is incorrect. A VARCHAR field will only use n + y bytes of space, with n being the length of the actual data being stored, and y being between 1 and 2 extra bytes to store the length. The value you pass into the column definition controls the maximum length of the field, and how many extra bytes are used.

 

The CHAR type on the other hand is fixed size, and will always use the declared number of bytes regardless of the length of the actual value being stored.

[objnoob]

After mysql version 5.03 ...you can set this all the way up to 65,535, although I don't know anyone that would want or need to.

 

All the way up to 65,535 minus the bytes used to store the length (as Kicken mentioned) and byte requirements of any other columns. 

 

The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject

to the maximum row size (65,535 bytes, which is shared among all columns) and

the character set used. See Section E.7.4, “Limits on Table Column Count and Row Size”.

 

mysql> create table test1 ( col1 varchar(65535) );

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

 

mysql> create table test2 ( col1 varchar(65532) );

Query OK, 0 rows affected (0.01 sec)

 

mysql> create table test3 ( col1 varchar(65530), tenbytefixed char(10));

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

 

 

[kwmlr439]

Thanks for your help, Here is what I have so far. I haven't made any changes yet except for the first few lines. Thanks a lot guys for your help. It is greatky appreciated:

 

 

-- phpMyAdmin SQL Dump
-- version 3.4.11.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 01, 2013 at 10:59 PM
-- Server version: 5.5.34
-- PHP Version: 5.2.17

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `mydtabasename`
--

-- --------------------------------------------------------

--
-- Table structure for table `listings`
--

DROP TABLE IF EXISTS `listings`;
CREATE TABLE IF NOT EXISTS `listings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `address` varchar(64) NOT NULL,
  `city` varchar(64) NOT NULL,
  `state` char(2) NOT NULL,
  `zipcode` int(5) NOT NULL,
  `bedrooms` varchar(2) NOT NULL,
  `sqft` varchar( NOT NULL,
  `type` varchar(64) NOT NULL,
  `status` varchar(64) NOT NULL,
  `price` varchar( NOT NULL,
  `mlsnumber` varchar( NOT NULL,
  `mlsarea` varchar(64) NOT NULL,
  `county` varchar(64) NOT NULL,
  `taxid` varchar(64) NOT NULL,
  `subdivnei` varchar(64) NOT NULL,
  `schooldistrict` varchar(64) NOT NULL,
  `elementaryschool` varchar(64) NOT NULL,
  `middleschool` varchar(64) NOT NULL,
  `highschool` varchar(64) NOT NULL,
  `beds` varchar(64) NOT NULL,
  `baths` varchar(64) NOT NULL,
  `propertytype` varchar(64) NOT NULL,
  `condohoa` varchar(64) NOT NULL,
  `recurfeefreq` varchar(64) NOT NULL,
  `age` varchar(64) NOT NULL,
  `unitfloornumber` varchar(64) NOT NULL,
  `waterfront` varchar(64) NOT NULL,
  `intsqft` varchar(64) NOT NULL,
  `propertydescription` varchar(64) NOT NULL,
  `lrgr` varchar(64) NOT NULL,
  `dinning` varchar(64) NOT NULL,
  `kitchen` varchar(64) NOT NULL,
  `family` varchar(64) NOT NULL,
  `inclusions` varchar(64) NOT NULL,
  `exclusions` varchar(64) NOT NULL,
  `mainbr` varchar(64) NOT NULL,
  `secondbr` varchar(64) NOT NULL,
  `thirdbr` varchar(64) NOT NULL,
  `fourthbr` varchar(64) NOT NULL,
  `totalrooms` varchar(64) NOT NULL,
  `totalbaths` varchar(64) NOT NULL,
  `uperfloors` varchar(64) NOT NULL,
  `mainfloors` varchar(64) NOT NULL,
  `lowerfloors` varchar(64) NOT NULL,
  `applyat` varchar(64) NOT NULL,
  `availdate` varchar(64) NOT NULL,
  `applicationfee` varchar(64) NOT NULL,
  `securitydeposit` varchar(64) NOT NULL,
  `minterm` varchar(64) NOT NULL,
  `maxterm` varchar(64) NOT NULL,
  `acreslsf` varchar(64) NOT NULL,
  `approxdim` varchar(64) NOT NULL,
  `otherschools` varchar(64) NOT NULL,
  `style` varchar(64) NOT NULL,
  `stories` varchar(64) NOT NULL,
  `parkingspace` varchar(64) NOT NULL,
  `taxrate` varchar(64) NOT NULL,
  `basement` varchar(64) NOT NULL,
  `construction` varchar(64) NOT NULL,
  `fireplace` varchar(64) NOT NULL,
  `builtin` varchar(64) NOT NULL,
  `heating` varchar(64) NOT NULL,
  `exteriorwalls` varchar(64) NOT NULL,
  `parking` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;