Tutorial 5
Interacting with MYSQL
1. Connecting to MYSQL
<?php
$conn = mysql_connect("localhost", "lohky", "welcome");
echo $conn;
?>
2. Creating a simple table
<?php
// Opening the initial connection. Remember to change it to your own login and password
$conn = mysql_connect("localhost", "lohky", "welcome");
// pick the database to use
mysql_select_db ("lohky", $conn);
// Create table
$sql = "Create table testtable (id int not null primary key auto_increment, testfield varchar (75))";
// Execute the MYSQL query
$result=mysql_query($sql, $conn);
// Display the result indentifier
echo $result;
?>
Lets check if the table is created or not
Telnet into 10.1.1.200
In order to login into MYSQL, type the following command in P200 server
mysql -u lohky
mysql > use lohky ; This should reflect your own student id
mysql > describe testtable
3. Using the same script connect02.php. Put in an error capture and display to screen.
<?php
// Opening the initial connection. Remember to change it to your own login and password
$conn = mysql_connect("localhost", "lohky", "welcome");
// pick the database to use
mysql_select_db ("lohky", $conn);
// Create table
$sql = "Create table testtable (id int not null primary key auto_increment, testfield varchar (75))";
// Execute the MYSQL query
$result=mysql_query($sql, $conn) or die(mysql_error());
// Display the result indentifier
echo $result;
?>
4. Lets use a form now and attach a php script to insert some data into the table.
The following is a html document called insert.html. It will called the script connect04.php and insert data onto the SQL table
<HTML>
<HEAD>
<TITLE>Insert Form</TITLE>
</HEAD>
<BODY>
<FORM ACTION="connect04.php" METHOD=POST>
<P>Text to add:<br>
<input type=text name="testField" size=30>
<p><input type=submit name="submit" value="Insert Record"></p>
</FORM>
</BODY>
</HTML>
Connect04.php
<?php
// open the connection
$conn = mysql_connect("localhost", "lohky", "welcome");
// pick the database to use
mysql_select_db("lohky",$conn);
// create the SQL statement
$sql = "INSERT INTO testtable values ('', '$_POST[testField]')";
// execute the SQL statement
if (mysql_query($sql, $conn)) {
echo "record added!";
} else {
echo "something went wrong";
}
?>
5. Retrieving some records
<?php
// open the connection
$conn = mysql_connect("localhost", "lohky", "welcome");
// pick the database to use
mysql_select_db("lohky",$conn);
// create the SQL statement
$sql = "SELECT * FROM testtable";
// execute the SQL statement
$result = mysql_query($sql, $conn) or die(mysql_error());
//go through each row in the result set and display data
while ($newArray = mysql_fetch_array($result)) {
// give a name to the fields
$id = $newArray['id'];
$testfield = $newArray['testfield'];
//echo the results onscreen
echo "The ID is $id and the text is $testfield <br>";
}
?>
Posts
No comments:
Post a Comment