The URLb@nk
Jotter - the work in progress
An excercise in Live-to-web, database and interface design.
What follows is a list of commands and code snippets used to explore this project. Many lead to live experiments, please feel free to try them.
Connect to the Database
Each contain a reference to connect.php which issues username and password stuff to MySQL. The following code is contained in a separate, stripped of anything else PHP file:
<?php
/* set the connections to the database */
$host = "localhost";
$username = "put your username here";
$passwd = "putyourpasswordhere";
$thedatabase = "name of your database here";
$connection = mysql_connect($host, $username, $passwd) or die("could not connect");
mysql_select_db($thedatabase) or die ("could not find the database, sorry.");
?>
Note1: using INCLUDE or REQUIRE is a neat way of farming off tasks to separate files so they can be reused by other pages (this is a form of modularisation that programmers might call procedures orfunctions). I like the REQUIRE for some things as it checks first if the REQUIREd file can be executed before proceeding with the remnants of the host file.
In the body of a php webpage, you would include something similar to:
<?php
require ("connect.php");
/* define the create table */
if (mysql_query("create table urlbank(
bankid integer unsigned not null auto_increment primary key,
url varchar(150) not null,
sitename varchar(100) not null,
dateadded char(20) not null,
category varchar(100) not null,
person varchar(100),
dessie text,
votes integer,
unique (url))"))
{ echo "URL b@nk table created successfully"; }
else
{ echo "had a problem - table probably already existed"; }
?>
Note1: REQUIRE is nice - it basically falls over if the file cannot be included (which means it falls over if it cannot connect to the database without then trying to do the table create which would also fail because you need to do that inside a database, if that makes sense).
Note2: I decided to include an address ID because, even though the URL should be unique, it is a bit of an ugly primary key (being so long and all). Interestingly, I then chose to negate that by making the URL unique also, defining a unique index on that column (I like the idea that the table should reject URLs that are the same as ones already stored).
Note3: TEXT is a costly storage class - it potentially is huuuuuuge - I have no way of knowing how much a user will enter when describing the site, so chose a bucket of characters.
<?php
require ("connect.php");
/* define the create table */
if (mysql_query("drop table urlbank"))
{ echo "URL b@nk table trashed successfully"; }
else
{ echo "had a problem - table probably already trashed"; }
?>
Note1: this is included only for testing purposes - this function must be buried or deleted when development is complete as there is nothing more annoying than your whole collection of URLs vanishing because some spotty herbert deleted them.
Note2: Again, I have wrapped the SQL statement in a conditional execution (if..then else) so it tidily executes or politely fails
<?php
$daterightnow = date("d-m-Y g:i a");
echo $daterightnow;
?>
Note1: I will need the date when adding a row so I thought I would get it and test the format. I have used a variable "$daterightnow" to read the formatted text value into - seemed like a plan as I can re-use this idea in other stages that need a date.
Note2: http://au.php.net/manual/en/function.date.php for date string formats - they are not intuitive nor sensible, but are powerful.
<?php
require ("connect.php");
$daterightnow = date("d-m-Y g:i a");
/* specify the dummy data */
if (mysql_query("insert into urlbank (url, sitename, dateadded, category,
person, dessie, votes)
values('http://www.wonko.info/', 'wOnKoSITE', '". $daterightnow ."' ,
'general', 'wonko', 'This is a site full of all sorts of junk', 42),
('http://www.terrace.qld.edu.au/','Teararse Site','".$daterightnow."',
'school webshites', 'wonko','St. Joes official website',1)"))
{ echo "table insertion successful"; }
else
{ echo "Error Processing Query: " .mysql_error(); };
?>
Note1: I decided to enter 2 lines of data in the one insert into command (because I can)
Note2: I pre-fetched the current date, then used it inside each of the values lines using ,' ".$daterightnow." '
Note3: The ELSE contians an error echoing statement - useful when looking for coding bugs (although the error messages tend to be a little cryptic at times, it is useful at spotting missing punctuation and the like)
Empty the table (without trashing the actual table)
<?php
require ("connect.php");
if (mysql_query("delete from urlbank"))
{ echo "table emptying successful"; }
else
{ echo "Error Processing Query: " .mysql_error(); };
?>
Note1: this merely does an uncondtitional delete from - again,this feature would be buried or hidden from the users else spotty herbert will trash your collection for fun.
<?php
require ("connect.php");
$thequery = "select * from urlbank";
/* attempt to retrieve the data */
$answertable = mysql_query($thequery, $connection);
if (!$answertable)
{
echo "no data was returned";
}
else
{
$numcolumns = mysql_num_fields($answertable);
echo "<table cellpadding=5 border=1 cellspacing=0>";
echo "<tr>";
for ($counter = 0; $counter < $numcolumns; $counter++)
{
$name = mysql_field_name($answertable,$counter);
echo "<th>$name</th>";
};
echo "</tr>";
while (list($bankid, $url, $sitename, $dateadded, $category, $person,
$dessie, $votes) = mysql_fetch_row($answertable))
{
echo "<tr>";
echo "<td>". $bankid . "</td>";
echo "<td><a href=". $url . ">". $url ."</a></td>";
echo "<td>". $sitename . "</td>";
echo "<td>". $dateadded . "</td>";
echo "<td>". $category . "</td>";
echo "<td>". $person . "</td>";
echo "<td>". $dessie . "</td>";
echo "<td>". $votes . "</td>";
echo "</tr>";
}
echo "</table>";
}
?>
Note1: There is a LOT of html schrapnel here to make the display look like a table. There is a LOOP that displays the table headings (retreiving the field names from the table first)
Note2: I decided to make the URL live - ie, surround it in an <a href> tag - that is why the url appears twice in the table data line.
Note3: When issuing the query, a pointer is returned to the stored answer table, you have to retrieve rows from the answer table one at a time (or rather, you keep retrieving them while it is possible to do so) - this approach allows for the same code to be used regardless of the size of the answer table which is a good thing.
<form name="form1" method="post" action="addformdatatotable.php">
<table border="0" align="center" cellpadding="5" cellspacing="0">
<tr>
<td>URL</td>
<td><input name="url" type="text" id="url" value="http://" size="80"></td>
</tr>
<tr>
<td>SITE NAME </td>
<td><input name="site" type="text" id="site" size="80"></td>
</tr>
<tr>
<td>CATEGORY</td>
<td><input name="category" type="text" id="category" size="80"></td>
</tr>
<tr>
<td>PERSON</td>
<td><input name="person" type="text" id="person" size="80"></td>
</tr>
<tr>
<td>DESCRIPTION</td>
<td><textarea name="dessie" cols="80" rows="3" id="dessie"></textarea></td>
</tr>
<tr>
<td> </td>
<td><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</form>
Note1: For now, this goes in a HTML file - you will note it's ACTION is the connection to the PHP file.
Process the form data and add to the table
<?php
include ("connect.php");
$whenisitnow = date("d-m-Y g:i a");
/* specify the sources of data - some posted, some not */
if (mysql_query("insert into urlbank (url, sitename, dateadded, category, person, dessie, votes)
values('".$_POST['url']."',
'".$_POST['site']."',
'".$whenisitnow."',
'".$_POST['category']."',
'".$_POST['person']."',
'".$_POST['dessie']."',
'1')"))
{ echo "form data insertion successful"; }
else
{ echo "data not inserted";
$error_number = mysql_errno();
$error_msg = mysql_error();
echo "error $error_number: $error_msg"; }
?>
Note1: Since the data was POSTED by a form, it now (well, at least temporarily) living in the HTTP_POST vars area on the server - you have to retrieve it from there (hence the ' ".$_POST['whatever']." ' construct, where whatever it the name of the form element that posted the value.
Note2: It would be more typical to include the form and the data handling on the same PHP file - we will do this in subsequent projects.
Populate a categories drop list (from existing categories)
Create a data entry form that includes a categories droplist
Create a voting page
Create a basic basic search engine
Achieve World Peace
...one can only hope
|
