Reading a value and outputting two corresponding values.

[Jeremy_H]

I have a large excel spreadsheet I created with three columns, and about 50,000 rows.

What I'm looking to do is to take an input provided by the website visitor, and find this value is the first column.

Then I need to read the two corresponding values in the same row that are in Column 2 and 3, and output them into a variable on the persons next page.

Is this something that is able to be done?

I'm totally new to this world of databases, but from my understanding, since my pages are built in PHP, and since I'm alloted ten free MySQL databases, then MySQL is probably the direction I should move into?

Will MySQL do something like this? And how might I move the content from my spreadsheet over into the MySQL database in the first place?

Thanks for any help.

[volatilegx]

First, you would export the data from your spreadsheet into a comma delimited text file.

Then, you would create a php script that would read the text file and perform insert queries to the MySQL database on the data.

Here's some basic PHP code:

$input = array();
$input = $file('commadelimitedfile.csv');
foreach ($input as $line){
    $line = trim($line);
$linearray = array();
$linearray = explode(","$line);
$query = "INSERT INTO tablename (Field1, Field2, Field3) VALUES ('".addslashes($linearray[0])."','".addslashes($linearray[1])."','".addslashes($linearray[2])."')";
$result = mysql_query($query);
if (!$result){
  // print some kind of error message
} else {
  // print a success message
}
}

[volatilegx]

Then, to get the info out of the MySQL database, you would run a query like this:

$query = "SELECT * FROM tablename WHERE Field1 = '".addslashes($parameter)."' LIMIT 1";
$result = mysql_query($query);
while ($obj = mysql_fetch_object($result)){
 $Field2 = stripslashes($obj->Field2);
 $Field3 = stripslashes($obj->Field3);
}

[Jeremy_H]

Your awesome volatilegx!

I've been busy with school in the past couple of days, so I haven't had a chance to give it a try, but spring break is right around the corner, and I've been making a long website to-do list, and trying this out is very high on that list.

Some people go on holiday. I type code. Ehhh, I figure it's better for my skin.

[Jeremy_H]

Thank you for your help.

When I placed a few variables into the table by hand, I was able to pull them out, and use them in a page. Awesome!

But I'm having some difficulties uploading the file to the table. And at tens of thousands, doing it by hand just isn't practical.

I'm getting an error with the following code. And as I go through the code, I think I understand why, but I don't know how else it should be.

With this code:

$input = array();
$input = $file('zip.csv');

foreach ($input as $line){
$line = trim($line);
$linearray = array();
$linearray = explode(","$line);
$query = "INSERT INTO zip (zip, city, state) VALUES ('".addslashes($linearray[0])."','".addslashes($linearray[1])."','".addslashes($linearray[2])."')";
$result = mysql_query($query);
if(!$result){echo("error"}else{echo("success")}
}

I get an unexpected T_VARIABLE parse error. I'm guess this is because I never really define what exactly $line is? (It always seems to use the variable on the same line when trying to define it.)

Might anybody have any ideas how I can alter this so I can upload values from the csv file?

Also, I'm assuming the csv file goes in the same directly as the php script, so it can read it. Then once its in the database, I can removed the csv file?

Thanks for any help!

[volatilegx]

I never really define what exactly $line is?

You define $line in the foreach statement.

Here's a problem

$linearray = explode(","$line);

should be:

$linearray = explode(",",$line);

Another problem:

if(!$result){echo("error"}else{echo("success")}

should be:

if(!$result){echo "error<br>\n"}else{echo "success<br>\n"}

Teach me to write code on the fly with no testing.

You didn't mention the line number the error was found on, so that makes it more difficult to track down the exact error, though.