MySQL, PHP, and other questions

noob needing help answering some basic questions

[robbiesqp]

Hello all,

I am a college student studying meteorology and oceanography at LSU. I am currently working this summer as an intern at a company that does various contracting jobs. The company is currently developing a fleet of "river drifter" buoys that will help to track the movement of the oil that has been released due to the disaster in the Gulf, which I am sure you have heard about.

I have been given some projects this summer that are a bit over my head and thought I would try to get some help here. I thought that I would be working in a more atmospheric science capacity this summer, rather than computer science. However, I am very happy to learn whatever I can about programming and web developing.

Luckily, I took a C++ course this past semester at school, so I have some basic knowledge with that. I have also used basic HTML stuff before. I am just having trouble understanding what it is I'm supposed to be doing here at work. The people here are all engineers. They are very helpful, but I think they just assume I have the same knowledge of PHP and databases that they do. I really just have no clue what they're talking about and if I ask them for help understanding what I'm supposed to be doing, they just explain in terms I cannot grasp, since I'm more used to weather!

I am good at working in the terminal in Linux (Ubuntu) to navigate around in there. I have been using Kate to modify php scripts and all that.

So last week this guy at work helped me create a database on phpMyAdmin. I created some different tables in there and it was pretty straightforward.

Now, I have been given the task of "successfully connecting to the MySQL database and getting some query results using PHP." If I am successful in getting that to work, I will then be "working on parsing the data files and creating the stand-alone buoy deployment table." Finally, I will be "making relational links to other tables".

The quotes denote actual quotes from an e-mail from my co-worker telling me what to work on. The problem is, I have no idea what any of that means. Forgive me, but I am the ultimate noob here. I am absolutely willing to learn how to do this stuff, I just do not know what it is I'm supposed to be doing!

So if anyone could explain this stuff in terms a guy like me, with no background in this stuff, could understand, I would be very appreciative. I am certainly not asking anyone to do my work for me, I just need a little push in the right direction. Like I said, the people here are nice, but they are just so skilled in this stuff already that I'm not sure they know how to explain it in very basic terms.

Thank you in advance and I hope this post was not too long!

-Robbie

[daveVk]

successfully connecting to the MySQL database and getting some query results using PHP

You have created some sql tables and probably put some test or real data into it.

You need make this data viewable over the network via a web browser.

working on parsing the data files and creating the stand-alone buoy deployment table

Work out how to get real data into sql table.

making relational links to other tables

If for example there is an existing buoy table, and the pink buoy is index 6 in that table, use '6' in you table to refer to it.

Welcome

[robbiesqp]

You have created some sql tables and probably put some test or real data into it.

You need make this data viewable over the network via a web browser.

Hey, Dave! Thanks for the response.

So to make the data viewable over the network, I would use php code that would "query" the data? I have looked at numerous online tutorials and things.

How do I go about connecting to those tables in phpMyAdmin. Do I use something similar to this (found this in another thread on webmasterworld.com):

 <?php
//you have to connect to mysql first. Your mysql connection will have a username and password.
mysql_connect("localhost", "your_username", "your_password") or die(mysql_error());

//you have to enter the name of your database.
mysql_select_db("your_database_name") or die(mysql_error());

//"websites" needs to be the name of your table
$result = mysql_query("SELECT * from websites")
or die(mysql_error());

//this will loop printing out every row of the table "websites"
while($row = mysql_fetch_array($result))
{
//when inserting data, specify the title of the column using the $row[''] command.
//$row['this_needs_to_be_the_title_of_the_column_you_are_referencing'];

//example lines
echo "Date Added: ".$row['web_date']."<br />";
echo "Website Type: ".$row['web_type']."<br />";

}
?> 

Am I headed in the right direction if I were to attempt to use that code to make this data viewable over the network?

In this line, would I need to change the localhost to "phpMyAdmin"?

mysql_connect("localhost", "your_username", "your_password") or die(mysql_error());

Or would that be done here?

mysql_select_db("your_database_name") or die(mysql_error());

Do I even need to specify that the data is on phpMyAdmin or does the computer already know which SQL database I'm using? I hope my question makes sense.

