After previous exercise, we have got two possible responses: the GET style response from direct links, or the POST style response from a Submit button use. Choose appropriate solution, dependent on your last week php scripts.

GET style response

If you have prepared table by phpWebAdmin (from the last week), you would like to generate a html code similar as:

<table border=1>
<tr><th>type<th>tax
<tr><td><a href="rent.php?o=1&z=wolf">Audi A100</a><td>12
<tr><td><a href="rent.php?o=2&z=wolf">Renault 12</a><td>6
<tr><td><a href="rent.php?o=3&z=wolf">Citroen 2CV</a><td>4
</table>

As a result of a click on some of the (prepared) links, you will get an associative array similar like this (for example, the last link):

key value
o 3
z wolf

In this case, the password should be passed using a cookie or a session variable. The second solution was with the password included in the link. Imagine, that we have included the password into link as a "p" parameter:

<table border=1>
<tr><th>type<th>tax
<tr><td><a href="rent.php?o=1&z=wolf&p=vlk">Audi A100</a><td>12
<tr><td><a href="rent.php?o=2&z=wolf&p=vlk">Renault 12</a><td>6
<tr><td><a href="rent.php?o=3&z=wolf&p=vlk">Citroen 2CV</a><td>4
</table>

As the first, we will check the password, using a sequence like this (see the end of the previous exercise):

$result=mysqli_query($link,'SELECT id FROM people WHERE login="'.$_GET['z'].'" AND pwd="'.$_GET['p'].'";');
if ($result) {  //we have value, so test, if it is a valid user...
$row=mysqli_fetch_assoc($result);
if ($row["id"]>0){  //yes, if "id" is bigger then zero, it is a valid user 
$id_z=$row["id"];

//**********
// there should be code, secured by password (1)
//**********

} else {echo "internal error of our script";};}

Reminding: As a first, we try (using the SELECT command) to find the "login" and the "pwd" (passed from the previous script, or provided by user) combination in the "people" (in some other examples the user) table. If there is this combination, we will read the user id from the result. In a consistent table, this will be bigger then zero.

Then, to the place marked with (1), we can write the SQL commands, where the first of them we should write a new row (data) to the table with records of all previous car rental (after returning a car, it stays here for a history or for an accounting). For this example, imagine, that customer has the id=6. In the table, we have a date, when the car has been rent. It is important to insert a date in the correct format. For this case, we can use an SQL function for inserting a current date ( the CURDATE function), or for inserting current date and time ( NOW() ). For inserting a row to the table, we use an INSERT command. The complete command will look like this:

INSERT INTO rent VALUES ("6", "3", CURDATE());

In the positions of "6" and "3" we need to use php variables. Because of we need to use double quotation for SQL command, we will have to use apostrophe for a string delimitation. For inserting a variable to this kind of string, we have to brake it and use a "." (dot) symbol to join them again.

The second task is to record to the "car" table, that this car is no longer free. In this case, it means to rewrite the "who" column with the customer id. Rewriting a single line in existing table will provide the UPDATE command:

UPDATE cars SET who="6" WHERE id="3";

We can add a parameter like "LIMIT=1" to make change only for a single line (the first which was found), but in this case it is not important, because of the id in this table was declared as the primary key, so there are no duplicities. Complete code (should be included in the line, marked on the previous php window as (1) ), could look like that:

$result=mysqli_query($link,'INSERT INTO rent VALUES ("'.$id_z.'", "'.$id_a.'", CURDATE());');
$result=mysqli_query($link,'UPDATE cars SET who="'.$id_z.'" WHERE id="'.$id_a.'";');

Note, that we didn't check, if the $result is true. It means, that we didn't check, if there is no error with writing to the tables. For checking it, see the end of the next (the POST) solution.

Nearly complete php response script should look similar like (for compatability with the POST example bellow, the variables has been renamed to $id_a and $id_z):

<?php
$link=mysqli_connect('mysql.webzdarma.cz','zpp','[password]','zpp')
 or die('Error: '.mysqli_error($link) );

$result=mysqli_query($link,'SELECT id FROM people WHERE login="'.$_GET['z'].'" AND pwd="'.$_GET['p'].'";');
if ($result) {  //we have value, so test, if it is a valid user...
$row=mysqli_fetch_assoc($result);
if ($row["id"]>0){  //yes, if "id" is bigger then zero, it is a valid user 
$id_z=$row["id"];
$id_a=$_GET["o"];

$result=mysqli_query($link,'INSERT INTO rent VALUES ("'.$id_z.'", "'.$id_a.'", CURDATE());');
$result=mysqli_query($link,'UPDATE cars SET who="'.$id_z.'" WHERE id="'.$id_a.'";');

} else {echo "internal error of our script";};}

mysqli_close($link); 
?>

I didn't try it, please tell me, if there are errors.

For debugging, it can be useful to assemble the text for the mysql_query as a variable and show it by using the "echo" first. It has to be withdrawn after application debugging. The version with debug commands is here.

POST style response

If you have prepared table by phpWebAdmin (from the last week), you can generate an html code similar as:

<table border=1>
<tr><th>type<th>tax<th>rent it!
<tr><td>Audi A100<td>12<td>
<Form action="rent.php" method="post">
<input type="submit" name="id" value="rent it">
<input type="hidden" name="login" value="novak">
<input type="hidden" name="pwd" value="password">
<input type="hidden" name="car" value="1">
</Form>
<tr><td>Renault 12<td>6<td>
<Form action="rent.php" method="post">
<input type="submit" name="id" value="rent it">
<input type="hidden" name="login" value="novak">
<input type="hidden" name="pwd" value="password">
<input type="hidden" name="car" value="2">
</Form>
<tr><td>Citroen 2CV<td>4<td>
<Form action="rent.php" method="post">
<input type="submit" name="id" value="rent it">
<input type="hidden" name="login" value="novak">
<input type="hidden" name="pwd" value="password">
<input type="hidden" name="car" value="3">
</Form>
</table>

