create single text feild to display in select list

Report · Sep 16, 2012

Hello, i am going round in circles with what i need to acheive. i have this on a previous post but not sure if what i was asking was correct

what i need to do is have a item size select list for the end user BUT in the backend i need to have a text feild or text feilds that can be manually changed. the db has a prod table with

ID

Desc

Price

Size

feilds, so i was thinking if it was on a single line text feild that could store all the sizes in one feild in the db and can be changed in the backend

OR

how paypal buttons do it where the backend has a add size function so you can add a set a

mount of sizes and can manually change them is need be, beacuse i will need the backend to show if a product has sold out and wanted to be able to type "sold out" then show that in the select list

thanks in advance for any help

Report · Sep 16, 2012

Comma separated values in a SQL database violates 1NF (First Normal Form) norm. It also leads to a whole lot of problems when your database is expanding.

As Bill Karwin - an author for SQL Best Practices quotes:

Can't ensure that each value is the right data type: no way to prevent 1,2,3,banana,5
Can't use foreign key constraints to link values to a lookup table; no way to enforce referential integrity.
Can't enforce uniqueness: no way to prevent 1,2,3,3,3,5
Can't delete a value from the list without fetching the whole list.
Hard to search for all entities with a given value in the list; you have to use an inefficient table-scan.
Hard to count elements in the list, or do other aggregate queries.
Hard to join the values to the lookup table they reference.
Hard to fetch the list in sorted order.

If you're still persistent in achieving this, you should use php explode function to explode your cell-data in the frontend.

Report · Sep 16, 2012

i am not set on using one feild but need to find a solution asap. i just need to be able to change the values of the feild manually and that is the only way i can think to do it

Report · Sep 16, 2012

Why dont you consider building a dynamic Admin CMS page to be able to update your content on the DB?

Report · Sep 16, 2012

i have built a dynamic admin cms page but i have become stuck on the sizes of the clothes.

each set of clothes have different sizes so there cant be a set size table, e.g

tshirts ( 10 designs) have 0-3 months 3-6 months 6-12 months

shorts ( 4 designs )have 3-6 months 6-12 months 12-24 months and 3 yrs

and these will sell out at different times so each design of each category needs its own size table

Report · Sep 17, 2012

Why don't you create a db table like this:

product designnumber from to age size availability

This will correlate with:

T-shirts 3 3 6 m xx sold

So, you'll have 10 rows for T-Shirt designs and 4 for shorts designs.

You can then use SQL Concat to fetch data from your database as:

T-shirt design 3, 3-6 months, xx size, sold

Dont you think this should work?

Report · Sep 17, 2012

Ok this looks good. It is rather confusing for me so i need to get it in my head how this will work.

so the DB table will be like this

product ID

DesignNumber

from

to

age

size

sold

is this what you mean?

Report · Sep 17, 2012

Yes. That is exactly what I meant. productID will just a numeric value for t-shirts/ shorts/ whatever else

Design number correlates with the productID - T-shirt 1, T-shirt 2, etc...

from is the start month/ year for clothing fit

to is end month/ year for clothing fit

age is month/ year value

size is xs,s,m,l, etc...

sold will be a y/n value

Report · Sep 17, 2012

ok im starting to understand it.I am going to try and make a form the correlates with the database. You think this will work then? I am still trying to understand it but will get there.

so this can be showed in a drop down select list?

thanks so much for you help so far

Report · Sep 17, 2012

Another question :

this is a seperate table in the DB and will be joined to the product page via and ID?

so t-shirt ID 1 will join to the Size table so the size table needs needs a t-shirt ID feild?

Report · Sep 17, 2012

Ideally, Yes.

Report · Sep 17, 2012

ok i will try this logic and let you know how i get on. I really hope I can get this working as I am getting very desperate.

thanks for yor help so far

Report · Sep 17, 2012

I will create a size form and can i have a dropdown list showing product ID and do it like that?

Report · Sep 17, 2012

You can do a conditional AJAX driven dropdown list for your frontend.

This will be the flow for the conditional display of dropdowns:

Product
Model
Size

Report · Sep 17, 2012

yes i was asking about the backend, i can have a dropdown list for the product name in the size form?

Report · Sep 17, 2012

You mean the Admin Area? Yes, In Admin Area you can have a dropdown list for product name in size form.

Report · Sep 17, 2012

yes the admin. thanks. i need to work on this in the next hour and try and get it working in the next few hours as this is all that is holding my project up.

thanks for your time

Report · Sep 17, 2012

ok i am starting work on the form now.

i am thinking about i am adding each size to each item not category then when i need to call the sizes from the fb i use the join of the product ID in the size table and the product ID from the product table

Report · Sep 17, 2012

ok i have made the table

CREATE TABLE `hostprop`.`beauSizes` (

`SizeID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 80 ) NOT NULL , -
`from` VARCHAR( 155 ) NOT NULL ,
`to` VARCHAR( 155 ) NOT NULL ,
`age` VARCHAR( 155 ) NOT NULL ,
`size` VARCHAR( 200 ) NOT NULL ,
`sold` VARCHAR( 20 ) NOT NULL

) ENGINE = MYISAM

I have made the design number "name" rather than design number (this is the name of the feild in the product table)

the product table looks like this

ID	int(11)			No		auto_increment
	name	varchar(200)	latin1_swedish_ci		No
	price	varchar(80)	latin1_swedish_ci		No
	desc	text	latin1_swedish_ci		No
	imageSmall	varchar(200)	latin1_swedish_ci		No
	imageLarge	varchar(200)	latin1_swedish_ci		No
	CatID	varchar(200)	latin1_swedish_ci		No
	SizesID	varchar(200)	latin1_swedish_ci		Yes	NULL

does this look correct?

Report · Sep 17, 2012

You were saying you want to include the criteria of 3-6 months and so on in your table. I don't see that in your table here.

Other than that, your table looks good so far for what you want to achieve.

Report · Sep 17, 2012

yes doesnt that go in the from and to feilds?

Report · Sep 17, 2012

also i need to delete the sizesID in the product table

Report · Sep 17, 2012

ok i have made the add product size table

<form action="<?php echo $editFormAction; ?>" method="post" name="name" id="name">

<option value="select Product Name">Select Product Name</option>

<?php

do {

?>

<?php

} while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));

$rows = mysql_num_rows($Recordset1);

if($rows > 0) {

mysql_data_seek($Recordset1, 0);

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

}

?>

</select></td>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</table>

</form>

with this i will have to make the Database table properties NULL wont I because depending on the item it will either be a 0-3 months etc or a year or a size??

Report · Sep 18, 2012

ok i have the table working (sort of) what did you mean by this

>You were saying you want to include the criteria of 3-6 months and so on in your table. I don't see that in your table here.

also i have a sold checkbox. so if this item has sold out i needs to show that its sold out. How do i achieve this?

Report · Sep 18, 2012

For the value of availability between 3-6 months of age, there is no cell in your DB that holds the data.

Sold checkbox - If you're talking about reading the value from checkbox on your SQL db then displaying its value in frontend, use this:

<?php $sql = "SELECT sold FROM yourtable"; $result = mysql_query($sql); $row = mysql_fetch_array($result); $checked = $result['sold']; ?><input type="checkbox" name="sold" value="1" <?php if ($checked == 1) echo 'sold'; ?> />

I'm using a checkbox to display the data if value is 1 it will show sold else, it'll be blank.

You can echo it as plain text too. That's upto you.

create single text feild to display in select list

1 Correct answer