Getting Mysql field from an array

Question

I have the following script to display files inside a directory

<?PHP
# The current directory
$directory = dir("./");

# If you want to turn on Extension Filter, then uncomment this:
 $allowed_ext = array(".deb", ".ext", ".ext", ".ext", ".ext", ".ext"); 

$do_link = TRUE; 
$sort_what = 0; //0- by name; 1 - by size; 2 - by date
$sort_how = 0; //0 - ASCENDING; 1 - DESCENDING


# # #
function dir_list($dir){ 
    $i=0; 
    $dl = array(); 
    if ($hd = opendir($dir))    { 
        while ($sz = readdir($hd)) {  
            if (preg_match("/^\./",$sz)==0) $dl[] = $sz;$i.=1;  
        } 
    closedir($hd); 
    } 
    asort($dl); 
    return $dl; 
} 
if ($sort_how == 0) { 
    function compare0($x, $y) {  
        if ( $x[0] == $y[0] ) return 0;  
        else if ( $x[0] < $y[0] ) return -1;  
        else return 1;  
    }  
    function compare1($x, $y) {  
        if ( $x[1] == $y[1] ) return 0;  
        else if ( $x[1] < $y[1] ) return -1;  
        else return 1;  
    }  
    function compare2($x, $y) {  
        if ( $x[2] == $y[2] ) return 0;  
        else if ( $x[2] < $y[2] ) return -1;  
        else return 1;  
    }  
}else{ 
    function compare0($x, $y) {  
        if ( $x[0] == $y[0] ) return 0;  
        else if ( $x[0] < $y[0] ) return 1;  
        else return -1;  
    }  
    function compare1($x, $y) {  
        if ( $x[1] == $y[1] ) return 0;  
        else if ( $x[1] < $y[1] ) return 1;  
        else return -1;  
    }  
    function compare2($x, $y) {  
        if ( $x[2] == $y[2] ) return 0;  
        else if ( $x[2] < $y[2] ) return 1;  
        else return -1;  
    }  

} 

################################################## 
#    Getting The information
################################################## 

$i = 0; 
while($file=$directory->read()) { 
    $file = strtolower($file);
    $ext = strrchr($file, '.');
    if (isset($allowed_ext) && (!in_array($ext,$allowed_ext)))
        {
            // dump 
        }
    else { 
        $temp_info = stat($file); 
        $new_array[$i][0] = $file; 
        $new_array[$i][1] = $temp_info[7]; 
        $new_array[$i][2] = $temp_info[9]; 
        $new_array[$i][3] = date("F d, Y", $new_array[$i][2]); 
        $i = $i + 1; 
        } 
} 
$directory->close(); 

################################################## 
# Sorting the information
################################################# 

switch ($sort_what) { 
    case 0: 
            usort($new_array, "compare0"); 
    break; 
    case 1: 
            usort($new_array, "compare1"); 
    break; 
    case 2: 
            usort($new_array, "compare2"); 
    break; 
} 

############################################################### 
#    Displaying the information
############################################################### 

$i2 = count($new_array); 
$i = 0; 
echo "<table class='CSSTableGenerator'> 
                <tr> 
                    <td width=290>File name (Download)</td>
                    <td align=center width=70>Downloads</td> 
                    <td align=center width=70>Depiction</td> 
                    <td align=center width=50>Size</td> 
                    <td align=center width=85>Modified</td>
                </tr>"; 
for ($i=0;$i<$i2;$i++) { 
if (!$do_link) { 
    $line = "<tr><td>" . $new_array[$i][0];
    $line .= '</td><td><a href="' . '../depictions/' . substr($new_array[$i][0], 0, strpos($new_array[$i][0], "_")) . '/index.php">Depiction</a></td>';
    $line .= "<td>" . number_format(($new_array[$i][1]/1024)) . " KB</td>"; 
    $line .= "<td>" . $new_array[$i][3] . "</td></tr>"; 

    }else{ 
$line = '<tr><td align=left ><A class="ex1" HREF="' .   
                        $new_array[$i][0] . '">' .  
                        $new_array[$i][0] .  
                        "</A></td>"; 
    $line .= '<td> </td>';
   $line .= '<td><a href="' . '../depictions/' . substr($new_array[$i][0], 0, strpos($new_array[$i][0], "_")) . '/index.php">Depiction</a></td>';
    $line .= "<td>" . number_format(($new_array[$i][1]/1024)) . " KB</td>"; 
    $line .= "<td>" . $new_array[$i][3] . "</td></tr>"; 
    } 
    echo $line; 
} 
echo "</table>"; 


