[pcinfoman]

I have an extensive website with lots of prices hard-coded into the html. Is there an article I can read or instructions somewhere that tel me how to use php to pull thos same prices from an excel spreadsheet or csv file? this way I can make one change and have it affect the site globally.

[mab]

Searching hotscripts.com and sourceforge for excel and csv does not return much! Here is one script that you can probably examine for ideas or make use of -
http://www.hotscripts.com/Detailed/28499.html

[pcinfoman]

Hmm, this looks good if I wanted to pint the contents of a csv file to the web. I am looking for something I can use to call one part of a csv file and display it on a page.

[mab]

Actually, I was playing with that code and see the following -

PHP Code:

<?php

include('onesqlcoma.php');



// define simple function to return value from "one.csv" given the value's name

function display_value($item_in){

 $result = nosql_selectAllFromWhere("one","name","=",$item_in);

 if ($record=nosql_fetch_array($result)){

 return $record[value];

 } else {

 return "value not found";

 }

}



echo "Name: item2, Value: ".display_value("item2")."<br>";



?>

Produces this output - Name: item2, Value: 11.91

The above gets and displays values from a .csv file (called one.csv in my example) with this data -
name,value
item1,12.13
item2,11.91
item3,39.21

Note, the author of that script missed the meaning of "C" in csv and used ; as a separator. To get his script to work for reading a standard MS CSV file, you need to search and replace all the quoted ";" with "," - hence my modified include filename of onesqlcoma

P.S. His code and my example assumes unique names in the file. My code would only return the 1st occurrence should there be any duplicates.

[pcinfoman]

OK, here is a question. My csv file looks like this:

Code:

item#,category,name,rental,price,sale
1840,amphibians,,Frogs,$500.00,
1841,amphibians,Frog,Call,$95.00,
1077,arachnids,Scorpion,Call,$950.00,
1081,arachnids,Tarantula,Call,$950.00,
1150,bears,"Bear, Kodiak","$7,300.00","$25,500.00",
1170,bears,Polar Bear Display,Call,"$2,500.00",
1195,bears,"Bear, Teddy",$275.00,$950.00,
1910,bears,Koala,"$2,750.00","$5,500.00",
1920,bears,Koala,$625.00,"$1,250.00",
2415,bears,Polar Bear Paws,"$1,500.00","$4,750.00",
3328,bears,"Bear Paws, Gloves",$550.00,"$2,250.00",
5575,bears,Panda,Call,"$7,750.00",
5656,bears,Dave's Bear,"$1,250.00","$9,500.00",
5665,bears,Momma Bear Suit,"$6,300.00","$18,500.00",
6225,bears,Polar Bear Head,Call,"$13,500.00",
7319,bears,Bear Character,"$3,750.00","$7,500.00"
7326,bears,Baby Bear Suit,"$5,300.00","$7,500.00"
7328,bears,Poppa Bear Suit,"$7,300.00","$25,500.00"

Now, what I want to do is take the rental value for item #5656 and place it one one spot of the page and the "sale" value for item #5656 and put it somewhere else. In addition, on the same page, I want to put in "name" somewhere else on the page.

I want to do this for each and every item# in my csv file.

How do I do this?

[mab]

The function is changed slightly to return an array with the contents of the row from the csv.

PHP Code:

<?php

include('onesqlcoma.php');



// define simple function to return row from "one.csv" given the item_no

function get_row($item_in){

 $result = nosql_selectAllFromWhere("one","item_no","=",$item_in);

 if ($record=nosql_fetch_array($result)){

 return $record;

 } else {

 return "value not found";

 }

}

$row = get_row("5656");

echo $row[rental];

echo $row[sale];

echo $row[name];



?>

[pcinfoman]

OK, a couple of stupid questions:

• What is "onesqlcoma.php"? I see a "onesql.php" file in the OneFile1.54.zip.
• Can you please break down the $result = nosql_selectAllFromWhere("one","item_no","=",$item _in); line?
  Where did ""one" come from? and "item_no"?

Can I put
<?php
include('onesqlcoma.php');

// define simple function to return row from "one.csv" given the item_no
function get_row($item_in){
$result = nosql_selectAllFromWhere("one","item_no","=",$item_in);
if ($record=nosql_fetch_array($result)){
return $record;
} else {
return "value not found";
}
}
?>

In a php included header file and then just use the following where I need the rental price to appear?

<?php $row = get_row("5656"); echo $row[rental]; ?>

Where do I tell the code where my csv file is located and what the filename is?

[mab]

The different file name was due to a modification I made to get this to read standard MS CSV files -

