how to fetch all rows data from the database using while loop and stored fetch values in variables in php

Question

hi everyone i have table in the database from where i fetch data one column of table has user input n i fetch the data on the basis of user input like if user enter 2 in the textbox only that rows that contain number 2 will display like code of insertion on the basis of no of room

   <?php
    $d=$_POST['roomno'];
    echo $d;
    ?>
      <?php
      //database connection
    $db = new PDO("mysql:host=localhost;dbname=ems",'root','');
    //query
    $sql = "INSERT INTO table1 (c,c1, c2, c3, c4,c5,c6,c7,c8,c9,c10,c11,c12,c13) VALUES ('',:c1, :c2, :c3, :c4,:c5, :c6, :c7, :c8, :c9,:c10, :c11, :c12,'$d')";
    $stmt = $db->prepare($sql);
    foreach ($data_t1 as $i => $value) {
        $stmt->execute(array(
           ':c1'=>$data_t1[$i],
           ':c2'=>$data_t2[$i],
           ':c3'=>$data_t3[$i],
           ':c4'=>$data_t4[$i],
           ':c5'=>$data_t11[$i],
           ':c6'=>$data_t22[$i],
           ':c7'=>$data_t33[$i],
           ':c8'=>$data_t44[$i],
           ':c9'=>$data_t111[$i],
           ':c10'=>$data_t222[$i],
           ':c11'=>$data_t333[$i],
           ':c12'=>$data_t444[$i],
        ));
    } ?>

coding for fetchng the data

 <?php
    include('config.php');
    $sa="select * from table1 where c13='$d'";
    $result=mysql_query($sa) or die(mysql_error());
    echo "<table border='1'>
    <tr>

    </tr>";

     <?php
    $i = 1;
    while($row = mysql_fetch_array($result)) {
       echo ${"r" . $i . "1"} = $row['c'];
      echo  ${"r" . $i . "2"} = $row['c1'] ;
       echo ${"r" . $i . "3"} = $row['c2'];
       echo ${"r" . $i . "4"} = $row['c3'];
       echo ${"r" . $i . "5"} = $row['c4'];
       $i++;
    }

result

$d=3;mean user enter 3
3 room deatils enter in three rows of table like ths

row1  2   4  5   6    3(user enter value)
row2  12  14 15   16   3(user enter value)
row3  22  44  55  63   3(user enter value)

now as i mention in my fetch query that fetch the data where c13(room)=user input($d);

result of theses above three row display

now i want to store each of theses three row values in different varaibles

in the similar manner if user enter 2 or 4 i want to store two or four rows values in different varaible

Consider using mysqli_ functions instead of the old mysql_ as these old functions are soon to be discontinued and your programs will stop working if the host upgrades the platform.. — itsols
– itsols, Commented May 26, 2013 at 12:50
…or use PDO. Anyway, it's better to switch to prepared statements to prevent SQL injection. — Marcel Korpel
– Marcel Korpel, Commented May 26, 2013 at 12:51

Michael Sazonov · Accepted Answer · 2013-05-26 13:33:11Z

2

Of course it will display only the last row. Within each iteration over the query result you are rewriting the $r -nth variable, so the result you'll see after the loop are only the values that were assigned during the last iteration.

Use

$row = array();
while($row[] = mysql_fetch_array($result)){
}

As result you will get all the data stored as an array in $row. What it does is as follows: Iterates over the query result and and fetching it. The fetching method returns an array of the current iterated row values. This array is pushed into the $row array. So, each row value could be accessed at a corresponding index of $row. Use print_r( $row ); to see the structure.

Further use of the array is as follows:

echo $row[ 0 ][ 'c1' ];
echo $row[ 0 ][ 'c2' ];
echo $row[ 1 ][ 'c3' ];

If you want to show the input depending on a dynamical rows amount, use this:

$count = count( $row );
for( $i = 0 ; $i < $count; $i++ ){
    echo $row[ $i ][ 'c1' ];
}

edited May 26, 2013 at 13:33

answered May 26, 2013 at 12:49

Michael Sazonov

1,53311 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user2421659 Over a year ago

sir i dont want to use array as i want these values to be used for further computataion

Michael Sazonov Over a year ago

@user2421659 Array is a set of values. Or am I missing something in your question?

user2421659 Over a year ago

sir your are rite that array are set of values but i need to store every value in some varaible instead to store in whole array

user2421659 Over a year ago

sir if 2 is present in 3 rows then how could be its is possible? here you mention $row[0] indicx of array

Michael Sazonov Over a year ago

@user2421659 I've edited the answer. Try this method and use the print_r to see the structure. Then you should understand how to handle it.

|

sybear · Accepted Answer · 2013-05-26 12:47:02Z

1

Simply:

$data = array() ; //Declare an array
while($row = mysql_fetch_array($result)){
  $data[] = $row ; //Add every row to array
}

echo $data['c4'] ; // Work with the array ETC

answered May 26, 2013 at 12:47

sybear

7,7921 gold badge25 silver badges38 bronze badges

1 Comment

user2421659 Over a year ago

sir i need to perform further computation can't i store the value in varaibles $r=$row?

iguider · Accepted Answer · 2013-05-26 13:18:26Z

0

I recommand you to use Michael Sazonov's way.. but if you don't want arrays here you go:

<?php
$i = 1;
while($row = mysql_fetch_array($result)) {
   ${"r" . $i . "1"} = $row['c'];
   ${"r" . $i . "2"} = $row['c1'] ;
   ${"r" . $i . "3"} = $row['c2'];
   ${"r" . $i . "4"} = $row['c3'];
   ${"r" . $i . "5"} = $row['c4'];
   $i++;
}

Now The contents are stored in $r11 $r12 $r13.. $r35 and you have to echo them manually or using 2 for loops

<?php
for($i=1; $i<=3; $i++) {
   for($j=0; $j<=5; $j++) {
      echo ${'r'.$i.$j};
   }
}
?>

answered May 26, 2013 at 13:18

iguider

7311 gold badge12 silver badges25 bronze badges

Collectives™ on Stack Overflow

how to fetch all rows data from the database using while loop and stored fetch values in variables in php

3 Answers 3

8 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related