...

View Full Version : Phpmyadmin



rafiki
03-22-2007, 02:28 AM
when making a table with PHP my admin i press save and it gives me a javascript alert saying this is not a number most fields are varchar bar 2 which one of them is longtext (for descriptions) other is tinyint (for level) if you want more details of the table ill post them, i've also not put any length values in on any of them, should i?

rafiki
03-22-2007, 03:34 AM
dw i just remembered that varchar needs a max length
:(
now gives me


MySQL said: http://localhost/phpmyadmin/themes/darkblue_orange/img/b_help.png (http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html)
#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 'UNSIGNED CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `password` VARCHAR' at line 1

this is the create table code


CREATE TABLE `users` ( `username` VARCHAR( 20 ) UNSIGNED NOT NULL ,
`password` VARCHAR( 20 ) UNSIGNED NOT NULL ,
`email` VARCHAR( 50 ) UNSIGNED NOT NULL ,
`userid` VARCHAR( 6 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`level` TINYINT( 3 ) UNSIGNED NOT NULL DEFAULT '1',
`country` VARCHAR( 15 ) UNSIGNED NOT NULL ,
`gender` VARCHAR( 6 ) UNSIGNED NOT NULL ,
`imtype` VARCHAR( 15 ) UNSIGNED NOT NULL ,
`imname` VARCHAR( 50 ) UNSIGNED NOT NULL ,
`age` VARCHAR( 2 ) UNSIGNED NOT NULL ,
`house` VARCHAR( 20 ) UNSIGNED NOT NULL DEFAULT 'bus shelter',
`clan` VARCHAR( 20 ) UNSIGNED NOT NULL DEFAULT 'n\\a',
`job` VARCHAR( 20 ) UNSIGNED NOT NULL DEFAULT 'n\\a',
`money` VARCHAR( 20 ) UNSIGNED NOT NULL DEFAULT '1000',
`membertype` VARCHAR( 4 ) UNSIGNED NOT NULL DEFAULT 'free',
`items` LONGTEXT UNSIGNED NOT NULL DEFAULT '0',
`rank` VARCHAR( 3 ) UNSIGNED NOT NULL DEFAULT '1') ENGINE = MYISAM

guelphdad
03-22-2007, 11:30 AM
char/varchar field can't be UNSIGNED, that is for numeric data field.
also why use LONGTEXT for a field and then sign a numeric default to it?

rafiki
03-22-2007, 02:36 PM
coz if its set to 0 then i can echo none, spose i could do that the same way i check if a username is taken or there email address is already registered
now i get this

SQL query:
CREATE TABLE `users` ( `username` VARCHAR( 20 ) NOT NULL ,
`password` VARCHAR( 20 ) NOT NULL ,
`email` VARCHAR( 50 ) NOT NULL ,
`userid` VARCHAR( 6 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`level` TINYINT( 3 ) NULL DEFAULT '1',
`country` VARCHAR( 15 ) NULL ,
`gender` VARCHAR( 6 ) NULL ,
`imtype` VARCHAR( 15 ) NOT NULL ,
`imname` VARCHAR( 50 ) NOT NULL ,
`age` VARCHAR( 2 ) NOT NULL ,
`house` VARCHAR( 20 ) NOT NULL DEFAULT 'bus shelter',
`clan` VARCHAR( 20 ) NOT NULL DEFAULT 'n\\a',
`job` VARCHAR( 20 ) NOT NULL DEFAULT 'n\\a',
`money` VARCHAR( 20 ) UNSIGNED NOT NULL DEFAULT '1000',
`membertype` VARCHAR( 4 ) NOT NULL DEFAULT 'free',
`items` LONGTEXT NOT NULL DEFAULT '0',
`rank` VARCHAR( 3 ) NOT NULL DEFAULT '1') ENGINE = MYISAM
MySQL said: http://localhost/phpmyadmin/themes/darkblue_orange/img/b_help.png
#1063 - Incorrect column specifier for column 'userid'

guelphdad
03-22-2007, 02:44 PM
what are you inputting in the LONGTEXT type column? You realize it can hold a vast amount of text right?
4294967296 bytes or about (4 GiB) of storeage?

You should also look into the use of NULL as that is what you want when an item is undefined.

With the use of NULL you can also use COALESCE to show no user if the value isn't available.



SELECT
COALESCE(username,'none') as name
FROM yourtablename


would show None when a username has not been filled in for a user that has other info in their row (id and age existing in the row for example but not their name).

rafiki
03-22-2007, 02:51 PM
`imtype` VARCHAR( 15 ) NOT NULL ,
`imname` VARCHAR( 50 ) NOT NULL ,
those two fields are the only ones which will not need data inputted by a user, most of them will be filled in by php
i know next to nothing about mysql which is why i am trying this project
would you like to see some php code from this page

guelphdad
03-22-2007, 04:24 PM
You have created a column as above with LONGTEXT as the data type and then set a default of 0. I'm asking why you set a default of a numeric value?

Then I'm asking if you realize that the size of the data you can store in a longtext field is in the order of 4 gigabytes?

Don't create a datatype that you don't need.

rafiki
03-22-2007, 05:22 PM
ok what should i use, i need to put a pretty long list of items in and then array them when i fetch them i can echo out singlely
for example the field will hold = chocolate, computer, keyboard, forum, smillies, attachment,
then i need to fetch them explode() them assign them to there own var then echo out something like you own $var $var1 $var2 $var3 and own a total of $amount items, understand, like i sed i no next to nothing about mysql and cant find the table drew out for what r field types to use,

guelphdad
03-22-2007, 05:36 PM
There is a problem right there. You should NEVER store data in a string of values. Check this article (http://guelphdad.wefixtech.co.uk/sqlhelp/lists.shtml) to explain the drawbacks of doing so.

rafiki
03-22-2007, 07:27 PM
im kinda busy atm, but i did read bit of it, does that mean i need to join to tables
how would igo about that
-------------+-----------+--------------
username | items | otherfield |
-----------------------------------------
rafiki | football, | something |
-----------------------------------------
admin | football, | something |
______| boots, |______|
______| gloves |______|
-----------------------------------------
thats how i wanted it to look
then when the admin wanted to see wat items he had it would show
football
boots
gloves
you own a total of 3 items

rafiki
03-23-2007, 01:25 AM
is that not possible/recommended? plz guys i need help with this i changed the items field to normal text

rafiki
03-25-2007, 03:06 PM
noone?

Fumigator
03-25-2007, 07:25 PM
I kind of got lost-- what is your question?

rafiki
03-26-2007, 06:18 PM
i need to store, what would be a list in html into a table, for example the admin has 5 items
football boots, football, pizza, chips, chicken
i have just 2
chicken, chips
i need to store them into a database and be able to store them 2 an array or something then explode them into single vars
then count how many there were
make sense? i think thats the best way to describe it :S

_Aerospace_Eng_
03-26-2007, 06:48 PM
Just store each item individually into its own field in your database and use a while or for loop to fetch them. You are probably going to have to limit the number of items each user has.

rafiki
03-26-2007, 07:42 PM
cant i do it buy using a text field and seperate them by comma's ','s then explode the results?

guelphdad
03-26-2007, 07:43 PM
items belong in their own row. As presented before, the article I linked to explains why you should not store a list in a column.

The article explains how you can draw up a list of what everyone has, as well as easily compare the items you have to see how many of them I have.

No loops necessary.

rafiki
03-26-2007, 09:29 PM
Name Type Sell Value Total Sell Value
Tank top [Info] [Send] Clothes $32 $32
Pair of trainers [Info] [Send] Clothes $35 $35
Jacket [Info] [Send] Clothes $40 $40
Personal computer [Info] [Send] [Turn on] Electronic $300 $300
Simple virus [Info] [Send] Virus $500 $500
thats how i want it to end up looking? do you think they use database normalization, as posted prior to this?
i read over the post again and im still not sure how it works,
so i create an extra table and each item has its own field?
then i would need to make a list of people in that table which own that item?
sorry i really am not trying to sound thick, this is pretty much my first real MYSQL experience
/*edit*/ i already have a table which has all the items in with description, id, price, etc.. /*edit*/

Fumigator
03-26-2007, 11:23 PM
Yes. Going back to your chicken and chips example, you'd have a "person" table and a "favoritefoods" table. Heck, let's make it a "favorites" table and add a column that describes what kind of favorite it is.

So the person table has a unique key, let's call it person_id.

The favorites table links to the person table by storing that person_id.

Person table data
person_id name
--------------------
10001 randy
10002 mike

Favorites table data
person_id description type_code
----------------------------------------
10001 chicken food
10001 chips food
10001 meatballs food
10001 blue color
10002 football sport
10002 pizza food
10002 liver and onions food

Then you write a join query to connect the two tables.


SELECT p.name, f.description, f.type_code
FROM person as p
JOIN favorites as f
ON p.person_id = f.person_id


This is a very basic example. You'd probably want to create one more table that stores types of favorites, especially if there is more data you want to store that relates to a favorite, such as price, and then use a table to link the person table with the favorites table. The article Gue linked to in a previous post has more fancy examples of how you can query data from two tables set up like this.

rafiki
03-27-2007, 12:09 AM
so its like having a foriegn key? (thats how i was taught at college) that connects to database's/tables? kinda making sense, but the table i need isnt favourite foods, its just random items, but i see where u were going, thanks for showing me the light :D



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum