MySQL
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...
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_ipThe column ‘times’ has not been created when we run the WHERE clause, so we have to use HAVING...
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...
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...
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….
<?php
// Set Vars
$dbname = 'DATABASE_NAME';
$dbhost = 'localhost';
$dbuser = 'USER_NAME';
$dbpass = 'PASS_WORD';
// Connect to DB
$conn = mysql_connect($...
Here’s another way to connect to the database:
// Connect with username, password, etc...
$link = mysqli_connect('localhost', 'userName', 'passWord');
if (!$link){
$error = 'Unable to connect to the database server.';
include 'error.html.php';
exit();
}
// Set Charset
if (!...
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...
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….