Creating a Search Function 2

In the last post, I mentioned how I would upload files to the server and collect their details into the db.
Here, I'll show you how I was able to search, format and display all the information:

$search=$_POST["search"];
$level = $_POST["level"];
$type = $_POST["type"];

//get the mysql and store them in $result
$result = mysql_query("SELECT * FROM Resources WHERE level LIKE '%$level%' AND (tags LIKE '%$search%' or name LIKE '%$search%') AND type LIKE '%$type%' ORDER BY date DESC ");


//grab all the content
while ($get_info = mysql_fetch_array($result)){
  $series[] = array('name' => $get_info['name'], 'url' => $get_info['url'], 'tags' => $get_info['tags'], 'level' => $get_info['level'], 'submittedby' => $get_info['submittedby'], 'date' => $get_info['date']);
}

?>
<!-- Show Content -->
<p>Here are all the resources...</p>

    <?php foreach ($series as $show): ?>
      <blockquote>
        <h2><?php echo htmlspecialchars($show['name'], ENT_QUOTES, 'UTF-8'); ?></h2>
        <p><em><a href="http://www.tracyr.comlu.com/SFVT/upload/<?php echo htmlspecialchars($show['url'], ENT_QUOTES, 'UTF-8'); ?>">click to download file </a></em><br/>
        Tags:
        <em><?php echo htmlspecialchars($show['tags'], ENT_QUOTES, 'UTF-8'); ?></em><br/>
        Submitted By:
        <em><?php echo htmlspecialchars($show['submittedby'], ENT_QUOTES, 'UTF-8'); ?></em><br/>
        Date added: <?php echo htmlspecialchars($show['date'], ENT_QUOTES, 'UTF-8'); ?>
      </p></blockquote>
    <?php endforeach; ?>

Basically, the code allows me to grab all of the content from the db- if I want to filter it using the search form, that's where this comes in:

WHERE level LIKE '%$level%' AND (tags LIKE '%$search%' or name LIKE '%$search%') AND type LIKE '%$type%'

The second part of the code allows me to format the results by fetching the array and mixing html and php to decide how each result displays. It also allows me to create the url of each file since they are all in the /upload folder and the filename comes from the database.

This all took me a little while to get working- especially understanding the fetch array and "for each..." sections. But got there in the end!

Creating a Search Function

One of the biggest projects of my site has been to create the ability for users to upload files, load them into a folder and their into a database, return the formatted results and format them.
Here are some of the things I've been working on:

Uploading Files

