...

View Full Version : Displaying my Query



kenwvs
08-07-2006, 09:05 PM
I have a table that has data in it that people have submitted via a form. Now I want to review that same data in the form that it was originally completed in. Is it possible to display this data, in the format by using one of the columns as an identifier..

Basically, these are work orders and if I key in a work order #, I want the work order to come up in the form, where I can review it, and then update it, after i have added comments and a purchase order #.

If someone can put me in the direction I need to go, it would be appreciated, I am new to this and totally confused. I think I would use the SELECT but not sure how to get it back into the form.

oracleguy
08-07-2006, 09:16 PM
Is this a web form? I assume that is the case. In any event, you would select the data from the table and filtering on the work order number you entered in.

e.g.

SELECT column1, column2, column3 FROM yourtable WHERE workorder = numberyouentered

Then you would just set the default values of the fields in the form using the data returned from the query.

kenwvs
08-07-2006, 09:20 PM
Thank you very much for the quick reply. when you say set the default values on the fields, do you mean, I would put a command in each field that corresponds to the column name?

oracleguy
08-07-2006, 11:05 PM
Well, can you clarify where the form is? Like is it a webpage or is the form in an application?

kenwvs
08-07-2006, 11:11 PM
the form is a webpage... Let me try to explain what I am trying to accomplish.

