0

I have been scratching my head over this one for a few days, and I think I am missing the obvious , so I was hoping someone could make some suggestions.

I have a sql statement that selects the last 8 records from the database, I then stuff it in an array and reverse it, for design reasons. I do this using the statement below.

mysql_select_db($database_OASDB, $OASDB);
$sql = "SELECT *, ROUND(InvoiceNo, 0) AS SnPf, RIGHT(InvoiceNo, 3) AS SnSf FROM table WHERE Unit = 'Param1' AND CustomerName = 'Param2' AND Component = 'Param3' AND Site = 'Param4' ORDER BY SnPf DESC, SnSf ASC LIMIT 8";
$result = mysql_query($sql) or die(mysql_error());

$array = array();

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

$a = array_reverse($array);

This works beautifully, and is displayed in a table using various

Now I need to somehow loop through that statement 5 times with different parameters.

I really hope this makes sense.

Any ideas would be appreciated..

Thanks

Andrew

Improve this question
3
  • You want to say that you can't loop through a bunch of statements, in php. It is easy my friend.Just crate a loop and paste your existing code inside the loop.And exclude this one mysql_select_db($database_OASDB, $OASDB); Commented Mar 3, 2015 at 13:17
  • how are the parameters different? Commented Mar 3, 2015 at 13:19
  • 1
    Just a tip: Use mysqli functions, mysql functions are depricated for some time now and will be disabled in coming PHP releases. You could also use pdo, but that is kind of a different story in comparison to mysql functions. Commented Mar 3, 2015 at 13:36

2 Answers 2

Reset to default
1

You can do this with PDO or MySQLi , your current version of MySQL is DEPRECATED and strongly advised not to use it, instead look into MySQLi and PDO which have methods in place for exactly what you want to do,

I will run a very quick example, but I think you'll need to read up on the differences between MySQL and MySQLi (there are many websites with good advise, please google)

Your MySQLi would be best in the form of Object orientated and you would have a MySQLi object which contains your connection data:

Mysqli object (here called Dbx())

Dbx() {
private $dbiUser = username;
private $dbiPass = password;
private $dbiName = database name;

public function __construct(){
      $this->dbiLink = new mysqli("localhost", $this->dbiUser, $this->dbiPass, $this->dbiName);

        if (mysqli_connect_errno()) {
            exit('Connect failed: '. mysqli_connect_error());
        }

        if ( ! $this->dbiLink )
        {
            die("Connection Error (" . mysqli_connect_errno() . ") "
                . mysqli_connect_error());
            mysqli_close($this->dbiLink);
        }
        else
        {
            $this->dbiLink->set_charset("latin1");
        }
        return true;
    }
}

 public function fetchResult($result)
    {
        /***
         * For use with MySQLi->dbiLink Object.
         * Returns the result as an array of KEY=>VALUE pairs from the Database.
         **/
        $resultsArray = array();
        if ($result instanceof mysqli_stmt) {
            $result->store_result();
            $variables = array();
            $data = array();
            $meta = $result->result_metadata();

            while ($field = $meta->fetch_field()) {
                $variables[] = & $data[$field->name]; // pass by reference
            }
            call_user_func_array(array($result, 'bind_result'), $variables);
            $i = 0;
            while ($result->fetch()) {
                $resultsArray[$i] = array();
                foreach ($data as $k => $v)
                    $resultsArray[$i][$k] = $v;
                $i++;
            }
        } elseif ($result instanceof mysqli_result) {
            while ($row = $result->fetch_assoc()) {
                $resultsArray[] = $row;
            }
        }
        unset($row);
        return $resultsArray;
    }

Now, in your PHP code page you first need to call the class file above:

require "includes/classes/dbx.class.inc.php";

Then you need to initiate the new object:

$database = new Dbx();

Now you can use $database as your MySQLi connection to your database.

First, set your 5 parameters as an array (as mentioned by Codrutz codrutz) thus:

$paramaters[0][] = $param1;
  $paramaters[0][] = $param2;
$paramaters[0][] = $param3;
$paramaters[0][] = $param4;
$paramaters[0][] = $param5;
...
$paramaters[1][] = $param12;
  $paramaters[1][] = $param22;
$paramaters[1][] = $param32;
$paramaters[1][] = $param42;
$paramaters[1][] = $param52;

etc.

Now you have all your parameters in $parameters, you can edit your original query to look through the five (or however many) parameter rows given.

NOTE: that data is not directly inserted into the SQL but applied vai the ? character on MySQL, this is slightly different from PDO which uses a ::variablename structure. 

foreach ($parameter as $paramRow){

$sql = "SELECT *, ROUND(InvoiceNo, 0) AS SnPf, RIGHT(InvoiceNo, 3) AS SnSf FROM table WHERE Unit = ? AND CustomerName = ? AND Component = ? AND Site = ? ORDER BY SnPf DESC, SnSf ASC LIMIT 8";
$sqlQuery = $database->prepare($sql);

Prepare the statement, above. The first part of the bind_param is the type of data that is being bound - (s)tring, (i)nteger, (d)double [numeric] or (b)lob [file/big chunks]. This binds the values in the inner array to the ? in the SQL query. 

$sqlQuery->bind_param("ssss",$paramRow[0],$paramRow[1],$paramRow[2],$paramRow[3]);
    $sqlQuery->execute();
    $result[] = $database->fetchresult($sqlQuery); ///fetches the results using the custom method, above. 
        $sqlQuery->close();
}
//end the foreach loop.

So this will now have a name value array of results from the $result array, each initial row will correspond to each initial row of the $parameters query. To reverse the array as you want you can still array_reverse but the data is selected by name, and should be referenced as such.

Some qualifiers:

I didn't expect this answer to get this big and PDO is much more flexible than MySQLi in handling table outputs. I also realise that much of this may be a bit "WOW,wtf?" to you, but don't worry, read up elsewhere and it should all fit nearly into place. I think to replace everything I've written with the following summary for MySQLi/PDO

$parameters = array of values(5) of values(4)
foreach ($parameter as $row){
Apply row array values to the SQL statement
Get the result
Arrange the result
Save the result to an array
}

Your output will be an array of the form

$result[parameter][row per parameter][table data from row]

Apologies again this has turned into a massive answer.

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

Comments

0

Create an array with the parameters that you want to use, and use foreach to loop through the statement using the elements of the array that you've created as parameters.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.