myProgrammingNote: 2010

Friday, December 31, 2010

php mySQL - added "formatted output" with "error trapping" and "include" to the previous code

Include File:

<?php
$user="root";
$password="passpass";
$database="myfirstdatabase";
?>

Script File:

<?php

include("dbinfo.inc.php");

$link = mysql_connect('localhost',$user,$password);
if (!$link){
die('Could not connect: ' . mysql_error());
}
echo "connected to server ";
if (!mysql_select_db($database)){
 die('Could not select database: ' . mysql_error());
}
echo "connected to database ";
$query="SELECT * FROM contacts";
$result=mysql_query($query);
$num=mysql_numrows($result);

if($num==0){
echo "The database contains no contacts yet";
}
else{
echo "<center>Database Output</center> ";
?>

<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th>Name</th>
<th>Phone</th>
<th>Mobile</th>
<th>Fax</th>
<th>E-mail</th>
<th>Website</th>
</tr>

<?php
$i=0;
while ($i < $num){
 $first=mysql_result($result,$i,"first");
 $last=mysql_result($result,$i,"last");
 $phone=mysql_result($result,$i,"phone");
 $mobile=mysql_result($result,$i,"mobile");
 $fax=mysql_result($result,$i,"fax");
 $email=mysql_result($result,$i,"email");
 $web=mysql_result($result,$i,"web");
?>

<tr>
<td><?php echo $first." ".$last; ?></td>
<td><?php echo $phone; ?></td>
<td><?php echo $mobile; ?></td>
<td><?php echo $fax; ?></td>
<td><a href="mailto:<?php echo $email; ?>">E-mail</a></td>
<td><a href="<?php echo $web; ?>">Website</a></td>
</tr>

<?php
$i++;
}

echo "</table>";
}
mysql_close($link);
echo "disconnected ";
?>

php mySQL - mysql_query();

Following the previous sample code, this one gets the data from the table and display them. I have edited the error handling parts. But again, there is something that bothers me about this code.

<?php
$user="root";
$password="passpass";
$database="myfirstdatabase";
$link = mysql_connect('localhost',$user,$password);
if (!$link){
die('Could not connect: ' . mysql_error());
}
echo "connected to server ";
if (!mysql_select_db($database)){
die('Could not select database: ' . mysql_error());
}
echo "connected to database ";
$query="SELECT * FROM contacts";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close($link);
echo "disconnected ";
echo "<center>Database Output</center> ";
$i=0;
while ($i < $num){
$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");
$phone=mysql_result($result,$i,"phone");
$mobile=mysql_result($result,$i,"mobile");
$fax=mysql_result($result,$i,"fax");
$email=mysql_result($result,$i,"email");
$web=mysql_result($result,$i,"web");
echo "$first $last 
Phone: $phone 
Mobile: $mobile 
Fax: $fax 
E-mail: $email 
Web: $web <hr /> ";
$i++;
}
?>

What is in $result?
$result=mysql_query($query);

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

(from: http://php.net/manual/en/function.mysql-query.php)

So, it is a resource.
What is a resource?

A resource is a special variable, holding a reference to an external resource.
(from: http://www.php.net/manual/en/language.types.resource.php)

I see..
It is just referencing to the table in the database, right?
Then, howcome it can still get to the data even after the connecton to database is deiconnected by mysql_close($link); ?

Thanks to the reference-counting system introduced with PHP 4's Zend Engine, a resource with no more references to it is detected automatically, and it is freed by the garbage collector. For this reason, it is rarely necessary to free the memory manually.
(from: http://www.php.net/manual/en/language.types.resource.php)

ummm...
I guess, it means...
The table file is left open even the connection to the table is disconnected, and the file won't be closed until the resource has no more reference to it.

But I think I perfer to have mysql_close($link); after it is done processing. Later, I need to find out the standard way of handling.

Thursday, December 30, 2010

php mySQL - mysql_close(); php 5.3 requires link_identifier

I went to several different tutorial sites for learning php/mySQL, and picked one of them. Well... intentionally or unintentionally, found few things were wrong with the sample codes given in the tutorial sessions. That prevented me to do copy & paste operations, and made me really dig into the stuff. I guess, after all, it was a good tutorial :O

Below is a code given in the tutorial session, which gets the data from the html forms and writes the data to the database.

<?php
$user="root";
$password="passpass";
$database="myfirstdatabase";
$first=$_POST['first'];
$last=$_POST['last'];
$phone=$_POST['phone'];
$mobile=$_POST['mobile'];
$fax=$_POST['fax'];
$email=$_POST['email'];
$web=$_POST['web'];
mysql_connect(localhost,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = "INSERT INTO contacts VALUES ('','$first','$last','$phone','$mobile','$fax','$email','$web')";
mysql_query($query);
mysql_close();
?>

Error 1:

When this code runs, there will be an error message in the browser.

The connection was reset
The connection to the server was reset while the page was loading.

Windows also spits out the error message, saying:

"Apache HTTP Server has stopped working"

    Problem signature:
    Problem Event Name: APPCRASH
    Application Name: httpd.exe
    Application Version: 2.2.11.0
    Application Timestamp: 493f5d44
    Fault Module Name: php_mysql.dll
    Fault Module Version: 5.3.0.0
    Fault Module Timestamp: 4a492311
      Exception Code: c0000005     ....

Error 2:

And, there is another error message, which doesn't show up until the above error gets taken care of.

Notice: Use of undefined constant localhost - assumed 'localhost' in C:\wamp\www\testFolder\tetsuro\tetsuroL01_php_mySQL\insert.php on line 15

The Cause For Error 1:

The cause of error is mysql_close() not having MySQL link identifier. Although the php manual says that if MySQL link identifier is not specified, it will close the last opened link, it seems that since php 5.3, mysql_close() must have the link_identifier parameter.

The Cause For Error 2:

Missing quotation marks for localhost.

Wrong!

mysql_connect(localhost,$username,$password);

Correct.

mysql_connect("localhost",$username,$password);or,

mysql_connect('localhost',$username,$password);

Either " or ' will work.

Double or Single Quote?

Double-quoted strings are parsed.

Single-quoted strings are taken literally.

$str = 'His name is Joe';
$name = 'Joe';
$str = "His name is $name";

Here's the updated working php script.

<?php
$user="root";
$password="passpass";
$database="myfirstdatabase";
$first=$_POST['first'];
$last=$_POST['last'];
$phone=$_POST['phone'];
$mobile=$_POST['mobile'];
$fax=$_POST['fax'];
$email=$_POST['email'];
$web=$_POST['web'];
$link = mysql_connect('localhost',$user,$password);
if (!$link){
die('Could not connect: ' . mysql_error());
}
echo "connected";
echo " ";
@mysql_select_db($database) or die( "Unable to select database");
$query = "INSERT INTO contacts VALUES
('','$first','$last','$phone','$mobile','$fax','$email','$web')";
mysql_query($query);
mysql_close($link);
echo "disconnected";
?>

Wednesday, December 29, 2010

php note - sending email from server

<?php
$to = "aaa@bbb.com";
$subject = "email sent with php";
$body = "sending email from server using php script.";
$headers = "From: xxx@yyy.com\n";
# error checking
if(mail($to,$subject,$body,$headers)){
echo "email was sent to $to with the subject: $subject";
}
else{
echo "there was a problem sending the email";
}
?>

php note - checking if form input is empty

if ($name == "" || $email == "" || $password == "") {
echo "Please fill in all the fields";
}

php note - use backslash

Place a backslash in front of ", which meant to be used with HTML.
Like this:

myProgrammingNote