Transcript PHP SQL functions

LIS651 lecture 7
PHP mySQL
Thomas Krichel
2010-03-11
PHP mySQL functions
• We are using here the new version of PHP
mySQL function, starting with mysqli_
• The interface is object-oriented, but can also be
accessed in a non-object-oriented way. This is
known as the procedural style, in the
documentation.
• You should use the online documentation at
http://php.net/mysqli
error suppression
• The function mentioned in this library usually
report any error that has occurred.
• It can be useful to suppress such errors with the
PHP error suppression operator @.
• @function() will run the function function without
reporting mistakes.
• You can then create your own customized
mistakes by checking for errors every time you
run a mysqli function. This is useful.
mysqli_connect()
• This is used to establish a connection to the
mySQL server. It is typically of the form
mysqli_connect('host', 'user', 'password');
• Example
$link= mysqli_connect('localhost','boozer','heineken');
• You can use localhost as the host name for wotan
talking to itself, but you could also connect to
other Internet hosts, if you have permission.
the mySQL connection with mysqli
• The mysqli module has the ability to handle
several connections to the mySQL server.
• Once a connection is established it is represented
by the variable returned by mysqli_connect().
• This variable, of type "resource" has the be
referenced later to let mysqli functions know what
connection you are using, because there may be
more than one open at the same time.
mysqli_connect_error()
• This function returns a string with the last
connection error.
$link = mysqli_connect("localhost", "bad_user", "");
if (!$link) {
print "Can't connect to localhost. The error is<br/>";
print mysqli_connect_error();
print "<br/>";
}
• Note the use of ! to express Boolean "not".
mysqli_error( link )
• This function return the error from the last mySQL
command. It returns false if there was no error.
$error=mysqli_error($link);
if($error) {
print "mySQL error: $error<br/>";
}
• This function requires the connection as a
parameter.
• The value returned from that function is a simple
string.
• It is a good idea to check out error messages.
mysqli_select_db()
• This command has the syntax where link is a
resource representing a connection and
database is the name of a database.
• This tells mySQL that you now want to use the
database database.
mysqli_select_db($link,'beer_shop');
• It has the same effect as issuing
USE beer_shop;
within mySQL.
mysqli_query()
• mysqli_query(link,query) send the query string
query to mySQL connection represented by link
$link = mysqli_connect("localhost", "owner", "bruch");
// you may then add some connection checks
$query="SELECT * FROM beer_shop.customers";
$result=mysqli_query($link,$query);
• Note that the query itself does not require a
terminating semicolon.
• The result is in $result.
result of mysqli_query()
• For SELECT, SHOW, DESCRIBE or EXPLAIN
mySQL queries, mysqli_query() returns a
resource that can be further examined with
mysqli_fetch_array(). This is very important
function that we look at in the next slide.
• For UPDATE, INSERT, DELETE, DROP and
others, mysqli_query() returns a Boolean value.
examining resulting rows
• mysqli_fetch_array(result) returns an array that is
the result row for the resource result representing
the most recent, or NULL if it the last result is
reached. Its results in an array that contains the
columns requested both by number and by
column name:
while($columns=mysqli_fetch_array($result)) {
print 'name: '.$columns['name'];
print 'first column: ‘.$columns[0];
}
examining a specific result
• mysqli_data_seek(result, number) sets the array
that is returned by mysqli_fetch_array to a number
number.
while($row=mysqli_fetch_array($result)) {
print 'first column: '.$row[0];
}
mysqli_data_seek($result,0);
// otherwise the second loop would not work
while($row=mysqli_fetch_array($result)) {
print 'first column: '.$row[0];
}
mysqli_num_rows()
• This command has the syntax
mysqli_num_rows(result) where the resource
result is the result of a query.
• It returns the number of rows that are in the
result.
• This is useful in announcing the number results
before display of results.
mysqli_real_escape_string()
• mysqli_real_escape_string( link,string) returns a
string escaped for the using in mySQL.
$name="John O'Guiness";
$s_name=mysqli_real_escape_string($link,$name);
print $s_name; // prints: John O\'Guiness
• Note that this function makes a call to mySQL,
therefore a connection must be established before
the function can be used.
• This function guards against SQL injections.
mysqli_close(link)
• This command closes a connection. It requires
the connection as an argument, so that it knows
which connection to close.
• This is the happiest command there is, because it
means that we have finished.
• Unfortunately it is not used very often because
the mySQL connection is closed automatically
when the script finishes running.
extra: sha1()
• This is a function that calculates a combination of
40 characters from a string.
• The result of sha1() can not be translated back
into the original string.
• This makes it a good way to store password.
$s_password=sha1($password);
http://openlib.org/home/krichel
Thank you for your attention!
Please switch off machines b4 leaving!