Mysqli Query Doesn't Work Twice

Mysqli query doesn't work twice

You are doing it wrong way.

Never mix your data manipulation code with presentation code.

First, get the posts into array:

require_once 'database.inc.php';
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = mysqli_connect($host, $username, $password, $database);

$sql = "SELECT variable_name, post_name, post_date, post_display 
        FROM blog_posts ORDER BY id DESC LIMIT 5"
$result = mysqli_query($mysqli, $sql);
$data = array();
while ($row = mysqli_fetch_assoc($result)) {
    $data[] = $row;
}

and then use this $data array to display posts any times you need, simply using foreach()

Why can't I run two mysqli queries? The second one fails

It is possible with mysqli_multi_query().

Example:

<?php

$mysqli = new mysqli($host, $user, $password, $database);

// create string of queries separated by ;
$query  = "INSERT INTO images (project_id, user_id, image_name, date_created, link_to_file, link_to_thumbnail, given_name) VALUES ('$project_id', '$user_id', '$image_name', '$date_created', '$link_to_file', '$thumbnail', '$ImageName');";
$query .= "INSERT INTO images_history (project_id, user_id, image_name, date_created, link_to_file, link_to_thumbnail, given_name, day, month, year) VALUES ('$project_id', '$user_id', '$image_name', '$date_created', '$link_to_file', '$thumbnail', '$ImageName', '$day', '$month', '$year');";

// execute query - $result is false if the first query failed
$result = mysqli_multi_query($mysqli, $query);

if ($result) {
    do {
        // grab the result of the next query
        if (($result = mysqli_store_result($mysqli)) === false && mysqli_error($mysqli) != '') {
            echo "Query failed: " . mysqli_error($mysqli);
        }
    } while (mysqli_more_results($mysqli) && mysqli_next_result($mysqli)); // while there are more results
} else {
    echo "First query failed..." . mysqli_error($mysqli);
}

The key is that you must use mysqli_multi_query if you want to execute more than one query in a single call. For security reasons, mysqli_query will not execute multiple queries to prevent SQL injections.

Also keep in mind the behavior of mysqli_store_result. It returns FALSE if the query has no result set (which INSERT queries do not) so you must also check mysqli_error to see that it returns an empty string meaning the INSERT was successful.

See:

mysqli_multi_query

mysqli_more_results

mysqli_next_result

mysqli_store_result

I can't execute SQL sentence twice

You can't use the same name for multiple queries.

Every name should be unique for multiple queries on the same page.

For example: $stmt for the first as it is, and for the second try a new name Like $stmt1 or $stmt_second, then it works perfectly.

For first query leave it as it is:

$stmt = $db->prepare('SELECT h.title, h.place, h.introduction FROM hotels h WHERE h.id=? LIMIT 1');

And for the second query change it to:

$stmt_second = $db->prepare('SELECT name FROM amenities');

Iterating twice over same mysql query in PHP

With one statment create three array for id , post and parent then add each row data to these arrays and use to for loop to create the display result somthing like this code may be work.

$id=array();
$post=array();
$parent=array();
while ($row = mysqli_fetch_assoc($result)) {
$id[]=$row["id"];
$post[]=$row["post"];
$parent[]=$row["parent"];
}
for($i=0;$i<count($id);$i++){
if($parent[$i]==0){
echo $parent[$i];
for($j=0;$j<count($parent);$j++){
if($parent[$j]==$id[$i]){
echo $post[$j];
}
}
}

How can I use mysqli_fetch_array() twice?

You don't need the while loop and you don't need to use mysqli_fetch_array() at all!

You can simply loop on the mysqli_result object itself many times. It implements Traversable interface that allows it to be used in foreach.

//Top row
foreach($db_res as $row) {
    echo "<td>". $row['Title'] . "</td>";
}

//leftmost column
foreach($db_res as $row) {
    echo "<tr>";
    echo "<td>". $row['Title'] . "</td>";
    .....
    echo "</tr>";
}

However, you should separate your DB logic from your display logic and to achieve this it is best to use fetch_all(MYSQLI_ASSOC) in your DB logic to retrieve all records into an array.

If you fetch all the data into an array, you can loop that array as many times as you want.

$data = $db_res->fetch_all(MYSQLI_ASSOC);

foreach($data as $row) {
    // logic here...
}

mysqli query returns true but fetch_assoc doesn't work

I found the solution, the problem is that the real database used greek characters in the records and the database had to be set to utf8-bin, after doing so, it worked.

---

Related Topics