Page 1 of 1

can't get results

Posted: Sat Dec 16, 2006 7:24 am
by SuperFly
Hi everyone,
I have code looks like this:

Code: Select all

//start with extarcion

        $comm = payment_getCommission($userid, $date);
        $trans = payment_getTransaction($userid, $date);
        
        //create one array with all details
        $data = array();
        $data = array_merge($comm, $trans);  
        //crate tmp table
            //  var_dump($data); exit;
        $sql = "CREATE TEMPORARY TABLE select_tbl (date VARCHAR(255),  paid DATETIME, amount varchar(255), Reason VARCHAR(255), UTS INT(11)) TYPE=HEAP;";
        for($i = 0; $i<count($data); $i++)
        {
                $sql .= "INSERT INTO select_tbl SET";
                $sql .= " date = '".$data[$i]["date"]."'" ;
               // $sql .= ", total = '".$data[$i]["total"]."'";
                //$sql .= ", ProductID = '".$data[$i]["ProductID"]."'";
                //$sql .= ", paid = '".$data[$i]["paid"]."'";
                $sql .= ", amount = '".$data[$i]["amount"]."'";
                $sql .= ", Reason = '".$data[$i]["ReasonLabel"]."'";
                $sql .= ", UTS = '".$data[$i]["UTS"]."';";
        }
            
          $sql .= "SELECT * FROM select_tbl ORDER BY UTS DESC";
           
          $result = mysql_query($sql);  
          $tmp_array = array();
          
          while($tmp = mysql_fetch_assoc($result))
          {
                $tmp_array[] = $tmp;
          }
        
        
        var_dump($tmp_array);
when I do echo $sql I get:

Code: Select all

CREATE TEMPORARY TABLE select_tbl (date VARCHAR(255), paid DATETIME, amount varchar(255), Reason VARCHAR(255), UTS INT(11)) TYPE=HEAP;
INSERT INTO select_tbl SET date = '12/11/2006 12:00 AM', amount = '250.00', Reason = 'Check Mailout', UTS = '1165816800';
INSERT INTO select_tbl SET date = '12/14/2006 1:13 PM', amount = '497.00', Reason = 'The Copywriting Secret Package', UTS = '1166123622';
INSERT INTO select_tbl SET date = '12/14/2006 10:03 AM', amount = '271.99', Reason = 'Make1Million Package', UTS = '1166112237';
INSERT INTO select_tbl SET date = '12/12/2006 1:38 PM', amount = '67.00', Reason = 'Resale-Rights-Solution membership', UTS = '1165952289';
SELECT * FROM select_tbl ORDER BY UTS DESC


and it works like it should in phpMyAdmin (u can try query),I get results, but I the rest of code:
$result = mysql_query($sql)...etc returns nothing, I get :
mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

Any help or advice how can I get result from working query

Posted: Sat Dec 16, 2006 8:00 am
by RobertGonzalez
Two things:

1. You are attempting to execute multiple queries in MySQL. This is not necessarily bad, but depending on your version, might not be available to you.
2. When executing multiple queries, you need to expect multiple results. Not rows, sets. Which means you need a function that can recurse result sets. The mysql_* family of PHP functions cannot do this. But the mysqli_* functions can. Have a look at mysqli_next_result() and the examples on that page for more information.

Posted: Sat Dec 16, 2006 9:01 am
by SuperFly
Thanks for reply,
I have PHP 4.4.1 version and your function works only with PHP5, any other idea?

Posted: Sat Dec 16, 2006 9:12 am
by volka
Why do you need the database for this at all? Only for sorting the data by UTS?
see http://de2.php.net/usort

Posted: Sat Dec 16, 2006 9:21 am
by SuperFly
because:
I get details from 2 different tables ($comm, $trans) which are not linked and then I need to show that details in list with perpage, so if I use:

Code: Select all

function my_compare($a, $b)
{
	  return $a['UTS'] - $b['UTS'];
}
uasort($data, 'my_compare');
how then I can create perpage?

If you or anyone else have some idea plase share it with me
Regards

Posted: Sat Dec 16, 2006 9:57 am
by volka
What your current code does:
- merging two arrays into one array, $data
- inserting the elements of $data into a temp table
- querying the elements in a certain order
- append each row to a new array.

From what I can see, you do not need a database for that. Just sort $data with usort.

Posted: Sat Dec 16, 2006 2:08 pm
by RobertGonzalez
Volka is right, all you are doing using the database to insert an array that exists and then select that same data. That seems unnecessary. You can probably achieve what you want using array_multisort()...

Code: Select all

<?php
//start with extarcion

$comm = payment_getCommission($userid, $date);
$trans = payment_getTransaction($userid, $date);
       
//create one array with all details
$data = array();
$data = array_merge($comm, $trans);

foreach ($data as $key => $row)
{
    $uts[$key] = $row['UTS'];
}

array_multisort($uts, SORT_DESC, $data);
echo '<pre>';
var_dump($data);
echo '</pre>';
?>
EDIT | Changed sort order to descending because I was stupid and didn't see original poster's DESC call.

Posted: Sun Dec 17, 2006 4:56 am
by SuperFly
Thanks for reply,
you was great help
:)