The first thing I did was create a form. The form posts to an external php file, collects inputted data about the file and there is a field that allows you to select a file (type:"file"). Here's the code I used to upload the file to the folder on the server:

  $file = ($_FILES['uploaded']['name']);
 
 /*upload*/
 $target = "upload/";
 $target = $target . basename( $_FILES['uploaded']['name']) ;
 $ok=1;
 if(move_uploaded_file($_FILES['uploaded']['tmp_name'], $target))
 {

The other main task was to add the data to the database:

  $name = $_REQUEST['name'] ;
  $tags = $_REQUEST['tags'] ;
  $submittedby = $_REQUEST['submittedby'] ;
  $file = ($_FILES['uploaded']['name']);
    $level=serialize($_POST['level']);
    $type=serialize($_POST['type']);


mysql_query("INSERT INTO Resources
(name, tags, level, type, url, submittedby) VALUES(' $name ', ' $tags ', ' $level ', ' $type ', '$file', '$submittedby') ")

or die(mysql_error()); 

echo "Data Inserted!";
 echo "The file ". basename( $_FILES['uploadedfile']['name']). " has been uploaded";
 }
 else {
 echo "Sorry, there was a problem uploading your file.";
 }

 if ($uploaded_type =="text/php")
 {
 echo "No PHP files<br>";
 $ok=0;
 }

... Just don't forget to connect to mySQL first.

What I've Been Up To

Hi All,
I was pretty ill for the last couple of weeks so missed the lectures, but I've been working hard on my site and getting it operating. I thought I would share with you some of what I've been doing.

Javascript form validation

I've learnt how to validate forms by checking if a field is empty...

  if (form.name.value == "") {
    alert( "Please enter the name of the resources." );
    form.name.focus();
    return false ;
  }

(For this you need to specify the name of the field you want to validate (name) and if necessary, the id of the form (in this case 'form'))

... and check two passwords are the same

if (form.password.value != form.retypePassword.value)
    {
    alert("The two passwords are not the same.");
    form.retypePassword.focus();
    return false;
    }

(This specifies the two password fields- 'password' and 'retypePassword')

To make these validations happen, you need to put them inside a function (in this case 'checkform') and add to the form an 'onsubmit' that executes the fuction, ie.

onsubmit="return checkform(this)

Login system

I've been working this week on trying to figure out how to register users and log them into my site. This tutorial has been really helpful to me and might be for you if you want to do the same thing:
http://net.tutsplus.com/articles/news/how-to-build-a-login-system-for-a-simple-website/
The only problem is it doesn't finish- it doesn't show you how to register users or set an expiration date to the session. But it has enough for it to make sense to me and do the rest on my own.
Good luck!

Designing Databases

This week's prac was about databases. In working through the idea of a video store database, here's a possible solution:

Id	Name	Address	Telephone No	Driver’s Licence No	Email address	Password
0001	Amber Jones	23 Bla bla la	01283471050	12893140509384	calksjdopfwn@nsfaadf.com	secret

Id	Title	Copy No	Year	Genre	Actors	Release to shelf date	Current rental code	Current status
0001	The Negotiator	1	2009	Action	Joe Blow	3.4.2010	Newrelease	On shelf

Id	Rental Period (days)	Daily price within rental period	Daily price overdue	Auto assign at x weeks after release to shelf date
Newrelease	1	5.00	5.00	0
Weekly	7	1.00	0.50	16

Customer id	Title id	Date out	Date in
0001	0001	6.4.10	7.4.10

Tool for Wireframing

It's probably a bit late for everyone, but I just thought I'd mention the tool I'm using to create my wireframes- so easy!
I'm using http://gomockingbird.com/
This is the type of thing you end up with:

MySQL

The prac today was about MySQL commands and using them to manipulate data. I'll be doing quite a bit of this interacting with php for my site. I've created a new page to display the final task in the prac- it took me a couple of tries to LIMIT, ORDER BY and SELECT in the same command, but here's what I came up with:

SELECT * FROM `books` WHERE LEFT(title, 1) = 'M' ORDER BY title LIMIT 0,3

This piece of code selects from the table called 'books' all titles that begin with M (where the left most character of the field 'title' is 'M'). It then orders the selection by title and limits it to three entries. The trick with this is that the ORDER BY needs to be done before the LIMIT- otherwise it doesn't work.

Just for a bit of fun, I had a play around with acually displaying the data on a site, and here's the code to do that:

<?php
$database="a4186211_wp";
mysql_connect ("mysql12.000webhost.com", "a4186211_tsr", "password");
@mysql_select_db($database) or die( "Unable to select database");
$result = mysql_query( "SELECT name, tags, url FROM Resources" )
or die("SELECT Error: ".mysql_error());


$query="SELECT * FROM `books` WHERE LEFT(title, 1) = 'M' ORDER BY title LIMIT 0,3";
$result=mysql_query($query);

$num_rows = mysql_num_rows($result);
print "There are $num_rows records.<P>";
print "<table width=200 border=1>\n";
while ($get_info = mysql_fetch_row($result)){
print "<tr>\n";
foreach ($get_info as $field)
print "\t<td><font face=arial size=1/>$field</font></td>\n";
print "</tr>\n";
}
print "</table>\n";

?>

Here's the link:
http://www.tracyr.comlu.com/Resources/new%202.php