mysql_fetch_array只返回查询的最后一个值

Question

Basically I want to make a download button for my project that will produce different csv files (one csv file per table) in memory and zip before download. It works fine but the problem is that I am only getting one row (the last result) on each mysql_fetch_array where it is supposed to return rows depending on how many are stored in the database. This code is depreciated, sorry for that.

Here is my code:

<?php  

require("../includes/connection.php");
require("../includes/myLib.php");
//get the ID that is passed
$ID = $_REQUEST['q'];
$ID = xdec($ID);

//All queries to fetch data  
$query  = mysql_query("SELECT * FROM `ge2`.`projects` WHERE `projects`.`proj_id`='$ID'");
$query2 = mysql_query("SELECT * FROM `ge2`.`attributes` WHERE `attributes`.`proj_id`='$ID'");
$query3 = mysql_query("SELECT * FROM `ge2`.`category` WHERE `category`.`proj_id`='$ID'");
$query4 = mysql_query("SELECT * FROM `ge2`.`multipletarget` WHERE `multipletarget`.`proj_id`='$ID'");
$query5 = mysql_query("SELECT * FROM `ge2`.`data_cut` WHERE `data_cut`.`proj_id`='$ID'");
$query6 = mysql_query("SELECT * FROM `ge2`.`raw` WHERE `raw`.`proj_id`='$ID'");

//getting all array
while ($row = mysql_fetch_array($query)) {

    $proj_alias = $row['proj_alias'];
    $proj_id    = $row['proj_id'];
    $date_added = $row['date_added'];        
}

while ($row1 = mysql_fetch_array($query2)) {

    $attrib_param_id = $row1['param_id'];
    $attrib_proj_id  = $row1['proj_id'];
    $attrib_cat_id = $row1['cat_id'];
    $attrib_val_id = $row1['val_id'];
    $attrib_name   = $row1['name'];
    $attrib_isCust = $row1['isCust'];        
}

while ($row2 = mysql_fetch_array($query3)) {

    $category_cat_id = $row2['cat_id'];
    $category_name = $row2['name'];
    $category_proj_id = $row2['proj_id'];
    $category_desc = $row2['desc'];
}

while ($row3 = mysql_fetch_array($query4)) {

    $multipletarget_id = $row3['id'];
    $multipletarget_proj_id = $row3['proj_id'];
    $multipletarget_mtarget1 = $row3['mtarget1'];
    $multipletarget_mtarget2 = $row3['mtarget2'];
}

while ($row4 = mysql_fetch_array($query5)) {

    $data_cut_id      = $row4['id'];
    $data_cut_proj_id = $row4['proj_id'];
    $data_cut_name  = $row4['name'];
    $data_cut_param = $row4['param'];
    $data_cut_lvl = $row4['lvl'];
    $data_cut_val = $row4['val'];
}

while ($row5 = mysql_fetch_array($query6)) {

    $raw_id      = $row5['raw_id'];
    $raw_proj_id = $row5['proj_id'];
    $raw_p_id = $row5['p_id'];
    $raw_url  = $row5['url'];
    $raw_ip   = $row5['ip'];
    $raw_pos  = $row5['pos'];
    $raw_datetaken = $row5['datetaken'];
    $raw_used   = $row5['used'];
    $raw_fdc_id = $row5['fdc_id'];
    $raw_dq     = $row5['dq'];
}

// some data to be used in the csv files
$records = array(
    $proj_alias, $proj_id, $date_added
);
$records2 = array(
    $attrib_param_id, $attrib_proj_id, $attrib_cat_id, $attrib_val_id, $attrib_name, $attrib_isCust
);

$records3 = array(
    $category_cat_id, $category_name, $category_proj_id, $category_desc
);
$records4 = array(
    $multipletarget_id, $multipletarget_proj_id, $multipletarget_mtarget1, $multipletarget_mtarget2
);
$records5 = array(
    $data_cut_id, $data_cut_proj_id, $data_cut_name, $data_cut_param,$data_cut_lvl,$data_cut_val
);
$records6 = array(
    $raw_id, $raw_proj_id, $raw_p_id, $raw_url,$raw_ip,$raw_pos,$raw_datetaken,$raw_used,$raw_fdc_id,$raw_dq
);
//making an array to be used in loop     
$set = array($records,$records2,$records3,$records4,$records5,$records6);
//names to be named for each csv file      
$names = array('projects', 'attributes', 'category', 'multipletarget', 'data_cut', 'raw');

// create zip file
$zipname = $proj_alias;
$zip = new ZipArchive;
$zip->open($zipname, ZipArchive::CREATE);

// loop to create csv files
$n = 0;
foreach ($set as $setk => $sets) {
    $n += 1;
    $fd = fopen('php://temp/maxmemory:1048576', 'w');
    if (false === $fd) {
        die('Failed to create temporary file');
    }

    fputcsv($fd, $sets);

    // return to the start of the stream
    rewind($fd);

    // add the in-memory file to the archive, giving a name
    $zip->addFromString('BrainLink-' . $proj_alias . "-" . $names[$setk] . '.csv', stream_get_contents($fd));
    //close the file
    fclose($fd);
}
// close the archive
$zip->close();


header('Content-Type: application/zip');
header('Content-disposition: attachment; filename='.$zipname.'.zip');
header('Content-Length: ' . filesize($zipname));
readfile($zipname);

// remove the zip archive
// you could also use the temp file method above for this.
unlink($zipname);  
?>

Thanks in advance.

Answer 1

Well, it seems that you're independently iterating over all query results and overwriting the variables over and over again. So in the end, you have only last table results to work with.

You might try using JOIN OR UNION SELECT in MySQL to get all the items in one big query result row:

$query = mysql_query('SELECT proj.*, attrs.* 
FROM `projects` AS proj
JOIN `attributes` AS attrs ON (attrs.project_id=proj.project_id)
<..more tables to join in the same manner..>
WHERE proj.`proj_id`= ' . $projectId);

And then, you'll just have to iterate only over a single query resource.

while ($row = mysql_fetch_array($query)) {
//your code here
}

Note, that if tables being JOIN'ed have the same column names, they will "overwrite" each other and you'll have to rename them yourself "on the fly".

Like so:

SELECT proj.field, proj.another_field, proj.conflicting_field_name AS unique_field_name