php Expand and Collapse search results

[jayhall]

my Table looks like this

id make client price cart
1 bmw day 500 5
2 gt3 mr king 450 9
3 gt3 mrs johan 600 9
4 bmw mr emir 350 2
5 golf ohar 150 5
6 golf mr smith 850 6
7 bmw mr smith 451 7
8 ford mr mary 495 8

what im looking to print

id make client price cart
5 golf ohar 150 5
4 bmw mr emir 350 2
2 gt3 mr king 450 9
8 ford mr mary 495 8

i am trying to make a search result table where it fetches the row with the lowest PRICED value of car, and then keeps the same MAKE car rows hidden in the expand and collapse format. So it only shows the hidden rows when I click the plus icon to expand the hidden rows and hides when i click the minus icon. However i have tried and cant find any success to group the MAKE of cars and only show the row with lowest PRICE for the make of car.

please some can give code or forward the links, please help

[deceptikon]

The simplest solution would be to make subsequent queries into your table for the make, excluding the id that you're already displaying, and order the results by price. Then you can insert those into your expanded rows display.

Alternatively, depending on the size of the table, cost of database queries, and expected frequency of clicks to expand the make, you could simply dump the whole table into objects and organize them in a more convenient manner for your display table.

[broj1]

SQL gurus would make up a query that would spit out the rows in correct order but I propose a solution with a simple query and PHP then sorting out the results so it shows what you want. See the comments in code. Please note all error checking is omitted for clarity.

<?php
 
    $dbhost = 'localhost';
    $usr = 'someuser';
    $pw = 'password';
    $db = 'test';
    $table = 'cars';
      
    // connect to the database and get $dblink
    $dblink = mysqli_connect($dbhost, $usr, $pw, $db);
    
    // query to select all fields order by 'make' first, to get makes together, then
    // by 'price' to get the lowest price for the make first
    $q  = 'SELECT id, make, client, price, cart FROM cars ORDER BY make,price';
    
    $res = mysqli_query($dblink, $q);

    // each row is read into $cars_array where associative index is make:
    // array('make' => 'lowest_price' => lowest price,
    //                 'data' => 
    //                      array('id' => id, 'client' => client, 'price' => price, 'cart' => cart),
    //                      array('id' => id, 'client' => client, 'price' => price, 'cart' => cart),
    //                      array('id' => id, 'client' => client, 'price' => price, 'cart' => cart),
    //                      ...
    //      )
    while($row = mysqli_fetch_assoc($res)) {
    
        // assign variables (code looks cleaner)
        $id = $row['id'];
        $make = $row['make'];
        $client = $row['client'];
        $price = $row['price'];
        $cart = $row['cart'];
        
        // this array element will hold the lowest price for the make
        // (for sorting later on)
        $cars_array[$make]['lowest_price'] = null;
        
        // this array element will hold the data for the make (one or more arrays
        // of ID, client, price and cart) 
        $cars_array[$make]['data'][] = array('id' => $id, 
                                   'client' => $client, 
                                   'price' => $price,
                                   'cart' => $cart);
    }

    // add the price from the first element (which is the lowest price due to
    // the ORDER BY id in the query)
    foreach($cars_array as $make => $car_data) {

        $cars_array[$make]['lowest_price'] = $cars_array[$make]['data'][0]['price'];
    }
    
    // sort the array (it will sort by the element 'lowest_price' since it is 
    // first element in each sub-array)
    asort($cars_array);

    // start a string for table
    $tbl = '<table border="1">';
     
    // title row
    $tbl .= "<tr><th>ID</th><th>Make</th>";
    $tbl .= "<th>Client</th><th>Price</th><th>Cart</th><th></th></tr>";    

    foreach($cars_array as $make => $cars) {

        // add rows from the $cars_array[$make]['data'] elements
        foreach($cars['data'] as $key => $data) {

            // assign varialbles (makes code cleaner)
            $id = $data['id'];
            $client = $data['client'];
            $price = $data['price'];
            $cart = $data['cart'];
            
            // if it is first array then it has the lower price (due to 
            // ORDER BY price statement in the query) - make the style for
            // the row visible
            if($key == 0) {
            
                $tr_sty = '{visibility:visible;}';
                
                // row class (you will need it for javascript function to show/hide rows)
                $row_class = "visible-$make";
                
                // + sign to show more rows and a javascript function
                $toggle_sign = '<a href="#" onclick="toggleRows(' . $make . ')">+</a>';
            
            // else make the style for the row collapse (not visible)
            } else {
            
                $tr_sty = 'visibility:collapse"';
                
                // row class (you will need it for javascript function to show/hide rows)
                $row_class = "toggle-$make";
                
                // no sign to show more rows in hidden rows
                $toggle_sign = '';
            }
            
            // prepare html code for the row using the appropriate style
            $tbl .= "<tr style=\"$tr_sty\" class=\"$row_class\">";
            $tbl .= "<td>$id</td><td>$make</td><td>$client</td>";
            $tbl .= "<td>$price</td><td>$cart</td><td>$toggle_sign</td></tr>";
        }
    }

    // end the table
    $tbl .= '</table>';

    // display the table
    echo $tbl;
?>

Now you can make a javascript function toggleRows(make) to show / hide certain rows.

[akashbarot51]

i am trying to insert data into database using form. if there is maximum 3 fields i can insert data. when i am trying to insert data using this code i can not insert data. what is wrong with this code. before i have added successfully data. now i can not insert for the last 2 days.

<?php
	$con=mysql_connect("localhost","root","");
	if(!$con)
	{
		die('could not connect'.mysql_error());
	}
	mysql_select_db("sncl",$con);
	$sql="insert into feedbacktable(name,add,phone,email,comment)values('$_POST[name]','$_POST[add]','$_POST[phone]','$_POST[email]','$_POST[comment]')";
	mysql_query($sql,$con);
	mysql_close($con);
?>

[diafol]

Eek! You're inputting raw post data. Sanitize it, e.g. with mysql_real_escape_string. If you have single quotes in your input data your sql could break.