PHP MySQL functions

MySQL

PHP MySQL functions

TAGs: mysql statements php mysql functions 5 Comment

Posted By: eligeske on October 24, 2009 in MySQL, PHP

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

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

5 Responses to "PHP MySQL functions"

"SHARING IS CARING"

2009-12-03

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.
2010-01-24

pe3sos says:

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

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
2010-12-29

eligeske says:

Hi Ahmed,
You are absolutely correct. I’ve been meaning to put those on there! Thanks
2011-08-30

Simon Ferragne says:

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.

MySQL