I have been working on this for some time now and i cant get it to work, idk if im missing something small which im sure is the case, but the strange part is it works on an older installation of apache, so maybe im using code thats no longer supported, whatever the case ive diagnosed that the line while ($row = mysql_fetch_array($result)) is returning false when it should not be, i think it might be in the $sql varibale but im not sure, ive changed it to a simple line but it still returns false, so any assistence would be overwhelmingly appreciated.

<?php

// Database Connection

$host="localhost";
$uname="root";
$pass="";
$database = "numbers"; 

$e = NULL;
$b = NULL;

$con=mysql_connect($host,$uname,$pass); 


// set query

if(isset($_GET['submit']))
{
    $e = $_GET['e'];
    $b = $_GET['b'];
}

//or die("Database Connection Failed");
$selectdb=mysql_select_db($database) or 
die("Database could not be selected");

// Fetch Record from Database

$output = "";

$table = "usernumdata"; // Enter Your Table Name 
$sql = "SELECT * FROM $table WHERE STR_TO_DATE('date','%Y-%m-%d') BETWEEN '".$b."' AND '".$e."'";
$result = mysql_query($sql,$con);
$columns_total = mysql_num_fields($result);


// only print the values from the table that align with search query

    // Get The Field Name

    for ($i = 1; $i < $columns_total; $i++) 
    {
        $heading = mysql_field_name($result, $i);
        $output .= '"'.$heading.'",';
    }
    $output .="\n";


    // Get Records from the table

    while ($row = mysql_fetch_array($result)) 
    {
        for ($i = 1; $i < $columns_total; $i++) {
            $output .='"'.$row["$i"].'",';
        }
        $output .="\n";
    }


// Download the file

$filename = "usernumdataexported.csv";
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);

echo $output;

exit;

?>

here is the entire code, my error is somewhere in here.

Recommended Answers

All 37 Replies

'date' is not a valid date string. If you mean date as a column use backticks instead if single quotes.

ok i did that but its still not printing anything to csv, is it the date string maybe?

What format is in date?

the part of the file that writes the date to the table looks like this
$mySqlDate = date('Y-m-d'); and it shows up in the table like this
2014-09-08

Isnt is stored as a date perhaps? And not a varchar.

well you see the weird thing is, if i modify it in any way then i get an error telling me im giving a boolean when a resource is needed :< this is wild cause this is literally my only issue left an di have this whole code ready to be deployed on our linux server at work.

my issue has to be in the sql date part when i pass it in, something isnt right or at least thats the only thing i can see as a potential issue

Show your table structure.

there is a screen shot of the tablein my program
and a screen shot of the table in phpmyadmin

Above you have this:

$sql = "SELECT * FROM $table WHERE STR_TO_DATE('date','%Y-%m-%d') BETWEEN '".$b."' AND '".$e."'";
$result = mysql_query($sql,$con);

Turn the second line into:

$result = mysql_query($sql, $con) or die($sql . '<br/>' . mysql_error());

And post the output (query and error) here.

there was no error, it returned the same result it always does
here is the file thats exported if there was an error it would have posted in the file. there was none :(

still nothing :< ive been changing evry little vale i can and so far all i can some up with that is completely conclusive is that for some reason unknown to me, mysql_fetch_array() is returning false and i dont know why.

OK HOLD EVERTHING, i finally see what i have done wrong but the only problem is that i have no idea how to fix it, turns out non of my code is actually wrong, it all worked fine but the issue was that i was already calling the mysql_fetch_array() function in my html table, thats how i was getting the contents of the database to display properly, and i have come to find out that you cannot call this function twice on TECHNICALLY the same page. How in the world do i get around this?

ugh my situation really sucks, i cant modify header information when im already echoing from another file on the main screen. So here is the code im using that is causing the issue.

<?php

// Database Connection

$host="localhost";
$uname="root";
$pass="";
$database = "numbers"; 
$filename = "usernumdataexported.csv";

$e = NULL;
$b = NULL;

$con=mysql_connect($host,$uname,$pass); 


// set query

if(isset($_GET['submit']))
{
    $e = $_GET['e'];
    $b = $_GET['b'];
}

//or die("Database Connection Failed");
$selectdb=mysql_select_db($database) or 
die("Database could not be selected");

// Fetch Record from Database

$output = "";

//$table = "usernumdata"; // Enter Your Table Name 
$sql = "SELECT * FROM usernumdata WHERE DATE_FORMAT(NOW(),'%Y-%m-%d') BETWEEN '".$b."' AND '".$e."'";
//$sql = "SELECT * FROM usernumdata WHERE DATE_FORMAT(NOW(),'%Y-%m-%d') >= '".$b."' AND DATE_FORMAT(NOW(),'%Y-%m-%d') <= '".$e."'";
$result = mysql_query($sql, $con) or die($sql . '<br/>' . mysql_error());
$columns_total = mysql_num_fields($result);


// only print the values from the table that align with search query

    // Get The Field Name

    for ($i = 1; $i < $columns_total; $i++) 
    {
        $heading = mysql_field_name($result, $i);
        $output .= '"'.$heading.'",';
    }
    $output .="\n";


    // Get Records from the table


    while ($row = mysql_fetch_array($result)) 
    {
        for ($i = 1; $i < $columns_total; $i++) {
            $output .='"'.$row["$i"].'",';
        }
        $output .="\n";
    }


// Download the file

header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);

