how to acess my database elements using the for loop?

Question

I'm learning PHP and I'm well versed with Java and C. I was given a practice assignment to create a shopping project. I need to pull out the products from my database. I'm using the product id to do this. I thought of using for loop but I can't access the prod_id from the database as a condition to check! Can anybody help me?! I have done all the form handling but I need to output the products. This is the for-loop I am using. Please let me know if I have to add any more info. Thanks in advance :)

for($i=1; $i + 1 < prod_id; $i++)
{
   $query = "SELECT * FROM products where prod_id=$i";
}

Answer 1

1. First of all, you should use database access drivers to connect to your database.

2. Your query should not be passed to cycle. It is very rare situation, when such approach is needed. Better to use WHERE condition clause properly.

3. To get all rows from products table you may just ommit WHERE clause. Consider reading of manual at http://dev.mysql.com/doc.

The statement selects all rows if there is no WHERE clause.

Following example is for MySQLi driver.

// connection to MySQL:
// replace host, login, password, database with real values.
$dbms = mysqli_connect('host', 'login', 'password', 'database');

// if not connected then exit:
if($dbms->connect_errno)exit($dbms->connect_error);

$sql = "SELECT * FROM products";

// executing query:
$result = $dbms->query($sql);

// if query failed then exit:
if($dbms->errno)exit($dbms->error);

// for each result row as $product:
while($product = $row->fetch_assoc()){
    // output:
    var_dump($product); // replace it with requied template
}

// free result memory:
$result->free();

// close dbms connection:
$dbms->close();

Answer 2

I would suggest that you use PDO. This method will secure all your SQLand will keep all your connections closed and intact.

Here is an example

EXAMPLE. This is your dbc class (dbc.php)

<?php

class dbc {

    public $dbserver = 'server';
    public $dbusername = 'user';
    public $dbpassword = 'pass';
    public $dbname = 'db';

    function openDb() {    
        try {
            $db = new PDO('mysql:host=' . $this->dbserver . ';dbname=' . $this->dbname . ';charset=utf8', '' . $this->dbusername . '', '' . $this->dbpassword . '');
        } catch (PDOException $e) {
            die("error, please try again");
        }        
        return $db;
    }

    function getproduct($id) {
        //prepared query to prevent SQL injections
        $query = "SELECT * FROM products where prod_id=?";
        $stmt = $this->openDb()->prepare($query);
        $stmt->bindValue(1, $id, PDO::PARAM_INT);
        $stmt->execute();
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $rows;
    }    
?>

your PHP page:

<?php 
require "dbc.php";
for($i=1; $i+1<prod_id; $i++) 
{

$getList = $db->getproduct($i);
//for each loop will be useful Only if there are more than one records (FYI)
foreach ($getList as $key=> $row) {
         echo $row['columnName'] .' key: '. $key;
    }
}

Answer 3

for($i=1;$i+1<prod_id;$i++) {
    $query = "SELECT * FROM products where prod_id=$i";
    $result = mysqli_query($query, $con);

$con is the Database connection details you can use wile loop to loop thru each rows

    while ($row = mysqli_fetch_array($result))
    {
        ......
    }
}

Answer 4

Hope this might work as per your need..

for($i=1; $i+1<prod_id; $i++) {
   $query  = "SELECT * FROM products where prod_id = $i";
   $result = mysql_query($query);

   while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
         print_r($row);  
   }
}

Answer 5

I think you want all records from your table, if this is the requirement you can easily do it

$query = mysql_query("SELECT * FROM products"); // where condition is optional   

while($row=mysql_fetch_array($query)){
   print_r($row);
   echo '<br>';
}

This will print an associative array for each row, you can access each field like

echo $row['prod_id'];