PHP is one of the most widely used languages for web development, and MySQL is a powerful open-source relational database management system (RDBMS). Establishing a secure and optimized connection between PHP and MySQL is crucial for developing dynamic and database-driven applications.
In this tutorial, we will cover:
Let’s dive into setting up a proper database connection before moving on to complex queries.
To interact with a MySQL database using PHP, the recommended approach is using MySQLi (MySQL Improved Extension) or PDO (PHP Data Objects).
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "test_db";
$conn = new mysqli($servername, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?><?php
$dsn = "mysql:host=localhost;dbname=test_db;charset=utf8mb4";
$username = "root";
$password = "";
try {
$conn = new PDO($dsn, $username, $password, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]);
echo "Connected successfully";
} catch (PDOException $e) {
die("Connection failed: " . $e->getMessage());
}
?>Now that we have successfully connected to the database, let’s explore 5 advanced MySQL queries that can help you retrieve and manipulate data efficiently.
By default, MySQL searches are case-insensitive because it uses the utf8_general_ci collation. However, you can perform a case-sensitive search by changing the collation or using the BINARY keyword.
$sql = "SELECT * FROM users WHERE username = 'john_doe'";
$result = $conn->query($sql);This will return both "John_Doe" and "john_doe".
$sql = "SELECT * FROM users WHERE BINARY username = 'John_Doe'";
$result = $conn->query($sql);This will only return records where the username is exactly "John_Doe" (case-sensitive).
Sorting data in ascending (ASC) or descending (DESC) order is useful for paginations and ranking systems.
$sql = "SELECT * FROM users ORDER BY created_at DESC LIMIT 5";
$result = $conn->query($sql);Fetches the latest 5 users from the database.
$sql = "SELECT * FROM users ORDER BY created_at ASC LIMIT 3";
$result = $conn->query($sql);Fetches the oldest 3 users based on the created_at field.
The LIKE operator is useful for searching specific patterns within a column.
$sql = "SELECT * FROM users WHERE name LIKE 'Jo%'";
$result = $conn->query($sql);Matches "John", "Joseph", "Jonah", etc.
$sql = "SELECT * FROM users WHERE name LIKE '%son'";
$result = $conn->query($sql);Matches "Jackson", "Emerson", "Davidson", etc.
$sql = "SELECT * FROM users WHERE name LIKE '%mi%'";
$result = $conn->query($sql);Matches "Emily", "Amir", "Tomis", etc.
A common problem is storing multiple values in a comma-separated format inside a column and retrieving them efficiently.
$sql = "SELECT * FROM users WHERE FIND_IN_SET('PHP', skills)";
$result = $conn->query($sql);If skills column contains values like "PHP,JavaScript,HTML", it will return users with PHP as one of their skills.
Sometimes, string values represent numeric data, and you may need to treat them as integers in queries.
$sql = "SELECT * FROM products ORDER BY CAST(price AS UNSIGNED) DESC";
$result = $conn->query($sql);Ensures proper sorting of prices stored as strings (e.g., "100", "50", "200").
To ensure a secure and high-performance PHP-MySQL application, follow these best practices:
$stmt = $conn->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute(['user@example.com']);
$result = $stmt->fetchAll();$email = filter_var($_POST['email'], FILTER_SANITIZE_EMAIL);Index commonly searched columns to improve performance.
CREATE INDEX idx_email ON users(email);$conn->close();Using close() ensures proper resource management and prevents memory leaks in long-running PHP scripts.
In PDO, the connection is automatically closed when the script ends. However, you can manually unset it:
$conn = null;Building a robust PHP-MySQL application requires mastering database connections, writing optimized queries, and implementing security measures. By leveraging advanced queries like case-sensitive searches, sorting with limits, pattern matching, handling comma-separated values, and casting string values as integers, you can build high-performance web applications.
Now it’s your turn! Experiment with these queries in your PHP projects and optimize them further for better performance. Happy coding!
Creating a stunning dispersion effect can elevate your profile picture and make it stand out on social media. This guide will walk you through the process step by step,
Creating visually appealing and professional designs in Adobe Illustrator often hinges on precise alignment. While freehand drawing has its place, achieving pixel-perfect
Typography is a cornerstone of effective web design, and integrating custom Google Fonts into your WordPress site can transform its visual appeal. This comprehensive guide
Double exposure portraits are a captivating artistic effect combining two images into one surreal composition. Using Adobe Photoshop’s layer blending modes and masking
HTML5 introduced a set of semantic elements that provide meaning to the structure of web pages. Unlike non-semantic elements like and , which tell us nothing about their
Ajax (Asynchronous JavaScript and XML) is a powerful web development technique used to load data asynchronously without refreshing the webpage. This helps create fast,
Mixing PHP and HTML efficiently is essential for PHP website development, ensuring clean, readable, and maintainable code. Whether you're working on a dynamic website, a CMS,
WordPress is a powerful content management system, but out of the box, it includes features that can have a performance impact—especially on high-traffic websites or those
Our online CSS beautifier & minifier is the professional choice for clean code. It offers customizable options for formatting, beautification, and minification. Enhance your CSS for optimal results now!
Our online HTML beautifier is the professional choice for cleaning up code. Compress & format HTML for improved structure and readability, with just a few clicks. Start beautifying today!
Design unique CSS gradients with our easy to use, professional generator. Choose colors and customize with advanced features. Lightweight for fast and optimized output!
Use our powerful sort words tool to arrange text by alphabetical order or character length. Many options available to format the output as desired. Clean up your lists now, quickly and easily!
Professional-grade text encoding and decoding is here with our advanced tool. Sophisticated features and capabilities for all your complex data transformation needs. Start now!
Our lightweight CSS filter generator lets you create CSS filters using hex values with multiple advanced options. Get the perfect look for your elements with this powerful & efficient tool!
Extract email IDs from messy text with a single click using our professional tool. Lightweight & efficient, streamlines the process for you, saving time. Try now for effortless email extraction!
Our online Lorem Ipsum generator provides the best solution for your demo content needs. It offers many options, allowing you to create perfect placeholder text with precision. Get started now!
Our Website Development Service offers custom, responsive design, ensuring seamless user experience across devices. From concept to launch, we create dynamic, SEO-friendly sites to elevate your online presence and drive engagement.
Revamp your online presence with our Website Redesign Service! We specialize in creating modern, user-friendly designs that boost engagement and conversion rates. Transform your site today for a sleek, professional look that stands out.
Transform your PSD designs into pixel-perfect, responsive HTML5 code with our professional PSD to HTML5 conversion service. Enjoy clean, SEO-friendly, and cross-browser compatible code tailored to bring your vision to life seamlessly.
Elevate your brand with our professional Logo Design Service. We create unique, memorable logos that capture your business's essence. Stand out in the market with a custom logo designed to leave a lasting impression.
Boost your site's search engine presence! We offer expert SEO solutions, including image and code enhancements, to achieve top positions on Google, Bing, and Yahoo. Let us drive qualified traffic to your business today!
Boost your brand with our Social Media Marketing Service! We specialize in crafting engaging content, driving growth through targeted ads, and maximizing your online presence. Drive growth and connect with your audience effectively.
Experience our WordPress development services, offering tailored solutions for custom themes, plugins, and seamless integrations. Enhance your online presence with our responsive, secure, and success-optimized WordPress solutions.
Enhance your website's visual appeal: We sharpen icons/images, correct RAW files & repair damaged/distorted/overly bright photos. Expect natural-colored, high-resolution JPEGs, complete with photographic effects & upscaling.
In the age of machine learning and smart algorithms, the lines between human creativity and artificial intelligence are blurring—fast. With tools like ChatGPT, Jasper, and Writesonic popping up everywhere, the content landscape is...
Boost Your SEO Rankings: Master UI Design Optimization for Higher Visibility In the digital age, SEO success isn’t just about keywords and backlinks. Google’s algorithms now prioritize user experience (UX), making your website’s User Interface...
What Are JavaScript Libraries? JavaScript libraries are pre-written collections of code that streamline complex web development tasks. They provide reusable functions, components, and utilities to handle everything from DOM manipulation to 3D rendering,...
In the dynamic world of web development, the visual appeal and user experience of a website are paramount. At the heart of this lies CSS (Cascading Style Sheets), the language that dictates how...
In today's digital landscape, a stunning and functional website is no longer a luxury but a necessity. Whether you're an aspiring web designer, a budding entrepreneur, or a seasoned professional looking to sharpen...
AI is fundamentally reshaping website development, automating tedious tasks, enabling hyper-personalization, and accelerating development cycles, which presents both immense opportunities for those who adapt and significant risks for developers who ignore this technological...
Choosing the right server infrastructure is one of the most critical decisions any business or individual with an online presence will make. Get it right, and you have a stable, performant foundation for...
In the fast-paced world of web development, efficiency and productivity are paramount. For PHP developers, the choice of a code editor can significantly impact their workflow, making the difference between a cumbersome coding...
