uploading text files

How to use phpmyadmin.... phpmyadmin related arguments. Even phpMyAdmin issues

Moderators: macek, egami, gesf

bufhal
New php-forum User
New php-forum User
Posts: 37
Joined: Tue May 04, 2004 5:11 pm

uploading text files

Postby bufhal » Wed Jun 30, 2004 2:12 pm

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

User avatar
ruturajv
php-forum Super User
php-forum Super User
Posts: 1280
Joined: Sat Mar 22, 2003 9:42 am
Location: Mumbai, India
Contact:

Postby ruturajv » Thu Jul 01, 2004 7:58 pm

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

bufhal
New php-forum User
New php-forum User
Posts: 37
Joined: Tue May 04, 2004 5:11 pm

data in MySQL but querys do not work

Postby bufhal » Fri Jul 02, 2004 5:20 am

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
New php-forum User
New php-forum User
Posts: 37
Joined: Tue May 04, 2004 5:11 pm

is there a way to refresh the db?

Postby bufhal » Fri Jul 02, 2004 6:39 am

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
New php-forum User
New php-forum User
Posts: 37
Joined: Tue May 04, 2004 5:11 pm

Postby bufhal » Fri Jul 02, 2004 9:06 am

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?

User avatar
ruturajv
php-forum Super User
php-forum Super User
Posts: 1280
Joined: Sat Mar 22, 2003 9:42 am
Location: Mumbai, India
Contact:

Postby ruturajv » Sun Jul 04, 2004 8:12 pm

bufhal wrote: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
New php-forum User
New php-forum User
Posts: 37
Joined: Tue May 04, 2004 5:11 pm

Postby bufhal » Mon Jul 05, 2004 5:44 am

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
New php-forum User
New php-forum User
Posts: 37
Joined: Tue May 04, 2004 5:11 pm

Can you take a look?

Postby bufhal » Mon Jul 05, 2004 12:58 pm

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

User avatar
ruturajv
php-forum Super User
php-forum Super User
Posts: 1280
Joined: Sat Mar 22, 2003 9:42 am
Location: Mumbai, India
Contact:

Postby ruturajv » Thu Jul 08, 2004 8:23 am

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

bufhal
New php-forum User
New php-forum User
Posts: 37
Joined: Tue May 04, 2004 5:11 pm

Postby bufhal » Thu Jul 08, 2004 8:35 am

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-

User avatar
ruturajv
php-forum Super User
php-forum Super User
Posts: 1280
Joined: Sat Mar 22, 2003 9:42 am
Location: Mumbai, India
Contact:

Postby ruturajv » Sun Jul 11, 2004 7:55 pm

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
New php-forum User
New php-forum User
Posts: 37
Joined: Tue May 04, 2004 5:11 pm

cannot add records to existing

Postby bufhal » Wed Jul 14, 2004 11:26 am

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:

User avatar
ruturajv
php-forum Super User
php-forum Super User
Posts: 1280
Joined: Sat Mar 22, 2003 9:42 am
Location: Mumbai, India
Contact:

Re: cannot add records to existing

Postby ruturajv » Thu Jul 15, 2004 12:12 am

bufhal wrote: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
New php-forum User
New php-forum User
Posts: 37
Joined: Tue May 04, 2004 5:11 pm

Postby bufhal » Thu Jul 15, 2004 5:09 am

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


Return to “phpMyAdmin”

Who is online

Users browsing this forum: No registered users and 1 guest