echo $output;

exit;

?>

as you can see in my export.php im using a header fucntion to name the file correctly and then tell the browser to download it with the infomation in it.

</thead>
                            <tbody>
                            <?php 
                            $query=mysql_query("SELECT * FROM usernumdata")or die(mysql_error());
                            $counter = 0;
                            while($row=mysql_fetch_array($query)){
                            $id=$row['id'];
                            $counter++;
                            ?>

                                        <tr>

                                         <td><?php echo $row['empname'] ?></td>
                                         <td><?php echo $row['firstname'] ?></td>
                                         <td><?php echo $row['lastname'] ?></td>
                                         <td><?php echo $row['email'] ?></td>
                                         <td><?php echo $row['streetadd'], " ", $row['city'], " ", $row['state'], " ", $row['zip'], " ", $row['prov'] ?></td>
                                         <td><?php echo $row['numb'] ?></td>
                                         <td><?php echo $row['date'] ?></td>
                                         <td><?php echo $row['time'] ?></td>
                                         <td><?php echo $row['called'] ?></td>
                                </tr>

                                  <?php } ?>
                            </tbody>
                        </table>

this is my table with mostly html but that little bit of php thats using the mysql_fetch_array function to return the values of the data in the database for displaying on the table, im using this here and cannot call it again which is why its returning false on my export function.

you can't compare by varchar
you must to compare by integer
make sure that: DATE_FORMAT(usernumdata.date,'%Y%m%d'), $b & $e are integer
eg:
DATE_FORMAT(usernumdata.date,'%Y%m%d') = 20140102
$b = 20140101
$e = 20140103

sorry for my bad english :p

i have come to find out that you cannot call this function twice on TECHNICALLY the same page. How in the world do i get around this?

Copy the data into an array first. Then use that array.

@nobita2811: DATE_FORMAT returns a string, so your comment is not quite right.

well u see the problem im having is that if i copied the data into an array with the first call of mysql_fetch_array then how would i access it from my export file, if i include the main file in the export file i get a cannot change header error, is there a way around this?

How are you calling export.php?

this is the entire html index file with the export.php call at the bottom

<?php include('header.php'); ?>

<body>

<head>
<style>
.nav {
    width: 100%;
    float: right;
    margin: 0 0 1em 0;
    padding: 0;
    border-bottom: 1px solid #ccc;
    text-align: center;  }
xl {
    list-style: none;
    width: 700px;
    margin: 0 auto;
    padding: 0; }
.nav li {
    display: inline; }

