select statement always return the last inserted row in php mysql

Question

When I wrote the select statement it always return the last inserted row in the database. What is the problem, and how can I fix it?

Important NOTE: A friend of mine took the same code and it worked for her properly!

    if (isset($_GET["name"])) {
    $pid = $_GET['name'];

    // get a product from products table
    //)or die(mysql_error()
    $result = mysql_query("SELECT * FROM food WHERE name = $pid");
    //mysql_query($result,$con);
    if (!empty($result)) {
        // check for empty result
        if (mysql_num_rows($result) > 0) {


            $result = mysql_fetch_array($result);

            $product = array();
            $product["name"] = $result["name"];
            $product["unit"] = $result["unit"];
            $product["calory"] = $result["calory"];
            $product["carbohydrate"] = $result["carbohydrate"];
            $product["category"] = $result["category"];


            // success
            $response["success"] = 1;

            // user node
            $response["product"] = array();

            array_push($response["product"], $product);

            // echoing JSON response
            echo json_encode($response);
        } else {
            // no product found
            $response["success"] = 0;
            $response["message"] = "No item found";

            // echo no users JSON
            echo json_encode($response);
        }
    } else {
        // no product found
        $response["success"] = 0;
        $response["message"] = "No product found";

        // echo no users JSON
        echo json_encode($response);
    } */
} else {
    // required field is missing
    $response["success"] = 0;
    $response["message"] = "Required field(s) is missing";

    // echoing JSON response
    echo json_encode($response);

you may also want to fix the sql injection. `yoursite.com?name=bla OR 1=1` is going to return all rows in your db — Lawrence Cherone, Mar 20 '13 at 11:58

score 0 · Answer 1 · edited Mar 20 '13 at 11:59

0

This is because mysql_fetch_array is not in a loop, place it into the while loop and check.

edited Mar 20 '13 at 11:59

answered Mar 20 '13 at 11:55

Bhumi Shah

9,323
7
63
104

score 0 · Accepted Answer · answered Mar 20 '13 at 11:57

        if (mysql_num_rows($result) > 0) {
        $result = mysql_fetch_array($result);

        $product = array();
        $product["name"] = $result["name"];
        $product["unit"] = $result["unit"];
        $product["calory"] = $result["calory"];
        $product["carbohydrate"] = $result["carbohydrate"];
        $product["category"] = $result["category"];


        // success
        $response["success"] = 1;

        // user node
        $response["product"] = array();

        array_push($response["product"], $product);

        // echoing JSON response
        echo json_encode($response);
    }

replace this with

while(mysql_num_rows($result) > 0 && ($result = mysql_fetch_array($result))) {

        $product = array();
        $product["name"] = $result["name"];
        $product["unit"] = $result["unit"];
        $product["calory"] = $result["calory"];
        $product["carbohydrate"] = $result["carbohydrate"];
        $product["category"] = $result["category"];


        // success
        $response["success"] = 1;

        // user node
        $response["product"] = array();

        array_push($response["product"], $product);

        // echoing JSON response
        echo json_encode($response);
    }

the result is array and you are not looping through it so it givesonly one element in the array

your code worked perfectly , you are a genious !! thank you so much — Bashayer Abdullah, Mar 20 '13 at 12:15

score 0 · Answer 3 · answered Mar 20 '13 at 12:05

Simply putting everything in a loop will not fix this. The code your gave will give the same result.. the last one.

$product needs to be declared BEFORE the loop otherwise, it will always be reset. Also, in order to populate the $product array without overwriting you will need to make it multidimensional

$product[]['name'] = $result["name"];

The ideal way of storing the products would be like this.. in my opinion.

$product = array();
while($result = mysql_fetch_array($result)) {    
        $product[$result['id']] = $result;

select statement always return the last inserted row in php mysql

3 Answers3

Linked