?>

The output looks like that: enter image description here

I am trying to fill the downloads column by getting the download counts of each file from mysql table

So i am facing 2 issues here:

How to get the stats using the array of the files $new_array[$i][0]
How can i then add this mysql query output inside $line .= '<td> </td>';

I have tried getting the stats from the table using this:

include("../config.php");
$query = "SELECT stats FROM download WHERE filename='$new_array[$i][0]'";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
    $line .=  '<td>' . $row['stats'] . '</td>';
}

but it didn't work, i think the issue is with the array $new_array[$i][0] because i tried writing a filename filename='com.name.app3_2.2-1_iphoneos-arm.deb' and i got the stats of this file

Where do you initialise $new_array in your code?

Philip Adler
– Philip Adler

2014-05-24 13:36:27 +00:00
Commented May 24, 2014 at 13:36 — Philip Adler
– Philip Adler, Commented May 24, 2014 at 13:36
In Getting the information part?

Fouad
– Fouad

2014-05-24 13:42:02 +00:00
Commented May 24, 2014 at 13:42 — Fouad
– Fouad, Commented May 24, 2014 at 13:42

Philip Adler · Accepted Answer · 2014-05-24 14:34:18Z

2

In your "getting the information part", you must initialise your arrays thusly:

################################################## 
#    Getting The information
################################################## 

$i = 0; 
$new_array = array();
while($file=$directory->read()) { 
    $file = strtolower($file);
    $ext = strrchr($file, '.');
    if (isset($allowed_ext) && (!in_array($ext,$allowed_ext)))
        {
            // dump 
        }
    else {
        $new_array[$i] = array();
        $temp_info = stat($file); 
        $new_array[$i][0] = $file; 
        $new_array[$i][1] = $temp_info[7]; 
        $new_array[$i][2] = $temp_info[9]; 
        $new_array[$i][3] = date("F d, Y", $new_array[$i][2]); 
        $i = $i + 1; 
        } 
} 
$directory->close(); 

##################################################

In addition, I have noted that your line:

$query = "SELECT stats FROM download WHERE filename='$new_array[$i][0]'";

Would cause $query to be equal to:

SELECT stats FROM download WHERE filename='Array[0]'

When $i is 0.

What you should do is use the mysqli library thusly:

$mysqli = new mysqli("example.com", "user", "password", "database");
$query = "SELECT stats FROM download WHERE filename=?";
$stmt = $mysqli->prepare($query);
$stmt->bind_param('s', $new_array[$i][0]);
$stmt->execute();
$stmt->bind_result($stats);//stats turns into a reference by default.
while($stmt->fetch()) { //$stats now contains the stats
    $line .= '<td>'. htmlentities($stats).'</td>';
}

To deal with the problem of missing cells you just described, you might try:

$mysqli = new mysqli("example.com", "user", "password", "database");
$query = "SELECT stats FROM download WHERE filename=?";
$stmt = $mysqli->prepare($query);
$stmt->bind_param('s', $new_array[$i][0]);
$stmt->execute();
$stmt->bind_result($stats);//stats turns into a reference by default.
$fetched = false;
while($stmt->fetch()) { //$stats now contains the stats
    $line .= '<td>'. htmlentities($stats).'</td>';
    $fetched = true;
}
if(!$fetched) {
    $line .= '<td></td>'
}

edited May 24, 2014 at 14:34

answered May 24, 2014 at 13:46

Philip Adler

2,20620 silver badges27 bronze badges

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

3 Comments

Fouad Over a year ago

That didn't fix the issue, its still the same output

Philip Adler Over a year ago

I have edited my answer to include a new segment, addressing other problems.

Fouad Over a year ago

Thanks! It works, got one more thing to ask you about, if a file didn't get downloaded yet which means its not yet included in the mysql table, how can i show a blank cell instead of this i.imgur.com/IuqdgQm.png

Collectives™ on Stack Overflow

Getting Mysql field from an array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related