.nav a:hover{
    color:red;
}
.nav li a {
    display: inline-block;
    padding: 8px 15px;
    text-decoration: none;
    font-weight: bold;
    color: #069;
    border-right: 1px solid #ccc; }

</style>
</head>

    <div class="row-fluid">
        <div class="span12">


            <div class="container">


<br><br>
                            <form method="post">
                        <table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="example">

                        <div class="nav">
                        <div id="menu">

                            <li class="menuitem"><a href="/">Home</a></li>
                            <li class="menuitem"><a href="/search/">Search</a></li>
                            <li class="menuitem"><a href="/table/">Reports</a></li>
                            <li class="menuitem"><a href="/upload/">Gov. File Upload</a></li>
                            <li class="menuitem"><a href="/Multi_Delete/">Report Management</a></li>
                          <li class="menuitem"><a href="/admin/">Administration</a></li>

                        <br>
                    </div>
                    </div>

                            <div class="alert alert-info">

                                <strong><i class="icon-user icon-large"></i>&nbsp;Do Not Call Information From User Submitted Data</strong>

                            </div>
                            <thead>

                                <tr>

                                    <th>Employee Name</th>
                                    <th>First Name</th>
                                    <th>Last Name</th>
                                    <th>Email Address</th>
                                    <th>Customer Address</th>
                                    <th>Phone Number</th>
                                    <th>Date Added</th>
                                    <th>Time Added</th>
                                    <th>Accidental Calls</th>
                                </tr>
                            </thead>
                            <tbody>
                            <?php 
                            $query=mysql_query("SELECT * FROM usernumdata")or die(mysql_error());
                            while($row=mysql_fetch_array($query)){
                            $id=$row['id'];
                            ?>

                                        <tr>

                                         <td><?php echo $row['empname'] ?></td>
                                         <td><?php echo $row['firstname'] ?></td>
                                         <td><?php echo $row['lastname'] ?></td>
                                         <td><?php echo $row['email'] ?></td>
                                         <td><?php echo $row['streetadd'], " ", $row['city'], " ", $row['state'], " ", $row['zip'], " ", $row['prov'] ?></td>
                                         <td><?php echo $row['numb'] ?></td>
                                         <td><?php echo $row['date'] ?></td>
                                         <td><?php echo $row['time'] ?></td>
                                         <td><?php echo $row['called'] ?></td>
                                </tr>

                                  <?php }
                                   ?>
                            </tbody>
                        </table>

</form>
<form method="post" action="export.php" >

<div style="width:100%;" class = "export"> 
<div class="alert alert-info">
&nbsp
<?php echo "Export By Date e.g. From 2014-06-27 Too 2014-07-01";?>

</div>
<br>
<span style="white-space: nowrap;">

<?php echo "From:" ?>&nbsp
<input type="text" name="b" id="text" />&nbsp
<?php echo "To" ?>&nbsp
<input type="text" name="e" id="text" />&nbsp

<div class = "exportbtn"> 
<input type="submit" class="btn btn-info" value="Export to CSV file" name="export">
</div>

</span>

</div>
</form>
        </div>
        </div>
        </div>
</body>
</html>

Ok, you are posting the form to it. The easiest way is to connect and execute the query again in export.php, just as you do in the other file.

sorry but can you give me a visual, i thought i was connecting the export.php like in the other file. idk im slightly confused lol

The file (or part) you posted earlier only showed mysql_query() and not mysql_connect()

sorry that was my bad for not clearly labeling, the connect is passed through to a header file which is passed to the index file
here is the connect

<?php
mysql_select_db('numbers',mysql_connect('localhost','root',''))or die(mysql_error());
?>

would it be better to do the array in one file and pas it through to any files that might need the stored data?

i just tried taking the data from the database file and putting it all into an array file called array.php but that failed miserably i have no idea what to do on this problem :(

okay i ultimately found the root of the problem here, its entirely in this one line $sql = "SELECT * FROM usernumdata WHERE DATE_FORMAT(NOW(),'%Y-%m-%d') BETWEEN '".$b."' AND '".$e."'"; if i change that line to $sql = "SELECT * FROM usernumdata"; then my export works BUT it doesnt export by dat eit just exports all the data, i need to be able to compare the dates and export based on those dates, is there a way to do it?

DATE_FORMAT returns a string, and BETWEEN doesn't work on strings AFAIK. Have you tried using > and < instead?

i did but im so unsure of myself that i have no idea how to set this up, i have been stuck on thi sfor days im gonna go insane 0.o

this is what i tried but its prolly wrong

$sql = "SELECT * FROM usernumdata WHERE DATE_FORMAT(NOW(),'%Y-%m-%d') >= '".$b."' AND DATE_FORMAT(NOW(),'%Y-%m-%d') <= '".$e."'";

am i missing a time on the end of date? idk i feel like im just shooting in the dark

This should be alright, even if the dates are strings.

If you give me an sql dump with the table structure and some data (text file, not image), then I'll make some time to test the issue.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.