MySQL

Connecting to MySQL is pretty simple. There are several different ways of doing it through PHP like MySQLi and PDO. The way I usually do it is creating a mysql class that contains the connect, close, select, insert, and all the other functions readily needed for using PHP and MySQL. Unfortunately some clients that I work with don’t always use PHP 5 or higher.. :( So here is the way to connect just using functions so that it will work with earlier versions of PHP.

You can assign the database connection properties as variables, contants, or hard coded. Today I am going to pass variables into the function. You could also get much more detailed with it and make error handling if connection fails, but again… not today.

Also below is all the necessary PHP MySQL function. Check em. Good for Reference when not using a framework.

MySQL CONNECT

<?php
$link = mysql_connect("DB_HOST", "DB_USER", "DB_PASSWORD") or die(mysql_error());
?>

MySQL CLOSE

<?php
mysql_close($link);
?>

MySQL QUERIES

<?php
// INSERT
$result = mysql_query("INSERT INTO table(field1, field2) VALUES('value1', 'value2' ) ") or die(mysql_error());
// SELECT
$result = mysql_query("INSERT INTO table(field1, field2) VALUES('value1', 'value2' ) ") or die(mysql_error());
// WHERE
$result = mysql_query("SELECT * FROM table WHERE field1='value1'") or die(mysql_error());
// ORDER BY
$result = mysql_query("SELECT * FROM table ORDER BY fieldName") or die(mysql_error());
// GROUP BY with ORDER BY (NOTE: GROUP BY must come before ORDER BY)
$result = mysql_query("SELECT * FROM table GROUP BY fieldName ORDER BY fieldName") or die(mysql_error());
// LIMIT
$result = mysql_query("SELECT * FROM `your_table` LIMIT 0, 10") or die(mysql_error());
// WHERE CONDITIONALS ..more
$result = mysql_query("SELECT * FROM table WHERE field1='value1' OR field2='value2'");
$result = mysql_query("SELECT * FROM table WHERE field1='value1' && field2='value2'");
// COMPARISONS
$result = mysql_query("SELECT * FROM table WHERE field1 LIKE value%");
$result = mysql_query("SELECT * FROM table WHERE field1 NOT LIKE value%");
$result = mysql_query("SELECT * FROM table WHERE field1 LIKE value_");
$result = mysql_query("SELECT * FROM table WHERE field1 LIKE value__");
$result = mysql_query("SELECT * FROM table WHERE field1 LIKE value%");
// SUB QUERY
$result = mysql_query("SELECT * table1 WHERE table2_id IN(SELECT table2_id FROM table2 WHERE data = data )");
?>

MySQL CONDITIONALS

= equals
< less than
> greater than
<= less than or equal to
>= greater than or equal to
!= not equal to
IS NOT NULL has any value
IS NULL has no value at all
BETWEEN within a specified range
NOT BETWEEN outside a specified range
OR one of two equations are true
|| same as OR
AND both equations are true
&& same as AND
NOT both equations are false
! same as NOT

MySQL COMPARISONS

LIKE Simple pattern matching
NOT LIKE Negation of simple pattern matching
% Matches any number of characters, even zero characters
_ Matches exactly one character
STRCMP() Compare two strings

6 Responses to "PHP MySQL functions"

"SHARING IS CARING"
  1. Miguel says:

    This is a great right up. I’ll be using this tomorrow to try and reconnect MySql. Also, don’t forget your writeup about the mod rewrite and .htaccess. Thanks agian.

  2. pe3sos says:

    Good but you forgot to select database
    mysql_select_db( ‘database_name’,$link_connect);

  3. Ahmed Sobh says:

    i think u forget a lot of functions which u could use in order to manage your MySQL DB inside your php code like

    mysql_fetch_array()
    mysql_fetch_object()
    mysql_fetch_assoc()
    mysql_fetch_field()
    mysql_fetch_lengths()
    mysql_fetch_row ()
    mysql_real_escape_string()
    mysql_num_rows()
    mysql_create_db ()
    mysql_result ()

    and those which i mentioned, is which have a high priority and using frequently

    thanks

  4. eligeske says:

    Hi Ahmed,

    You are absolutely correct. I’ve been meaning to put those on there! Thanks

  5. I strongly recomment moving the the mysql pdo extension. It’s structure is object oriented and I have read that the mysql_xxxx libs will get deprecated in the future version of PHP. You can extend the pdo_mysql class.

  6. PaulH says:

    I strongly agree with Simon Ferragne – in fact, we should no longer be advocating the use of mysql at all (soon to be deprecated!). It’s insecure and we should always use properly parameterised queries so as to avoid sql injection. PDO makes this easier, or we can use mysqli but never mysql.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Learning DHTMLX Suite UI

Learn the foundation of the DHTMLX Suite quickly while building a single page application with multiple components in harmony.

Popular post