how do I use Top to get multiple strings of multiple lines form SQL

I wrote this code and I'm not sure how to get it to work. I want to get the 3 last indexes, titles and comments, all in different variables. I want index1, title1 and comment1 to get from the last row in the table, index2, title2 and comment2 to get the second from the bottom row and the index3, title3 and comment3 with the third from the bottom row. I don't really know PHP so please be patient with me :)

Thanks in advance! Itay.

Code:

<?php
    $con = mysqli_connect("MYHOST", "MYUSER", "MYPASS", "MYDB");

    $statement = mysqli_prepare($con, "SELECT TOP 3 * FROM table ORDER BY index DESC);
    mysqli_stmt_bind_param($statement, "iss", $index, $title, $comment);
    mysqli_stmt_execute($statement);

    mysqli_stmt_store_result($statement);
    mysqli_stmt_bind_result($statement, $index, $title, $comment);

    $response = array();
    $response["success"] = false;  

    while(mysqli_stmt_fetch($statement)){
        $response["success"] = true; 
        $response["index1"] = $index; 
        $response["index2"] = 
        $response["index3"] = 
        $response["title1"] = $title;
        $response["title2"] =
        $response["title3"] =
        $response["comment1"] = $comment;
        $response["comment2"] =
        $response["comment3"] =
    }

    echo json_encode($response);
?>

I'm not sure where it fails and what error and exactly what you want to achieve. But I have never seen top being used, that might just be me.. But you should be able to remove top 3 and add LIMIT 3 at the end instead

Yeah it has been a while ago since I used those functions. I can see you need to use the mysqli interface now. Should work if you replace mysql with mysqli

@ItayMeir You need to use either mysqli or PDO to avoid these warnings. mysql_fetch_assoc was deprecated in PHP 5.5.0.

ok, is this code ok now? will it work?

$con = mysqli_connect("host", "user", "pass", "db");

    $query = mysqli_query($con, 'SELECT * FROM `jaralele` ORDER BY `index` DESC LIMIT 3');

    $response = mysqli_fetch_assoc($query);
    $response[success] = false;  

    while($row = mysqli_fetch_array($query)){
        $response["success"] = true; 
        $response["index"] = index; 
        $response["title"] = title;
        $response["comment"] = comment;
    }

    echo json_encode($response);

You could Query the length of the table you want, then just search the table but with index with it decrementing off of length.

ok, can you guide me though it? also, what do I need to change in my code after adding a Query for the code to work and return me those 9 variables at the end?

@ItayMeir Well I actually haven't worked with sql in a while but I have done what you are trying to do in the past.

What I did: Encapsulating this code I made a loop that I could determine the number of loops I wanted. Then I made a variable that had a count, then decremented it because it was the length of the list you are searching for. I queried the index of that count and pulled all those values.

This whole for loop though was calling a simple function that called this query and stored the value in an array. Then to get the contents out all I needed to do was look through the array. This was of course a temporary array so it never had the same values unless you queried the exact same thing.

Sorry I could not give you exact code on how to fix it but at least an idea if it is of any use to you.

thank you! I will try and get the parts I understand to a code and then I hope things will get clearer.

According to this TOP doesn't exist in MySQL - they say to use limit / orderby

ok, I changed it in my code, but I still don't get how to continue this afterwords, I want to get those int and 2 strings from every raw in the 3 and return them in the end.. how do I do it?

Thread

how do I use Top to get multiple strings of multiple lines form SQL

Responses(3)

Recent threads

Search Hashnode

how do I use Top to get multiple strings of multiple lines form SQL

Responses(3)

Recent threads