Hello fellas :)
I have a task to do, there are 10 questions and i obviously need 10 answers :) I have done (attempted) some of them /not every yet/ but i wonder if there are any errors or any correction or improvements that could be made :)
Thank you in advance!
Question1:
1. (a)modify the addnames.php script to read in and store two fields of data – username and password (call your script adduser.php and call the table ‘passwords’).
(b)modify matchname.php to identify when a username and password exists in the database table (call your script checkuser.php ).
Answer:
adduser.php
<?php
// adduser.php adds form values to database table (but adds blank row first time!)
$dbhandle = sqlite_popen("passwords", 0666, $err_msg);
if(!$dbhandle) die("Could not open the database");
$query = "CREATE TABLE passwords(name VARCHAR(255), password VARCHAR(6))";
if(!sqlite_query($dbhandle, $query)){ echo "table not created (maybe already created)"; }
//$query = "INSERT INTO passwords VALUES('John', '2534')";
$username = $_POST['username'];
$password = $_POST['password'];
$query = "INSERT INTO passwords VALUES('$username', '$password')";
//print " sql is $query ";
if(!sqlite_query($dbhandle, $query)) { echo "Could not insert table row"; }
//sqlite_query($dbhandle, $query);
$query = sqlite_query($dbhandle, 'SELECT * FROM passwords'); //result set goes into query
$result = sqlite_fetch_all($query, SQLITE_ASSOC); //calls columns by name (or NUM for col num eg 0,1..)
// each result array element contains a row of table. The row holds pairs of row name, row value
print_r( $result); // useful debug - show all results
foreach ($result as $arow) {
echo '<br> Name: ' . $arow['name'] . ' password: ' . $arow['password'];
}
sqlite_close($dbhandle);
?>
<form action='' method=post>
Username <input type='text' name='username' /> <br>
Password <input type='text' name='password' /> <br>
<input type='submit' value='Submit' />
</form>checkuser.php
<script type="text/javascript">
function validateForm()
{
var x=document.forms["login"]["username"]["password"].value;
if (x==null || x=="")
{
alert("Wrong login details");
return false;
}
}
</script>
<form name="login" action='' method=post>
Username <input type='text' name='username' /> <br>
Password <input type='text' name='password' /> <br>
<input type='submit' value='Submit' />
</form>
<?php
// matchnames.php to see how many times a friends and phoneNo name matches in table
// only sees a match if matches once only (not 2 times)
//if (!(isset($_POST['username']))) { exit; } // password not sent in ... so exit
$username = $_POST['username'];
$password = $_POST['password'];
$dbhandle = sqlite_popen("passwords", 0666, $err_msg);
if(!$dbhandle) die("Could not open the database");
// Count rows with this
$sql = "SELECT COUNT(password) from username WHERE name = '$username' AND password = '$password' ";
$query = sqlite_query($dbhandle, $sql); // result set goes into query
$result = sqlite_fetch_all($query, SQLITE_NUM); //calls columns by num (use ASSOC for col names)
print_r( $result); // useful debug - show all results
// each result array element contains a row of table. The row holds pairs of row name, row value
$firstrow = $result[0]; //sql result has just one row - so get the first row
$rowcount = $firstrow[0]; //the first array element (first column) in that first row
print "Count result is: $rowcount ";
// count rows with this
$sql = "SELECT * from username WHERE name = '$username' AND password = '$password' ";
$query = sqlite_query($dbhandle, $sql); // result set goes into query
$result = sqlite_fetch_all($query, SQLITE_NUM); //calls columns by num (use ASSOC for col names)
$rowcount = sqlite_num_rows($query);
print " Alternative count is $rowcount ";
if ( $rowcount != 1)
{
print " name didnt match once ";
// exit; // we could stop them trying again with end program
}
else
{
print " name matched once ";
}
sqlite_close($dbhandle);
?>Question 2:
Write a script to allow a user to add property data into a property table ( call it addproperty.php ). The table should initially have 4 columns for the property data (postcode, price, imagefile, visits). At the end of the script display the whole table contents.
Answer
addproperty.php
Edit Properties <BR><BR>
<?php
$dbhandle = sqlite_popen("propertytable", 1234, $err_msg);
if(!$dbhandle) die("Could not open the database - run CretaActivityTable.php first");
//$field is a variable name, field is the table column name and also the form field name
if ($_POST['use']=="add") { //ADD NEW ITEM
$Postcode = $_POST['postcode'];
$Price = $_POST['price'];
$Imagefile = $_POST['imagefile'];
$Visits = $_POST['visits'];
$query = "INSERT INTO activity (postcode, price, imagefile, visits) VALUES('$Postcode', '$Price', '$Imagefile', '$Visits')";
if(!sqlite_query($dbhandle, $query)) { echo "Could not insert table row"; }
} // ADD=TRUE
$Xid = $_POST['Xid']; //the table row ID needed to change/update or delete that row
if ($_POST['use']=="change") { //CHANGE EXISTING ITEM
$Postcode = $_POST['postcode'];
$Price = $_POST['price'];
$Imagefile = $_POST['imagefile'];
$Visits = $_POST['visits'];
$query = "UPDATE activity SET postcode='$Postcode', price='$Price', imagefile='$Imagefile', visits='$Visits' WHERE id='$Xid'";
// print " sql is $query endsql";
if(!sqlite_query($dbhandle, $query)) { echo "Could not update table row"; }
}
// DELETE ITEM
if ( ($_POST['use']=="delete") AND ( isset($_POST['allowdelete']) ) ) {
$query = "DELETE FROM activity WHERE id='$Xid'";
if(!sqlite_query($dbhandle, $query)) { echo "Could not delete table row"; }
}
//DISPLAY
$query = sqlite_query($dbhandle, 'SELECT * FROM activity ORDER BY postcode'); //result set goes into query
$result = sqlite_fetch_all($query, SQLITE_ASSOC); //calls columns by name (or NUM for col num eg 0,1..)
//simple display
foreach ($result as $arow) {
print "RowID:". $arow['id'].", Post Code:". $arow['postcode'].", Price:". $arow['price'].", Imagefile:". $arow['imagefile'].", Visits:". $arow['visits']." <br>";
}
//table and form display
print "<table>";
print '<tr><td>RowID ...Post Code...Price...Imagefile...Visits </td><td> </td></tr> ';
foreach ($result as $arow) {
?>
<tr>
<td>
<form action='' method=post>
<input type='hidden' name='Xid' value='<?=$arow['id']?>' />
<?=$arow['id'] ?>
<input type='text' name='Postcode' value="<?=$arow['postcode']?>" size=4 />
<input type='text' name='Price' value="<?=$arow['price']?>" />
<input type='text' name='Imagefile' value="<?=$arow['imagefile']?>" />
<input type='text' name='Visits' value="<?=$arow['visits']?>" />
<input type='hidden' name='use' value='change' />
<input type='submit' value='Save Row' /> Or
</form>
</td>
<td> <form action='' method=post>
<input type='hidden' name='Xid' value='<?=$arow['id']?>' />
<input type='checkbox' name='allowdelete' />
<input type='hidden' name='use' value='delete' />
<input type='submit' value='Delete Row (check box first)' />
</form>
</td>
</tr>
<?
}
print "</table>";
sqlite_close($dbhandle);
?>
<br><br>
ADD NEW PROPERTY <BR>
<form action='' method=post>
Post Code <input type='text' name='Postcode' />
Price <input type='text' name='Price' />
Image file <input type='text' name='Imagefile' />
Visits <input type='text' name='Visits' />
<input type='hidden' name='use' value='add' />
<input type='submit' value='Add' />
</form>addproperttable ( for $dbhandle)
<?php //this script can be run once to re-initialise the database - to drop the existing table and recreate the table
$dbhandle = sqlite_popen("propertytable", 1234, $err_msg);
if(!$dbhandle) die("Could not open the database");
$query = "DROP TABLE activity";
if(!sqlite_query($dbhandle, $query)){ echo "table 'activity' not deleted (may be not yet exists)"; }
$query = "CREATE TABLE activity( id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY , postcode VARCHAR(25), price VARCHAR(25), imagefile VARCHAR(25), visits(VARCHAR(25))";
if(!sqlite_query($dbhandle, $query)){ echo "table 'activity' created"; }
?> Table createdQuestion3:
Write a script to search for the properties which match a postcode (call it search.php ). Use SQL search. You will need an HTML form which allows the user to type a postcode they are searching for.
Answer
<html>
<body>
<form action="properties.php" method="post">
Postcode: <input type="text" name="postcode" />
<input type="submit" />
</form>
<?php
$con = mysql_connect("localhost","maciej","kupadupa1"); // sql database server/ username/ password
if (!$con) // if unable to access database
{
die('Could not connect: ' . mysql_error()); //throw this error
}
mysql_select_db("properties", $con); // select my database "properties"
// select from properties row postcode where postcode = user type data
$result = mysql_query("SELECT * FROM properties
WHERE postcode='$_POST['postcode'];'");
while($row = mysql_fetch_array($result)) // when found display data
{
echo $row['postcode']; // display all matching post codes
echo "<br />";
}
?>
</body>
</html>Question 4 + 5
Add user authentication. Modify checkuser.php so that a user can log in, and modify search.php so it will only run for an authenticated user logged in with checkuser.php.(Use sessions)
Add JavaScript user input form validation to the log-in form.
Answer:
checkuser.php + javascript
<script type="text/javascript">
function validateForm()
{
var x=document.forms["login"]["username"]["password"].value;
if (x==null || x=="")
{
alert("Wrong login details");
return false;
}
}
</script>
<form name="login" action='' method=post>
Username <input type='text' name='username' /> <br>
Password <input type='text' name='password' /> <br>
<input type='submit' value='Submit' />
</form>
<?php
// matchnames.php to see how many times a friends and phoneNo name matches in table
// only sees a match if matches once only (not 2 times)
//if (!(isset($_POST['username']))) { exit; } // password not sent in ... so exit
$username = $_POST['username'];
$password = $_POST['password'];
$dbhandle = sqlite_popen("passwords", 0666, $err_msg);
if(!$dbhandle) die("Could not open the database");
// Count rows with this
$sql = "SELECT COUNT(password) from username WHERE name = '$username' AND password = '$password' ";
$query = sqlite_query($dbhandle, $sql); // result set goes into query
$result = sqlite_fetch_all($query, SQLITE_NUM); //calls columns by num (use ASSOC for col names)
print_r( $result); // useful debug - show all results
// each result array element contains a row of table. The row holds pairs of row name, row value
$firstrow = $result[0]; //sql result has just one row - so get the first row
$rowcount = $firstrow[0]; //the first array element (first column) in that first row
print "Count result is: $rowcount ";
// count rows with this
$sql = "SELECT * from username WHERE name = '$username' AND password = '$password' ";
$query = sqlite_query($dbhandle, $sql); // result set goes into query
$result = sqlite_fetch_all($query, SQLITE_NUM); //calls columns by num (use ASSOC for col names)
$rowcount = sqlite_num_rows($query);
print " Alternative count is $rowcount ";
if ( $rowcount != 1)
{
print " name didnt match once ";
// exit; // we could stop them trying again with end program
}
else
{
print " name matched once ";
}
sqlite_close($dbhandle);
?>As far as im aware on this questions there are some errors or think that could be improved + added
Question 6:
Write a script (similar to the end of addproperty.php) to display your property table data on to the screen, and then modify it to write out the property data to a text file called property.xml. Then modify the script to write the correct XML tags out between the data being written out. Agentname and agentphone can be made up and fixed in the code in the script. (see the end of simpleXMLdemo.php for how to create and write out XML). Your script should be called xmlout.php.
Answer:
property.xml
<?xml version="1.0"?>
<estateagent>
<property>
<agentname>Maciej Cygan</agentname>
<agentphone>07581223456</agentphone>
<propertyname>Ja Jebie5</propertyname>
<postcode>NW13HA</postcode>
<price>250.000</price>
<imagefile>house1.jpg</imagefile>
<visits>14</visits>
</property>
<property>
<agentname>Maciej Cygan</agentname>
<agentphone>07581223456</agentphone>
<propertyname>Westminster</propertyname>
<postcode>NW70EE</postcode>
<price>252.000</price>
<imagefile>house2.jpg</imagefile>
<visits>22</visits>
</property>
<property>
<agentname>Maciej Cygan</agentname>
<agentphone>07581223456</agentphone>
<propertyname>Park Royal</propertyname>
<postcode>SE38RA</postcode>
<price>199.999</price>
<imagefile>house3.jpg</imagefile>
<visits>34</visits>
</property>
<property>
<agentname>Maciej Cygan</agentname>
<agentphone>07581223456</agentphone>
<propertyname>Hunslow</propertyname>
<postcode>KT81QQ</postcode>
<price>365.000</price>
<imagefile>house4.jpg</imagefile>
<visits>77</visits>
</property>
<property>
<agentname>Maciej Cygan</agentname>
<agentphone>07581223456</agentphone>
<propertyname>Richmond</propertyname>
<postcode>SW19TN</postcode>
<price>265.549</price>
<imagefile>house5.jpg</imagefile>
<visits>56</visits>
</property>
<property>
<agentname>Maciej Cygan</agentname>
<agentphone>07581223456</agentphone>
<propertyname>Twickenham</propertyname>
<postcode>GW66UP</postcode>
<price>320.850</price>
<imagefile>house6.jpg</imagefile>
<visits>49</visits>
</property>
<property>
<agentname>Maciej Cygan</agentname>
<agentphone>07581223456</agentphone>
<propertyname>Brent</propertyname>
<postcode>NM88AS</postcode>
<price>350.999</price>
<imagefile>house7.jpg</imagefile>
<visits>99</visits>
</property>
<property>
<agentname>Maciej Cygan</agentname>
<agentphone>07581223456</agentphone>
<propertyname>Dollis Hill</propertyname>
<postcode>NW101E</postcode>
<price>499.999</price>
<imagefile>house8.jpg</imagefile>
<visits>159</visits>
</property>
<property>
<agentname>Maciej Cygan</agentname>
<agentphone>07581223456</agentphone>
<propertyname>Wemblay</propertyname>
<postcode>RK381KE</postcode>
<price>465.444</price>
<imagefile>house9.jpg</imagefile>
<visits>88</visits>
</property>
<property>
<agentname>Maciej Cygan</agentname>
<agentphone>07581223456</agentphone>
<propertyname>Harrow</propertyname>
<postcode>SS69EX</postcode>
<price>357.159</price>
<imagefile>house10.jpg</imagefile>
<visits>142</visits>
</property>
</estateagent>xmlout.php
<html>
<body>
<script type="text/javascript">
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.open("GET","property.xml",false);
xmlhttp.send();
xmlDoc=xmlhttp.responseXML;
document.write("<table border='1'>");
var x=xmlDoc.getElementsByTagName("property");
for (i=0;i<x.length;i++)
{
document.write("<tr><td>");
document.write(x[i].getElementsByTagName("agentname")[0].childNodes[0].nodeValue);
document.write("</td><td>");
document.write(x[i].getElementsByTagName("agentphone")[0].childNodes[0].nodeValue);
document.write("</td><td>");
document.write(x[i].getElementsByTagName("postcode")[0].childNodes[0].nodeValue);
document.write("</td><td>");
document.write(x[i].getElementsByTagName("price")[0].childNodes[0].nodeValue);
document.write("</td><td>");
document.write(x[i].getElementsByTagName("imagefile")[0].childNodes[0].nodeValue);
document.write("</td><td>");
document.write(x[i].getElementsByTagName("visits")[0].childNodes[0].nodeValue);
document.write("</td></tr>");
}
document.write("</table>");
</script>
</body>
</html>There are few things that i have no idea how to do (by that i mean i am not sure how to connect every single file so they work as one website, so when user can add property details, remove them, when he does that properties will be displayed in xml etc etc. Like a normal working site
Question 7+8+9+10 Not yet attempted
Any help would be greatly appreciated
Thank you :)!