Need Help with SQL

[Tom Bottiglieri]

hi, im trying to set up a SQL database for our teams scouting needs. I have NO idea how to do any of this stuff. I have the SQL server, and i know how to set up tables and stuff, but i have no idea how to enter info or access it.

[Mr. Ivey]

for access, just using the command line you type something like this
SELECT t.team_number=384, d.drive_type=hyper;
FROM team t, drive d;
if you don't have multiple tables, and the info is just in the rows it goes like this
SELECT team_number=384, drive_type=hyper;
FROM team;
for entering info, i will get back to you, i need to find my Oracle 9i and SQL notes from last year in OIA. But I will let you know.
ivey

Found my notes, you need to use the INSERT INTO command, and then give the colums to insert info in, and then the corresponding data. After you type the inital command you need to put the name of the table, and then after that the schema. The whole thing looks like this:
INSERT INTO RoboInfo.SchemaIvey (drive_train, wheels) VALUES (hyper, MonsterTruck);

If you have any more questions search for information on Data Manipulation Language, or DML in SQL. Here is also a tutorial that helped me get through my Oracle class last year: SQL Help and Tutorial

[deltacoder1020]

in all of these examples, "..." just means you can have as many fields as you want.

basic insert:

INSERT INTO table (field1, field2, ...) VALUES ("field1data","field2data",...);

another way to insert:

INSERT INTO table SET field1="field1data", field2="field2data", ...;

basic retrieval of results:

SELECT * FROM table;

only retrieve certain fields:

SELECT field1,field2,... FROM table;

selective retrieval of results:

SELECT * FROM table WHERE field3="field3value";

ordering the results retrieved:

SELECT * FROM table ORDER BY field3;

only retrieve the first few results (up to the number specified):

SELECT * FROM table LIMIT number;

deleting all rows:

DELETE FROM table;

deleting certain rows:

DELETE FROM table WHERE field1="field1data";

you can combine just about any of the modifiers above, for example,

SELECT userid,username FROM users WHERE usergroup="test" ORDER BY userid LIMIT 30;

would give me the username and userid of the first 30 people in the users table whose usergroup is "test", ordered by their userids.

[Raven_Writer]

For SQL tutorials (and a slew of others), W3Schools.org is the site to goto.

[steven114]

If you have PHP available, I would suggest using PHPMyAdmin. It makes using mySQL so much easier.

(I am assuming that you are using mySQL, you didn't specify)

[Tom Bottiglieri]

yeah i have PHPmyAdmin and mySQL. Being a web design noob, i still cant figure it out.

[KevinB]

www.webmonkey.com has some pretty good PHP+MySQL tutorials also.

[pedro]

I think your problem lies in connecting to the database using PHP or whatever else you may be using. I don't know much PHP, but i'm guessing it shouldn't be more than 10 lines of code to set up a connection, and then use some of the SQL statements that were mentioned above.

Of course if it were up to me, I wouldn't use PHP in the first place (ColdFusion is way more straight forward), but that's another topic...

[Raven_Writer]

Originally Posted by pedro

I think your problem lies in connecting to the database using PHP or whatever else you may be using. I don't know much PHP, but i'm guessing it shouldn't be more than 10 lines of code to set up a connection, and then use some of the SQL statements that were mentioned above.

Of course if it were up to me, I wouldn't use PHP in the first place (ColdFusion is way more straight forward), but that's another topic...

Just for the record, it depends on how you'd like to connect to the db on how many lines it'd take.

Here's the smallest way to connect to a db IMO (incase you are having trouble with connecting):

PHP Code:

<?php
$db = mysql_connect_db("localhost", "fred", "loves_wilma");
 
mysql_connect($db);
 
mysql_close();
?>

It may not be totally correct syntax, but that's because I haven't touched PHP in a while

[deltacoder1020]

Originally Posted by Raven_Writer

Just for the record, it depends on how you'd like to connect to the db on how many lines it'd take.

Here's the smallest way to connect to a db IMO (incase you are having trouble with connecting):

PHP Code:

<?php
$db = mysql_connect_db("localhost", "fred", "loves_wilma");
 
mysql_connect($db);
 
mysql_close();
?>

It may not be totally correct syntax, but that's because I haven't touched PHP in a while

PHP Code:

<?php

mysql_connect("dbhost","dbuser","dbpass") or die("could not connect to db";
mysql_select_db("dbname") or die("could not select database");
$result = mysql_query("query string") or die("could not query database");

//process data

?>

(php automatically closes the database connection at the end of the script)

[Raven_Writer]

Heh, thanks. I thought I didn't do the coding right. But yea, that example works the best. (IMO though, it might be safer to close the connection on your own anyways, incase PHP forgets to do that)