PHP & MySQL do action for each row in database

Question

I am building a site, and essentially what this PHP algorithm will do is look at a product (row in MySQL database) one at a time, and do a process accordingly.

I put a lot of research into this but couldn't find anything, any help would be greatly appreciated!

My Code (currently returning nothing for echo variables):

<?php
include_once 'dbconnect.php';


$query = "SELECT * FROM track"; 
$result = mysql_query($query);


while($row = mysql_fetch_array($result)){ 
    $pro_code = mysql_result(mysql_fetch_array(mysql_query('SELECT product_code FROM track')));
    $currency = mysql_fetch_array(mysql_query('SELECT currency FROM track'));
    $cc = mysql_fetch_array(mysql_query('SELECT cctld FROM track'));
    $initial_price = mysql_fetch_array(mysql_query('SELECT initial_price FROM track'));

    $url = 'test';
}

echo $pro_code;
echo $currency;
echo $initial_price;

?>

Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Feb 01 '16 at 21:14
Thanks, could you please provide an example as to how it would work using those extensions? — Anonymous Asker, Feb 01 '16 at 21:15
It is pretty easy to add actions for each row, but it looks like you might try to issue a number of queries for the same table when one will likely work fine. Other than that it is hard to know what you're trying to do, your question is not particularly clear. There are examples in the links in that comment. — Jay Blanchard, Feb 01 '16 at 21:15
I want to compare the price of each item with a price from an external source, one at a time. I hope that's a little clearer — Anonymous Asker, Feb 01 '16 at 21:16
That's rather nasty code. Why are you fetching all records from `track`, then **RE-QUERYING** the exact same table within the loop? plus, nesting calls like that is just asking for trouble. queries can/will/do fail, and your code simply assumes nothing could ever go wrong. if any of the queries barfs, you'll get spammed with "expected mysql, got boolean"-type warnings. Plus, there's far better ways to fetch multiple fields from a table than running multiple queries, one per field. — Marc B, Feb 01 '16 at 21:19
I do not know how to do it.. I looked all over the web but I must be wording my search queries wrong as I did not find anything — Anonymous Asker, Feb 01 '16 at 21:19

score 1 · Answer 1 · answered Feb 01 '16 at 21:41

First of all, try the advice about PDO and stuff from Jay Blanchard some day.

Secondly I've tried to answer your question anyway and I've tried to interpret your complete intention. I put comments in the code:

    <?php
    include_once 'dbconnect.php';

    $query = "SELECT * FROM track"; 
    $result = mysql_query($query);

    while($row = mysql_fetch_array($result)){ 

        //You need to read the row variable as an array
        $pro_code       = $row['product_code'];
        $currency       = $row['currency'];
        $cc             = $row['cctld'];
        $initial_price  = $row['initial_price'];

        //$url is not used.. I asume a test to get the external source ;-)
        $url = 'test';

        if ($url == $cc) {
            //if you want to print every row, you must echo inside the while loop
            echo $pro_code;
            echo $currency;
            echo $initial_price;    
        } elseif ($url == 'test') {
            //do something else here
        } else {
            //do something for all the other cases here
        }//end if

    }//end while
    ?>

score 0 · Answer 2 · answered Feb 01 '16 at 21:38

Why do you query the same table multiple times, your code should be written like this:

include_once 'dbconnect.php';


$query = "SELECT product_code, currency, cctld, initial_price FROM track"; 
$result = mysql_query($query);


while($row = mysql_fetch_array($result, MYSQL_ASSOC)){ 
    echo $row['product_code'];
    echo $row['currency'];
    echo $row['cctld'];
    echo $row['initial_price'];
}

and please upgrade to mysqli or PDO

PHP & MySQL do action for each row in database

2 Answers2