Insert check box data into mysql table

[emvy03]

Hi,

I am trying to build a shop and so need to build a page where the user can specify which sizes are available. I was therefore wondering if there is a way to grab the data from a check box group in a from and insert that into a row in a mysql table?

Thanks.

[fugix]

absolutely, if you need help with this, please post you code

[emvy03]

Hi,

Ta for the quick reply.

 

The only code I have is for for the checkbox:

 

<p>

      <label>

        <input type="checkbox" name="size" value="small" id="size_0" />

        Small</label>

     

      <label>

        <input type="checkbox" name="size" value="medium" id="size_1" />

        Medium</label>

     

      <label>

        <input type="checkbox" name="size" value="large" id="size_2" />

        Large</label>

     

      <label>

        <input type="checkbox" name="size" value="extralarge" id="size_3" />

        Extra Large</label>

     

 

I can connect to the mysql database fine, and can insert other fields, like normal text, but I don't know how to take the info from the check boxes and put them into the database.

Cheers.

 

 

 

[emvy03]

Ok,

So to start off with I have made a new column in the database table for each field, i.e. a 'small' column, 'medium' column, 'large' column since the check box is meant to represent clothing size.

 

Then in my check box I have given each 'option' a respective name i.e. Small => 'small, Medium => 'medium', Large => 'large'.

 

Then I have set a new php variable for each 'option'

 

Then in my mysql query I have got:

 

$sql = mysql_query("INSERT INTO products (product_name,price,details,category,date_added,Small,Medium,Large)

VALUES('$product_name','$price','$details','$category',now()),'$small','$medium','$large'") or die(mysql_error());

 

However, when I run this code I get the error:

 

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 ''Small','Medium','Large'' at line 2

 

Any ideas.

 

 

P.S. I'm trying to start off with the basics and then eventually write the code so that only selected values are uploaded.

 

Cheers.

[Eiolon]

Please use the CODE tags when posting code.

 

Now, it looks like you are trying to create a new product, and allow the user to select what size types are available for this product.

 

The way you are approaching it is fine, but if you want the user to be able to add "extra extra large" without needing you to create a new database field and add it to the PHP, then I'd create the sizes in their own table and use an array.

 

But continuing with your method, here is what I would do with your checkboxes:

 

      <label>
        <input type="checkbox" name="small" value="1" />
        Small</label>
      
      <label>
        <input type="checkbox" name="medium" value="1" />
        Medium</label>
      
      <label>
        <input type="checkbox" name="large" value="1" />
        Large</label>
      
      <label>
        <input type="checkbox" name="x_large" value="1" />
        Extra Large</label>

 

Now here is what I would do with your table fields:

 

product_name, price, details, category, date_added, small, medium, large, x_large

 

For the four size fields, make it a tinyint(1) and set default to '0'.

 

When you check the size box, the value of the selection now becomes '1' instead of '0'.  Thing of it like a bit check.

 

So, for your SQL:

 

$sql = mysql_query("INSERT INTO products (product_name,price,details,category,date_added,small,medium,large,x_large)
   VALUES('$product_name','$price','$details','$category',now()),'$small','$medium','$large,'$x_large'") or die(mysql_error());

[fenway]

Those sizes are screaming for another table.

[emvy03]

Hi guys,

 

Sorry for the lack of the 'code' tags, won't happen next time!

 

Thanks alot for the help, it's greatly appreciated. I am still getting a similar error:

 

'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 ''1','1','1'' at line 2'

 

Does it matter that I am doing things locally on my system and not live on a website yet?

 

Ta very much.

[fenway]

Show us raw queries and error messages, not just the latter.

[emvy03]

Okay, here's the code for my 'add_item' page. I'm new to php so i'm anticipating there to be a few errors  :-\

 

<?php
if(isset($_POST['product_name'])){

$product_name = mysql_real_escape_string($_POST['product_name']);
$price = mysql_real_escape_string($_POST['price']);
$category= mysql_real_escape_string($_POST['category']);
$details = mysql_real_escape_string($_POST['details']);
$small = mysql_real_escape_string($_POST['small']);
$medium = mysql_real_escape_string($_POST['medium']);
$large = mysql_real_escape_string($_POST['large']);


$sql = mysql_query("SELECT id FROM products Where product_name='$product_name' LIMIT 1");
$productMatch = mysql_num_rows($sql);
if($productMatch > 0){
echo 'Sorry, that product name already exists <a href="add_item.php">click here</a>"';
exit();
}

$sql = mysql_query("INSERT INTO products (product_name,price,details,category,date_added,small,medium,large)
   VALUES('$product_name','$price','$details','$category',now(),'$small','$medium','$large'") or die(mysql_error());
$pid = mysql_insert_id();
$newname = "$pid.jpg";
move_uploaded_file($_FILES['fileField']['tmp_name'],"../shop/images/$newname");

$sql = mysql_query("SELECT * FROM products");
$test = mysql_num_rows($sql);
if($test = 5){
echo 'Uploaded Successfully. Click <a href="add_item.php">here</a> to add another item or go to <a href="inventory_list.php">your store list</a>';
exit();
}


}


?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Lad Label Home</title>
<link rel="stylesheet" href="../shop/style/style.css" type="text/css" />
</head>

<body>
<div align="center" id="mainWrapper">
<? include "../pagecomponents/header.php"; ?>
</div>
<div id="pageContent">
<div align="right">
<a href="index.php">Manager Home</a>   <a href="inventory_list.php">Back to Store List</a> 
</div>

<div align="left">
<h2>Add New Item</h2>
<form action="add_item.php" enctype="multipart/form-data" name="myForm" id="myForm" method="post" >

<table width="100%" border="0">
  <tr>
    <td width="20%">Product Name</td>
    <td width="80%"><label>
    <input name="product_name" type="text" id="product_name" size="64" />
    </label>
    </td>
  </tr>
  <tr>
    <td>Price</td>
    <td><label>
    £
    <input name="price" type="text" id="price" size="12" />
    </label>
    </td>
  </tr>
  <tr>
    <td>Category</td>
    <td><label for="category">
      <select name="category" id="category"> 
        <option value="T-Shirt">T-Shirt</option>
        <option value="Hoody">Hoody</option>
        <option value="Polo">Polo</option>
      </select></label></td>
  </tr>
  <tr>
    <td>Details</td>
    <td><label>
    <textarea name="details" id="details" cols="64" rows="5"></textarea> 
    </label>
    </td>
  </tr>
  <tr>
    <td>Sizes Available</td>
    <td><p>
      <label>
        <input type="checkbox" name="small" value="1" />
        Small</label>
      
      <label>
        <input type="checkbox" name="medium" value="1" />
        Medium</label>
      
      <label>
        <input type="checkbox" name="large" value="1" />
        Large</label>
      </p>    </tr>
  <tr>
    <td>Colours Available</td>
    <td> </td>
  </tr>
  <tr>
    <td>Product Image</td>
    <td><label><input type="file" id="fileField" name="fileField" /></label></td>
  </tr>
  <tr>
    <td> </td>
    <td><label>
    <input type="submit" name="button" id="button" value="Add this Item" />
    </label></td>
  </tr>

</table>




</form>

</div>
</div>
<div id="pageFooter"></div>
</div>
</body>
</html>

[fenway]

That's PHP code -- not SQL queries -- I mean the actual statement that the server will execute.

[emvy03]

Apologies for being thick!!

 

I'm assuming the query the server will execute is:

 

 INSERT INTO products (product_name,price,details,category,date_added,small,medium,large)
   VALUES('$product_name','$price','$details','$category',now(),'$small','$medium','$large')

 

Cheers lads!

 

 

[fenway]

So close -- echo that string -- I still see php variables.

[emvy03]

I don't follow

 

I see there are php variables in there, '$product_name' etc, but I get how to publish the 'raw query'.

[Eiolon]

What happens when you manually execute the query in MySQL by manually putting your own variables in?

 

Example:

 

INSERT INTO products (product_name, price, details, category, date_added, small, medium, large) VALUES ('Three wolf moon shirt','15.00','Cool shirt!','T-shirts',now(),'1','1','1');

 

Secondly, in your PHP I notice you have a double quote near the end of your insert query near $large.

[emvy03]

Hi, many thanks for the reply.

Yea, by executing the query directly into mysql it works perfectly and the Moon T-Shirt goes in fine. Yet, when the php tries to do the query I get the error message.

 

The second double quote is in there to accompany the double quote just before the 'INSERT ...'

 

 

 

P.S. My error message is now

 

(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 '' at line 2)

[fenway]

Echo the string you're passing to mysql_query, followed by mysql_error output.

[emvy03]

Ta very much for all your help. Managed to find a way to do it. Just having issues with another piece of code now to do with 'fwrite' function. May start a new topic.

Cheers