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!
Layer styles are the secret weapon of many professional designers, offering a quick and effective way to add depth, texture, and dimension to your artwork. Whether you’re
User interaction pseudo-classes in CSS allow developers to apply styles dynamically based on how users interact with elements. They enable changes when a user hovers, clicks,
Image zoom effects are widely used in modern web development to enhance user experience. They provide a detailed view of images without requiring users to open a separate
Photoshop’s “Select and Mask” tool is an incredibly powerful feature that allows users to achieve professional-level background removal. Whether you’re editing
Learn how to master the Rotate Tool in Adobe Illustrator to create stunning designs with ease. This detailed guide will cover the process of duplicating, transforming, and
The Pen Tool in Adobe Illustrator is one of the most powerful tools for creating custom shapes and paths. Mastering it can significantly enhance your design skills, allowing
In modern web design, CSS pseudo-classes play a vital role in enhancing the user experience by applying styles based on the position, interaction, or relationship of elements
Adobe Illustrator is a powerful vector graphics editor that offers a wide range of tools and features to help designers create precise and visually appealing artwork. In this
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.
Introduction In today's digital age, having a well-optimized website is crucial for businesses and individuals alike. A website that loads quickly, is easy to navigate, and provides a seamless user experience can greatly...
Introduction Graphic design is a dynamic and creative field that requires the right tools to bring your visions to life. While there are many high-end paid software options available, not everyone can afford...
HTML5 Semantic Elements have become an important factor in improving SEO rankings due to their ability to provide search engines with more meaningful information about the content of a webpage. These elements go...
JavaScript extended libraries offer a wide range of capabilities for creating interactive and dynamic elements on websites. With these libraries, you can easily incorporate features such as drop-down menus, popups, modals, banner sliders,...
Colors are an incredibly important factor in website design, because they can have a significant effect on user experience and engagement. Colors create visual stimulation, which can influence how users process information. Using...
If you want your website and graphic designs to capture attention, incorporating exceptional fonts is a must! Incorporating elegant typefaces has the capacity to bring your design up a notch, making it more...
In the ever-evolving landscape of digital marketing, Search Engine Optimization (SEO) remains an important strategy for increasing organic traffic and increasing a website's online visibility. However, as search engines continually refine their algorithms...
Adobe Photoshop is a prominent software in image editing and retouching, offers a variety of functionalities. However, it might not be the ideal choice for all users due to several drawbacks. Its interface,...
