A quick example of a SQL statement with REPLACE

$query = 'SELECT id FROM `user` WHERE email ="'.$email.'" AND REPLACE(tel_1, " ","") = "'.$phone.'" LIMIT 1';

I often forget the exact syntax of things like this… So here’s an example…

$query1 = 'SELECT * FROM `'.$dbName.'`.`users` WHERE email NOT IN (SELECT email FROM `'.$setupDBName.'`.`users`)';

From Mark Jackson

Also the ‘IN’ and ‘NOT IN’ can be used to specify multiple conditional values – for example:

SELECT * FROM users WHERE email IN (‘[email protected]’,'[email protected]’,'[email protected]’,'[email protected]’);

Saves a bit of time writing it out in full with loads of email = ‘…’ OR email = ‘…’

The following SQL query causes an error

SELECT vote_vo_ip, COUNT(*) as times FROM `vote` 
LEFT OUTER JOIN `voter` ON vote.vote_vo_id = voter.vo_id
WHERE vote_co_id = 2
AND times > 1
GROUP BY vote_vo_ip

The column ‘times’ has not been created when we run the WHERE clause, so we have to use HAVING after the WHERE, for example

SELECT vote_vo_ip, COUNT(*) as times FROM `vote` 
LEFT OUTER JOIN `voter` ON vote.vote_vo_id = voter.vo_id
WHERE vote_co_id = '.$co_id.'
GROUP BY vote_vo_ip 
HAVING times > 1

Which works

This is something you’re likely to do several times in several different scenarios. The common one for me is generating reports from a database.

I tend to build a $_SESSION array as I run the SQL query that is displayed on the page, and then provide a link to ‘Download CSV’. Then the csv-export script just needs to output the array….

I love the ‘LIKE’ SQL command for searching within phpMyAdmin, but to do the job properly on a page, you really need to know how to use ‘Full-text Searching’…

This article by Jim Ferrara does the trick…. Thanks Jim!

Once you’ve added or edited a database table, it’s good to check that the change/addition was made… then either move on or show an error, etc.

Here’s how to do it with mysql_affected_rows().

Note, if the same data was submitted, i.e. a form with values filled in from the database was submitted without the values being changed, then the ‘affected row count’ is likely to be ‘zero’, so you might trigger an error, when infact there’s no error, just nothing was requested to change

Here’s a great little script that will back-up your database to a zipped .gz file and store it in a folder on your server….

Here’s another way to connect to the database:

There are several methods of connecting to a database, here’s one example:

// Set the database access information as constraints
DEFINE ('DB_USER', 'userName);
DEFINE ('DB_PASSWORD', 'passWord');
DEFINE ('DB_HOST', 'localhost');    
DEFINE ('DB_NAME', 'databaseName');   

Use DEFINE to set your login details, then:

// Make the connection
$dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Error: Check UN, PW, and host ' . mysql_error());

Connect using mysql_connect, then:

Simple enough….

$result = mysql_query('SELECT * FROM table1');
$num_rows = mysql_num_rows($result);

$num_rows returns the number of records returned by the query….