Online Repair Status system?

[Mal]

Hey, looking for ideas here. I'm considering setting up an online repair status system for my company, where a customer can simply enter a repair number (we generate a number already based on the date of the repair, i.e. 0401-01 would be the first machine on April 1st), and I'm looking to find the best method. It doesn't need to contain any personal information, simply the type of machine so they know they're looking at the right status message, and then "In Queue", "On Bench", "Waiting for Part X", "Finished", etc on the page once they enter that number. I'm not really quite knowledgeable enough to set this up from scratch, but if there was a system that I could simply adapt to do this I think I could handle that (I can edit PHP/MySQL, that kind of stuff, just wouldn't feel comfortable writing original code, unless I had a good tutorial specifically on that to follow).

Please offer any advice/links/etc that you are willing to share! I will be very appreciative.

jW

 

[SrWebDeveloper]

This is not a tough job to do but maybe this is just a tad more complex that you actually described if I follow your requirements as stated. I think there needs to be a separate back end tool where the database is managed, i.e. someone in charge of entering each record. And managing records properly means at least allowing for insert, update, edit and delete of that record even if no other features added. The record structure you described is simple enough, but did you realize you'll need both a front end and back end? And anyone who learns your no so random numbering system could "steal" the status record of someone else, so maybe there are basic security concerns, if you care (and should).

So my first thought is, it's not a hard job and it is small, but not quite as small as you thought. My next thought is, based on your needs, I cannot think of any pre-made software that does this without introducing features you don't need, such as generating it's own unique ID's (not your method) and confusing users with email notifications, fields you don't need, etc. My final thought is you could do this on your own very easily, but would require basic knowledge of:

* How to create a MySQL database, tables and associated fields (structure)
* Basic knowledge of forms processing
* How to use PHP to query MySQL via a submitted form

There are plenty of links on each in this forum, but the first one is likely the toughest for you - so if your webhost provides phpMyAdmin or other CPANEL controls to manage databases, by all means use them. Just some thoughts. Someone else might have done this before and might post code or a link to some open source project or whatever that does just this, but nothing off the top of my head.

-jim

 

[Mal]

This is not a tough job to do but maybe this is just a tad more complex that you actually described if I follow your requirements as stated. I think there needs to be a separate back end tool where the database is managed, i.e. someone in charge of entering each record. And managing records properly means at least allowing for insert, update, edit and delete of that record even if no other features added. The record structure you described is simple enough, but did you realize you'll need both a front end and back end? And anyone who learns your no so random numbering system could "steal" the status record of someone else, so maybe there are basic security concerns, if you care (and should).

I do realize we need a back end and a front end. We have CPanel and phpMyAdmin available, so I can simply use those for entry, that's not a big deal. The front end and the actual database are my biggest needs. Actually, for that matter, I can set up a database, I just need to know how to reference it from the front end form and display it properly on the page. BTW, security is pretty much a non-issue because I don't plan to have any personal information. I pretty much would just want to have the page show the type of machine (MacBook, iMac, etc), the date received, and the current status. No names, no serial numbers, etc.

So my first thought is, it's not a hard job and it is small, but not quite as small as you thought. My next thought is, based on your needs, I cannot think of any pre-made software that does this without introducing features you don't need, such as generating it's own unique ID's (not your method) and confusing users with email notifications, fields you don't need, etc. My final thought is you could do this on your own very easily, but would require basic knowledge of:

And this is where I expected this to go. I need to do this on my own. I guess I just need to buckle down and learn it.

[ol]* How to create a MySQL database, tables and associated fields (structure)
* Basic knowledge of forms processing
* How to use PHP to query MySQL via a submitted form[/ol]

1: This I can handle, though a tutorial wouldn't be bad. I expect this to be as simple as possible.
2: This is one of my weaknesses and has been for a while. I need a good tutorial that skips over the building of the form and goes into the sending/retrieving data to/from the database.
3: Tied to the previous one. I know only the most basic steps to retrieving information, but not enough to start from scratch on that step.

There are plenty of links on each in this forum, but the first one is likely the toughest for you - so if your webhost provides phpMyAdmin or other CPANEL controls to manage databases, by all means use them. Just some thoughts. Someone else might have done this before and might post code or a link to some open source project or whatever that does just this, but nothing off the top of my head.

-jim

I was afraid of that, but expecting it.

jW

 

[angelwatt]

For the user front end I threw something together very quickly (which means untested and not that pretty) that should give you a little bit of a start. Everything is contained in a single PHP file.

<?php
$form = <<<EO
<form action="$_SERVER['PHP_SELF']" method="get">
<fieldset>
  <legend>Enter Repair ID</legend>
  <input type="text" name="id" />
  <input type="submit" value="Lookup Repair" />
