Noob/Hypothetical Question

[knystrom18]

Can I use a database I've created for a site to double as the list of recipients for a mailing list?

[jeffman]

A database exists independent of your PHP scripts. A new script can certainly access an existing database and do whatever it wants with it. If enough information exists to send emails, then, yes, of course.

[knystrom18]

Nice! Alright then, here's another question for you.I've been reading up on SQL through the tut on W3schools and it seems MAD easy. The only thing is...I don't have a RDBMS. Do I need one to originally create a database to use? Or can I use "CREATE DATABASE" to make one in a separate file?About that separate file: what would the extension be, and how would I call it into a PHP generated page?Thanks!

[Synook]

You can't run SQL queries without something (i.e. an RDBMS) to interpret them. From PHP, you would then interact with the RDBMS, which would read the database and return the relevant results.MySQL is a popular RDBMS, but there is also SQLite, ProgreSQL, etc.

[justsomeguy]

I've been reading up on SQL through the tut on W3schools and it seems MAD easy.

I don't think I've ever seen anyone say that... I guess SQL is not all that bad, assuming you don't need to do things like this:

create procedure sql_execute(								 pSqlID in integer,								 pBindValue in varchar2,								 pRefCursor out sys_refcursor,								 pIsError out boolean,								 pErrorMsg out varchar2									   )as   lSqlListIdx integer default 1;   lStrPos	 integer default 1;   lSqlStmt	sql_statement.sqlstmt%type;   lIsSelect   sql_statement.isselect%type;   lSqlCursor  integer;   lSqlOut	 integer;   lSqlList	dbms_sql.varchar2a;   lBindId	 varchar2(10);   lBindType   varchar2(1);   lBindValue  varchar2(4000);   lBindCnt	integer;begin   pIsError := false;   pErrorMsg := '';   begin	  select sqlstmt, isselect into lSqlStmt, lIsSelect		from sql_statement	   where sqlid = pSqlID;   exception	  when no_data_found then		 pIsError := true;		 pErrorMsg := 'Invalid SQL ID ' || pSqlID;   end;   if pIsError = false   then 	  lSqlCursor := dbms_sql.open_cursor;	  loop		 lSqlList(lSqlListIdx) := dbms_lob.substr(lSqlStmt, 32767, lStrPos);		 exit when lSqlList(lSqlListIdx) is null;		 lStrPos := lStrPos+32767;		 lSqlListIdx := lSqlListIdx+1;	  end loop;	  dbms_sql.parse(lSqlCursor, lSqlList, 1, lSqlListIdx, null, dbms_sql.native);	  	  lBindCnt := (length(pBindValue) - length(replace(pBindValue,'#')))+1;	  for i in (select level no, regexp_substr(pBindValue, '[^#]+', 1, level) str				  from dual				connect by level <= lBindCnt)	  loop		 lBindId	:= substr(i.str, 1, instr(i.str,'^',1,1)-1);		 lBindType  := substr(i.str, instr(i.str,'^',1,1)+1, instr(i.str,'^',1,2)-(instr(i.str,'^',1,1)+1));		 lBindValue := substr(i.str, instr(i.str,'^',1,2)+1);		 case 			when lBindType = 'N' then			   dbms_sql.bind_variable(lSqlCursor, lBindId, to_number(lBindValue));			when lBindType = 'D' then			   dbms_sql.bind_variable(lSqlCursor, lBindId, to_date(lBindValue));			when lBindType = 'C' then			   dbms_sql.bind_variable(lSqlCursor, lBindId, lBindValue);		 end case;	  end loop;	  lSqlOut := dbms_sql.execute(lSqlCursor);	  dbms_sql.close_cursor(lSqlCursor);   end if;end;

You can't run SQL queries without something (i.e. an RDBMS) to interpret them.

To expand on that, PHP doesn't understand SQL, they're two different languages. PHP just sends the SQL queries to the DBMS, which does understand them. So if you don't have a DBMS, then you don't have anywhere to send the queries.

[End User]

I've been reading up on SQL through the tut on W3schools and it seems MAD easy.

Everything seems easy before you understand it. Cooking: Put it in a pan and heat it. Poetry: Write some words, make them rhyme. Car Repair: Replace the broken part. Heart surgery: See "Car Repair".Flying: Keep airplane above zero meters altitude.War: Kill all the other guys before they kill you.Music: Play all the right chords, beats, or notes, in order.College: Pass all the tests and get good grades. Particle Physics: Think the same way Einstein did. Racing: Go faster than the other guy(s).Engineering: Build it so it doesn't break.Space Flight: Get the rocket into orbit.SQL: SELECT * FROM some_table

[jeffman]

On the other hand, there are a lot of things you can do with SELECT * FROM some_table WHERE x=y . This is one of the things that makes web-related technologies inviting to noobs. You really can create a useful application with just a few key strokes and zero cash investment. A triumph of open-source democracy, but robust enough to scale into something like Yahoo with a few more years of experience.In contrast, there is no such thing as entry-level particle physics (not the kind you do, anyway). --though ironically, the web was created in a particle physics lab.

[End User]

You really can create a useful application with just a few key strokes and zero cash investment.

So I've heard.

[thescientist]

So I've heard.

I'm doing it right now! I just started using SQL to create a backend for my website redesign, and its been pretty easy going so far. Especially with good resources here at W3Schools. Honestly I've had more trouble managing escaping my quotes and apostrophes than anything logic/syntax related to PHP and SQL, heh.

[End User]

I'm doing it right now!

Yes, I know. My comment was tongue-in-cheek, hence the smiley-face. That's what I've been doing for the last 10 years or so. :)If you can type and have some good ideas, you can build nearly anything you can think of. I use only open source software (PHP, Apache, mySQL, etc, running on a couple of dedicated Debian servers) and it's worked out well for me.

[knystrom18]

I don't think I've ever seen anyone say that... I guess SQL is not all that bad, assuming you don't need to do things like this:

Wicked complicated SQL

Dag... I see the syntax is basically the same, but there's a TON of stuff in there... I know Access 07 well and the logic for queries is pretty much the same, just with typing instead of mouse clicks. That's why it seems easy to me I guess.

To expand on that, PHP doesn't understand SQL, they're two different languages. PHP just sends the SQL queries to the DBMS, which does understand them. So if you don't have a DBMS, then you don't have anywhere to send the queries.

Ok. I'm confused as to whether or not MySQL is an actual executable program like Access, or if it's a virtual DBMS that runs on a server. Which one is it? Depending on that answer, I have another couple questions for later

[knystrom18]

Everything seems easy before you understand it. Cooking: Put it in a pan and heat it. Poetry: Write some words, make them rhyme. Car Repair: Replace the broken part. Heart surgery: See "Car Repair".Flying: Keep airplane above zero meters altitude.War: Kill all the other guys before they kill you.Music: Play all the right chords, beats, or notes, in order.College: Pass all the tests and get good grades. Particle Physics: Think the same way Einstein did. Racing: Go faster than the other guy(s).Engineering: Build it so it doesn't break.Space Flight: Get the rocket into orbit.SQL: SELECT * FROM some_table

Excellent examples, haha. And very true.

[boen_robot]

Most RDBMS (MySQL included) work as their own server - a separate always on executable that accepts content (queries) in a certain (non standard) fashion from a certain other application (a client), and return a certain other content (the result) to the calling application (i.e. the client).

[justsomeguy]

Ok. I'm confused as to whether or not MySQL is an actual executable program like Access, or if it's a virtual DBMS that runs on a server. Which one is it?

Yes. It's an executable program that runs on a server. All programs are executable, actually, regardless of where they run. MySQL is itself a database server. You can connect to and use a web server on a certain port, an FTP server on a certain port, a mail server on a certain port, a MySQL server on a certain port, etc.

[knystrom18]

Ok, if MySQL is a RDBMS, how come I can't open it up and create a database like I'd open up Access 07 and create one there? Essentialy, how do I create a database w/ MySQL?

[Synook]

MySQL has no native graphical frontend like Access does. You can use the command line, available when you execute mysql.exe, and just run your SQL queries there (CREATE DATABASE to create a database). Or you can download a third-party graphical application like phpMyAdmin to use instead.

[boen_robot]

There is also a separate tool by MySQL called MySQL Workbench in which you can visually design database schemas (personally, I think it's even more convinient than Access, but you won't hear many people say that), and then export them to MySQL for live use.

[23.12.2012]

Regarding SQL being easy, I'd say it's harder than PHP, mainly because all the errors I've ever got from MySQL are something like "Syntax error over there; Check the manual; kthxbye;", while the errors PHP returns have always helped me.Also, I recommend you learn how to use the command-line and how to write your queries by hand, remember that in your PHP code, it's all text, you can't go ahead and create a visual query, so learning some extra things would pay off, eventually.

[End User]

Regarding SQL being easy, I'd say it's harder than PHP, mainly because all the errors I've ever got from MySQL are something like "Syntax error over there; Check the manual; kthxbye;", while the errors PHP returns have always helped me.

Couldn't agree more. That's one of the things that caused me to jump at PHP in a big way- helpful error messages. Perl by default doesn't show you a bloody thing except a "500 Error", no matter what the problem is. Out of space? Bad variable? Missing a semicolon? Got an extra bracket somewhere? Are Martians invading?Who cares, here's your &$%#! "500 Error". Perl is like an autistic kid who can only count to 'potato'. Loveable but frustrating. And perl can be monstrously cryptic, it's the only programming language that can look exactly like line noise. And not even good line noise. When I first saw The Matrix in the theater, and they had those green lines of flying text dropping down, someone shouted, "*THAT'S* Perl!"You can use carp to get more useful error descriptions from perl, but even carp can crash without telling you anything useful. The first time I used PHP and caused an error, I practically fell out of my chair when it told me what the error was and what line it was on. Coming from a perl environment it was undeniably friendlier and more useful by orders of magnitude. SQL can be tricky to learn, but one nice thing about it is that it doesn't change. By and large, the queries you wrote 10 years ago still work today, and chances are they'll work 20 years from now. Even complex queries are almost always "timeless" in their usability. MySQL errors, however, are uniformly unhelpful as output. For example, "error 28" doesn't tell you anything except that there is an error. Even just a couple of words would be extremely helpful, like "no space". At least then you'd have an idea right off the bat what the nature of the problem is.

[knystrom18]

MySQL has no native graphical frontend like Access does. You can use the command line, available when you execute mysql.exe, and just run your SQL queries there (CREATE DATABASE to create a database). Or you can download a third-party graphical application like phpMyAdmin to use instead.

Using the command line seems cumbersome, typing EVERYTHING and if a single character is off, the whole thing is shot... I've got phpMyAdmin with my installation of XAMPP. It seems complicated, but it's the visual end of the RDBMS I've been looking for...

[knystrom18]

There is also a separate tool by MySQL called MySQL Workbench in which you can visually design database schemas (personally, I think it's even more convinient than Access, but you won't hear many people say that), and then export them to MySQL for live use.

... but if this thing proves to be easier to use than phpMyAdmin, I'll try that out.What I really need to do is take some classes/read some books and stop asking. That and actually get some real world experience.

[knystrom18]

Regarding SQL being easy, I'd say it's harder than PHP, mainly because all the errors I've ever got from MySQL are something like "Syntax error over there; Check the manual; kthxbye;", while the errors PHP returns have always helped me.Also, I recommend you learn how to use the command-line and how to write your queries by hand, remember that in your PHP code, it's all text, you can't go ahead and create a visual query, so learning some extra things would pay off, eventually.

learn how to use the command-line and how to write your queries by hand

That seemed like the simple part...

[knystrom18]

So just to get this all straight...1. MySQL is a program that runs on a server, be it a web host's server, or ones own local machine.2. There are multiple methods to use MySQL: the command line, phpMyAdmin, or Workbench.3. To make queries you have SQL code in a PHP variable and... then what? Send it to the database?And the SQL code for a database is linear meaning that if one makes a change to the first record in the first table in the database, those changes would show up later in the code and not where that first record in the first table was first coded in.Right?

[boen_robot]

1. You're thinking of the term "server" as a separate computer - it isn't. What makes a "server" be a "server" is it's software - a "server" is a program that runs on a computer, but which may be "called" upon by another program, where the other program may be on another computer. You installing MySQL on your local machine turns your local machine into a MySQL server. If MySQL is installed on another computer, that other computer becomes a MySQL server.So, MySQL is a server that runs on a computer.2. Yes... kind of... there are multiple MySQL clients, i.e. programs that can ask MySQL to do something (e.g. create a database, give some data, add some data, etc.). The command line executable is one client, MySQL Workbench is another, PHP's MySQL and MySQLi extensions are also such clients. phpMyAdmin uses the MySQL extension under the hood, so... I suppose that makes it a graphical front end of the MySQL PHP extension.3. Yes. Though the query doesn't have to be in a variable. The important part is to send it to the MySQL server via functions like mysql_query.

And the SQL code for a database is linear meaning that if one makes a change to the first record in the first table in the database, those changes would show up later in the code and not where that first record in the first table was first coded in.

Yes... though I'm not sure if "linear" is the correct way to put it... The database is changeable (in every sense of the word), and every query affects the database from its state at that moment - essentially the same thing you say - if you do one thing first (e.g. add some data), doing a second thing (e.g. get some data) now works on the DB as it looks after the first thing (i.e. you'll also get the recenly added data).

[knystrom18]

Ok, that seems to make sense. I'm beginning to see the "light" now, hehe. Just talking about it though is no substitute for actual experience, so I'll need to get on that...Thanks for the replies and help everyone, I appreciate it.- K