Page 1 of 1

Data being inserted twice

Posted: Thu Feb 28, 2008 4:20 pm
by Gooster
Hi, I'm relatively new to PHP and having a hard time with a piece of code. What the code is supposed to do is figure out, based on what is stored in the database, whether or not a particular user has a limo. If they don't have a limo, it is supposed to allow them to request one. Once they request a limo, the code finds the next available limo number from a table, deletes the number from the table, updates the user's data with the number, and adds the limo number to a different table. It is supposed to only do this once. The problem is, it does it more than once if the "refresh" button on the browser is clicked. Here's the code:

Code: Select all

<?php
// Get the email cookie info and assign it to a variable
$email = $_COOKIE['email'];
// Determine if there is anything in the limo_number field of the member traveling table, assign it to a variable
$result = mysql_query("SELECT * FROM member_traveling WHERE email ='$email'");
while ($row = mysql_fetch_array($result))
{
$limo = $row['limo_number'];
// Get the flight number and assign it to a variable
$flight = $row['flight'];
}
// Find out if the customer is or is not already scheduled to take a limo and store the result in a variable
if ($limo == "")
{
echo 'You are not currently scheduled for a limo.<br />';
}
else
{
echo 'You are scheduled for limo #' . $limo . '.<br />';
}
// Connect to our database and list limos that are part of the pool
// Match the flight number with other members' flight numbers and display the matches - here is where we use the flight variable
$result = mysql_query("SELECT * FROM member_traveling WHERE flight='$flight'");
// List all matches for the flight number
while ($row = mysql_fetch_array($result))
{
// If the flight number matches and they have something in the limo number field, list them
if (!empty($row['limo_number']))
  {
  echo 'Limo Number: '.$row["limo_number"].' Email: <a href="mailto:'.$row["email"].'">'.$row["email"].'</a><br />';
  }
}
// Allow the customer to request a limo
if ($limo == "")
{
echo '<form method="post" action="showlimo.php"><input type="submit" name="get_limo" value="Request a Limo" /><input type="hidden" name="limo_count" value="0" /></form>';
}
// Or allow them to revise plans if they already have a limo requested
else
{
echo '<form method="post" action="showlimo.php"><input type="submit" name="revise" value="Revise Plans" /><input type="hidden" name="limo_count" value="1" /></form>';
}
/* If they clicked on "get_limo" put the limo number in the itp table, update the member traveling table, and delete the limo from the free limo table */
if (isset ($_POST['get_limo']))
{
$query = mysql_query("SELECT limo_number FROM free_limos LIMIT 1");
$limnum = mysql_result($query,0);
mysql_query("INSERT INTO itp_limos SET limo_number='".$limnum."';");
mysql_query("UPDATE member_traveling SET limo_number='".$limnum."',requested='1' WHERE email='".$email."' LIMIT 1;");
mysql_query("DELETE FROM free_limos WHERE limo_number='".$limnum."' LIMIT 1;");
}
else
{
echo $limnum;
}
// Close the connection
mysql_close($con);
?>
I know that's a lot of code, and I am grateful for any suggestions.

Thank you!

Re: Data being inserted twice

Posted: Thu Feb 28, 2008 10:18 pm
by hawkenterprises
This looks like homework... Who uses PHP and a Web Page to do Limo Requests.

Re: Data being inserted twice

Posted: Thu Feb 28, 2008 10:48 pm
by califdon
Gooster wrote:Hi, I'm relatively new to PHP and having a hard time with a piece of code. What the code is supposed to do is figure out, based on what is stored in the database, whether or not a particular user has a limo. If they don't have a limo, it is supposed to allow them to request one. Once they request a limo, the code finds the next available limo number from a table, deletes the number from the table, updates the user's data with the number, and adds the limo number to a different table. It is supposed to only do this once. The problem is, it does it more than once if the "refresh" button on the browser is clicked.
. . .
I know that's a lot of code, and I am grateful for any suggestions.

Thank you!
Yes, I'm afraid it's more code than I have time to study, right now, but let me give you some general comments about the way you described the task. I would surely not do it that way. Anytime you think you have to routinely delete records from a table, there's a good chance you're approaching the problem wrong. The Limo table should always contain a record for every Limo. Each time a user requests a Limo, a new record should be created in a third table, with the user's id, the Limo's id and the date(/time?) the request was made. Each time a Limo is returned, the date(/time?) it was returned should be updated in that record. It takes a little more logic to determine the next available Limo to assign, but you would then have a real relational database, and it would track both users and Limos, something that would surely be valuable information to be able to extract.

Thank you for the suggestion

Posted: Thu Feb 28, 2008 11:23 pm
by Gooster
Thank you for the suggestion! I'm supposed to ultimately be getting the limo number from a list of free limos from the limo company's database (and I have no idea what kind of database they'd be using, I'm using MySQL so I created a dummy table as a stand-in for their database) so I'd have to have some way of letting them know that the limo assigned to the user is no longer free. My solution was to remove the number from their "free limo" table in their database so that it couldn't be assigned twice. The really crazy thing is this is all spec. I probably should talk to a limo company to see how they would want the interfacing done. :roll: Your suggestion of having a table that lists the assigned limos instead, where the limo company would need to interface with my database, is a good one. I do have that, and I could change the code so that I no longer delete the limo from the waiting table. Thanks again.

Re: Thank you for the suggestion

Posted: Fri Feb 29, 2008 1:53 pm
by califdon
Gooster wrote:Thank you for the suggestion! I'm supposed to ultimately be getting the limo number from a list of free limos from the limo company's database (and I have no idea what kind of database they'd be using, I'm using MySQL so I created a dummy table as a stand-in for their database) so I'd have to have some way of letting them know that the limo assigned to the user is no longer free. My solution was to remove the number from their "free limo" table in their database so that it couldn't be assigned twice. The really crazy thing is this is all spec. I probably should talk to a limo company to see how they would want the interfacing done. :roll: Your suggestion of having a table that lists the assigned limos instead, where the limo company would need to interface with my database, is a good one. I do have that, and I could change the code so that I no longer delete the limo from the waiting table. Thanks again.
You're welcome. Generally, it's a good idea to retain all data that's collected, rather than just delete it when it appears to not be needed. Surely there are exceptions to that, but I would always start from that point, revising when indicated.