SQL problems

[Harrison]

Depends what access rights you have to the hosting server. Do you have SSH shell access? That is the best way. Otherwise you will be stuck with phpMyAdmin which as you have encountered can cause problems.

[v85rawdeal]

Would would be the best source materials and software to use in order to learn php/sql programming?

I have an old website design I want to update, and that means getting a database system working on it... eventually. I would appreciate any advise or help given, thanks.

[Harrison]

Many people tell everyone these days to just learn from websites and not to bother with books, but personally I find there is no better way than to have a book in front of you with some examples that you can take your time learning and experimenting with.

I highly recommend the books published by Wrox. If you are completely new to PHP then I recommend you have a look at one titled "Beginning PHP". They also have some others such as "Beginning PHP, Apache, MySQL Web Development" which is also a good beginners guide, but as it lumps everything in together it doesn't quite cover as much on actual PHP and everything you can do with it. You can easily identify all books by Wrox as they are completely red with yellow writing.

If you also need to brush up on your HTML, with XHTML and CSS, then I have always used "HTML, The Complete Reference" by Powell and published by McGrawHill.

[Stephen Coates]

Depends what access rights you have to the hosting server. Do you have SSH shell access? That is the best way. Otherwise you will be stuck with phpMyAdmin which as you have encountered can cause problems.

I don't have shell access.

Does that mean i am going to have to stick with phpMyAdmin?

[FOL]

Depends what access rights you have to the hosting server. Do you have SSH shell access? That is the best way. Otherwise you will be stuck with phpMyAdmin which as you have encountered can cause problems.

I don't have shell access.

Does that mean i am going to have to stick with phpMyAdmin?

I would believe so. I have to admit, I have never really bothered with shell, always used myphpadmin, and it has never failed me once, aslong as you select the correct export options.

[Stephen Coates]

I'm pretty sure I did select the correct export options, but I will probably find out later that I didn't. I exported the database and then restored it using the same method last year and it worked perfectly, but I was following some instructions on a web page that I am no longer able to find.

[Harrison]

You will be stuck with phpmyadmin without any shell access. If used correctly and the backup was exported correctly then it should restore perfectly well.

I have to admit, I have never really bothered with shell, always used myphpadmin, and it has never failed me once

phpmyadmin is fine but you don't realise how slow it is until you have used a shell session. Directly being able to use Linux commands to backup and restore a database is very fast. For example if I backup the main classicamiga database via the shell you type the command, hit return, and the backup is sitting waiting for you on the server before you can blink. It also makes it possible to automate these backup commands using a cron job, so you don't even need to remember to do the backups.

But the big speed advantage you will notice is in restoring a database. Using phpmyadmin this can take a long time as the program processes through all of the SQL commands from the backup, checking for any errors, and building the new database tables and then populates them with data. Via the shell you type the command to create the database and then restore from the backup and 2 seconds later the restored database is sitting there waiting to be used. The first time I did this I couldn't believe it had actually done anything and restored it. I had to load up the database in phpmyadmin just to check.

Another great use for a shell session is file backups. Backing up a server file structure via ftp is a very slow and tedious job as you have to wait for each file to download before the next starts so it slows the whole process down. Using the shell it is easy to backup the whole file structure into a single gzipped file which you can then download. This has two big advantages. The file is compressed so the download is smaller, and as the whole file structure is contained within the one big file so you know you will not miss any files in the backup, and the file integrity is better.

[Stephen Coates]

I added loads of \ to the ' and ", and now have this error:

MySQL said:  
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Worldwide Developers Conference keynote address exclusively in QuickTime.\r\n\r\' at line 1

And it is in this part of the database:

INSERT INTO `phpbb_posts_text` VALUES (3361, 'bcacceda26', 'Watch the Worldwide Developers Conference keynote address', 'Tune-in following the event on Monday afternoon June 23, 2003 to see Steve Jobs' Worldwide Developers Conference keynote address exclusively in QuickTime.\r\n\r\nFor optimal viewing experience, viewers should download QuickTime 6.3.\r\n\r\nhttp://stream.apple.akadns.net/');

I'm guessing I'd just need another \ or similar, but thought I'd better check before I go and mess it up even more.

[Harrison]

Looking at the code you posted you have Steve Jobs'. Did you escape the ' on the end of Jobs'?

[Stephen Coates]

That was the problem. Sorted.

Just got lots more to do now.

BTW, how big is the classicamiga database?