Thanks again for your help!

[SteveWh]

As someone who's been working on a similar task for the past month, I might be able to provide some tips that will get you oriented to what you're doing. I think you'll find the linux environment easier for these tasks than Windows.

While MSAccess is an all-in-one db app that provides its own user interface, MySQL is just a database server, so you need to "connect" to it somehow, using an external user interface, so you can send it commands and get back the results.

phpMyAdmin is one, somewhat clunky, GUI interface, but there are other interfaces you can use.

For experimenting with MySQL commands, the "MySQL Command Line Client" is good. It is likely installed on your PC if the MySQL server is. In Ubuntu Terminal or Windows command prompt:

mysql -uYOURMYSQLUSERID -p

It will prompt for your mysql password. Then you get a mysql command prompt where you enter SQL commands for it to execute. The results are presented in text mode.

Another good interface is the MySQL Query Browser, a GUI in both Linux and Windows, also likely installed on your PC with MySQL.

What your task will involve is:

1) Create HTML (and PHP) web page with form(s) where your user will enter the search terms for the things they want to see from the database. They will "submit" their form data from the web page. The data will go back to the server. Your web page will have a PHP forms handler in it (like the code snippet you posted).

2) PHP has built-in commands that it will use for connecting to MySQL, sending it commands, getting results, and then building a result page with the results on it, which it will send back to your user.

The script you posted uses the "mysql" extension of PHP. The object oriented "mysqli" extension methods are better, more secure, and more modern.

So you can, but don't have to, use phpMyAdmin to set up the database. You could also use the MySQL client. You can also create batch ".sql" scripts that you can run from the client. Very handy.

For the rest of your tasks, though, phpMyAdmin isn't involved. It's just an administrative tool.

In Ubuntu, you probably have very good manuals installed for both PHP and MySQL. They'll be somewhere in /usr/share/doc. With Apache installed, I go to the manuals from http: // localhost / doc / (remove spaces). But you can also search thru the massive quantities of files in /usr/share/doc/. When you get to the PHP or MYSQL section you need, look for the file index.html and launch it in a browser. These are great manuals, and easier to read and navigate than the PDFs that come with the Windows installations.

So MySQL is a database server where your database will be stored.
PHP is the web-page creating language that you use for building your web page AND for doing the behind-the-scenes code that connects to the database, passes the user's query to it, and presents the result back to the user on another web page.

The quicker you can stop having to use phpMyAdmin as your means of working with the database, the better. Just my opinion.

mysqli example code in the online version of the PHP manual:
[php.net...] .

The online version of the MySQL manual:
[dev.mysql.com...]

-----

As an afterthought, you might not *have* to use PHP to make this data available on the network. If you create a separate MySQL user who has NO rights to alter the database tables, only to query them (i.e. read-only), then they could use their own MySQL client (Terminal) or Query Browser (GUI) to connect to MySQL on the central machine where the db is stored.

The downside is that the users will have to know how to use SQL commands. If your users are the engineers you're working with, that might be just fine, but if your users will be less knowledgable people, you will probably have to create the web pages for them to use. They can enter the search terms on the web page, and submit them, and it will be your job, using PHP code in the forms handler, to translate their requests into properly formed SQL queries, send the queries to MySQL, get the result, and put the results on a new web page to send back to them.

-----

Section 3 of the MySQL manual (see link) is a Tutorial for working in command mode. I'd suggest that be your first task, to become comfortable working in command mode. Once you're writing your code in PHP, it will all be the same SQL commands that you use in command mode, no GUI interface or assistance of any kind, so you need to be comfortable working with SQL commands.

[rocknbil]

Welcome aboard robbiesqp, here's another link [w3schools.com] to get your feet wet and get a little bit under your belt. Run through the tut's and do the exercises.

[daveVk]

Your approach looks good to me.

How do I go about connecting to those tables in phpMyAdmin

If you created the tables on your own machine, then use localhost. If you go back into phpMyAdmin, you can confirm this and username, password, databasename etc.

Interesting project.

[robbiesqp]

Wow! Thanks for the responses guys! It really helped! I was able to successfully connect to the database and understood knew what I was doing!

My co-worker set me up on phpMyAdmin so I'm pretty sure that's the system they want to use. Besides I am just a summer intern so it's not really up to me. However I haven't found it to be too difficult to deal with.

Anyhow, like I said I was able to connect to the database, now I am on to working on parsing the data. I understand what that means now. So I have a text file with a couple of lines in it that represents data transmitted from a buoy (latitude, longitude, water temperature, etc).

I have attempted to write some code that would open that text file and insert it into the rows of the table (data) that I created in the database (buoy_viewer) on phpMyAdmin.

Here are the relevant lines of code:

$filename = "riverine.txt";
$fp = fopen($filename, "r") or die("Couldn't open $filename");

$lat = "";
$long = "";
$depth = "";
$speed = "";
$direction = "";
$temp = "";
$quality = "";

mysql_query("INSERT INTO data (`lat``long``depth``speed``direction``temp``quality`) VALUES('$lat', '$long', '$depth', '$speed', '$direction', '$temp', '$quality') ") or die(mysql_error());

As you can see, the table in which I am inserting this data has seven fields, which is the same number of columns of data in the text file (riverine.txt).

I believe that the code is correct, however when I run it in a browser window, I receive the error message

Column count doesn't match value count at row 1

I have looked at some tutorials online to troubleshoot this problem, but the only thing I could really find mentioned something about an "id" field, which is not present in the table I am working with.

Is there something wrong with my code or do I need to add an "id" field? Thank you all very much for your help!

[AlexK]

Your example mysql_query() contains bad SQL syntax; the fields should each be separated with a comma.

robbiesqp:

the only thing I could really find mentioned something about an "id" field

Go to the `Structure' tab of that table in phpMyAdmin. From the top screen table, look at each entry from the `Field' column. Those should be your `lat', `long', etc.. Look in the `Extra' column, and if one is marked "auto_increment" you have discovered your ID field. If not, look at the `Indexes' table on screen, and see which is the Primary index - if a multiple-field table, then you do NOT have an ID (bad idea IMO).

Some advice:

1 Check all variables after input from text file, and set to the correct type (text, int, float, etc.) before insertion to the DB.

You may hope that the buoy data is transmitted in a pristine state. The day that it is not, it will bite you in the bum. That day will be tomorrow. What can go wrong, will go wrong.

2 Separate SQL query & php code

eg:
$q = "some query"
mysql_query( $q, $connection ) OR die(mysql_error());

You can then easily prototype/check your SQL in phpMyAdmin before you ever get to the PHP stage, which makes for faster coding in the end.

I also prefer

"INSERT INTO some_table SET field1=$field1, ..."

rather than your example, because it is self-documenting. Which brings me to the final advice...

3 DOCUMENT YOUR CODE!

