Need a hand on mysql / php / server

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
yy885
Forum Newbie
Posts: 3
Joined: Mon Nov 28, 2011 8:19 am

Need a hand on mysql / php / server

Post by yy885 »

Hello all

I am a new beginner with some basic knowledge of html/css/ a bit of php and mysql.

I am now creating a website which will have a table need to connect to mysql (only 9 fields).

basic web layout > done.

mysql field > done.

convert to php > done.

Now I have to start building a mysql server.

By this, how can I build a online mysql server? can i use apache server for the testing phears? if so then what should I do first?

what do i need to be aware of?



sorry for the english problem.

thank you for your help
Regards.

below is the code Ive got for it.

connect.php

Code: Select all

<?php

$hostname='***'; //// specify host, i.e. 'localhost'
$user='****'; //// specify username
$pass='****'; //// specify password
$dbase='****'; //// specify database name
$connection = mysql_connect("$hostname" , "$user" , "$pass")
or die ("Can't connect to MySQL");
$db = mysql_select_db($dbase , $connection) or die ("Can't select database.");
?>

add.php

Code: Select all

<form id="FormName" action="added.php" method="post" name="FormName">
<table width="448" border="0" cellspacing="2" cellpadding="0">
<tr><td width = "150"><div align="right"><label for="name">Name</label></div></td>
<td><input id="name" name="name" type="text" size="25" value="" maxlength="60"></td></tr><tr><td width = "150"><div align="right"><label for="info">Info</label></div></td>
<td><textarea id="info" name="info" rows="4" cols="40"></textarea></td></tr><tr><td width = "150"><div align="right"><label for="type">Type</label></div></td>
<td><select id="type" name="type" size="1">
<option value="one">test 1</option>
<option value="two">test 2</option>
<option value="two">test 3</option>
<option value="two">test 4</option>
<option value="two">test 5</option>
</select></td></tr><tr><td width = "150"><div align="right"><label for="risk">Risk</label></div></td>
<td><select id="risk" name="risk" size="1">
<option value="one">test 1</option>
<option value="two">test 2</option>
<option value="two">test 3</option>
</select></td></tr><tr><td width = "150"><div align="right"><label for="category">Category</label></div></td>
<td><select id="category" name="category" size="1">
<option value="one">test 1</option>
<option value="two">test 2</option>
<option value="two">test 3</option>
<option value="two">test 4</option>
</select></td></tr><tr><td width = "150"><div align="right"><label for="solution">Solution</label></div></td>
<td><textarea id="solution" name="solution" rows="4" cols="40"></textarea></td></tr><tr><td width = "150"><div align="right"><label for="public">Public</label></div></td>
<td><input id="public" name="public" type="checkbox" value="Y"></td></tr><tr><td width = "150"><div align="right"><label for="date">Date</label></div></td>
<td><input id="date" name="date" type="text" size="25" value="" maxlength="255"></td></tr><tr><td width = "150"><div align="right"><label for="date_edited">Date edited</label></div></td>
<td><input id="date_edited" name="date_edited" type="text" size="25" value="" maxlength="255"></td></tr><tr><td width="150"></td><td>
<input type="submit" name="submitButtonName" value="Add"></td>
</tr></table></form>
added.php

Code: Select all

<a href="index.php">Back to List</a>
<?php

include("connect.php");
$name = trim($_POST['name']);
$info = trim($_POST['info']);
$type = trim($_POST['type']);
$risk = trim($_POST['risk']);
$category = trim($_POST['category']);
$solution = trim($_POST['solution']);
$public = trim($_POST['public']);
$date = trim($_POST['date']);
$date_edited = trim($_POST['date_edited']);

$query = "INSERT INTO issues_data (id, name, info, type, risk, category, solution, public, date, date_edited)
VALUES ('', '$name', '$info', '$type', '$risk', '$category', '$solution', '$public', '$date', '$date_edited')";
$results = mysql_query($query);

if ($results)
{
echo "Details added.";
}
mysql_close();
?>
index.php

Code: Select all

<a href="add.php">Add entry</a><br>
<br>
<?php

include("connect.php");

$query = "SELECT * FROM issues_data ";
$result = mysql_query($query);
$num = mysql_num_rows ($result);
mysql_close();

