wont write to database

woolseyr · December 4, 2006

Hi I have been trying to get this to work for some time and no luck... First off i have never been able to get anything to write to a database when i am using a primary key. here is the code for what i am doing. can anyone see what i am doing wrong. And i am not getting any errors with this either.
Thanks

CREATE TABLE `workorder` (
`printer_make` varchar(20) NOT NULL default '',
`product_num` varchar(40) NOT NULL default '',
`location` varchar(50) NOT NULL default '',
`serial_number` varchar(50) NOT NULL default '',
`work_order_num` tinyint(4) NOT NULL auto_increment,
`date` date NOT NULL default '0000-00-00',
`time_in` time NOT NULL default '00:00:00',
`time_out` time NOT NULL default '00:00:00',
`total_time` time NOT NULL default '00:00:00',
`reported_problem` text NOT NULL,
`resolution` text NOT NULL,
`pt_description` text NOT NULL,
`pt_number` varchar(50) NOT NULL default '',
`pt_qty` varchar(3) NOT NULL default '',
`milage` varchar(4) NOT NULL default '',
`cost_parts` varchar(10) NOT NULL default '',
`cost_work` varchar(10) NOT NULL default '',
`total` varchar(10) NOT NULL default '',
`problem_sum` text NOT NULL,
PRIMARY KEY (`work_order_num`)

################Page where you enter the data#########
<html>
<head>
<title>Untitled Document</title>
<body>

<form method=post action="order_submit.php">

<p><strong>Printer Make
<input name=printer_make type=text></strong></p>
<p><strong>Product Number <input name=product_num type=text></strong></p>
<p><strong>Location</strong><input name=location type=text>
<br>
</p>
<p><strong>Serial Number<input name=serial_number type=text>
<br>
</strong></p>
<p><strong> Date <input name=date type=text></strong></p>
<p><strong> Time In <input name=time_in type=text>
</strong></p>
<p><strong>Time Out <input name=time_out type=text></strong></p>
<p><strong>Total Time<input name=total_time type=text>
<br>
</strong></p>
<p><strong>Problem Summary:
<input type=text name=problem_sum>
</strong></p>
<p><strong>Description:</strong></p>
<p><strong>
<textarea name=reported_problem cols=100 rows=7></textarea>
</strong></p>
<p> </p>
<p><strong>Resolution:</strong></p>
<p>
<textarea name=resolution cols=100 rows=10></textarea>
</p>
<p><strong>Parts:</strong></p>
<p><strong>
Descrition:
<input type=text name=pt_description>
</strong></p>
<p><strong> Part Number:
<input type=text name=pt_number>
</strong></p>
<p>
<strong>Quantity:
<input type=text name=pt_qty>
</strong></p>
<p> <strong>Parts Cost:
<input type=text name=cost_parts>
</strong></p>
<p><strong>Cost:</strong></p>
<p><strong>
Milage @ 0.35
<input type=text name=milage>
</strong></p>
<p><strong>
Work
<input type=text name=cost_work>
</strong></p>
<p><strong>
Total
<input type=text name=total>
</strong></p>
<p> </p>
<p> </p>
<p align="center">
<input type="submit" name="Submit" value="Submit">
<input type="reset" name="Reset" value="Reset">
</p>

</form>
</body>
</html>

########Page where data is processed (order_submit.php)####

<html>
<head>
<title>Untitled Document</title>
<body>

<?
include ("../db_connect.php");
$printer_make=$_POST['printer_make'];
$product_num=$_POST['product_num'];
$location=$_POST['location'];
$serial_number=$_POST['serial_number'];
$date=$_POST['date'];
$time_in=$_POST['time_in'];
$time_out=$_POST['time_out'];
$total_time=$_POST['total_time'];
$reported_problem=$_POST['reported_problem'];
$resolution=$_POST['resolution'];
$problem_sum=$_POST['problem_sum'];
$pt_description=$_POST['pt_description'];
$pt_number=$_POST['pt_number'];
$pt_qty=$_POST['pt_qty'];
$milage=$_POST['milage'];
$cost_parts=$_POST['cost_parts'];
$cost_work=$_POST['cost_work'];
$total=$_POST['total'];

mysql_query("INSERT INTO $table_workorder
(printer_make, product_num, location, serial_number, date, time_in, time_out, total_time, reported_problem, resolution, problem_sum, pt_description, pt_number, pt_qty, milage, cost_parts, cost_work, total)
VALUES ('$printer_make', '$product_num', '$location', '$serial_number', '$date', '$time_in', '$time_out', '$total_time', '$reported_problem', '$resolution', '$problem_sum', '$pt_description', '$pt_number', '$pt_qty', '$milage', '$cost_parts', '$cost_work', '$total')");
Print "<center>Your information has been successfully added to the database.</center>";

?>

</body>
</html>

onlyican · December 4, 2006

Start with the main query

CREATE TABLE `workorder` (
`printer_make` varchar(20) NOT NULL default '',
`product_num` varchar(40) NOT NULL default '',
`location` varchar(50) NOT NULL default '',
`serial_number` varchar(50) NOT NULL default '',
`work_order_num` tinyint(4) NOT NULL auto_increment,
`date` date NOT NULL default '0000-00-00',
`time_in` time NOT NULL default '00:00:00',
`time_out` time NOT NULL default '00:00:00',
`total_time` time NOT NULL default '00:00:00',
`reported_problem` text NOT NULL,
`resolution` text NOT NULL,
`pt_description` text NOT NULL,
`pt_number` varchar(50) NOT NULL default '',
`pt_qty` varchar(3) NOT NULL default '',
`milage` varchar(4) NOT NULL default '',
`cost_parts` varchar(10) NOT NULL default '',
`cost_work` varchar(10) NOT NULL default '',
`total` varchar(10) NOT NULL default '',
`problem_sum` text NOT NULL,
PRIMARY KEY (`work_order_num`)

You need to close the query

CREATE TABLE `workorder` (
`printer_make` varchar(20) NOT NULL default '',
`product_num` varchar(40) NOT NULL default '',
`location` varchar(50) NOT NULL default '',
`serial_number` varchar(50) NOT NULL default '',
`work_order_num` tinyint(4) NOT NULL auto_increment,
`date` date NOT NULL default '0000-00-00',
`time_in` time NOT NULL default '00:00:00',
`time_out` time NOT NULL default '00:00:00',
`total_time` time NOT NULL default '00:00:00',
`reported_problem` text NOT NULL,
`resolution` text NOT NULL,
`pt_description` text NOT NULL,
`pt_number` varchar(50) NOT NULL default '',
`pt_qty` varchar(3) NOT NULL default '',
`milage` varchar(4) NOT NULL default '',
`cost_parts` varchar(10) NOT NULL default '',
`cost_work` varchar(10) NOT NULL default '',
`total` varchar(10) NOT NULL default '',
`problem_sum` text NOT NULL,
PRIMARY KEY (`work_order_num`)
);

woolseyr · December 4, 2006

that part is working it was created using phpmyadmin, and i missed the last ");" when i was coping it sorry

trq · December 4, 2006

Start with some debugging. eg;

[code=php:0]
mysql_query("YOURQUERY") or die(mysql_error());
[/code]

You might also try storing your query in a variable and then echoing that variable to the screen for debugging. This will let you see exactly what the query looks like.

woolseyr · December 4, 2006

when i add
mysql_query("YOURQUERY") or die(mysql_error());
into the code i get

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 '(printer_make, product_num, location, serial_number, date, time_in, time_out, to' at line 1

I am using this similar code elsewhere the only difference is this one is using a primary key and the other one isnt.
they both have the same style of input and form processing

trq · December 4, 2006

You'll need to echo your query to see what it looks like.

[code=php:0]
$sql = "INSERT INTO $table_workorder
(printer_make, product_num, location, serial_number, date, time_in, time_out, total_time, reported_problem, resolution, problem_sum, pt_description, pt_number, pt_qty, milage, cost_parts, cost_work, total)
VALUES ('$printer_make', '$product_num', '$location', '$serial_number', '$date', '$time_in', '$time_out', '$total_time', '$reported_problem', '$resolution', '$problem_sum', '$pt_description', '$pt_number', '$pt_qty', '$milage', '$cost_parts', '$cost_work', '$total')";
mysql_query($sql) or die($sql);
[/code]

jsladek · December 4, 2006

.

jcbarr · December 4, 2006

mysql_query("INSERT INTO $table_workorder

should be
mysql_query("INSERT INTO workorder

You just use the name of your table there. Not quite sure why you have the $table_ there. Also in your include file you might want to select the database that you want to connect to once you connect to your SQL server.

mysql_select_db()

woolseyr · December 5, 2006

Hey thanks everyone for you help. that seemed to fix it...
oh and i did have the mysql_select_db() it is in my db_connect file.
and i remembered where i got the $table from now. it was from another piece of code where they selected a datatable from a list of choices..
and thanks everyone again.

craygo · December 5, 2006

you should always have a primary key-auto increment field in a table. make it easier to update and to link if you decide to add relational tables later. The key to remember is when you insert data into a table with a primary key you do not insert anything into that field. it will increment on its own. and you do not insert anything into a table with a where clause.

insert:
[code]$sql = "INSERT INTO table SET name='$name', description='$descrtion'";[/code]
notice no reference to the id field

So when you update you can use
[code]$sql = "UPDATE table SET name='$name', description='$description' WHERE id = '$id'";[/code]

I also looked at your page and you should not have an auto-increment field in a form. You should insert the data and then return the auto-increment(or order_num) back to the user after the insert. The user should not be inserting a number because if they pick a number that is already in use you will get an error on your insert.

Ray

Sign In

wont write to database

Recommended Posts

woolseyr

Link to comment

Share on other sites

onlyican

Link to comment

Share on other sites

woolseyr

Link to comment

Share on other sites

trq

Link to comment

Share on other sites

woolseyr

Link to comment

Share on other sites

trq

Link to comment

Share on other sites

jsladek

Link to comment

Share on other sites

jcbarr

Link to comment

Share on other sites

woolseyr

Link to comment

Share on other sites

craygo

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information