uploading text files

[bufhal]

Hi;
can someone offer some insight on uploading text files to phpmyadmin for a MySql database?
I have 12 fields-the first is "id" which I leave blank for auto-increment. When I upload a text file with a few records, all looks ok in phpmyadmin, however, the queries don't work on my web page. However, when I "insert" the data one record at a time manually, the results display.
Any ideas?
Thank you for any insight..
bufhal

[ruturajv]

code please... ?
the webpage where it is not showing results...

[bufhal]

Thank you-
Here is the main webpage, index.php

Code: Select all

<?php
// Connection to the db server and select active db
$SQLlink = @mysql_connect("su", "wnyaic", "jes"); //creates a connection
if (!$SQLlink)
  Die("Couldn't connect to the db server."); // display error message on error
if (!mysql_select_db("bu", $SQLlink))
  Die("Couldn't access database.");          // display error message on error
// perform query
$data   = mysql_query("SELECT date, agency, city FROM agencies");

$agencies = Array();
$dates = Array();
$cities = Array();

while($row = mysql_fetch_array($data)) {     // assign results into arrays
  $dates[]    = $row["date"];
  $agencies[] = $row["agency"];
  $cities[]   = $row["city"];
}

$dates = Array_Unique($dates);               // remove duplicate values
$agencies = Array_Unique($agencies);
$cities = Array_Unique($cities);

Sort($dates);                                // sort arrays
Sort($agencies);
Sort($cities);

$date_out   = "<select name='date'   onchange=\"window.open('results.php?action=date&value='+this.value, 'agencyWin', 'location=yes,left=20,top=20');\">";
$agency_out = "<select name='agency' onchange=\"window.open('results.php?action=agency&value='+this.value, 'agencyWin', 'location=yes,left=20,top=20');\">";
$city_out   = "<select name='city'   onchange=\"window.open('results.php?action=city&value='+this.value, 'agencyWin', 'location=yes,left=20,top=20');\">";

$date_out   .= "<option>-- select date ---</option>";

$agency_out .= "<option>-- select agency ---</option>";

$city_out   .= "<option>-- select city ---</option>";

forEach ($dates as $value)
  $date_out   .= "<option value='$value'>$value</option>";

forEach ($agencies as $value)
  $agency_out .= "<option value='$value'>$value</option>";

forEach ($cities as $value)
  $city_out   .= "<option value='$value'>$value</option>";
$date_out   .= "</select>\n";
$agency_out .= "</select>\n";
$city_out   .= "</select>\n";

echo $date_out."";
echo $agency_out."";
echo $city_out."<BR>";

?>

Here is the results popup:

Code: Select all

<?php

if (!IsSet($action) || !IsSet($value))       // check if both vars are set
  Die("Both vars must be set");
if (Trim($value) == "")                      // check if value is non-blank
  Die("Value can't be left blank");
if ($action != "date" && $action != "agency" && $action != "city")
  Die("Unknown action requested");
// Connection to the db server and select active db
$SQLlink = @mysql_connect("su", "bu", "jes"); //creates a connection

if (!$SQLlink)
  Die("Couldn't connect to the db server."); // display error message on error

if (!mysql_select_db("bu", $SQLlink))
  Die("Couldn't access database.");          // display error message on error
// escape data from user
if (ini_get('magic_quotes_gpc')) {           // unescaping data if needed
  $value = StripSlashes($value);
}
$value = mysql_escape_string($value);        // escaping data for MySQL db

$data  = mysql_query("SELECT * FROM agencies WHERE $action = '$value'"); // perform a query

if (!$data)
  Die(mysql_error());                        // display MySQL error message on error

$agencies = Array();

while($row = mysql_fetch_array($data)) {
  $agencies[] = $row;
}

