Tag Archive: mysql


LIKE and NOT LIKE

Filed Under: MySQL
Jun.14, 2010

The like and not like have two search symbols. The underscore _ character that looks for one character and the percentage % character that looks for zero or more characters. I use function  table which has function_name,  function_name and function_description fields. Lets see the example:

SELECT *
FROM `functions`
WHERE function_name LIKE 'a%'
LIMIT 0 , 30

Above query will only pick out result that provide a TRUE result according to the WHERE equation. We can see that equation will equal the LIKE value plus some possible extra characters afterwards.

The LIKE search is not case sensitive, so it will accept anything starting with ‘a’ as well.

So how LIKE search can make a different lowercase or uppercase letters? by adding BINARY word after LIKE.

SELECT *
FROM `functions`
WHERE function_name LIKE BINARY "a%"
LIMIT 0 , 30;

And the change the query like below to see the different:

SELECT *
FROM `functions`
WHERE function_name LIKE BINARY "A%"
LIMIT 0 , 30;

Queries using the LIKE or NOT LIKE parameters may be a bit slower than a normal query search considering they are a broader value and do not take advantage of any indexing.

Note: If you want to have an underscore or percentage character actually be part of the search value, put an escape slash \ in front of the character.

The underscore wildcard can be used a number of times to find a specific number of characters. Example, this would be used in an equation to return a value of ‘Stan’ plus 3 characters.

SELECT *
FROM `functions`
WHERE function_name LIKE BINARY "mdat___"
LIMIT 0 , 30;

The underscore and percentage characters (also known as wildcard) can be used in front, at the end, or both ends of a value.

Tags: , , , ,
read more

Working with MySQL Database

Filed Under: PHP
Apr.14, 2010

In this chapter we will try to run the basic SQL queries using mysql_query() function. Throught this function we can inserting, selecting, updating, deleting and retrieving data all. For INSERT, SELECT, UPDATE, and DELETE, no additional scripting is required after the query has been executed because you’re not displaying any results (unless you want to). For SELECT, you have a few options for displaying the data retrieved by your query. But first you need create tabel in Database that we have been create before. As a sample I’m create table Person with some field like this:

 CREATE TABLE `person` (
  `id` int(5) NOT NULL auto_increment,
  `lastname` varchar(15) collate latin1_general_ci NOT NULL,
  `firstname` varchar(15) collate latin1_general_ci NOT NULL,
  `address` varchar(25) collate latin1_general_ci NOT NULL,
  `city` varchar(20) collate latin1_general_ci NOT NULL,
  `age` int(2) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=7 ;

And now we learning Basic SQL Command to create and manipulate MySQL database tables.
THE INSERT:
The simple code for adding new records is syntax like this:

INSERT INTO table_name (column list) VALUES (column values)

The SELECT:
We used this command is to retrieve records. This command syntax can be totally simplistic or very complicated.

 SELECT expressions_and_columns FROM table_name
 [WHERE some_condition_is_true]
 [ORDER BY some_column [ASC | DESC]]
 [LIMIT offset, rows]

One handy expression is the * symbol, which stands for “everything.” So, to select “everything” mean all rows/ all columns.

THE UPDATE:
Update is used to modify the contents of one or more columns in an wxisting record. Basic update syntax is look like:

 UPDATE table_name
 SET column_name='new value'
 [WHERE some_condition_is_true]

The guidelines for updating a record are similar to those used when inserting a record—the data you’re entering must be appropriate to the data type of the field, and you must enclose your strings in single or double quotes, escaping where necessary.

THE DELETE:
This command is used to remove specification column. And the basic syntax is look like:

 DELETE FROM table_name
 [WHERE some_condition_is_true]
 [LIMIT rows]

You have learned the basic of SQL, from tabel creation to manipulating records. See you in the next chapter.

Tags: , , , ,
read more

Connecting to MySQL with PHP

Filed Under: PHP
Apr.12, 2010

To connecting MySQL Database, make sure you have MySQL running at a location to which your Web server can connect. Then you have created a user, password and the database to which you want to connect. The mysql_connect() is the first function in PHP to connect to MySQL.
The basic syntax for the connection is:

 mysql_connect("hostname", "username", "password");

Using actual sample values, the connection function looks like this:

 mysql_connect("localhost", "root", "pass");

This function returns a connection index (connection is succesful) or return false (connection fails). Lets see a working example of a connection script below. It assign the value of the connection index to a variable called $conn, then prints the value of $conn as proff of a connection. If you are not using password, just empty the password variable.

<?php
$host="localhost";
$user="root";
$pass="";
$conn=mysql_connect($host, $user, $pass);
echo "$conn";
//eof

Save this script as config.php (sometimes I save as config.inc), then place it on your work folder. Check the result on your web browser and you will see something like this:

 Resource id #2

Connecting to MySQL using the mysql_connect() function is pretty straightforward. The connection closes when the script finishes its execution, but if you would like to explicitly close the connection, simply add the mysql_close() function at the end of the script.
And now we will try to connecting a Database, using function called mysql_select_db() with the following syntax:

 mysql_select_db($db,$conn);

To connect to a database named test_conn, first use mysql_connect(), then use mysql_select_db(), like this:

<?php
$host="localhost";
$user="root";
$pass="";
$db="test_conn";
$conn=mysql_connect($host, $user, $pass);
mysql_select_db($db,$conn);
//eof

You can modified simple connection script above by adding error message. The mysql_error() function will return a helpful error message when there is a mistake. Don’t forget to insert conjunction with the PHP die() function. See the modified script below:

<?php
  $host="localhost";
  $user="root";
  $pass="";
  $db="test_conn";
  $conn=mysql_connect($host, $user, $pass);
   if (!$conn){
       die('Could not connect Database.'.mysql_error());
   }
   mysql_select_db($db,$conn);
//eof

Move on to the next section to start inserting data into your table, and soon you’ll be retrieving and formatting it via PHP. Good Luck.

Tags: , , ,
read more
Powered by WordPress.