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
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
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: