SubJeff on 27/8/2003 at 10:09
I'm having a lot of trouble with some SQL queries in PHP. I can't get INTO working using the format:
SELECT id,name FROM test_table INTO newtable
I get a syntax error. When I used to use Oracle this was fine. Also I find that I have to "quote" table and field names when creating tables. This isn't in the mySQL manual - it looks like bog standardd SQL to me.
I'm remotely connecting to my host and they use PHPmyAdmin so my queries (outside PHP) aren't going in to a mySQL command line.
Is this all normal or are there extra rules and changes from SQL I need to be aware of, because I'm sure I'm sticking to the documentation?
Vigil on 27/8/2003 at 11:13
The best way to use the MySQL documentation is to line birdcages, wrap seafood, or mop up accidental spills with it.
That said, another use for it (of which "finding out how to do something you want to do in less than half an hour" isn't one) is to find out that something terribly clever and useful isn't supported (like subselects or unions). I found this in the 3.23 docs:
<blockquote>
MySQL doesn't yet support the Oracle SQL extension: SELECT ... INTO TABLE .... MySQL supports instead the ANSI SQL syntax INSERT INTO ... SELECT ..., which is basically the same thing. See section 7.21.1 INSERT ... SELECT Syntax.
INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE tblTemp1.fldOrder_ID > 100;
</blockquote>
I've never had to quote table or field names, but then I'm weak and rely on PHPMyAdmin to do my dirty table-creation work. The only time I can think of that quoting would be necessary is for fields or tables that share the names of reserved keywords (like 'order').
SubJeff on 27/8/2003 at 11:23
Thanks Vigil.
I don't mind using PHPMyAdmin for table creation really but for dta entry it'd be nightmare using the interface. In Oracle SQL you write an insert template in notepad, copy'n'paste it and then enter the data. Paste the result at the sql> command prompt and it al goes in ! (You have to be sure you've go it right though.)
How do you input data quickly in PHPMyAdmin? I've seen a "insert data from text file" option but I've no idea about the formatting.
Vigil on 27/8/2003 at 11:31
The Insert Data From Textfile will accept the regular kind of comma/tab-delimited text that exports from Excel or most databases produce. Some older and now newer versions of PHPMyAdmin are finicky about it (to the extent of not even importing text they exported themselves), but ~2.2 is pretty robust.
SubJeff on 29/8/2003 at 08:37
But how do you tell it which field they go in? I guess I should experiment.
Vigil - does all of the actual text on your side reside in a DB? I ask cause thems some laaaaarge VARCHAR fields if they do.
Vigil on 29/8/2003 at 09:15
Basically a LOAD DATA INFILE text import fills up the fields in the order they are specified in the table. So if your table fields are <code>id, title, content</code>, then the first delimited value in the textfile will go into id, the second into title, the third into content, and so on. A newline indicates a new row, any extra fields in the textfile off the 'end' of the table will get discarded, and any missing fields are filled with default values.
VARCHAR fields are limited to a max of 255 characters and are intended for easily-indexable strings like titles or codes. For lots of content there's heavy-duty fields like TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT (LONGTEXT holds up to 2^32 - 1 characters, which is...a lot.)
And yah, all my blogstuff is stored in big text fields. I could just store a pointer to an HTML file instead (it would be easier to code for a start), but this way the text is fully searchable.
SubJeff on 29/8/2003 at 10:44
Aha, LONGTEXT is the one I want. I thank you kind sir.
HTML? Yeah but by doing it your way means you can write a script which just pulls out he newest vesion and places it on top. If you've got your realationships down pat you can automate the menu generation just by adding one entry in the DB.
ASP (I know, I know) with a Microsoft server and Access allows real SQL queries a la Oracle. I guess that's why its still used. If mySQL gets sorted. . .
Vigil on 29/8/2003 at 11:38
I meant more that you can have a hybrid system, with a database tracking blog entries and allowing them to be indexed and ordered (and build up dynamic navigation with the data, as you say), but have the actual blog content stored externally in HTML files whose filenames are referenced by a field in each entry.
LONGTEXT is probably too long for most needs - the MySQL manual itself says that packet-limit on MySQL transactions is too small to be able to access the entire content of a full LONGTEXT. I never ran into the character limits on a MEDIUMTEXT field (16 million characters, i.e. 16MB of raw text) just doing blogging stuff.
I've dealt with ASP-Access combinations, and thoroughly loathed them (though that had more to do with the hatefulness of VBScript syntax and the wanky featurelessness of the JScript function set). You can use Oracle, MSSQL and the like with PHP too though - it has builtin function libraries for most major database platforms (though not Access, strangely). Most ISPs don't tend to offer those combinations though, since PHP and MySQL are free and the other systems aren't.
EDIT: Ok, you can use Access with PHP too, using the ODBC functions.
SubJeff on 29/8/2003 at 14:25
I see. Interesting plan. I gues for blogs it's fine. But if you want to do any large amounts of information I guess MEDIUMTEXT will do me. YOu could store whole HTML docs in mySQL though and have no need for actua HTML documents at all. All PHP with calls to the mySQL.
VBScript is like all scripts. They're all the same to me. JavaScript, PHP script, VBScript. Even Perl looks pretty similar. The differences seem to be where and how you use them and whether they're strongly typed or not. You can do pseudo object oriented codign in all (although I never get really serious).
VBScript is nice to know because you can use it to make VB programs for sending to you "pals" over the email. Call it hot_kylie_ass_dance.exe and they'll surely open up that 1million pop-up spawning script . Har har. Knowledge is dangerous and can make you into a real arse. :cheeky:
SubJeff on 10/9/2003 at 08:15
Vigil, I can't use PHP-mySQL on my computer because of some missing dll. Nevermind. I CAN use mySQL through a command line. Nice.
What I want to know is - if I create a DB on my computer and work out all the queries for it at home can I just copy'n'paste them to the .php pages for use with my mySQL DB that is online?
