phpmyadmin issue

bufhal

Hope I can get some help on this.
Here is what is happening: I upload a text file-comma-delimited-with first column blank..then all columns seperated with commas and the end of record which is ended with a semi-colon-and it looks good in phpmyadmin. Auto-increment works and all the info uploads, however, when queried from the MYSQL databse, don't get returns. However, if I manually insert records one at a time, the queries work.

rachel2004

I don't use phpmyadmin myself, but from what you're describing, you may want to check out the syntax that you used for your delimted file:

http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html

When skipping columns, I have found the keyword NULL (with no quotes, otherwise it is treated like a string) to be good in places where the table is expecting an auto_increment value.

bufhal

thanks Rachel;
can you tell me why I have old values in my dropdown menu from the mysql database even after changing the data and deleting my cache and temp files?
You mentioned you do not use phpmyadmin-is there an easy way for a newbie to upload records to a mysql database? I have ssh access with my hosting co for an extra fee, but I am not a "shell sql guru"
Any feedback is appreciated...
bufhal

minuteman

Have you tried as a txt file? Are you using the insert feature of myadmin? To locate: lick on "structure" in the top nav bar and look at the bottom of the page., browse to your file, and run it.

Try adding the item numbers manually to see if having the number field blank is the problem?

rachel2004

You had a couple of questions. Let me see...

Regarding the old values in your drop down menu: I couldn't begin to help you with that without looking at your code and seeing how you have your table(s) set up. If you post it and if it's not too much code, I may have time to look at it. Or someone else could also lend a hand too.

As foryour second question, okay, good, you have SSH access to your hosting server. I have been working with MySQL for several years and I have not once used phpMyAdmin or one of the other GUI products. That does not mean that I do not think they are good products, but what I mean to say is that you can accomplish just about everything via the MySQL monitor (via SSH or Telnet).

Okay, so if you want to load records from a file (rather than do it one record at a time), my choice is to use the LOAD DATA INFILE command. It's not difficutl to do, but I remember when I first used it a few years ago, I had to play around with the syntax. So if at first you do not succeed, just play with it some more.

The basic steps:

Read the link to the MySQL manual that I supplied to you so you know how to create your "file" to be read by MySQL.
Upload that file to a location on your web server.
Using SSH, connect to your MySQL server and select your db.
The general command is something as follows:

LOAD DATA INFILE "/path/to/my/file/file.txt" INTO table_name FIELDS TERMINATED BY "," ENCLOSED BY "\"" LINES TERMINATED BY '\n' (column1, column2, column3);

Now, before you try this, you NEED to look at the MySQL manual because you may be using different things to delimit your file. In my example above, I am using a comma to delimit my values.

This may seem like a bit of an effort to do, but if you have a lot of records to enter, this is the way to go. The other benefit of all of this is once you get it working, you will have your file "file.txt" (or whatever you're calling it) and if you want to load a second database with identical records, you can immediately do so. Something to give thoughts to in case you want to leave your hosting company.

You may want to install MySQL on your home computer and try doing all of this so you don't overwrite anything on your live db server or end up INSERTing records multiple times.

bufhal

Thank you both. Rachel..I have printed out your instructions and will look into them. Thank you very much for your excellent insight.

Minuteman-I have indead done the steps you mentioned--used insert-and it uploads fine-but my webpage dropdown menu querys only work when I manually insert. They do not work when I upload the txt file-even with the comma delimited and first column empty..
Thank you

minuteman

It sounds like a syntax problem in your query in the php page. Look at this snippet:
$sql1 = "SELECT * FROM mdl_university order by '$srtdata'";
$result1 = @($sql1, $conn) or die("Couldn't execute 'Continue addition' query1 ".$sql1);
while ($row = mysql_fetch_array($result1))
{

$university_num =$row['university_num']; Notice the Couldn't execute 'Continue addition' query1 ".$sql1 . Particularly notice the .$sql1. What that will do is print out the sql query code used if it dies without executing. Then what you do when it does that is copy the query and bring it over to myadmin and paste it into the sql query box. More than likely it will not connect their either, but myadmin will give you some instruction on where the error is occuring. It may be an apostrophy, a comma, whatever (those whatevers are buggers :evilgrin: ) Once you get your query working copy it back to your php code and it should work.

bufhal

Thank you for your suggestion. Here is the index.php page. I can post the results.php page if need be. Can you point out and show me where to make the changes?

<?php 
// Connection to the db server and select active db 
$SQLlink = @mysql_connect("su.com", "bufhal" , jes"); //creates a connection

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

if (!mysql_select_db("bufhal", $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>";
?>

minuteman

Re: // perform query $data = mysql_query("SELECT date, agency, city FROM agencies");

Copy and paste "SELECT date, agency, city FROM agencies" into your sql window in MyAdmin (click the sql tab in the upper nav bar or use the link at the bottom of the left nav bar where the table list is).

This process double checks to make sure your query is correct. If it works as a sql query in myadmin then there is another coding issue.

Is date, agency, city in the proper order and spelled correctly? The word agencies might need single quotes around it

tekky

another thing you can do with phpmyadmin is to BROWSE your table you are loading from the Comma delimited file....

see if its actually interpretting the columns as you intend them to be... I bet its not....

and if this is the case....

10 to 1 you can have a simple (very simple I mean) php script parse the file into the database for ya....

bufhal

Thank you. I tries the sql query: SELECT date, agency, city FROM agencies
and the only record was returned. Itr must be another coding issue. Do you see any problems in the index.php code? You mention the word agencies might need single quotes--can you point out where. Thanks

minuteman

Well, the next thing I would try is to place echo statements behind some of the variables as they parse. For example, where there is something like $dates = Array_Unique($dates);

add
echo 'line # dates = ", $dates; ?><BR><?

this will give you a print out of the value at that particular point in the script. Add one of those at different locations to track the variable in the script.