how would i do this?

[jbrill]

Hey guys,

Just a quick question,

Im working on a program to manage jobs for a machine shop.

 

Im in a bit of a dilemma right now.

For this project, each job has a "job number" and then there are multiple steps for each "job number"

example would be job number= 1 step number=1 so the job number would be 1.2 and then the next step would be 1.3 etc.

 

how would i do this? my "job number" is an auto incrementing field, so that is taken care of. but how do i add the decimal and the number after? right now i have the number after the decimal in another table with an auto incrementing field aswell, problem is that it keeps incrementing so so for job 2 the decimal would be what ever was after the last step from job number 1

 

how would i do this?

[The Little Guy]

in your second table, the one that contains the number after the period, does it have a number to match up with the number before the period for each row?

[jbrill]

yes, the second table contains all  the "step" info, and the 1st table contains all the main "job info"

[cooldude832]

its called a sub primary key (at least I call it that)

so your table is structured like this (depending on the data you might want 2 tables)

 

1 Table version

ID JobID StepID Data

1 1 1 yes

2 1 2 No

3 1 3 No

4 2 1 yes

5 2 2 no

 

 

2 Table Version

ID JobData

1 Client1

2 Client2

3 Cleint3

 

//Second Table

 

ID JobID StepID StepData

1 1 1 yes

2 1 2 no

3 1 3 no

4 1 4 yes

5 2 1 no

6 2 2 yes

7 2 3 no

8 3 1 yes

9 3 2 no

 

1 table lets you set parameters for a job outside the step data (its a more logical choice)  But if you have no info for the job outside the ID number than maybe 1 table will do

 

Note:

so you can query off tables simply by saying

select * from `table2` Where JobID = '$jobID' ORDER BY StepID ASC

and you will have all the steps relating to that job in order

you don't need to join them together at all using this method and if you get them in in a form $step = "1.2" you just need to string manipulate it

 

Note 2:

Sorry about tabs being off, but if you are questioning my version yes I'm using the primary key ID in table 1 for the jobs as a sub primary key in table2 its that JobID number that creates the relationship between table 1 and table 2, this allows you to write up job fields like client name, contact, date due, date started, summary etc et in there and then simply have the raw step data in the second table

[The Little Guy]

but... does the second table also contain the main job number?

 

like 1.2, 3.6, 7.9

 

exactly cooldude

[jbrill]

yes, but how to do i make the numebr after the decimal auto increment for each job number, and then on the next job number, start again at .1 and go up?

[cooldude832]

What do you mean add up the number give us an example of why this would be needed

This is what i see to add steps

 

<?php

$q = "select StepID From `Table2` WHERE JobID = '$jobid' ORDER BY StepID DESC LIMIT 1";
$r = mysql_query($q) or die(mysql_error());
//Checks to see if a job exsist because the num rows would be >0
if(mysql_num_rows($r)){
$row = mysql_fetch_assoc($r);
$InsertRow = $row['StepID']+1;
}
else{
$InsertRow = 1;
}
//Make sense?
$Jobstring = $jobnumber.".".$InsertRow;
?>

 

[jbrill]

getting more and more confused with every post, here is the code i have so far. This is the "add a part" page.

<?php
include 'admin_header.php';
$jobid = $_GET['jobid'];
if($_SESSION['type'] != "admin")
{
echo "You are either not logged in, or logged in but with no authority to access this page.<br>please visit the log in page <a href=index.php>here</a>.";
}




else
{
// execute the real stuff if the login is valid

include 'admin_jobmenu.php';


?>


				<div align="center">

					<!-- end header -->
<?php

if($_POST['drawing_number']!="")
{
// process the data load scheme




$update = 'INSERT INTO job_part (quote_id, drawing_number, quantity, material, purchased_by, supplier, status) VALUES ("'.$_GET['jobid'].'","'.$_POST['drawing_number'].'","'.$_POST['quantity'].'","'.$_POST['material'].'","'.$_POST['purchased_by'].'","'.$_POST['supplier'].'","'.$_POST['status'].'")';

echo $update."<br>";

$check = mysql_query($update);

$lastid = mysql_insert_id();

echo "<table align=\"center\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\" class=\"tableoutline\">
<tr>
<td>";
echo "New Job Part Created<META HTTP-EQUIV=\"Refresh\" CONTENT=\"1; URL=admin_findparts.php?idr=$jobid\"></td></tr></table>";


}

// data load scheme ends here

else
{// if no data loaded, run this..
?>
<br>
<form name="createpart" method="post">
						<br>
						<table border="0" width="100%" align="center">

		<td align="center">
		<?php
			if ($msg == "") {
				echo(" ");					
			} else {
				echo($msg);
			}
		?>
		</td>
	</tr>
</table>
						<table align="center" border="0" cellpadding="0" cellspacing="0" class="tableoutline">
<tr>
<td>
<table align="center" border="0" cellpadding="10" cellspacing="0" bgcolor="#ffffff" style="border:1px solid #000000">
<tr class="colorheader">
	<td align="center" colspan="2" class="colorhedr"><font class="section">Create A New Job Part</font></td>
</tr>
<tr class="tablelight">
	<td align="right" class="editpdL"><strong>Drawing Number</strong></td>
	<td class="editpdR"><input type="text" name="drawing_number" size="20"></td>
</tr>
	<tr class="tablelight">
	<td align="right" class="editpdL"><strong>Quantity</strong></td>
	<td class="editpdR"><input type="text" name="quantity" size="20"></td>
</tr>
<tr class="tabledark">
	<td align="right" class="editpdL"><strong>Material</strong></td>
	<td class="editpdR">
        <select name="material">
<option selected value=''>-- Select Category --</option>
<?

$statussql = "SELECT * FROM materials ORDER BY cat";
$cats = mysql_query($statussql);
$status = mysql_fetch_array($cats);

do

{
echo "<option value='".$status["material"]."'>".$status["cat"]." - ".$status["material"]."</option>";


} while ($status = mysql_fetch_array($cats));



?>
</select>
</td>
</tr>

<tr class="tabledark">
	<td align="right" class="editpdL"><strong>Purchased By</strong></td>
	<td class="editpdR">
        <input type="Checkbox" name="purchased_by" value="MGM"> MGM<br>
	<input type="Checkbox" name="purchased_by" value="Customer"> Customer
        </td>
</tr>

	<tr class="tabledark">
	<td align="right" class="editpdL"><strong>Supplier</strong></td>
	<td class="editpdR">
        <select name="supplier">
<option selected value=''>-- Select Supplier --</option>
<?

$supplysql = "SELECT * FROM suppliers ORDER BY name";
$supplier = mysql_query($supplysql);
$supply = mysql_fetch_array($supplier);

do

{
echo "<option value='".$supply["name"]."'>".$supply["name"]."</option>";


} while ($supply = mysql_fetch_array($supplier));



?>
</select>

        </td>
</tr>

	<tr class="tabledark">
	<td align="right" class="editpdL"><strong>Status</strong></td>
	<td class="editpdR">
                <select name="status">
<option selected value=''>-- Select Supplier --</option>
<?

$statussql = "SELECT * FROM complete";
$status = mysql_query($statussql);
$stat = mysql_fetch_array($status);

do

{
echo "<option value='".$stat["status"]."'>".$stat["status"]."</option>";


} while ($stat = mysql_fetch_array($status));



?>
</select>
        </td>
</tr>

<tr class="tabledark">
	<td class="editfootSingle" colspan="2" align="center">

		<input type="submit" name="Create" value="Create Job Part" class="inputsubmit">
	</td>
</tr>
</table>

</td>
</tr>	  
    </table>		

	</form>
        
        

					<br>
					<!-- begin footer --></div>
			<?
				 }
}


			include 'admin_footer.php';




			?>

 

[jbrill]

anyone?  ???

[jbrill]

can someone please show me how this is done? I really need to get this done

[akitchin]

assuming you're using cooldude's method (ie. a second column for the stepID), you can retrieve the current stepID for a given jobID from the table using a query like this:

 

SELECT MAX(stepID) FROM table WHERE jobID='job_ID_here'

 

if you want to save yourself some PHP and let MySQL do some footwork (usually a good idea), you can use a subquery to auto-insert the next stepID for a given part:

 

INSERT INTO table (jobID, stepID, data) VALUES ('job_ID_here', (SELECT MAX(stepID) + 1 FROM table WHERE jobID='job_ID_here'), 'some data')

 

i'll be honest, i haven't gone through this entire thread, nor have i read through all your code - i'm just hoping these queries will give you a bit of inspiration into how to get the job.stepID system going.

[cooldude832]

akitchin what if that jobID has no steps yet will that method work?  If not mine is fool proof for getting step id

[jbrill]

akitchin what if that jobID has no steps yet will that method work?  If not mine is fool proof for getting step id

 

Could you show me what you technique would look like when placed in my code? i really dont understand your technique, pretty new to php but if you place it where it should be then i should be able to get it to work.