As a result of a click to a "Submit" button, you will get an associative array similar like this (for example, the last button):

key value
id rent it
login novak
pwd password
car 3

We have to check the name and password, as in the beginning of all of the forms:

$result=mysqli_query($link,'SELECT id FROM people WHERE login="'\
.$_POST['login'].'" AND pwd="'.$_POST['pwd'].'";');
if ($result) {  //we have value, so test, if it is a valid user...
$row=mysqli_fetch_assoc($result);
if ($row["id"]>0){  //yes, if "id" is bigger then zero, it is a valid user 
$id_z=$row["id"];

//**********
// there should be functions, secured by password (1)
//**********

} else {echo "internal error of our script";};}

The basic solution of our problem will be nearly the same, as in the GET solution. We should add the same SQL commands:

INSERT INTO rent VALUES ("6", "3", CURDATE());

And, if it is written, we should correct the value in the "cars" table:

UPDATE cars SET who="6" WHERE id="3";

We can add a parameter like "LIMIT=1" to make changes only for the single line, but in this case it is not important, because of the id in this table was declared as the primary key, so there are no duplicities. Complete code (should be included in the space, marked on the previous php window as (1) ) could look again like that:

$result=mysqli_query($link,'INSERT INTO rent VALUES ("'.$id_z.'", "'.$id_a.'", CURDATE());');
$result=mysqli_query($link,'UPDATE cars SET who="'.$id_z.'" WHERE id="'.$id_a.'";');

Note, that we didn't check, if the $result is true, i.e. there are no errors with writing to the table. If there is everything ok, it will return true, if it fails, it will return false. For checking it, we should check a result:

if (!$result)
  {
  echo('Could not write into table "rent", with message: ' . mysqli_error($link));
  }

Into the "else" part of the "if" command, we should put all the other commands to be done. Complete solution will look like the following example:

<?php
$link=mysqli_connect('mysql.webzdarma.cz','zpp','[password]','zpp')
 or die('Error: '.mysql_error() );

$result=mysqli_query($link,"SELECT id FROM people WHERE login='".$_POST['login'].
                      "' AND pwd='".$_POST['pwd']."';");
if ($result) {  //we have value, so test, if it is a valid user...
$row=mysqli_fetch_assoc($result);
if ($row["id"]>0){  //yes, if "id" is bigger then zero, it is a valid user 
$id_z=$row["id"];

$id_a = $_POST['car'];
if ($id_a > 0)
{

$result=mysqli_query($link,'INSERT INTO rent (id_z,id_a,rfrom) VALUES ("'
                      .$id_z.'", "'.$id_a.'", CURDATE());');
if (!$result)
  {
  echo('Could not write into table "rent", with message: ' . mysql_error());
  } else {
$result=mysqli_query($link,'UPDATE cars SET who="'.$id_z.'" WHERE id="'.$id_a.'";');
if (!$result)
  {
  echo('Could not write into table "cars", with message: ' . mysql_error());
  } else {echo "One line added OK, debug info: z=$id_z, a=$id_a"); 
    // there should be link back to the main menu passing hidden variables, if any
} }

} else {echo "internal error of our script while decoding car selection";};
} else {echo "internal error of our script while decoding user";};
} else {
  echo "Error accessing database. Please, ";
  echo "<a href=index.htm>login again</a>";
     };

mysqli_close($link); 
?>

Pink: the password checking (if it is an internal page, username and password are passing from the previous script, but have to be tested, because in other case anybody can easy hack this application. Blue - if the database is consistent, there is impossible to have zero or negative or even no id for a valid user, but everything should be checked (application should be trottleproof [i.e. idiot save]). Brown - the car number. Note, that even there could not be any error, the valid value is tested again. $_POST['car'] is from the calling form (=previous form).

After all the checking (and $id_a and $id_z decoding), values can be written to the tables. The black lines contain adding new line into the rent table (something like accounting record). Only, if it was successful, it will change in the car table, that user $id_z has the car $id_a rented. Orange: the rewriting of the car table. For the car, selected by user, there are recorded, who has this car now.

Note, that code is nested - colored lines continues after inserted block (usually the else part of the if command). For example, the pink one means, that username and password combination doesn't exist, and the new login (on the very first page of the application) is required (it cannot happen by standard application use, typically it is sign of hacking of the page).

Back to menu

Note, that there should be a link (or a blank form with hidden login and password) on the end of the page to go back to the main menu. This part of the page will be the same on all pages of your application. You can copy it from the "work with forms" chapter.

Debug note

If you get an error message from MySQL, try to prepare the SQL command to variable and use an additional "echo" to check it:
from:
$result=mysqli_query($link,'INSERT INTO rent (id_z,id_a,rfrom) VALUES ("'
                      .$id_z.'", "'.$id_a.'", CURDATE());');

should be rewritten to:
$q='INSERT INTO rent (id_z,id_a,rfrom) VALUES ("'
      .$id_z.'", "'.$id_a.'", CURDATE());';
echo
$q;
$result=mysqli_query($link,
$q);
Then, from the page (or source code), copy this to your SQL client to check, what went wrong (www).

Final note

This is all for the assessment/creditals for the project of this subject. Now, you can write your application.