Note, the author of that script missed the meaning of "C" in csv and used ; as a separator. To get his script to work for reading a standard MS CSV file, you need to search and replace all the quoted ";" with "," - hence my modified include filename of onesqlcoma

From the author's code/documentation - "one" is the filename portion of the csv file. From the comment in my sample code -

// define simple function to return row from "one.csv" given the item_no

Item_no is the column heading from the csv. You listed item#, but I suspect the "#" would cause a problem at some point in time.

Also from the author's code/documentation - this function selects all the rows where the values in the item_no column = the passed variable $item_in

The answer to the next two questions are yes.

The filename is discussed above, you can change this or make use of a variable. The default location would be the same folder as your script, but you can add a path if it is located somewhere else.

If you are essentially building a table, the following would be more efficient then the above method -

PHP Code:

<?php

include('onesqlcoma.php');



$result = nosql_selectAllFrom("one");



echo "<table border = 1><tr><td>Item Name<td/><td>Rental Price<td/><td>Sale Price<td/><tr/>";

  while ($record=nosql_fetch_array($result)){

    echo "<tr><td>$record[name]<td/><td>$record[rental]<td/><td>$record[sale]<td/><tr/>";

  }

echo "<table />"; 



?>

The above code produces the output in the attached image -

[pcinfoman]

Originally Posted by mab

From the author's code/documentation - "one" is the filename portion of the csv file. From the comment in my sample code. The filename is discussed above, you can change this or make use of a variable. The default location would be the same folder as your script, but you can add a path if it is located somewhere else.

Can you tell me how to build a $filename variable?. Is that something like

$filename = /secured_path/filename.csv

Originally Posted by mab

Item_no is the column heading from the csv. You listed item#, but I suspect the "#" would cause a problem at some point in time.

You are right, I just changed that.

Originally Posted by mab

If you are essentially building a table, the following would be more efficient then the above method -

PHP Code:

<?php
include('onesqlcoma.php');

$result = nosql_selectAllFrom("one");

echo "<table border = 1><tr><td>Item Name<td/><td>Rental Price<td/><td>Sale Price<td/><tr/>";
while ($record=nosql_fetch_array($result)){
echo "<tr><td>$record[name]<td/><td>$record[rental]<td/><td>$record[sale]<td/><tr/>";
}
echo "<table />";

?>

OK, your latest code works great. However, it seems to be designed to print the entire contents of the csv file. I can see a use for that soon, but not yet. I will even have a need to print only those items in a particular category. But for now, I need to pull individual parts from the csv as needed.

When I use the following code

PHP Code:

<?php
include('includes/onesqlcoma.php');

// define simple function to return row from "one.csv" given the item_no
function get_row($item_in){
$result = nosql_selectAllFromWhere("ami_productlist","item_no","=",$item_in);
if ($record=nosql_fetch_array($result)){
return $record;
} else {
return "value not found";
}
}

?>

<b>Item #:</b> <?php $row = get_row("5656");echo $row[item_no]; ?><br>
<b>Daily Rental #:</b> <?php $row = get_row("5656");echo $row[rental]; ?><br>
<b>Sale Price:</b> <?php $row = get_row("5656");echo $row[sale]; ?><br>
<b>Item Name:</b> <?php $row = get_row("5656");echo $row[name]; ?>

I get the results of.

Item #: 5656
Daily Rental #: $1
Sale Price: $9
Item Name: Dave's Bear

Why is it not showing the full rental price of $1,250.00 and sale price of $9,500.00 like it is in my csv file?

Also, when excel exports out to a csv file, there are quotes in the file, how do I get the php to strip them out? Or is that not important?

[mab]

For the file name, use something like this - $filename = "/secured_path/filename";

Where "one" appears now, change this to $filename

The author's code appends the .csv so your file name variable does not include it.

Only the price up to the , is printed, as the , is the field delimiter. The authors code is not sophisticated enough to ignore , located within quoted data. You will need to either change the number format in the csv to eliminate the , in the price or modify the author's code.

For removing the " on the display, change this function near the bottom of the author's include file -

PHP Code:

function _getLineArray($filename)

    {

    $f = fopen($filename, "rb");

    flock($f,LOCK_EX);



    fseek($f,0,SEEK_END);

    $filesize = ftell($f);

    fseek($f,0,SEEK_SET);

    $dbVar = trim(fread($f,$filesize));

    flock($f,LOCK_UN);

    fclose($f);

    $dbVar = explode("\n", $dbVar);

      $dbVar = str_replace("\"", "", $dbVar); <---- ADD THIS LINE ------------

    return $dbVar;

    }