if ($num > 0 ) {
$i=0;
while ($i < $num) {
$name = mysql_result($result,$i,"name");
$info = mysql_result($result,$i,"info");
$type = mysql_result($result,$i,"type");
$risk = mysql_result($result,$i,"risk");
$category = mysql_result($result,$i,"category");
$solution = mysql_result($result,$i,"solution");
$public = mysql_result($result,$i,"public");
$date = mysql_result($result,$i,"date");
$date_edited = mysql_result($result,$i,"date_edited");
$id = mysql_result($result,$i,"id");

echo "<b>Name:</b> $name<br>";
echo "<b>Info:</b> $info<br>";
echo "<b>Type:</b> $type<br>";
echo "<b>Risk:</b> $risk<br>";
echo "<b>Category:</b> $category<br>";
echo "<b>Solution:</b> $solution<br>";
echo "<b>Public:</b> $public<br>";
echo "<b>Date:</b> $date<br>";
echo "<b>Date edited:</b> $date_edited<br>";
echo "<a href=\"update.php?id=$id\">Update</a> - <a href=\"delete.php?id=$id\">Delete</a>";
echo "<br><br>";

++$i; } } else { echo "The database is empty"; }?>
update.php

Code: Select all

<?php

include("connect.php");
$id = $_GET['id'];

$qP = "SELECT * FROM issues_data WHERE id = '$id'  ";
$rsP = mysql_query($qP);
$row = mysql_fetch_array($rsP);
extract($row);
$name = trim($name);
$info = trim($info);
$type = trim($type);
$risk = trim($risk);
$category = trim($category);
$solution = trim($solution);
$public = trim($public);
$date = trim($date);
$date_edited = trim($date_edited);

mysql_close();
?>
<form id="FormName" action="updated.php" method="post" name="FormName">
<table width="448" border="0" cellspacing="2" cellpadding="0">
<tr><td width="150"><div align="right">
<label for="name">Name</label></div>
</td>
<td>
<input id="name" name="name" type="text" size="25" value="<?=$name ?>" maxlength="60"></td>
</tr>
<tr><td width="150"><div align="right">
<label for="info">Info</label></div>
</td>
<td>
<textarea id="info" name="info" rows="4" cols="40"><?=$info ?></textarea></td>
</tr>
<tr><td width="150"><div align="right">
<label for="type">Type</label></div>
</td>
<td>
<select id="type" name="type" size="1">

<option value="one" <?php if($type == "first") {echo"selected";} ?>>first</option>
<option value="two" <?php if($type == "second") {echo"selected";} ?>>second</option>
</select></td>
</tr>
<tr><td width="150"><div align="right">
<label for="risk">Risk</label></div>
</td>
<td>
<select id="risk" name="risk" size="1">

<option value="one" <?php if($risk == "first") {echo"selected";} ?>>first</option>
<option value="two" <?php if($risk == "second") {echo"selected";} ?>>second</option>
</select></td>
</tr>
<tr><td width="150"><div align="right">
<label for="category">Category</label></div>
</td>
<td>
<select id="category" name="category" size="1">

<option value="one" <?php if($category == "first") {echo"selected";} ?>>first</option>
<option value="two" <?php if($category == "second") {echo"selected";} ?>>second</option>
</select></td>
</tr>
<tr><td width="150"><div align="right">
<label for="solution">Solution</label></div>
</td>
<td>
<textarea id="solution" name="solution" rows="4" cols="40"><?=$solution ?></textarea></td>
</tr>
<tr><td width="150"><div align="right">
<label for="public">Public</label></div>
</td>
<td>
<input id="public" name="public" type="checkbox" <?php if ($public == "Y") { echo "checked";} ?> value="Y"></td>
</tr>
<tr><td width="150"><div align="right">
<label for="date">Date</label></div>
</td>
<td>
<input id="date" name="date" type="text" size="25" value="" maxlength="255"></td>
</tr>
<tr><td width="150"><div align="right">
<label for="date_edited">Date edited</label></div>
</td>
<td>
<input id="date_edited" name="date_edited" type="text" size="25" value="" maxlength="255"></td>
</tr>
<tr>
<td width="150"></td>
<td><input type="submit" name="submitButtonName" value="Update"><input type="hidden" name="id" value="<?=$id?>"></td>
</tr>
</table>
</form>

updated.php

Code: Select all

<a href="index.php">Back to List</a><br>
<br>
<?php

include("connect.php");

$id = $_POST['id'];
$name = trim($_POST['name']);
$info = trim($_POST['info']);
$type = trim($_POST['type']);
$risk = trim($_POST['risk']);
$category = trim($_POST['category']);
$solution = trim($_POST['solution']);
$public = trim($_POST['public']);
$date = trim($_POST['date']);
$date_edited = trim($_POST['date_edited']);

$update = "UPDATE issues_data SET name = '$name', info = '$info', type = '$type', risk = '$risk', category = '$category', solution = '$solution', public = '$public', date = '$date', date_edited = '$date_edited' WHERE id = '$id' ";
$rsUpdate = mysql_query($update);
if ($rsUpdate)
{
echo "Update successful.";
}
mysql_close();
?>
delete.php

Code: Select all

<?php

$id = $_GET['id'];?>
<div align="center">
<h2>Are you sure?</h2>
<h2><a href="deleted.php?id=<?php echo "$id" ?>">Yes</a> - <a href="index.php">No</a></h2>
</div>
deleted.php

Code: Select all

<a href="index.php">Back to List</a><br>
<br>
<?php

include("connect.php");

$id = $_GET['id'];

$delete = "DELETE FROM issues_data WHERE id = '$id' ";
mysql_query($delete);
mysql_close();

echo "Entry deleted";

?>
MySQL Database

Code: Select all

CREATE TABLE `issues_data` (
`id` int(6) NOT NULL auto_increment,
`name` varchar(60) NOT NULL default '',
`info` text NOT NULL default '',
`type` varchar(255) NOT NULL default '',
`risk` varchar(255) NOT NULL default '',
`category` varchar(255) NOT NULL default '',
`solution` text NOT NULL default '',
`public` enum('N','Y') NOT NULL default 'N',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`date_edited` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY  (`id`),
UNIQUE KEY `id` (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Need a hand on mysql / php / server

Post by twinedev »

doesn't where you have the PHP code hosted already have mySQL server set up?
yy885
Forum Newbie
Posts: 3
Joined: Mon Nov 28, 2011 8:19 am

Re: Need a hand on mysql / php / server

Post by yy885 »

for now, I didnt put them in the server yet as this is the first time I use php and mysaql so I want to make sure how am I going to process first before I start installing things.

I had html pages and a table with 9 coloums. Which I want to link it to mysql to read the data.

I did some research and tried to make them into php (whcih I show above)

And now, I am going to install apache server on my winXP for the testing phears. (I do have Ubuntu and bind9 set up on the other machine but I feel more comfortable on windows as this is the one I start learning of.)

So, right now I ve to install apache server and make sure the php part is working. Then the next questino is how to install the mysql table above into the apache server?

Thank you for your time.
Regards
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Need a hand on mysql / php / server

Post by Celauran »

If you have a Linux machine available, I strongly recommend setting up Apache/PHP/MySQL there as it will more closely approximate your site's live environment.

That aside, you don't install MySQL tables into an Apache server. Apache is a web server. You also need to install a MySQL server.
yy885
Forum Newbie
Posts: 3
Joined: Mon Nov 28, 2011 8:19 am

Re: Need a hand on mysql / php / server

Post by yy885 »

Finally have some time to do my own work now...anyway long story short.

I implemented these code into pages and the mysql.
I am using xampp to run the test.

Now, I am having some problem and this is one of the issue that I cannot fix.

This is the output.
Image

I use test 4 as an example. I my now in the edit page and trying to edit the record.
Image

If i only put down the time in the Date edited.
Image

update successful
Image

but then the Date Added will be turn to 0.
Image

So...I am wondering I did the thing wrong from the very beginning. Can someone please help me or give me some suggestion?
All I want for these 2 fields are:
Date Added: showing the date and time for the record created.
Date edited: showing the date and time for the record edited.
Is there have a way that they can do it automatically rather then have to manually input them?

Thank you for your time and suggestion.

----------------
additional information
here is an example for what I trying to build.

http://home.mcafee.com/VirusInfo/VirusP ... key=926437

I am trying to create a mysql which will input this kind of record and then display it like this.

Risk Assessment: Home Low | Corporate Low
Date Discovered: 3/11/2012
Date Added: 3/11/2012
Origin: Unknown
Length: 839680
Type: Trojan
Subtype: Remote Access
DAT Required: 6646
Post Reply