can't get results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
SuperFly
Forum Newbie
Posts: 19
Joined: Thu Nov 10, 2005 9:47 am
Contact:

can't get results

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
SuperFly
Forum Newbie
Posts: 19
Joined: Thu Nov 10, 2005 9:47 am
Contact:

Post by SuperFly »

Thanks for reply,
I have PHP 4.4.1 version and your function works only with PHP5, any other idea?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
SuperFly
Forum Newbie
Posts: 19
Joined: Thu Nov 10, 2005 9:47 am
Contact:

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
SuperFly
Forum Newbie
Posts: 19
Joined: Thu Nov 10, 2005 9:47 am
Contact:

Post by SuperFly »

Thanks for reply,
you was great help
:)
Post Reply