When some engines go down, we need the techs to fill out the work order and submit it to the database (I have this working). A supervisor will then review the workorder, make any comments (in a field that wasn't available to original tech) and assign a purchase order to it. (purchase order not available to tech either) The original tech will then be able to review the form and use the purchase order to order parts for this engine....the process will continue until it is closed when the repair is finished, but I am not that far. It will basically repeat the process once I get this second step working.

Here is what i have for the SELECT query, but not sure if I am even close to getting it to work....


<?php
include_once "myconnect.php";
?>
<form action="" method="post" enctype="multipart/form-data">
<center><div>Work Order #:<input type="text" size="10" maxlength="10" name="woorder">
<?php $query=mysql_query("SELECT Work, Sched, Name, Site, Serial, Hours, Starts, Issue, Severity,
Resolution, Assistance, PartsA, PartsB, PartsC, PartsD, PartsE, PartsF, PartsG,
Safety, Image1, Image2, Image3, Image4 FROM work WHERE Work=$woorder")or die(mysql_error())
;?>
<div><input type="submit" name="Submit" value="Request Work Order"></div>
</form>

oracleguy
08-07-2006, 11:24 PM
Alright, you are headed in the right direction.



<form action="nextpage.php" method="post">
<center><div>Work Order #:<input type="text" size="10" maxlength="10" name="woorder">
<div><input type="submit" name="Submit" value="Request Work Order"></div>


nextpage.php


<?php
include_once "myconnect.php";
$query=mysql_query("SELECT Work, Sched, Name, Site, Serial, Hours, Starts, Issue, Severity,
Resolution, Assistance, PartsA, PartsB, PartsC, PartsD, PartsE, PartsF, PartsG,
Safety, Image1, Image2, Image3, Image4 FROM work WHERE Work=$woorder")or die(mysql_error())
;?>

<form action="" method="post">
<input type="text" value="<? echo mysql_result($query, 0, 'Work') ?>" size="10" id="Work">
</form>


This is sort of what I mean by set the default values. When the page loads the value for work will be already loaded into the textbox. This is a very basic example but should hopefully get you going in the right direction.

And you have to make sure the $woorder variable gets set on the next page and stuff.

kenwvs
08-07-2006, 11:27 PM
Thank You very much.....this will get me going, where I should be able to put together something. Your willingness to share your talents is greatly appreciated.

Ken

kenwvs
08-07-2006, 11:45 PM
Quick Question, just so I am understanding what is happening.


value="<? echo mysql_result($query, 0, 'Work')

What does the 0 do, between the $query and Work I am thinking that the query is the query we ran, and the work is the name of the column in the DB, but not sure about the 0.

thanks

Fumigator
08-08-2006, 12:37 AM
The 0 says "get the first row of the query (row zero)". In the case you had several rows with a work order that matched, you'd need to specify which one you wanted.

mysql_result definition (http://us3.php.net/manual/en/function.mysql-result.php)

kenwvs
08-08-2006, 04:31 AM
I thought I had this working the way it was suppose to, but then I changed the table name, and one of the fields, as it was getting confusing as the names were all the same...... and now for some reason, it won't work. I am wondering if you can tell what isn't right.

This is the error I am getting. Before I changed the name, the old name wasn't in my DB anywhere, so I am not sure what is causing this.

Unknown column '$Contact' in 'where clause'


<?php
include_once "myconnect.php";
$query=mysql_query('SELECT Work, Sched, Name, Site, Serial, Hours, Starts, Issue, Severity,
Resolution, Assistance, PartsA, PartsB, PartsC, PartsD, PartsE, PartsF, PartsG,
Safety, Image1, Image2, Image3, Image4 FROM workorder WHERE Work=$Contact')or die(mysql_error());?>

<form action="" method="post">
<head>
<!--<link href="ge.css" rel="stylesheet" type="text/css"> -->
<title>GE Work Order</title>
</head>
<BODY BGCOLOR="gainsboro">
<?php
/* womanager.php */
if (!isset($_POST['Submit'])){
?>
Purchase Order #:<input type="text" size="12" maxlength="120" name="Purchase">
<center><div>Work Order #:<input type="text" value="<?php echo mysql_result($query, 0, 'Work') ?>" size="10" id="Work">
Date:<input type="text" value="<?php echo mysql_result($query, 0, 'Sched') ?>" size="10" id="Sched">

Fumigator
08-08-2006, 02:39 PM
$Contact is a PHP variable, or at least that's how you're trying to use it. Where are you setting its value? Is your column "Work" an integer? If not, you'll need to enclose $Contact in quotes.

kenwvs
08-08-2006, 03:05 PM
Thanks for the tip on this. I am feeling dumb, but, I can't figure out how to set the value of $contact. I know that $contact=work but I am not sure how to set it up. Work is a varchar in the table, but contact is not in the table.

Ken

kenwvs
08-09-2006, 12:09 AM
I have got it to the point where it isn't generating errors, but now it is putting the code in where the fields normally go, and i am thinking this is because i don't have it thinking that it is in php mode. If I take the " out after value and after the ?> then it doesn't put it in the text field, and it is now in php mode, but, then the closing php tag is in the text field.

Here is what I have for the line of code and this is appearing in the textbox.

<center><div>Work Order #:<input type="text" value="<? echo mysql_result($query, 0, 'Work') ?>" size="10" id="Work">

When you say that I need to "set the variable" is this what you mean?
$Contact = $_POST['Work'];

oracleguy
08-09-2006, 08:07 PM
So it is just showing the PHP code?

Try changing it from "<? echo mysql_result($query, 0, 'Work') ?>" to "<?php echo mysql_result($query, 0, 'Work') ?>"

Your server's PHP configuration might not be setup to accept the former method of encapsulating PHP code.

kenwvs
08-09-2006, 09:21 PM
It is actually working exactly like it is suppose to, except for the fields tht are textboxes. They only display one row of text, so some text gets cut off. Is it possible to have 4 rows, which matches what they had on th origianal form.

oracleguy
08-09-2006, 09:44 PM
You could try using a textarea element instead.

kenwvs
08-10-2006, 12:43 AM
I have it set as a textbox, but it won't recognize how many rows you stipulate. You can scroll past the end of the box to get the rest of the text, but it should be possible to set it up as a text box.

oracleguy
08-10-2006, 05:11 PM
The <input type="text" ... > element is a single line element. It doesn't support being multiple rows high.

To do that you need to use the textarea element, e.g.

<textarea id="txt1" rows="4" cols="30"><?php echo mysql_result($query, 0, 'Work') ?></textarea>

Sorry if I mis-understood and you are already doing this.

kenwvs
08-10-2006, 05:27 PM
When I said textbox, I meant textarea, my apologies......

I have tried configuring it the way you have outlined it and it looks like that is going to work....just need to figure out why all the information after that point is going inside the textarea field.

thanks again for the help, it is appreciated.

Does this command to UPDATE only 2 fields looks close....I don't need the answer, just if I am on the right track....

mysql_query ("UPDATE 'workorder'setPurchase = $Purchase, Comment = $Comment")or die(mysql_error());
echo "This Work Order has been Updated Successfully";

oracleguy
08-10-2006, 05:37 PM
I have tried configuring it the way you have outlined it and it looks like that is going to work....just need to figure out why all the information after that point is going inside the textarea field.

Did you make sure to have a closing </textarea> tag?

As for the query, it looks okay except you didn't put a space after 'SET' and there is no where clause so it will update all rows in the table when it executes.

kenwvs
08-10-2006, 05:45 PM
Dang you are good........I did miss the closing tag.

With the query, would it update all the rows with this info, or with the info that pertains to the appropriate row? It will only have one row of data open, so it wouldn't make a difference, but I should do it properly, so would it look like this...

mysql_query ("UPDATE `workorder` set Purchase = $Purchase, Comment = $Comment WHERE "work=$contact"")or die(mysql_error());
echo "This Work Order has been Updated Successfully";

It isn't liking something around the ,Comment= is the comma the right separator?

oracleguy
08-10-2006, 06:56 PM
If you execute the update query without the WHERE clause against that table, regardless of only having one row open on the page, it would make those changes to those columns in all the rows. Unless you are doing something special but I'm speaking in general.

But what you have looks good now.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum