[SOLVED] More advanced Query issue.

[NerdConcepts]

I was helped before with code similar to this. I was given the code to do check boxes to post information, etc. Well, I have been trying to use similar code to get make it where all my jobs show up in tables. Then there are drop down boxes with a list of all my employees. I can select an employee for each job, doesn't matter which one. Hit submit and it takes the work order number (which is hidden) and whoever I selected in the drop down box and updates the work orders with the user_id WHERE the wo_id is however the list is. Well, enough, here is the code.

 

<center>
<table border="0" cellpadding="0" cellspacing="0" align="center" width="100%">
	<tr>
		<td>
			<fieldset>
				<legend>Unassigned Work Orders</legend>

				<table border="0" cellspacing="0" cellpadding="0" height="300" width="100%">
					<tr valign="top">
						<td>
							<form action="jobs.php?assign" method="post">

								<table border="0" cellspacing="0" cellpadding="2" width="100%">
									<tr class="title">
										<td>MGT Area</td>
										<td>Type</td>
										<td>Customer Name</td>
										<td>Customer Address</td>
										<td>Customer City</td>
										<td> </td>
									</tr>
									<?PHP

									$query = "SELECT * FROM work_orders WHERE user_id='' ORDER BY wo_mgtarea ASC";
									$result = mysql_query($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());

									$bg = '#f4f3f2'; // Set background colour.

									while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
										$bg = ($bg=='#f4f3f2' ? '#f0efed' : '#f4f3f2'); // Switch background colour.
										?>
										<tr class="Text" bgcolor="<?PHP echo $bg; ?>">
											<td><?PHP echo $row['wo_mgtarea'] ?></td>
											<td><?PHP echo $row['wo_type'] ?></td>
											<td><?PHP echo $row['wo_cust_name'] ?></td>
											<td><?PHP echo $row['wo_cust_addy'] ?></td>
											<td><?PHP echo $row['wo_cust_city'] ?></td>
											<td>
												<input type="hidden" name="wo_id[]" value="<?PHP echo $row['wo_id']; ?>" />
												<select name="assign_select[]" class="assSelect">
													<option value="">ASSIGN</option>
													<option disabled="disabled">----------------------------</option>
													<?PHP
													$qR = "SELECT user_id,user_name FROM users ORDER BY user_id ASC";
													$rR = mysql_query($qR) or trigger_error("Query: $qR\n<br />MySQL Error: " . mysql_error());
													while ($rowR = mysql_fetch_array($rR, MYSQL_ASSOC)) {
														echo '<option value="' . $rowR['user_id'] . '">' . $rowR['user_id'] . ' - ' . $rowR['user_name'] . '</option>\n';
													}
													?>
												</select>
											</td>
										</tr>
										<?PHP
									}
									?>
								</table>
								<input type="submit" value="Assign" name="submit" class="regButton" />
								<input type="hidden" name="subAssign" value="TRUE" />
							</form>
						</td>
					</tr>
				</table>
			</fieldset>
		</td>
	</tr>
</table>
</center>

 

Once the "Assign" button is pressed it performs:

 

if (isset($_POST['subAssign'])) {
	$qUpdate = "UPDATE work_orders SET user_id IN('" . implode("','",$_POST['assign_select']) . "') WHERE wo_id IN('" . implode("','",$_POST['wo_id']) . "')";
	$rUpdate = mysql_query($qUpdate) or trigger_error("Query: $qUpdate\n<br />MySQL Error: " . mysql_error());
}

 

Although I get the following error. Doesn't seem like it should be throwing this error though, everything seems to look right.

 

An error occurred in script 'C:\apache2triad\htdocs\csvtophp\jobs.php' on line 10:
Query: UPDATE work_orders SET user_id IN('001','001','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','') WHERE wo_id IN('25','26','27','28','29','30','31','32','33','34','35','36','38','39','40','42','43','44','45','46','1','8','7','12','19','20','22','2','3','6','10','14','15','16','17','18','21','48')
MySQL 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 'IN('001','001','','','','','','','','','','','','','','','','','','','','','',''' at line 1
Date/Time: 7-07-2007 22:19:03 

 

NOTE: the "001" in the user_id comes from the database assigned INT(3) will zerofill.

[teng84]

your sql statement is very wrong you try to set the value under that implode?

[teng84]

explain what you want with this and ill correct it for you

if (isset($_POST['subAssign'])) {

$qUpdate = "UPDATE work_orders SET user_id IN('" . implode("','",$_POST['assign_select']) . "') WHERE wo_id IN('" . implode("','",$_POST['wo_id']) . "')";

$rUpdate = mysql_query($qUpdate) or trigger_error("Query: $qUpdate\n<br />MySQL Error: " . mysql_error());

}

[NerdConcepts]

Well, what it did before was if a box is checked it gets a value and puts "001" to all wo_id's that are checked. Im trying to be able to have drop down boxes for each work order. That way all I have to do it select an employee for all jobs that I currently want to assign, then click one submit button and it take all the wo_id's which are assigned from "hidden" form elements and the user_id which is assigned from the corresponding drop down box and updates the database. Say wo_id=25 and I select user_id of 001. It would update the database where wo_id is 25 and put 001 in the column labeled "user_id".

 

The "subAssign" is if the button is pressed is passes on the information. Thus all wo_ids like '1','2' etc. and whatever user_id is selected, if any at all.

 

Here is the code that I use something similar to this with inventory.

 

	$qUpdate = "UPDATE inventory_data SET user_id='$id' WHERE inventory_id IN('" . implode("','",$_POST['assign_select']) . "')";
	$rUpdate = mysql_query($qUpdate) or trigger_error("Query: $qUpdate\n<br />MySQL Error: " . mysql_error());

 

what that does is what was described above. It grabs all values form a check box. If the box is checked is passes on the inventory_id and then assigns whatever employee I had previously select (ex: 001) and sets 001 in user_id in all the ones that the box was checked. This would take WAY to much time to go back and forth selecting an employee then assign 3 jobs, then going back and forth with 10+ employees.

 

[NerdConcepts]

I still can't figure this out, tried a few combinations of different ideas but still not working. Not sure what to do, I do know this is possible though.

[per1os]

UPDATE work_orders SET user_id IN('001','001','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','')

 

That is inproper, you are trying to set the userid to be something, you either have to run a different query for each of those records or what. I really do not understand what you are trying to do there.

 

$qUpdate = "UPDATE inventory_data SET user_id='$id' WHERE inventory_id IN('" . implode("','",$_POST['assign_select']) . "')";
	$rUpdate = mysql_query($qUpdate) or trigger_error("Query: $qUpdate\n<br />MySQL Error: " . mysql_error());

 

That doesn't work? That is how it should be setup

 

if (isset($_POST['subAssign'])) {
      $qUpdate = "UPDATE work_orders SET user_id = '" . $id . "' WHERE wo_id IN('" . implode("','",$_POST['wo_id']) . "')";
      $rUpdate = mysql_query($qUpdate) or trigger_error("Query: $qUpdate\n
MySQL Error: " . mysql_error());
   }

 

You cannot use the IN statement in the set portion of the query.

[NerdConcepts]

the whole $qUpdate = "UPDATE inventory_data SET user_id='$id' .... that works, I didn't say it didn't work. That is the code I was trying to modify.

 

If you look at the code at the very top you can see what I'm trying to do. It runs a query and finds all works order that have yet to be assigned to anyone.

 

Displays the results in a table with a hidden field that contains the wo_id (work order id, from the database) and a select box that includes all employees. Again, it does this for ever work order that I have yet to assign to anyone.

 

At the bottom of the page is a submit button, this passes on the the information from the table which would include:

wo_id[] (which is a list of the wo_id's from the database, since I organize by it's area code (not zip code) it displays in a weird order.)

assign_select[] (which is what, if anything, that I selected from the drop down box.)

 

the assign_select[] stuff allows me to select the same, different, or no employee at all. That way I can just select whoever I want (if anyone) for each job and only have to submit once.

 

this is why is has the ('001','001','',''....) It was an example of me selecting the employee that has the id of '001' on the first two work orders and not selecting anyone for the other work orders.

 

With the inventory it is fine since I select 1 employee at a time to assign inventory to, because as they walk out at the beginning of the day I select them on the site, see what they are taking with them and assign the inventory over to them. That allows me to make sure that they bring it back if it wasn't used at a job.

[NerdConcepts]

I guess what I am trying to say is you have, say 5 rows that have been displayed with PHP, you would have

 

MGT AREA      Type      Customer Name      City        State      (Blank)

TT                  1          WHATEVER        NOWHERE  OK        (SELECT BOX with Employees) and (Hidden field yielding wo_id of '4')

TT                  1          WHATEVER        NOWHERE  OK        (SELECT BOX with Employees) and (Hidden field yielding wo_id of '3')

TT                  1          WHATEVER        NOWHERE  OK        (SELECT BOX with Employees) and (Hidden field yielding wo_id of '2')

TT                  1          WHATEVER        NOWHERE  OK        (SELECT BOX with Employees) and (Hidden field yielding wo_id of '1')

TT                  1          WHATEVER        NOWHERE  OK        (SELECT BOX with Employees) and (Hidden field yielding wo_id of '6')

TT                  1          WHATEVER        NOWHERE  OK        (SELECT BOX with Employees) and (Hidden field yielding wo_id of '5')

 

If I select, top to bottom for the select boxes. '001','001','003','012','003','001'

I need a ways to where is updates each work order (tracked by wo_id) and puts whatever employee ID i select. Thus:

UPDATE work_orders SET user_id='001' WHERE wo_id='4'

UPDATE work_orders SET user_id='001' WHERE wo_id='3'

UPDATE work_orders SET user_id='003' WHERE wo_id='2'

UPDATE work_orders SET user_id='012' WHERE wo_id='1'

UPDATE work_orders SET user_id='003' WHERE wo_id='6'

UPDATE work_orders SET user_id='001' WHERE wo_id='5'

 

Would be easy if it was dynamic. That is just an example of if I choose those user_id's for the wo_id's shown above. But since the list of work_orders is created by running: "SELECT * FROM work_orders WHERE user_id='' ORDER BY wo_mgtarea ASC". This selects all the work orders where there is no one assigned to the job. Hence, nothing, in the single quotes. Because of this I can't just change the "name" of the select box on each one, since sometimes there is 1, and others there are 40 records. That is why I was trying to use the assign_select[] and wo_id[] so it would work, but I'm really doing something wrong. Not sure if a foreach or something along those lines needs to be used so that individual query(s) can be ran.

[teng84]

$ctr=0;

foreach($_POST['test']=$x)

{

$ctr++;

mysql_query('update from tablename set "'.$_POST['another form'][$ctr].'" where id='.$x.'');

}

 

maybe

[NerdConcepts]

Well, that got me enough code that I was able to re-write the code to work.

 

$ctr=0;
foreach ($_POST['wo_id'] as $x) {
$ctr++;
$query = "UPDATE work_orders SET user_id='" . $_POST['assign_select'][$ctr] . "' WHERE wo_id='" . $x . "'";
$result = mysql_query($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());
}

 

I get this error:

An error occurred in script 'C:\apache2triad\htdocs\csvtophp\jobs.php' on line 15:
Undefined offset: 36
Date/Time: 7-07-2007 21:53:19

Note: There are 36 work orders being listed. So not sure what is going on, but no query issue and it still doesn't update anything tho.

[NerdConcepts]

I've been look a lot at "foreach" commands and the code just doesn't seem to be all there. Did some more experimenting and I've noticed that only the last piece throws the error. For example: 35 records, I hit the assign button after selecting and it throws and error before the last one says that it is undefined. Meaning "Undefined offset: 35"

 

Not sure why. And sometimes it updates in the database and sometimes it doesn't. Still not sure why, I think it may have something to do with the offset error.

 

UPDATE:

Found out if I select just the top work order and hit the submit. It says that it is not being updated. But if I select 2 to be updated it miss places saying that the 1st one has the 2nd ones selection and the 2nd one has no selection. So yeah, something is wrong with the foreach but I've never used foreach before so I have no idea what it should look like and can't find any other posts that point to this.

[NerdConcepts]

Just posting what I've figured out that fixes the problems. $ctr must be -1, sense every time the foreach is looped it adds one, and PHP seems to start at 0 and go forward. Here is the final foreach code, the other code hasn't changed.

 

$ctr = -1;
foreach ($_POST['wo_id'] as $x) {
$ctr++;
$assign = $_POST['assign_select'][$ctr];
$query = "UPDATE work_orders SET user_id='$assign' WHERE wo_id='$x'";
$result = mysql_query($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());
echo $query . ' was updated<br />';
}

 

Had to sleep on it and it clicked. Thanks for helping.