checkboxes and mysql/ php
05-18-2005, 04:50 AM
|
checkboxes and mysql/ php
|
Posts: 17
Location: UK
|
Hi everyone, its been an age since I posted here, how is everyone?
I'm currently working on a php script for a small company database, its for my dads company and basicly he wants to be able to find his suppliers at the click of a button.
I have created a database, with three tables, one with company details and one with products, the other to join them together.
All he has to do is select a product from a drop down menu and click submit to get a list of all the companies that supply that products, this bit of the script works perfectly..
What I'm struggling with is when adding this data to the database, he has 70+ products so I have added a list of all these, and checkboxes, to a form at: http://www.bagnboxman.co.uk/insert.htm so you can see what I mean!
I do not know, and need help with, naming the checkboxes on the insert form, and knowing what code to addto my script so it gets submitted in the right place.
here is my script.php file:
PHP Code:
<?
$DBhost = "localhost";
$DBName = "database";
$table = "company";
mysql_connect($DBhost) or die("Unable to connect to database");
@mysql_select_db("$DBName") or die("Unable to select database $DBName");
$sqlquery = "INSERT INTO $table VALUES('$id','$name','$phone','$web','$notes')";
$results = mysql_query($sqlquery);
mysql_close();
print "<html><body><center>";
print "<p>You have just entered this record<p>";
print "<strong>Company Name :</strong> $name<br>";
print "<strong>Phone Number :</strong> $phone<br>";
print "<strong>Web Site :</strong> $web<br>";
print "<strong>Notes :</strong> $notes<br>";
print "</body></html>";
?>
and my database.sql file:
PHP Code:
# phpMyAdmin SQL Dump
# version 2.5.6
# http://www.phpmyadmin.net
#
# Host: localhost
# Generation Time: May 18, 2005 at 12:11 AM
# Server version: 4.0.18
# PHP Version: 4.3.6
#
# Database : `database`
#
# --------------------------------------------------------
#
# Table structure for table `company`
#
CREATE TABLE `company` (
`companyid` tinyint(4) NOT NULL auto_increment,
`name` text NOT NULL,
`phone` text NOT NULL,
`web` text NOT NULL,
`notes` text NOT NULL,
PRIMARY KEY (`companyid`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;
#
# Dumping data for table `company`
#
INSERT INTO `company` VALUES (1, 'The Bag N Box Man', '01295788522', 'www.bagnboxman.co.uk', 'lsdfng podfgadv hgadpfh alduyoer gfjohgfh g');
INSERT INTO `company` VALUES (2, 'NOW:design', '07789176017', 'www.now-design.co.uk', 'dsfg');
INSERT INTO `company` VALUES (3, 'Home', '01295730112', 'localhost', 'bugger me this works');
# --------------------------------------------------------
#
# Table structure for table `coprod`
#
CREATE TABLE `coprod` (
`companyid` tinyint(4) NOT NULL default '0',
`productsid` tinyint(4) NOT NULL default '0'
) TYPE=MyISAM;
#
# Dumping data for table `coprod`
#
INSERT INTO `coprod` VALUES (1, 1);
INSERT INTO `coprod` VALUES (1, 2);
INSERT INTO `coprod` VALUES (1, 3);
INSERT INTO `coprod` VALUES (1, 4);
INSERT INTO `coprod` VALUES (2, 1);
INSERT INTO `coprod` VALUES (2, 2);
INSERT INTO `coprod` VALUES (2, 3);
INSERT INTO `coprod` VALUES (2, 4);
INSERT INTO `coprod` VALUES (3, 3);
# --------------------------------------------------------
#
# Table structure for table `products`
#
CREATE TABLE `products` (
`id` tinyint(4) NOT NULL auto_increment,
`name` text NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;
#
# Dumping data for table `products`
#
INSERT INTO `products` VALUES (1, 'bags');
INSERT INTO `products` VALUES (2, 'boxes');
INSERT INTO `products` VALUES (3, 'envelopes');
INSERT INTO `products` VALUES (4, 'tubes');
for good measure, a slimline version of my insert.htm file
PHP Code:
<html>
<head>
</head>
<center>
<form method="post" action="script.php">
<input type="hidden" name="id" value="null">
<table align="center">
<tr>
<td align="left"><div align="left"><strong>Company Name: </strong></div></td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td align="left"><div align="left"><strong>Phone Number: </strong></div></td>
<td><input type="text" name="phone"></td>
</tr>
<tr><td>
<p align="left"><strong>Web site:</strong></td>
<td><input type="text" name="web"></td>
</tr>
<tr>
<td><div align="left"><strong>Notes:</strong></div></td>
<td><input type="text" name="notes"></td>
</tr>
<tr>
<td colspan="2"><div align="center">Bags
<input type="checkbox" name="checkbox" value="checkbox">
</div></td>
</tr>
<tr>
<td colspan="2"><div align="center">
<input name="submit" type="submit" value="Enter record">
</div></td>
</tr>
</table>
</form>
</center>
</html>
If anyone can help me, or point me in the right direction, id be really really gratefull!
Thanks in advance,
Tjobbe |
|
|
|
|
05-18-2005, 07:09 AM
|
|
Posts: 297
|
Oooook then, this isn't too complex, but you gotta really listen up!!
Firstly, about checkboxes. To easily parse checkboxes, the best way to parse checkboxes is to pass their value over into the script as Comma Seperated Values (item1,item2,item3,item4). To do this, we have to give one generic name to all of the checkboxes, so that when they get parsed they are all kept together. I like to name my checkboxes "selitems", shortened from "selected items". Next thing, tell HTML to parse all the values in one query item, we just add []. So now we have:
HTML Code:
<INPUT TYPE=CHECKBOX NAME="selitems[]" VALUE="1">
Now, everything with the name of selitems[] will parse as one!
We need to give a value to each of these products (this bit's gonna show i aint read your db structure properly). Ideally we need to dynamically create the page that you posted the link to, because we need ID codes for everyone of those products - this way when it comes to the script we just have a list of numbers, that can then be cross-referenced with the database to find out what product they are. Hopefully, this isn't going to cause too much trouble!
As for how you should parse the checkboxes in the PHP itself, we can use a loop:
PHP Code:
foreach($selitems as $val) {
// Whatever you want
}
So in the loop, each checkbox is assigned to $val (shortened from value, suprise!) and you can then manipulate it however you want.
Hopefully this gives you a pretty good foothold. Sorry i couldn't post a whole script for you, but it's a fairly big task! and im lazy as ****.
__________________
A lie gets halfway around the world before the truth has a chance to get its pants on. - Sir Winston Churchill
Please visit my sites:
Please login or register to view this content. Registration is FREE
|
Please login or register to view this content. Registration is FREE
|
|
|
|
|
05-18-2005, 07:26 AM
|
|
Posts: 17
Location: UK
|
thats looks do-able, thansk for your reply mate, lets see if I understand this:
PHP Code:
<td colspan="2"><div align="center">Bags
<input type="checkbox" name="checkbox" value="checkbox">
</div></td>
the above is an example line, before I change it!
PHP Code:
<td colspan="2"><div align="center">Bags
<input type="checkbox" name="selitems[]" value="1">
</div></td>
that much I know, the VALUE is the number corresponding to its place in the table of the database am I correct?
if that is so, then the following must be true!
PHP Code:
<td colspan="2"><div align="center">Bags
<input type="checkbox" name="selitems[]" value="1">
</div></td>
<td colspan="2"><div align="center">Boxes
<input type="checkbox" name="selitems[]" value="2">
</div></td>
<td colspan="2"><div align="center">Envelopes
<input type="checkbox" name="selitems[]" value="3">
</div></td>
Am I understanding you correctly?
then, submitting this to the database, on script.php, I'm afraid I am lost:
PHP Code:
foreach($selitems as $val) {
// Whatever you want
}
I am quite new at PHP,a s you may guess, but this is a bit too advanced for me, where would I put it? |
|
|
|
|
05-18-2005, 07:54 AM
|
|
Posts: 297
|
Ok, the value in the checkbox should not be in correspondance to the placement in the DB table (because placements start from 0 and it's just too complicated!). The value of the in the checkbox should be the ID value in the database.
Now i've noticed you don't actually have all the products that are listed at http://www.bagnboxman.co.uk/insert.htm so im afraid you're going to have to do that! Rename the current 'products' table to 'categories' and create another table with ID, ProductName, ProductCategory. This way, we've given each product a unique ID (for ease, make the ID auto_increment). Also, in the ProductCategory field, we must give a a number, not a name. This number corresponds to the 'categories' table, so if we wanted Paper Bags (plain) to be in the bags category, we punch '1' into the 'ProductCategory' field in the new 'products' table, right?
I know you've had to redesign your whole database, but it's for the better! Now then, as for http://www.bagnboxman.co.uk/insert.htm, you can either dynamically create it to be able to get the product IDs into the values in each corresponding checkbox, or you can add all the products into the database, and manually place each value - either one is gonna take some time.
I noticed in your DB structure you have a 'coprod', no idea wat that means, but im guessing it's a linking table telling you what company supplies what right? Fair enough, but don't you want to identify each individual product, and not simply if they supply bags or boxes or whatever, we want to know what bags and boxes they supply??
In that case, when we hit that "Enter Record" button, we're going to have to add that into coprod.
So in the PHP script, in the section you handle what happens when this form comes through, you'll need to *** something like this:
PHP Code:
foreach($selitems as $val) {
INSERT INTO `coprod` VALUES ($companyID, $val);
}
This is just suedo code, it's not a complete solution, but hopefully it gives you an idea.
I'm really not sure that's the best way of explaining, might just have confused you even more. I know what i mean tho 
__________________
A lie gets halfway around the world before the truth has a chance to get its pants on. - Sir Winston Churchill
Please visit my sites:
Please login or register to view this content. Registration is FREE
|
Please login or register to view this content. Registration is FREE
|
|
|
|
|
05-18-2005, 08:15 AM
|
|
Posts: 17
Location: UK
|
Quote:
|
Originally Posted by leavethisplace
I noticed in your DB structure you have a 'coprod', no idea wat that means, but im guessing it's a linking table telling you what company supplies what right? Fair enough, but don't you want to identify each individual product, and not simply if they supply bags or boxes or whatever, we want to know what bags and boxes they supply??
|
no, we only need to choose a product from a drop down menu, and then ALL the companies that supply that particular product are listed. does this make sense to you?
Its basicly if we want to find suppliers of a product.
I think i understand what you wrote above now, but does it still apply with what ive just written?? |
|
|
|
|
05-18-2005, 12:06 PM
|
|
Posts: 17
Location: UK
|
I am getting closer to this now, I need one final bit of help!
attached image is a section screen dump of phpmyadmin, so you can see what it looks like.
I know I need to enter the check box values like this:
"INSERT INTO `coprod` VALUES ('$companyid', '$productid')";
wherecompanyid is the id number of the company and productid the id number of the product.
How do I get a check box to carry over the ID number of the company when submitting the form, and then to post to the database alongside the product id?
I know I need to get the ID of the company from somewhere, but I dont know how, and also how do I assign the product id to the check box?
Thanks in advance..
|
|
|
|
|
« Reply to checkboxes and mysql/ php
|
|
|
| Thread Tools |
Search this Thread |
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
