...

View Full Version : Queries not giving desired results



kenwvs
09-06-2006, 11:20 PM
I have a form, with some queries to get the data into the various DB tables, but I am thinking I am not understanding how it is suppose to work with parent/child tables, as I am not even close to getting the desired results.

Here is what I am getting.

I complete the form and then press submit and here is what happens

1. I get seven rows of data in the workorder table (I am thinking this has something to do with the fact that I have 7 rows for part numbers and descriptions....even though they are suppose to go to their own table)

2. I get 2 rows in the parts table.... I think one row for the number and one row for the description because one row has NULL in the description field and the other row has NULL in the number field.

3. The employee table adds the employee name to the table, even though it is already in there.

The set up that I have is tables that are called workorder, parts, employees, remarks, images. I also have workorder_parts, workorder_employees, workorder_images. I haven't got the form set up to deal with the images and remarks yet. I want to solve these issues, then will be able to do the others.

The parts table has one column for parts number and one column for description. I am trying to set it up so that it checks the form and if the field is empty it won't put empty rows in the table. Right now it will only put two rows in the table (one for part number and one for description) regardless of how many of the 7 fields are completed.

Here is how the parts are set up in the form.


<div><B><center>Part #Part Description</center></B>
</div>
<div><center>
<input type="text" size="10" maxlength="10" name="number[]">
<input type="text" size="93" maxlength="93" name="description[]"><BR>
<input type="text" size="10" maxlength="10" name="number[]">
<input type="text" size="93" maxlength="93" name="description[]"><BR>
<input type="text" size="10" maxlength="10" name="number[]">
<input type="text" size="93" maxlength="93" name="description[]"><BR>
<input type="text" size="10" maxlength="10" name="number[]">
<input type="text" size="93" maxlength="93" name="description[]"><BR>
<input type="text" size="10" maxlength="10" name="number[]">
<input type="text" size="93" maxlength="93" name="description[]"><BR>
<input type="text" size="10" maxlength="10" name="number[]">
<input type="text" size="93" maxlength="93" name="description[]"><BR>
<input type="text" size="10" maxlength="10" name="number[]">
<input type="text" size="93" maxlength="93" name="description[]"><BR></center>
</div><BR>

Here is how I have the scripting all set up.


if (!isset($_POST['Submit'])){

The Form itself goes here and is set up and working.

}
else
{
$work = $_POST["work"];
$sched = $_POST["sched"];
$name = $_POST["tech"];
$site = $_POST["site"];
$serial = $_POST["serial"];
$hours = $_POST["hours"];
$starts = $_POST["starts"];
$issue = $_POST["issue"];
$severity = $_POST["severe"];
$resolution = $_POST["resolve"];
$assistance = $_POST["assist"];
$safety = $_POST["safe"];
$number = $_POST["number"];
$description = $_POST["description"];

$_POST['number'][0];
$_POST['number'][1];
$_POST['number'][2];
$_POST['number'][3];
$_POST['number'][4];
$_POST['number'][5];
$_POST['number'][6];
$_POST['description'][0];
$_POST['description'][1];
$_POST['description'][2];
$_POST['description'][3];
$_POST['description'][4];
$_POST['description'][5];
$_POST['description'][6];

if (!empty($_POST['number']))
$parts = array($number);
foreach ($number as $key => $number)

if (!empty($_POST['number']))
$parts = array($description);
foreach ($description as $key =>$description)

mysql_query ("Insert into `workorder`(work, sched, site, serial, hours,
starts, issue, severity, resolution, assistance, safety) VALUES
('$work', '$sched', '$site', '$serial', '$hours', '$starts',
'$issue', '$severity', '$resolution', '$assistance', '$safety')")or die(mysql_error());

mysql_query ("Insert into `employees` (name) VALUES ('$name')")or die(mysql_error());

mysql_query ("Insert into `parts`(number)VALUES ('$number')")or die(mysql_error());

mysql_query ("Insert into `parts`(description)VALUES ('$description')")or die(mysql_error());

echo "Work Order # '$work' has been Successfully Added to the System";

GJay
09-06-2006, 11:42 PM
yeah, your logic is a bit off.

I'd written the stuff below, and was just checking something, when it occured to me that you're missing curly brackets, in many places
conditionals (if, foreach etc.) will only act on the line that follows, unless they have a block enclosed with {}s

I'm not quite sure what


$_POST['number'][0];

and friends are there for? they just get in the way...



$parts = array($number);

and


$parts = array($description);

aren't doing anything, care to explain the reasoning?

When you have a form like this:


<input type="text" name="description[]" />
<input type="text" name="number[]" />
<input type="text" name="description[]" />
<input type="text" name="number[]" />

and you enter 'widget1', 10, 'widget2' and 20 (respectively), then when the form is submitted, $_POST looks like:
[code]
Array (
'description'=> Array (
0=>'widget1',
1=>'widget2'
),
'number'=>Array(
0=>10,
2=>20
)
)
[/php]

to insert an entry for each line, you'd want something like:


foreach($_POST['description'] as $key=>$description) {
$query='INSERT INTO table (description,number) VALUES (\''.mysql_real_escape_string($description).'','.intval($_POST['number'][$key].')';
mysql_query($query) or die(mysql_error().$query);

}


Basically, you seem to be trying to do to much too soon.
Figure out one bit at a time, check that it works properly in isolation and then worry about putting things together- problems will be much easier to identify.

kenwvs
09-07-2006, 01:57 AM
foreach($_POST['description'] as $key=>$description) {
$query='INSERT INTO table (description,number) VALUES (\''.mysql_real_escape_string($description).'','.intval($_POST['number'][$key].')';
mysql_query($query) or die(mysql_error().$query);

}


is there something missing in this code.... when I try to use it, it complains about an unexpected comma, but if I remove the comma in there, it still complains about an unexpected error. With it in with the rest of my code, it causes all php code after it to not function in php....example my mysql_query are not working after this peice of code...

vinyl-junkie
09-07-2006, 04:26 AM
Put an echo in there to see what the query itself looks like. That should tell you what the problem is.


foreach($_POST['description'] as $key=>$description) {
$query='INSERT INTO table (description,number) VALUES (\''.mysql_real_escape_string($description).'','.intval($_POST['number'][$key].')';
echo $query . "<br><br>";
mysql_query($query) or die(mysql_error().$query);

}

GJay
09-07-2006, 09:16 AM
).'','.intval

needs a /, i.e.


).'\','.intval


it was just an example though, not necessarily appropriate to your situation...



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum