{"id":25124,"date":"2024-04-03T01:20:58","date_gmt":"2024-04-03T00:20:58","guid":{"rendered":"https:\/\/nelkodev.com\/blog\/dominando-mysqli_query-interactua-con-mysql-en-php\/"},"modified":"2024-06-03T18:40:13","modified_gmt":"2024-06-03T17:40:13","slug":"dominando-mysqli_query-interactua-con-mysql-en-php","status":"publish","type":"post","link":"https:\/\/nelkodev.com\/en\/blog\/mastering-mysqli_query-interacts-with-mysql-in-php\/","title":{"rendered":"Mastering mysqli_query: Interact with MySQL in PHP"},"content":{"rendered":"<p>PHP and MySQL form a powerful duo for web development, providing a robust platform for database management. Using the function <code>mysqli_query<\/code> in PHP allows operations on MySQL databases, from simple queries to more complex transactions. <\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_80 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #ffffff;color:#ffffff\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewbox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #ffffff;color:#ffffff\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewbox=\"0 0 24 24\" version=\"1.2\" baseprofile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-mysqli_query-interacts-with-mysql-in-php\/#Comprendiendo_la_funcion_mysqli_query\" >Understanding the mysqli_query function<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-mysqli_query-interacts-with-mysql-in-php\/#Conectandose_a_la_Base_de_Datos\" >Connecting to the Database<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-mysqli_query-interacts-with-mysql-in-php\/#Realizando_una_Consulta_Simple\" >Performing a Simple Query<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-mysqli_query-interacts-with-mysql-in-php\/#Insertando_Datos_en_la_Base_de_Datos\" >Inserting Data into the Database<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-mysqli_query-interacts-with-mysql-in-php\/#Actualizando_Registros_Existentes\" >Updating Existing Records<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-mysqli_query-interacts-with-mysql-in-php\/#Eliminando_Registros_de_la_Base_de_Datos\" >Deleting Records from the Database<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-mysqli_query-interacts-with-mysql-in-php\/#Transacciones_con_mysqli_query\" >Transactions with mysqli_query<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-mysqli_query-interacts-with-mysql-in-php\/#Conclusion\" >Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Comprendiendo_la_funcion_mysqli_query\"><\/span>Understanding the mysqli_query function<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Before we dive into practical examples, let&#039;s understand what it is and how it works <code>mysqli_query<\/code>. This PHP function is essential for sending queries to our MySQL database. The basic structure is as follows:<\/p>\n<pre><code class=\"&quot;language-php&quot;\">mysqli_query($connection, $query);<\/code><\/pre>\n<p>Where <code>$connection<\/code> is an object that represents an open connection to a MySQL database, and <code>$query<\/code> is the string that contains the SQL query to be executed.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Conectandose_a_la_Base_de_Datos\"><\/span>Connecting to the Database<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To perform any operation on the database, we first need to establish a connection. Suppose we have the following database credentials:<\/p>\n<ul>\n<li>Host: localhost<\/li>\n<li>User: root<\/li>\n<li>Password: password<\/li>\n<li>Database: my_database<\/li>\n<\/ul>\n<p>The following code snippet shows how to initiate a connection:<\/p>\n<pre><code class=\"&quot;language-php&quot;\">$connection = mysqli_connect(&quot;localhost&quot;, &quot;root&quot;, &quot;password&quot;, &quot;my_database&quot;); if (!$connection) { die(&quot;The connection has failed: &quot; . mysqli_connect_error()); } echo &quot;Connection successful&quot;;<\/code><\/pre>\n<p>Make sure you select your database correctly and handle potential connection errors.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Realizando_una_Consulta_Simple\"><\/span>Performing a Simple Query<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>With the connection already established, let&#039;s see how to perform a simple query to get data from a table called <code>users<\/code>:<\/p>\n<pre><code class=\"&quot;language-php&quot;\">$query = &amp;quot;SELECT * FROM users&amp;quot;; $result = mysqli_query($connection, $query); if (!$result) { die(&amp;quot;Query error: &amp;quot; . mysqli_error($connection)); } while ($row = mysqli_fetch_assoc($result)) { echo &amp;quot;Name: &amp;quot; . $row[&amp;quot;name&amp;quot;] . &amp;quot; - Email: &amp;quot; . $row[&amp;quot;email&amp;quot;] . &amp;quot;&lt;br&gt;&amp;quot;; } mysqli_free_result($result);<\/code><\/pre>\n<p>In the loop <code>while<\/code>, <code>mysqli_fetch_assoc<\/code> obtains each row of the result as an associative array and we store it in <code>$row<\/code>. This allows us to access each column in the row by name.<\/p>\n<p>Don&#039;t forget to release the result with <code>mysqli_free_result<\/code> to free the memory associated with the query result.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Insertando_Datos_en_la_Base_de_Datos\"><\/span>Inserting Data into the Database<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Next, let&#039;s see how we could insert a new user inside our table <code>users<\/code>:<\/p>\n<pre><code class=\"&quot;language-php&quot;\">$name = &amp;quot;John Perez&amp;quot;; $email = &amp;quot;JohnPerez@example.com&amp;quot;; $query = &amp;quot;INSERT INTO users (name, email) VALUES (&amp;#039;$name&amp;#039;, &amp;#039;$email&amp;#039;)&amp;quot;; if (mysqli_query($connection, $query)) { echo &amp;quot;New record created successfully&amp;quot;; } else { echo &amp;quot;Error: &amp;quot; . $query . &amp;quot;&lt;br&gt;&amp;quot; . mysqli_error($connection); }<\/code><\/pre>\n<p>This code snippet performs an insert into the database. Always remember to validate and sanitize input to avoid SQL injection attacks.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Actualizando_Registros_Existentes\"><\/span>Updating Existing Records<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If we want to update information for a specific user, we use the instruction <code>UPDATE<\/code>. For example, to change a user&#039;s email:<\/p>\n<pre><code class=\"&quot;language-php&quot;\">$newEmail = &quot;newemail@example.com&quot;; 1TP4UserTid = 3; $query = &quot;UPDATE users SET email = &#039;$newEmail&#039; WHERE id = $idUsuario&quot;; if (mysqli_query($connection, $query)) { echo &quot;Record updated successfully&quot;; } else { echo &quot;Error updating record: &quot; . mysqli_error($connection); }<\/code><\/pre>\n<p>Please note that you must specify a search criteria in the <code>WHERE<\/code> to not update more records than desired.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Eliminando_Registros_de_la_Base_de_Datos\"><\/span>Deleting Records from the Database<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If you want to delete a record, you would do it as follows:<\/p>\n<pre><code class=\"&quot;language-php&quot;\">1TP4UserTid = 4; $query = &quot;DELETE FROM users WHERE id = $idUsuario&quot;; if (mysqli_query($connection, $query)) { echo &quot;Record deleted successfully&quot;; } else { echo &quot;Error deleting record: &quot; . mysqli_error($connection); }<\/code><\/pre>\n<p>As with updating, it is crucial to make sure you properly specify the <code>WHERE<\/code> to avoid deleting more data than intended.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Transacciones_con_mysqli_query\"><\/span>Transactions with mysqli_query<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To carry out operations that involve multiple steps and require all or none of them to be done, such as a banking transaction, we use transactions. This is how you could do it with <code>mysqli_query<\/code>:<\/p>\n<pre><code class=\"&quot;language-php&quot;\">mysqli_begin_transaction($connection); try { mysqli_query($connection, &quot;UPDATE accounts SET balance = balance - 100 WHERE id = 1&quot;); mysqli_query($connection, &quot;UPDATE accounts SET balance = balance + 100 WHERE id = 2&quot;); mysqli_commit($connection); echo &quot;Transaction completed successfully&quot;; } catch (Exception $e) { mysqli_rollback($connection); echo &quot;An error occurred. Transaction reversed&quot;; }<\/code><\/pre>\n<p>In this case, we are simulating a transfer of funds from one account to another. If something goes wrong, the <code>catch<\/code> catches the exception and all operations are rolled back to maintain data integrity.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The use of <code>mysqli_query<\/code> It is essential in programming with PHP and MySQL. From simple to more complex tasks, this feature is crucial for direct interaction with the database. By mastering this brand of CRUD (Create, Read, Update, Delete) operations, you become a more competent developer capable of creating dynamic and secure web applications.<\/p>\n<p>Don&#039;t miss our articles on development and technology, visit <a href=\"https:\/\/nelkodev.com\/en\/\">my blog<\/a>. And if you have any questions or need assistance, do not hesitate to contact us through <a href=\"https:\/\/nelkodev.com\/en\/contact\/\">my contact page<\/a>. Keep coding and learning more every day!<\/p>","protected":false},"excerpt":{"rendered":"<p>PHP y MySQL forman una dupla poderosa para el desarrollo web, proporcionando una plataforma robusta para la gesti\u00f3n de bases de datos. El uso de la funci\u00f3n mysqli_query en PHP permite operaciones sobre bases de datos MySQL, desde consultas simples hasta transacciones m\u00e1s complejas. Comprendiendo la funci\u00f3n mysqli_query Antes de sumergirnos en ejemplos pr\u00e1cticos, entendamos [&hellip;]<\/p>","protected":false},"author":1,"featured_media":25125,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[420,2185,2206],"tags":[205,90,2189,1289,869,352,1716,15],"class_list":["post-25124","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-curso-mysql","category-php","tag-blog","tag-con","tag-curso","tag-dominando","tag-interactua","tag-mysql","tag-mysqli_query","tag-php"],"_links":{"self":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/25124","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/comments?post=25124"}],"version-history":[{"count":0,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/25124\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media\/25125"}],"wp:attachment":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media?parent=25124"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/categories?post=25124"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/tags?post=25124"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}