How to Use MySQLi_Query() in PHP

How to use mysqli_query() in PHP?

I have to admit, mysqli_query() manual entry doesn't contain a clean example on how to fetch multiple rows. May be it's because the routine is so routine, known to PHP folks for decades:

$result = $link->query("DESCRIBE students");
while ($row = $result->fetch_assoc()) {
    // to print all columns automatically:
    foreach ($row as $value) {
        echo "<td>$value</td>";
        // OR to print each column separately:
        echo "<td>",$row['Field'],"</td><td>",$row['Type'],"</td>\n";
    }
}

In case you want to print the column titles, you have to select your data into a nested array first and then use keys of the first row:

// getting all the rows from the query
// note that handy feature of OOP syntax
$data = $link->query("DESC students")->fetch_all(MYSQLI_ASSOC);
// getting keys from the first row
$header = array_keys(reset($data));
// printing them
foreach ($header as $value) {
    echo "<td>$value</td>";
}
// finally printing the data
foreach ($data as $row) {
    foreach ($row as $value) {
        echo "<td>$value</td>";
    }
}

Some hosts may have no support for the fetch_all() function. In such a case, fill the $data array the usual way:

$data = [];
$result = $link->query("DESC students");
while ($row = $result->fetch_assoc())
{
    $data[] = $row;
}

Two important notes I have to add.

1. You have to configure mysqli to throw errors automatically instead of checking them for each mysqli statement manually. To do so, add this line before mysqli_connect():

    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
   

2. The most important note: unlike mysql_query(), mysqli_query() has a very limited use. You may use this function only if no variables are going to be used in the query. If any PHP variable is going to be used, you should never use mysqli_query(), but always stick to prepared statements, like this:

    $stmt = $mysqli->prepare("SELECT * FROM students WHERE class=?");
    $stmt->bind_param('i', $class);
    $stmt->execute();
    $data = $stmt->get_result()->fetch_all();
   

It's a bit wordy, I have to admit. In order to reduce the amount of code you can either use PDO or adopt a simple helper function to do all the prepare/bind/execute business inside:

$sql = "SELECT * FROM students WHERE class=?";
$data = prepared_select($mysqli, $sql, [$class])->fetch_all();

Converting from mysql _query to mysqli_query

mysqli_query needs two parameters:

• Your database connection
• A query

That will result in this:

$result = mysqli_query($con, "SELECT image, caption FROM tbllinkcat, tblimages WHERE tbllinkcat.catid = tblimages.catid AND tbllinkcat.catid=1;");   

---

You also have some weird looking quotation marks in your connect function, correct them:

$con = mysqli_connect("localhost", "my_user", "my_password", "my_db");

To fetch the array, you would use:

while($row = mysqli_fetch_assoc($result))
{
    echo $row['image'];
    echo "<br />";
    echo $row['caption'];
    echo "<br />";
}

And finally, to close the connection:

mysqli_close($con);

---

Note: Just so you know, when using queries with user input, please use prepared statements and bind_param. This will prevent SQL injection attacks. I will show and example below.

$stmt = $db->prepare("SELECT * FROM users WHERE username = ?");

$username = $_POST['username'];

$stmt->bind_param('s', $username);

$result = $stmt->execute();

mysqli_query works in phpmyadmin but not in php

you can't print the result from mysqli_query, it is mysqli_resource and for dumping the error you need to change mysql_error() to mysqli_error()

$username = "bob";
$db = mysqli_connect("localhost", "username", "password", "user_data");
$sql1 = "select id from user_information where username='$username'";
$result = mysqli_query($db, $sql1) or die(mysqli_error());
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { 
    echo $row['id'].'<br>'; 
} 

How to use the for loop, the IN operator in same mysqli_query in PHP

You can use implode().

Usage: string implode ( string $glue , array $pieces )

mysqli_query($this->conn, "SELECT * FROM `tale_name` WHERE `id` in (". implode(',', $id_array) .")");

How to write mysqli query in php function?

You can add in your $db using the global identifier, and then as Florian Lefèvre has said add the link to your mysqli_query().

function pagination($query, $per_page = 10,$page = 1, $url = '?')
{
    global $db;

    $query = "SELECT COUNT(*) as `num` FROM $query";
    $row = mysqli_fetch_array(mysqli_query($db, $query));
}

What is the output of mysqli_query?

I believe it's a fair question. That's a very good intention to understand how things work too.

However, what does a thing do and what does it output are two different things. mysqli_query is not intended to output anything. So you just bit a bit too deep and went astray.

The answer to the question "what does mysqli query do" is fairly simple: it runs your query against a database.

What does it return is a different thing. For SELECT queries it returns an object of mysqli_result class. Hence such output.

This object is needed to make use of the selected data. You can use this object's methods and properties to get the information about the returned data.

• There are several properties though you'll never need them. Properties, however, describe the returned resultset, not the whole table. If you select only one column, the field count will be 1, not 5. This number is rather useless though as you can always count the number of fields in the fetched row. The same goes for the number of rows.
• of methods you'd likely use only three - fetch_assoc() or fetch_all() most of time, depends on the number of rows expected, and fetch_row() occasionally when fetch_assoc() is not convenient.

An important note.

You actually seldom use mysqli_query(), because most of time we run SQL queries using PHP variables. And once even a single variable is going to be used in the query, another set of functions must be used instead of mysql_query(). Here is my article that explains the process, Mysqli SELECT query with prepared statements

Hope it's clear now.

using a variable in a mysqli_query?

Then $_POST['Beam Number'] is empty.

Also you should never put any user input such as post of get data directly into a query. Google sql injection and how to prevent it.

Try debugging $_POST['Beam Number']. You should see that it is empty and then you have to find out why it's empty.

Edit If there is a value, then debug out the query string by first placing it into a variable.

error_log($beam_num);

$query = "SELECT cost_ft FROM Beams WHERE number = '$beam_num'";

error_log($query);

$sql_beam = mysqli_query($link,$query);

Then if you are still having trouble, please supply the values logged.

---

Related Topics