Deprecated: Function ereg() is deprecated in /home3/vvaswani/db-mysql.php on line 174

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 60
The Melonfire Community - Trog
Logo         Community
  Trog


Copyright notice:

This article is copyright Melonfire,
Strict Standards: mktime(): You should be using the time() function instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 119
2017. All rights reserved.

All source code, brand names, trademarks and other content contained herein is proprietary to Melonfire,
Strict Standards: mktime(): You should be using the time() function instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 121
2017. All rights reserved.

Source code within this article is provided with NO WARRANTY WHATSOEVER. It is meant for illustrative purposes only, and is NOT recommended for use in production environments.

Copyright infringement is a violation of law.

Printed from http://www.melonfire.com/community/columns/trog/article.php?id=18



PHP 101 (part 4): Look, Ma...It's Alive!
Use PHP's database support to create dynamic data-driven Web sites.


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170
The Marriage Of PHP And mySQL

One of the most important factors driving PHP's popularity over the last couple of years has been its support for a variety of databases, including mySQL, mSQL, Oracle and Microsoft Access. By simplifying and streamlining database access, PHP allows developers to build complex data-driven Web applications while enjoying short development cycles because of the simplicity and flexibility of the language.

One of the most powerful combinations in the open source arena is the PHP/mySQL combination. For those of you new to open-source technology - where have you been, you galoots?! - mySQL is a fast, reliable, open-source database management system. By using PHP and mySQL, developers can provide customers with huge savings on the licensing costs of other commercially-licensed software, and also benefit from the tremendous amount of thought that PHP and mySQL developers have put into making sure that the two packages work together seamlessly and smoothly.

OK. Enough of the marketing talk. Let's get down to business.

In this issue of PHP 101, we're going to show you how to use PHP to extract data from a database, and use that data to build a dynamic Web page. All you need are the usual pre-requisites: a sense of humour, and a willingness to try something new. Some knowledge of SQL (Structured Query Language, the language used to interact with a database server) would be helpful, though not essential.

If you're planning on trying out some of the examples below, you'll also need to download and install the mySQL database server, available at http://www.mysql.com. We'll be assuming that you've installed and configured mySQL, and have the appropriate permissions to create and edit database tables.

If you're using a database other than mySQL, fear not - PHP supports all major databases, and you can use the techniques described over the next few pages to talk to other databases too. Your PHP manual will help you locate corresponding functions for other databases.


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170
Different Strokes...

What you just saw was a very basic example. Our next example will query the database, return the list of users with their URL list, and display it all in a neat <table>


<html>

<head>
</head>

<body>

<?php

// set up some variables

// server name
$server = "localhost";

// username
$user = "test";

// password
$pass = "test";

// database to query
$db = "php101";

// open a connection to the database
$connection = mysql_connect($server, $user, $pass) or die("Invalid server or user");


// formulate the SQL query
$query = "select * from url_list" or die("Error in query");

// run the query on the database
$result = mysql_db_query($db, $query, $connection) or die("Error in query");

// display the result
echo "<table width=450 border=1 cellspacing=0 cellpadding=0>";
echo "<tr><td width=150 align=left>User</td><td width=100 align=left>Site #1</td><td width=100 align=left>Site #2</td><td width=100 align=left>Site #3</td></tr>";

// with a while loop
// this loop will iterate as many times as there are records
while($myrow =  mysql_fetch_array($result))
{  
    $user =  $myrow["uid"];
    $title1 = $myrow["title1"];
    $url1 = $myrow["url1"];
    $title2 = $myrow["title2"];
    $url2 = $myrow["url2"];
    $title3 = $myrow["title3"];
    $url3 = $myrow["url3"];
   
    echo "<tr><td width=150 align=left>$user</td><td width=100 align=left><a href=$url1>$title1</a></td><td width=100 align=left><a href=$url2>$title2</a></td><td width=100 align=left><a href=$url3>$title3</a></td></tr>";      
}   

// memory flush
mysql_free_result($result);

?>

</body>
</html>


As in our previous example, the script first sets up a connection to the database. The query is formulated and the result set is returned to the browser. In this case, since there's going to be much more data than before, it's a good idea to use the mysql_fetch_array() function - this function returns the values from the database as an enumerated array, allowing you to access each element by the column name


$user =  $myrow["uid"];
$title1 = $myrow["title1"];
$url1 = $myrow["url1"];


and so on.

Note our use of a "while" loop to parse and display each available record of the result set, and of the die() function to exit the script with an error message should there be an error in the configuration information or the query string.

And here's the output:


<html>

<head>
</head>

<body>

<table width=450 border=1 cellspacing=0 cellpadding=0>

<tr><td width=150 align=left>User</td><td width=100 align=left>Site #1</td><td width=100 align=left>Site #2</td><td width=100 align=left>Site #3</td></tr>

<tr><td width=150 align=left>john</td><td width=100 align=left><a href=http://www.melonfire.com>Melonfire</a></td><td width=100 align=left><a href=http://www.devshed.com>Devshed</a></td><td width=100 align=left><a href=http://www.php.net>PHP.Net</a></td></tr>;

<tr><td width=150 align=left>bill</td><td width=100 align=left><a href=http://www.yahoo.com>Yahoo</a></td><td width=100 align=left><a href=http://www.slashdot.org>Slashdot</a></td><td width=100 align=left><a href=http://www.32bit.com>32Bit.com</a></td></tr>;
</body>
</html>



Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170
...For Different Folks

You can use PHP's mysql_fetch_row() function to obtain a simple array of values, and then use these values according to the array index - a slightly different variation of the technique used above. Take a look:


<html>

<head>
</head>

<body>
<?php

// set up some variables

// server name
$server = "localhost";

// username
$user = "test";

// password
$pass = "test";

// database to query
$db = "php101";

// open a connection to the database
$connection = mysql_connect($server, $user, $pass) or die("Invalid server or user");


// formulate the SQL query
$query = "select * from url_list" or die("Error in query");

// run the query on the database
$result = mysql_db_query($db, $query, $connection) or die("Error in query");

// display the result
echo "<table width=450 border=1 cellspacing=0 cellpadding=0>";
echo "<tr><td width=150 align=left>User</td><td width=100 align=left>Site #1</td><td width=100 align=left>Site #2</td><td width=100 align=left>Site #3</td></tr>";

// with a while loop
// this loop will iterate as many times as there are records
// and return an array named $myrow
while ($myrow = mysql_fetch_row($result))
{  
echo "<tr><td width=150 align=left>$myrow[0]</td><td width=100 align=left><a href=$myrow[2]>$myrow[1]</a></td><td width=100 align=left><a href=$myrow[4] >$myrow[3]</a></td><td width=100 align=left><a href=$myrow[6]> $myrow[5]</a></td></tr>";      
}

// memory flush
mysql_free_result($result);

?>

</body>
</html>


You can also use PHP's mysql_fetch_row() and list() functions to obtain a simple array of values, and then assign these values to different variables - another variation of the technique we've shown you above. Take a look - only the "while" loop will change:


<html>

<head>
</head>

<body>
<?php

// set up some variables

// server name
$server = "localhost";

// username
$user = "test";

// password
$pass = "test";

// database to query
$db = "php101";

// open a connection to the database
$connection = mysql_connect($server, $user, $pass) or die("Invalid server or user");


// formulate the SQL query
$query = "select * from url_list" or die("Error in query");

// run the query on the database
$result = mysql_db_query($db, $query, $connection) or die("Error in query");

// display the result
echo "<table width=450 border=1 cellspacing=0 cellpadding=0>";
echo "<tr><td width=150 align=left>User</td><td width=100 align=left>Site #1</td><td width=100 align=left>Site #2</td><td width=100 align=left>Site #3</td></tr>";

// with a while loop
// this loop will iterate as many times as there are records
// and the values returned will be assigned to different
// variables via list()
while (list($user, $title1, $url1, $title2, $url2, $title3, $url3) = mysql_fetch_row($result))
{  
echo "<tr><td width=150 align=left>$user</td><td width=100 align=left><a href=$url1>$title1</a></td><td width=100 align=left><a href=$url2>$title2</a></td><td width=100 align=left><a href=$url3>$title3</a></td></tr>";      
}   

// memory flush
mysql_free_result($result);

?>

</body>
</html>


In this case, the list() function is used to assign different elements of the result set to PHP variables, which are then used when rendering the page.


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170
New Friends

Thus far, we've simply been using SELECT queries to pull information out of a database. But how about putting something in?

SQL aficionados know that this happens via an INSERT query - and our next example demonstrates how you can use an HTML form to insert data into the database.


<?
// if form has not been submitted, display form
if (!$submit)
   {
?>
<html>
<head>
<basefont face=Arial>
</head>
<body>

<h3>Enter your bookmarks:</h3>
<form method="POST" action="push.php4">
<table>
<tr>
<td>
Username
</td>
<td>
<input name="username" length="10" maxlength="30">
</td>
</tr>        
<tr>
<td>
Web site
</td>
<td>
<input name="title1" length="30" maxlength="30">
</td>
</tr>        
<tr>
<td>
URL
</td>
<td>
<input name="url1" length="30">
</td>
</tr>        
<tr>
<td>
Web site
</td>
<td>
<input name="title2" length="30" maxlength="30">
</td>
</tr>        
<tr>
<td>
URL
</td>
<td>
<input name="url2" length="30">
</td>
</tr>        
<tr>
<td>
Web site
</td>
<td>
<input name="title3" length="30" maxlength="30">
</td>
</tr>        
<tr>
<td>
URL
</td>
<td>
<input name="url3" length="30">
</td>
</tr>        
<tr>
<td colspan="2" align="center">
<input type="submit" name="submit" value="Submit">
</td>
</tr>        
</table>
</form>   
</body>
</html>
<?
   }
// or process form input
else
   {
?>
<?php

// connect to database
$connection = mysql_connect("localhost", "test", "test") or die("Invalid server or user");

// select database
mysql_select_db("php101",$connection);

// formulate and run query
$query = "insert into url_list(uid,title1,url1,title2,url2,title3,url3) values('$username','$title1','$url1','$title2','$url2','$title3','$url3')";

$result = mysql_query($query,$connection) or die("Error in query");

?>

<html>
<head>
<basefont face=Arial>
</head>
<body>
<center>
<h3>Success!</43>
<table>
<tr>
<td>
<?php echo $username; ?>'s bookmarks have been saved.
</td>
</tr>
</center>
</body>
</html>

<?
   }
?>


This time around, we've demonstrated yet another PHP function - mysql_select_db(), which allows you to specify the database which will be used when connecting to the database. And since you've specified the database, you can use mysql_query() instead of mysql_db_query() - while the latter needs the database name as parameter [as you've seen in the examples above], the former simply needs the query string and the connection identifier.

As you can see, inserting a record into the database is very straightforward - simply fire the query and your INSERT statement will be executed. The only way to find out if the INSERT was successful is to check the value of the variable $result - if the variable isn't set, it implies that something didn't go as planned.


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170
Oops!

All done? Nope, not quite yet - before you go out there and start building cool data-driven Web sites for your customers, you should be aware that PHP comes with some powerful error-tracking functions which can speed up development time. Take a look at the following example, which contains a deliberate error in the SELECT query string:


<?php

// connect
$connection = mysql_connect("localhost", "test", "test") or die("Invalid server or user");
mysql_select_db("php101",$connection) or die("Invalid database");

// query
$query = "select from url_list";

// result
$result = mysql_query($query,$connection);

if(!$result)
{
    $error_number = mysql_errno();
    $error_msg = mysql_error();
    echo "MySQL error $error_number: $error_msg";   
}

?>  


And here's the output:


MySQL error 1064: You have an error in your SQL syntax near 'from url_list' at line 1


The mysql_errno() function displays the error code returned by mySQL if there's an error in your SQL statement, while the mysql_error() function returns the actual error message. Turn these both on, and you'll find that they can significantly reduce the time you spend fixing bugs.

And that's about all we have for this issue of PHP 101. Next week, we'll be wrapping things up with a look at PHP's file read/write capabilities, and we'll also show you how to define your own PHP functions. Don't miss it!

Note: All examples in this article have been tested on Linux/i586 with Apache 1.3.12 and PHP 4.0. YMMV!


Copyright notice:

This article is copyright Melonfire,
Strict Standards: mktime(): You should be using the time() function instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 193
2017. All rights reserved.

All source code, brand names, trademarks and other content contained herein and proprietary to Melonfire,
Strict Standards: mktime(): You should be using the time() function instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 195
2017. All rights reserved.

Source code within this article is provided with NO WARRANTY WHATSOEVER. It is meant for illustrative purposes only, and is NOT recommended for use in production environments.

Copyright infringement is a violation of law.

Printed from http://www.melonfire.com/community/columns/trog/article.php?id=18



Copyright © 1998-
Strict Standards: mktime(): You should be using the time() function instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 211
2017 Melonfire. All rights reserved
Terms and Conditions | Feedback