A simple one-line `this is what we are going to do' can save a future maintainer much heartache. That person may be you. Get into the habit now.

[rocknbil]

As your programs get more and more complex, you will be thankful if you get into this habit.

$query = "INSERT INTO data (`lat`,`long`,`depth`,`speed`,`direction`,`temp`,`quality`) VALUES('$lat', '$long', '$depth', '$speed', '$direction', '$temp', '$quality')";

mysql_query($query) or die(mysql_error());

A side note, it's always helpful to know why you are doing something. The backtick operators on field names,

`depth`

are a sort of "insurance" against reserved names and are not required in most cases. For example, date is a data type in mySQL, if you attempted to create a field name "date" for a table you would get a mySQL error, but creating `date` would be fine.

I raise this point because one of the most troublesome aspects of debugging is missing a pair of **anything** - quotes, parentheses, code block parentheses, delimiters. Backticks are just one more thing to trip you up, not using them, one less thing to worry about. Though I should say, using them is considered "recommended."

[robbiesqp]

Ok I've managed to get the code to upload data to the table, but it only inputs zeros. I'm still having some trouble understanding what the significance of this "id" field is, so I just omitted it.

Here is the set of text I am attempting to upload to the table:

30.30077,-89.70399,0.0,5.40,105.93,22.8,1
30.30067,-89.70371,0.0,5.14,112.88,22.8,1
30.30057,-89.70346,0.0,5.17,114.68,22.9,1
30.30046,-89.70321,0.0,5.10,124.66,22.9,1
30.30029,-89.70302,0.0,5.15,141.80,23.0,1
30.30008,-89.70287,0.0,5.00,153.77,23.0,1
30.29985,-89.70277,0.0,5.23,164.95,23.1,1
30.29962,-89.70271,0.0,5.19,172.38,23.2,1
30.29938,-89.70268,0.0,5.17,176.67,23.2,1
30.29915,-89.70267,0.0,5.20,177.89,23.3,1
30.29891,-89.70266,0.0,5.23,175.52,23.3,1
30.29868,-89.70264,0.0,5.07,170.47,23.4,1
30.29846,-89.70259,0.0,4.10,167.45,23.4,1
30.29824,-89.70255,0.0,4.87,174.12,23.5,1
30.29803,-89.70251,0.0,3.91,170.50,23.5,1
30.29783,-89.70252,0.0,3.91,170.50,23.6,1
30.29765,-89.70256,0.0,3.83,174.54,23.6,1
30.29761,-89.70234,0.0,3.26,121.18,23.7,1

Each row contains data for lat, long, depth, speed, direction, water temperature, and quality, in that order. Originally, the text file had some weird numbers at the top that I just deleted. I know that once these buoys start automatically transmitting, that text will still be there so I will have to adjust the code accordingly. However, for the time being I am just trying to get to a point where I can upload this data to the database with the SQL query instead of having to go into phpMyAdmin and do it all by hand.

These buoys are going to report every five minutes or so, which is obviously a lot of data.

Is the code that I have written not appropriate for parsing multiple rows of data?

Once again, I am not attempting to get anyone else to do my work for me and I sincerely appreciate the help! I believe I am capable of doing this stuff, I just need to know if I am going in the right direction. Thank you all!

[robbiesqp]

Ok I understand why it is entering all zeros. In my code, it seems I am just telling the thing that the individual fields are blank ""

$lat = "";
$long = "";
$depth = "";
$speed = "";
$direction = "";
$temp = "";
$quality = "";

In order to correct this, I want each of those fields to be updated with the columns present in the text file:

30.30077,-89.70399,0.0,5.40,105.93,22.8,1
30.30067,-89.70371,0.0,5.14,112.88,22.8,1
30.30057,-89.70346,0.0,5.17,114.68,22.9,1
30.30046,-89.70321,0.0,5.10,124.66,22.9,1
30.30029,-89.70302,0.0,5.15,141.80,23.0,1
30.30008,-89.70287,0.0,5.00,153.77,23.0,1
30.29985,-89.70277,0.0,5.23,164.95,23.1,1
30.29962,-89.70271,0.0,5.19,172.38,23.2,1
30.29938,-89.70268,0.0,5.17,176.67,23.2,1
30.29915,-89.70267,0.0,5.20,177.89,23.3,1
30.29891,-89.70266,0.0,5.23,175.52,23.3,1
30.29868,-89.70264,0.0,5.07,170.47,23.4,1
30.29846,-89.70259,0.0,4.10,167.45,23.4,1
30.29824,-89.70255,0.0,4.87,174.12,23.5,1
30.29803,-89.70251,0.0,3.91,170.50,23.5,1
30.29783,-89.70252,0.0,3.91,170.50,23.6,1
30.29765,-89.70256,0.0,3.83,174.54,23.6,1
30.29761,-89.70234,0.0,3.26,121.18,23.7,1

So the question is, what is missing from my code (a couple of posts up) that would allow the php to know which column correlates to which $field?

[daveVk]

reading csv files [php.net...]

[AlexK]

Another alternative is the use of

file()

([uk.php.net ]):

$file = "filename_of_text_file"; 
$fileArray = file( $file ); 
foreach( $fileArray as $lineNumber => $line ) { 
list( $lat, ... $quality ) = each $line; 
}