How to fetch particular field value multiple time.. My output come from two different table through INNER JOIN

Question

I want output from two table..
One table for Product_detail and second table for Product_image

In product_image table all images store separately for particular product_id

I want output from both table :
For particular product_id all detail from Product_detail table and all images from Product_image table

My Code :

<?php
error_reporting(0);

$response = array();
$response1 = array();

require_once __DIR__ . '/db_Connect.php';


// check for post data
if (isset($_GET["pro_id"])) {

    $pid = $_GET['pro_id'];

    // get a product from products table
    //$result = mysql_query("SELECT * FROM product_list WHERE pro_id = '".$pro_id."'");
    $q="SELECT product_list.pro_id,product_list.product_name,product_list.product_desc,product_images.image
    FROM product_images
    INNER JOIN product_list ON product_list.pro_id = product_images.pro_id
    WHERE product_list.pro_id =  '$pid'";

    $res=mysql_query($q);


    if (!empty($res)) {

            // user node
            $response["product"] = array();
            $result = mysql_fetch_assoc($res);
            //var_dump($result);
            $count=count($result['image']);
            $count++;

            var_dump($count);

                $product=array();
                $product['pro_id']=$result['pro_id'];
         //$product['cat_id']=$result['cat_id'];  
                $product['product_name']=$result['product_name'];
                $product['product_desc']=$result['product_desc'];

                //$product['image']="http://friendzfashionz.com/pandora/admin/".$result['image'];

                $clr=array();
                for($i=0;$i<$count;$i++)
                {

                    $clr[$i]="http://friendzfashionz.com/pandora/admin/".$result['image'];
                    //var_dump($clr[$i]);
                    array_push($response1["images"], $clr[$i]);

                }
                $product['image']=$clr;


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

            $response["success"] = 1;
            echo json_encode($response);

    } else {
        // no product found
        $response["success"] = 0;
        $response["message"] = "No user 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);
}
?>

Output of this code is :

int(2) {"product":[{"pro_id":"13","product_name":"jeans","product_desc":"Monkey wash ","image":["http:\/\/friendzfashionz.com\/pandora\/admin\/Sub_uploads\/download (1).jpg","http:\/\/friendzfashionz.com\/pandora\/admin\/Sub_uploads\/download (1).jpg"]}],"success":1}  

I have two different image of pro_id in product_image table
I want product_details one time and all product_image of that pro_id..
But the problem is it give me first image two times..

Please help to solve this problem...

Product_detail table:

product_image table:

Answer 1

The problem is that you are getting two rows returned but then only make one call to

$result = mysql_fetch_assoc($res);

so you only process the first row.

Instead, use GROUP BY on the product_list values and GROUP_CONCAT() on the images.

This will return a single row for the product with a comma-separated list of images.

You can then get an array of the images separately with EXPLODE().

e.g.

SELECT pl.pro_id, pl.product_name, pl.product_desc, 
   GROUP_CONCAT(pi.image) AS 'images'
FROM product_images pi
INNER JOIN product_list pl ON (pl.pro_id = pi.pro_id)
WHERE pl.pro_id =  ?
GROUP BY pl.pro_id, pl.product_name, pl.product_desc;

Also, you are still using mysql_query() which was deprecated in PHP 5.5 and removed in PHP 7. You SHOULD be using paramaterized queries with either PDO or mysqli or your app will break when you upgrade PHP and you leave yourself wide open to SQL injection in the meantime.

https://secure.php.net/manual/en/class.pdo.php

https://secure.php.net/manual/en/class.mysqli.php

Answer 2

The problem with your code resides in the use of mysql_fetch_assoc function. By directly calling it, e.g. without including it in a while loop (like at the end of Example #1), you are fetching only one record from the database. Therefore, the count($result['image']) statement will return the value 1. Though you are expecting multiple records: one for each image in the product_images table.

Note that you are using the mysql extension. Though it has been removed as of PHP 7.0.0! Use mysqli or PDO instead. I adapted your code to use mysqli - with some changes regarding building the $response array, too.

You can read this article about the use of mysqli prepared statements, which are used to avoid SQL injection.

At last, for proper error and exception handling you should read this article. Whereas this article is focused on mysqli.

---

index.php

<?php

require __DIR__ . '/db_Connect.php';

// Array to hold the final response.
$response = array();

// Validate the product id.
if (!isset($_GET['pro_id']) || empty($_GET['pro_id']) || !is_numeric($_GET['pro_id'])) {
    $response['success'] = 0;
    $response['message'] = 'No product id provided.';
} else {
    // Read the product id.
    $productId = $_GET['pro_id'];

    /*
     * The SQL statement to be prepared. Notice the so-called markers, 
     * e.g. the "?" signs. They will be replaced later with the 
     * corresponding values when using mysqli_stmt::bind_param.
     * 
     * @link http://php.net/manual/en/mysqli.prepare.php
     */
    $sql = 'SELECT 
                pl.pro_id,
                pl.product_name,
                pl.product_desc,
                pi.image
            FROM product_images AS pi
            INNER JOIN product_list AS pl ON pi.pro_id = pl.pro_id
            WHERE pi.pro_id = ?';

    /*
     * Prepare the SQL statement for execution - ONLY ONCE.
     * 
     * @link http://php.net/manual/en/mysqli.prepare.php
     */
    $statement = $connection->prepare($sql);

    /*
     * Bind variables for the parameter markers (?) in the 
     * SQL statement that was passed to prepare(). The first 
     * argument of bind_param() is a string that contains one 
     * or more characters which specify the types for the 
     * corresponding bind variables.
     * 
     * @link http://php.net/manual/en/mysqli-stmt.bind-param.php
     */
    $statement->bind_param('i', $productId);

    /*
     * Execute the prepared SQL statement.
     * When executed any parameter markers which exist will 
     * automatically be replaced with the appropriate data.
     * 
     * @link http://php.net/manual/en/mysqli-stmt.execute.php
     */
    $statement->execute();

    /*
     * Get the result set from the prepared statement.
     * 
     * NOTA BENE:
     * Available only with mysqlnd ("MySQL Native Driver")! If this 
     * is not installed, then uncomment "extension=php_mysqli_mysqlnd.dll" in 
     * PHP config file (php.ini) and restart web server (I assume Apache) and 
     * mysql service. Or use the following functions instead:
     * mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch.
     * 
     * @link http://php.net/manual/en/mysqli-stmt.get-result.php
     * @link https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
     */
    $result = $statement->get_result();

    /*
     * Fetch data and save it into an array.
     * 
     * @link http://php.net/manual/en/mysqli-result.fetch-all.php
     */
    $productRecords = $result->fetch_all(MYSQLI_ASSOC);

    /*
     * Free the memory associated with the result. You should 
     * always free your result when it is not needed anymore.
     * 
     * @link http://php.net/manual/en/mysqli-result.free.php
     */
    $result->close();

    /*
     * Close the prepared statement. It also deallocates the statement handle.
     * If the statement has pending or unread results, it cancels them 
     * so that the next query can be executed.
     * 
     * @link http://php.net/manual/en/mysqli-stmt.close.php
     */
    $statement->close();

    /*
     * Close the previously opened database connection.
     * 
     * @link http://php.net/manual/en/mysqli.close.php
     */
    $connection->close();

    if (!$productRecords) { // No product records found.
        $response['success'] = 0;
        $response['message'] = 'No product data found.';
    } else {
        // Array to hold the final product data.
        $product = array();

        foreach ($productRecords as $productRecord) {
            $productId = $productRecord['pro_id'];
            $productName = $productRecord['product_name'];
            $productDescription = $productRecord['product_desc'];
            $productImage = $productRecord['image'];

            if (!$product) { // Array is empty
                $product[0] = array(
                    'pro_id' => $productId,
                    'product_name' => $productName,
                    'product_desc' => $productDescription,
                );
            }

            $product[0]['image'][] = 'http://friendzfashionz.com/pandora/admin/' . $productImage;
        }

        $response['success'] = 1;
        $response['product'] = $product;
    }
}

echo json_encode($response);

db_Connect.php

<?php

// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'tests');
define('USERNAME', 'root');
define('PASSWORD', 'root');

/*
 * Error reporting.
 * 
 * Also, define an error handler, an exception handler and, eventually, 
 * a shutdown handler function to handle the raised errors and exceptions.
 * 
 * @link https://phpdelusions.net/articles/error_reporting Error reporting basics
 * @link http://php.net/manual/en/function.error-reporting.php
 * @link http://php.net/manual/en/function.set-error-handler.php
 * @link http://php.net/manual/en/function.set-exception-handler.php
 * @link http://php.net/manual/en/function.register-shutdown-function.php
 */
error_reporting(E_ALL);
ini_set('display_errors', 1); /* SET IT TO 0 ON A LIVE SERVER! */

/*
 * Enable internal report functions. This enables the exception handling, 
 * e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions 
 * (mysqli_sql_exception).
 * 
 * MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
 * MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings. 
 * 
 * @link http://php.net/manual/en/class.mysqli-driver.php
 * @link http://php.net/manual/en/mysqli-driver.report-mode.php
 * @link http://php.net/manual/en/mysqli.constants.php
 */
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// Create a new db connection.
$connection = new mysqli(HOST, USERNAME, PASSWORD, DATABASE, PORT);

JSON encoded result

As yours.