A good practice of increasing SQL efficiency is to reduce the number of separate queries as much as possible. In a situation where you want a row updated if it exists and inserted if it doesn’t may take a newbie MySQL developer to write 2 independent queries, namely: first, check if the row exists with …
MySQL: Insert if doesn’t exist otherwise update the existing row Read More »
It’s hard to describe this scenario precisely with limited title length. Basically, this is when you want to dedicate a field in ‘category‘ table to store the number of articles or posts in ‘post‘ table that fall into this category. Previously, you had both ‘category’ table to store post categories and ‘post’ table to store …
MySQL: Add Statistics Column for the Number Count of Records from Another Table Read More »
Suppose you have a ultra large table of 1 million records and you need to page all those records across 10,000 web pages with 100 records on each page. For page 2, the most straightforward approach is undoubtedly: SELECT * FROM table LIMIT 100, 100 However, this query would introduce performance issues when the starting …
Easier than it appears. Just create a new table and import all those rows and records random selected and ordered by the RAND() SQL function: CREATE TABLE new_table SELECT * FROM old_table ORDER BY RAND() Of if you have created a table identical to the structure of the old one, use INSERT INTO instead: INSERT …
SQL: Randomly Shuffle Rows or Records – Reorder them in a random order Read More »
With PHP and MySQL, you may fulfill the same task by selecting all the rows into an array and choosing a random index with php function array_rand or mt_rand (better) but that would be all big overhead to do as you have to first read in the entire table. The correct way is to get …
To successfully run a query with text data containing single quotes ‘ as well as other SQL reserved punctuations, AND to prevent SQL injections, you will always want to escape the text values before using them in a SQL query. In PHP 4.0, we are stuck with mysql_real_escape_string. With PHP 5.0, mysqli:prepare and mysqli::real_escape_string are …
SQL injection is a typical code injection attack that exploits weaknesses of application in the database layer. SQL injection vulnerability is created when one scripting or programming language is embedded in or used as input in another with failure to verify the legality or filter for potential dangerous codes. SQL injections are possible when input …
As Web becomes one of the most fundamental means of communication and information delivery nowadays, and as its usage reaches population level in regards to that when the televisions prevailed, the protection of it has never been so critical. SANS institute in association with US government and various other weighty contributors, most of whom IT …
Top 25 Most Dangerous Web Programming Errors, Loopholes and Bad Habits Read More »
With database development, a perfect data structure paradigm is that data records should all be uniquely distinguishable from each other. Therefore when you are designing and creating data fields or columns for an entity, you must always have an identifying column or field for each and every of the records stored in a table, enabling …
Create SQL Auto Increment Column (ID: Primary Key) in Table Read More »
Scratching your head for a mystic error in your SQL query? No matter how you look into it, it just seems right and should by the God’s name work as you have wished. But it doesn’t and keeps pumping out annoying SQL syntax errors. Don’t panic, I have a few tips on this that might …
Update: Here is a more advanced use of INSERT INTO … SELECT … to combine multiple tables into one. While CREATE TABLE … SELECT … mix and mingle records by column from other tables into a table that’s created on the fly, INSERT INTO … SELECT … insert those generated or combined records into an …
CREATE TABLE … SELECT comes rather useful when you need to generate reports from existing tables or do a mixture. The title just shows the basic usage of it, for instance, to create a new table xxx from 2 columns in yyy, namely col1, col2. After executing SQL: CREATE TABLE xxx SELECT col1, col2 FROM …
SQL: CREATE TABLE … SELECT … to generate new table from existing tables Read More »
Yes you can log into phpmyadmin, backup your database by simply using Export and recover it by using Import. However all lamp servers come with a max upload file size, limited by the smaller of upload_max_filesize and post_max_size while also restricted by php.ini configurations such as memory_limit and max_input_time. What if you’ve got a huge …
Backup and recover a MySQL database under command line Read More »
The 2 tables are built with an identical data structure. Say you have records spread in both tables but neither of them are complete because both contain unique records. You need to consolidate the 2 tables into one with all unique records from each table. NOT EXISTS clause is just the way to do it …
Using SQL to Find records existing in one table but not in another Read More »