</fieldset>
</form>
EO;
// If request has been made
if (isset($_GET['id'])) {
  mysql_connect("localhost", "user", "pass") or die(mysql_error());
  mysql_select_db("test") or die(mysql_error());
  // sanitize request
  $id = mysql_real_escape_string($_GET['id']);
  $sql = 'SELECT * FROM repairs WHERE id = '. $id .' LIMIT 1;';
  $result = mysql_query($sql) or die(mysql_error());
  $num = mysql_num_rows($result);
  // if no records found
  if ($num < 1) {
    echo '<h1>Repair ID not found!</h1>';
    echo $form;
  }
  else {
    $row = mysql_fetch_assoc($result);
    echo "<p>Machine: ". $row['mach'] ." is currently ". $row['status'] ."</p>";
  }
}
// Otherwise show form for user to make request
else {
  echo $form;
}
?>

 

[SrWebDeveloper]

I do realize we need a back end and a front end. We have CPanel and phpMyAdmin available, so I can simply use those for entry, that's not a big deal. The front end and the actual database are my biggest needs. Actually, for that matter, I can set up a database, I just need to know how to reference it from the front end form and display it properly on the page. BTW, security is pretty much a non-issue.....

Just for the record, the code angelwatt posted is a summary of essentially what you need to do, and although not broken into front end/back end, it fits nicely with the new requirements and expanded criteria which you wrote after reading my various concerns. I would have done the same with the additional info. So by using the CP and other tools available to you it's just a matter of creating the database and the rest can be construed from the example code posted here and good old fashioned learnin' ...

Now that I know more about your skills and needs, here are some links to help you acheive your goal:

MySQL creating and using a database tutorial
PHP and MySQL Basics

And something work checking out which I thought might be useful:

phpMyEdit

A tool which facilitates selecting a database table and generating a PHP script used to create forms to List, Add, Change, Copy, View, or Delete records.

Best of success with your project! Cheers.

-jim

 

[Mal]

Thanks all, I'll be working on implementing this over the next week or so so I'll check back!

jW

 

[Mal]

OK, so I set up the database and used phpMyEdit to insert about 5 records into the database. That part seems to work fine. I tried the code provided by angelwatt, with the proper edits made for the username and password (I've removed them here for security), and I get an error. The code:

<?php
	include ('head.inc');

$form = <<<EO
<form action="$_SERVER['PHP_SELF']" method="get">
<fieldset>
  <legend>Enter Repair ID</legend>
  <input type="text" name="id" />
  <input type="submit" value="Lookup Repair" />
</fieldset>
</form>
EO;
// If request has been made
if (isset($_GET['id'])) {
  mysql_connect("localhost", "***", "***") or die(mysql_error());
  mysql_select_db("connec36_repair") or die(mysql_error());
  // sanitize request
  $id = mysql_real_escape_string($_GET['id']);
  $sql = 'SELECT * FROM repairs WHERE id = '. $id .' LIMIT 1;';
  $result = mysql_query($sql) or die(mysql_error());
  $num = mysql_num_rows($result);
  // if no records found
  if ($num < 1) {
    echo '<h1>Repair ID not found!</h1>';
    echo $form;
  }
  else {
    $row = mysql_fetch_assoc($result);
    echo "<p>Machine: ". $row['mach'] ." is currently ". $row['status'] ."</p>";
  }
}
// Otherwise show form for user to make request
else {
  echo $form;
}
?>

	include('foot.inc');
?>

The error:
Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/connec36/public_html/repairstatus.php on line 5

Any ideas? I'm not sure if I just missed something or if there's a problem with the code/structure.

jW

 

[angelwatt]

Yea, I wondered if that $form was set right (like I said before, untested). Here's an update.

$form = "<form action='". $_SERVER['PHP_SELF'] ."' method='get'>
<fieldset>
  <legend>Enter Repair ID</legend>
  <input type='text' name='id' />
  <input type='submit' value='Lookup Repair' />
</fieldset>
</form>";

Or (notice {} around $_SERVER)

$form = <<<EO
<form action="{$_SERVER['PHP_SELF']}" method="get">
<fieldset>
  <legend>Enter Repair ID</legend>
  <input type="text" name="id" />
  <input type="submit" value="Lookup Repair" />
</fieldset>
</form>
EO;

 

[Mal]

Thanks, angelwatt, the brackets worked perfectly. I now have a fully functional page up and running, thanks to ya'll. Now just have to make it look good...

jW

 

[fagan]

Thanks, angelwatt, the brackets worked perfectly. I now have a fully functional page up and running, thanks to ya'll. Now just have to make it look good...

jW

Any chance that you can share this as i have been looking everywhere for a script to do this. I do not have the ability to make my own haha