Querying a csv array

PHP
1

Hi,
so i might be barking up the wrong tree but i am just designing my table and want to know if this would work and how to query it.

So basically we have a table of fish and seasonality dates and i want to return recipes for fish that are in season.
There may be a split season eg Jan-Apr, Sep-Oct so i was thinking of putting the seasonality in a single field with comma separated dates eg start,end,start,end

If i wanted to return any fish in feb for example i would want a query along the lines of

select * from fish where seasonality >‘20140201’ AND seasonality <'20140301

But i don’t think that would work with it being an array.
Is my concept wrong? or is there a way of doing this like this?

The only other way i can think is 4 columns Start1,End1,Start2,End2 but then that would also be a bit messy as not all the 2nd start/end would be used.

any thoughts/examples would be much appreciated.

2

Hi,

Check this example for converting MySQL query to CSV in PHP

SELECT * INTO OUTFILE "c:/mydata.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM my_table;

check this documentation : http://dev.mysql.com/doc/refman/5.0/en/select.html)

or:

$select = "SELECT * FROM table_name";

$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );

$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
    $header .= mysql_field_name( $export , $i ) . "\t";
}

while( $row = mysql_fetch_row( $export ) )
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}
$data = str_replace( "\r" , "" , $data );

if ( $data == "" )
{
    $data = "\n(0) Records Found!\n";                        
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=your_desired_name.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";

Hope this helps.

3

ah sorry i think i’ve confused you. I don’t want to output as a csv (probably shouldn’t have called it a csv) i want to be able to query a date within a field that contains an array and return the result to a webpage.

Thinking about it the query would have to be able to search the start date which would be an even number in the array [0] and end date which would be an odd number[1], and again for any further start/end in the array … start[2] end [3].

But it would have to only search start and end within each ‘set’ [0][1] or [2][3] or it could pick up a start date from the first[0] and end date from the last[3], which would be wrong.

does that make any sense ?!

4

Hi Noppy,

I’m a little bit confused as to where the CSV/array part comes into it, but as far as table design and queries go, for the information you describe I’d do something like this:

TABLE: fish
id
name
etc

TABLE fish_season
id
fish_id
start_month ← digit representing the month
end_month ← digit representing the month

Then if we want to query for fish that are in season in February, we can do this:

SELECT * FROM fish
JOIN fish_season ON fish.id = fish_season.fish_id
WHERE fish_season.start_month <= 2 AND fish_season.end_month >= 2
GROUP BY fish.id

EDIT: Just seen your reply to vembutech - I’d advise against storing multiple values in a single column, it’s much harder to work with!

5

thanks thats what i would normally do but i have the problem of some fish having an additional season.
so would it be easier to just add

start_month2
end_month2

for fish with 2 seasons.

just thought it was neater as a comma separated array. but perhaps over complicating things :smile:

6

For fish with two seasons they’d just have two entries in the fish_seasons table.

Obviously if all fish only had a single season, you’d put all the data into a single table. As you say, you could just add extra fields to the fish table, but it’s considered bad practice to have fields that only apply to some rows, which is why it’s a good idea to normalize the design and pull that data out into its own table.

1 Like
7

CSV files cannot be traversed with SQL. You’ll have to load the CSV file into an SQL database. You can use SQLlite in memory mode for this purpose, but it will be slow. If you are working with the data of that file consistently, it needs to be in a db.

Note that you could read the file into an array with

$array = str_getcsv(file_get_contents($file));

Then use the array sorting functions to find what you are looking for, but this isn’t as flexible as a query.

8

Also the old mysql_* extension is deprecated as of version 5.5 of php

9

Ah ok so i will need another table for the entries.
I was trying to keep it in the same table as the table is never likely to be more than 1000 records so whilst not optimised for a million records it’s fast enough for the job. But by the sounds of it it will be better as a separate table.

The source database is in excel and is managed by someone else so for ease of them maintaining that i think the excel table will have the four columns added. When i upload i’ll just separate out into the fish and seasonality tables.

thanks for all the replies it’s really helped

10

It would be far more efficient to get the data ported into a CMS and then have the manager use the cms for updates, not excel.

11

agreed. But they do a lot of calculations on the data in excel. We have tried in the past and got into a silly situation where someone would update one record via the cms and then we’d later do a complete upload (couple of times a year) and overwrite the change, as it wasn’t changed in the excel version.

Whilst not perfect it does seem to be a lesser of two evils right now as we treat the excel version as the master and just upload as and when needed.

thanks

12

I sincerely doubt those calculations cannot be implemented in SQL.

13

@Noppy If the calculation formulas rarely change then just like @Michael_Morris pointed out these could be implemented in SQL or, additionally, in code then switch over to using the CMS as the master.

14

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.