$output = "<table border=1>\n";
if (mysql_num_rows($data) == 0) {            // in the case of no results found - display alert message
  $output .= "<tr><td colspan=4>No results found</td></tr>";
 
} else {
    $output .= "<tr>";
    $output .= "<td align=\"center\"><b>Date</b></td>";
    $output .= "<td align=\"center\"><b>Agency</b></td>";
    $output .= "<td align=\"center\"><b>City</b></td>";
    $output .= "<td align=\"center\"><b>Time</b></td>";
    $output .= "<td align=\"center\"><b>Day</b></td>";
    $output .= "<td align=\"center\"><b>Location</b></td>";
    $output .= "<td align=\"center\"><b>Building or Room</b></td>";
    $output .= "<td align=\"center\"><b>Street</b></td>";
    $output .= "<td align=\"center\"><b>Zip</b></td>";
    $output .= "<td align=\"center\"><b>Phone</b></td>";
    $output .= "<td align=\"center\"><b>Contact</b></td>";
    $output .= "</tr>";
  forEach ($agencies as $agency) {           // display row for each result (eg. you can have more agencies in one town)

    $output .= "<tr>";
    $output .= "<td align=\"center\">".$agency["date"]."</td>";
    $output .= "<td align=\"center\">".$agency["agency"]."</td>";
    $output .= "<td align=\"center\">".$agency["city"]."</td>";
    $output .= "<td align=\"center\">".$agency["time"]."</td>";
    $output .= "<td align=\"center\">".$agency["day"]."</td>";
    $output .= "<td align=\"center\">".$agency["location"]."</td>";
    $output .= "<td align=\"center\">".$agency["building_room"]."</td>";
    $output .= "<td align=\"center\">".$agency["street"]."</td>";
    $output .= "<td align=\"center\">".$agency["zip"]."</td>";
    $output .= "<td align=\"center\">".$agency["phone"]."</td>";
    $output .= "<td align=\"center\">".$agency["contact"]."</td>";

$output .= "</tr>\n";
  }
}

$output .= "</table>\n";

echo $output;

?>

[bufhal]

Hi ruturajv;
I do not know why phpmyadmin is giving me such problems. I know I am new but this does not seem user-friendly.
Although I delete and upload a new index.php and delete history and cache, I still have old values in my dropdowns.
Is there a way to refresh the db?
Here is my table structure so you can see it next to the code.

id tinyint(4) No auto_increment
date varchar(12) Yes NULL
agency varchar(20) Yes NULL
city varchar(15) Yes NULL
time varchar(12) Yes NULL
day varchar(15) Yes NULL
location varchar(25) Yes NULL
building_room varchar(25) Yes NULL
street varchar(25) Yes NULL
zip varchar(12) Yes NULL
phone varchar(15) Yes NULL
contact varchar(15) Yes NULL

Thank you,
bufahl :sad:

[bufhal]

Hi
I got the querys working on the webpage perfectly-but only when I manually insert each record with phpmyadmin. When I upload a text file, the query's do not work, although they appear in my table.
Any ideas, ruturajv?

[ruturajv]

Hi
I got the querys working on the webpage perfectly-but only when I manually insert each record with phpmyadmin. When I upload a text file, the query's do not work, although they appear in my table.
Any ideas, ruturajv?

Hi,
can you show us what is the text file you are referring to ?

[bufhal]

Here is a single record. The first column is empty to allow for auto-incremment.

, 2/18/2005 , Univera , North Tonawanda ,2-4 pm , Tues , Gary's Senior Citizen , Room 12 , 51 Cry Rd , 14221 , 542-6633 , Bill Roach ;

If there is a format for txt files that you know of that are phpmyadmin-friendly, please let me know..
Thanks

[bufhal]

Hi Ruturajv:
This is the response I received from the hosting company rregarding my inability to upload text files via phpmyadmin to MySQL db.

"The problem is in the inappropriate input file format (the txt file) - there are spaces before and after the comas.
To solve the problem - please strip the spaces arround the comas. "
Can you point me in the right direction to solve this problem? Can I edit index.php to automatically strip the spaces?
Thank you

[ruturajv]

instead of changing the phpmyadmin page,
try modifying your text file, does removing spaces help in phpmyadmin ?
If it does,

[bufhal]

I have my web application working perfectly. The last thing is exporting csv from excel to a format that can be uploaded to mysql via phpmyadmin EASILY. I need to leave the first column empty for auto-increment. Thanks for your reply-

[ruturajv]

for importing, remove the first comma

in the query, use the field names where you want to insert the values,

or you can give empty quotes like '' in the first field

and while exporting, seems you can make your own script, as phpmyadmin gives the auto increment id as well,

otherwise you have to manually reomve the auto id, use editplus, and simple regex to remove it. that would be fast enough

[bufhal]

Hi Thank you for the replies.
I can now upload 150 records with the first column empty except col 1 row 1 has a "1".(for auto-increment). I try to add on to the existing file with anoth file with just a few records and same format (except I put "" instead of the primary num 1), and the second file will not upload.
What do I have to do to be able to add to existing files? This is a .csv file I have exported from Excel-I want to use this because the people can send in this easy format.
Thank you, ruturajv
bufhal :grin:

[ruturajv]

What do I have to do to be able to add to existing files? This is a .csv file I have exported from Excel-I want to use this because the people can send in this easy format.
Thank you, ruturajv
bufhal :grin:

hi I did not get you ?

[bufhal]

Hello;
I have uploaded a .csv file (127 records) by placing a "1" in the first column and row to allow auto-increment. Now I cannot add records with the same process( I used "" instead of a 1 because 1 is primary and cannot be used again. No files will upload and add on.
Thanks