Skip to main content

MySQL

Replace in MySQL
Replace in 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';
SELECT inside a SELECT
SELECT inside a SELECT
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...
#1054 – Unknown column in 'where clause'
#1054 – Unknown column in 'where clause'
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...
Export to CSV file
Export to CSV file
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...
Full Text Search in MySQL Database
Full Text Search in MySQL Database
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!
mysql_affected_rows()
mysql_affected_rows()
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...
Backup Database with PHP
Backup Database with PHP
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($...
Connect To Database With mysqli_connect
Connect To Database With mysqli_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 (!...
Connect To Database With mysql_connect
Connect To Database With mysql_connect
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...
Count Rows / Records
Count Rows / Records
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….

Pages

  • 1
  • 2
  • next ›
  • last »
 
 
Jon Moore

Search form

Jon Moore

Tips 'n' Snips

 
  • Home
  • General
  • Drupal
    • Drupal 6
    • Drupal 7
    • Module Building
  • HTML & CSS
  • Javascript
  • jQuery
  • PHP
    • CodeIgniter
    • Handy Functions
    • MySQL
  • WordPress
  • About

BBC Technology News

  • Huawei launches new legal challenge against US ban
  • Lotus Evija: The £2.2m electric hypercar
  • Cave diver tells court Elon Musk tweets 'humiliated' him
  • Mars rover aims to grab a piece of history
  • BolaWrap: LA police to use 'Batman-style' device to snare suspects
More

Sitepoint

  • The Real Future of Remote Work is Asynchronous
  • 7 Ways Developers Can Contribute to Climate Action
  • How to Divert Traffic Using IP2Location in a Next.js Website
  • 10 Zsh Tips & Tricks: Configuration, Customization & Usage
  • Building a Habit Tracker with Prisma, Chakra UI, and React
More

Heart Internet

  • 15 web design books of 2019 that you should read
  • 12 podcasts for every creative person’s commute
  • Ten TED Talks every designer and web creative should watch before 2019 is over
  • The rise of the robots: Will AI take over graphic design?
  • 34 WordPress tutorials for modern web development
More
 

Backend Coders

  • PHP
  • Handy PHP Functions
  • PHP MySQL

Frontend Coders

  • HTML & CSS
  • jQuery
  • Javascript

CMS/CMF Systems

  • Drupal
  • Drupal 6 Specific
  • Drupal 7 Specific
  • Drupal Module Building
  • WordPress

About

  • Home
  • About
 

© Jon Moore 2019

All stock images are from www.istockphoto.com