MySQL (and PHP)

[mike_w]

I'd like to learn how to use MySQL, since it would be useful to be able to implement a database driven website. Does anybody know of any decent resources online that will help me use it in conjunction with PHP?

While I'm at it, are there any good resources for installation on an Apache server?

Thanks

Mike

[Iain]

use wampserver.com to set up an Apache, MySQL and PHP setup. If you want to understand how it hangs to gether download the elements indivdually and google for a guide, there are loads out there.

For starting from scratch try the Sitepoint book at http://www.sitepoint.com/books/phpmysql1/. The sample chapters should get you going but I recommend the book

[Kezzer]

sitepoint are excellent, they do some awesome books as well

EDIT: Gah, beat me to it by a second

[mike_w]

use wampserver.com to set up an Apache, MySQL and PHP setup. If you want to understand how it hangs to gether download the elements indivdually and google for a guide, there are loads out there.

Thanks, forgot to mention that I already have an Apache server with PHP working. (Edit: On Debian)

And thanks to both of you for the site - I'll have a look.

[Dorza]

You might want to look at the PHP Anthology books from site point as well mike_w. I would also recommend the book Iain linked to. Its a very good thorough read and starts you off in the right direction. In short, sitepoint has you covered

PS. Beginning PHP5 and MySQL is a good book as well, but this is more of a reference book i feel. Note: the link i just give is for the 2nd edition which is not yet out.

Dorza

[aidanjt]

The PHP Manual is a great reference with plenty of examples, toy around with some ideas, the MySQL docs aren't *as* clean, but still helpful for constructing complex queries. phpMyAdmin is also handy if the commandline mysql client annoys you.

And of course, Google is always your friend

[mike_w]

Okay, managed to get a small database going, but I've got a problem with a SELECT bit. Here's the first part of the PHP:

<?php $mysqlconnect = @mysql_connect('localhost', 'root', 'baz');

$dbconnect = @mysql_select_db('database');

$name = $_GET['n'];

$result = @mysql_query('SELECT * FROM foobar WHERE (name = $name)');

$row = mysql_fetch_array($result);

?>

The address is page.php?n=foo. The idea is to grab just the one row from a table. The problem is with name=$name. When I substitute $name for an actual name in the table, it works fine, yet with $name, it can't find the row. When I do an echo showing $name, $name is the right value.

An example of where the row is being used:

<?php echo $row['title']; ?>

Any help is appreciated.

Edit: Solved it by changing it to: $result = @mysql_query("SELECT * FROM websitearticles WHERE (name = '$name')");

[Norky]

Best thing to do is put your query into a string, then you can recall it for debugging purposes

$sql = "Your query";
mysql_query($sql) or die($sql);

Echoing out the string is useful when you've got variables in the query.

[aidanjt]

<?php $mysqlconnect = @mysql_connect('localhost', 'root', 'baz');
$dbconnect = @mysql_select_db('database');
$name = $_GET['n'];
$result = @mysql_query('SELECT * FROM foobar WHERE (name = $name)');
$row = mysql_fetch_array($result);
?>

Should read:

<?php
    $mysqlconnect = mysql_connect("localhost", "root", "baz");
    $db = mysql_select_db("database");
    $name = $_GET['n'];
    $result = mysql_query("SELECT * FROM foobar WHERE name='$name'");
    $row = mysql_fetch_array($result);
?>

note, WHERE clause variables need to be quoted, I use " quotes in php code/functions, and ' for sql queries, thats just me though.. this *should* work, keep it up.

the @ just suppresses function warnings/errors, might want to keep them out for learning, so if you bugger something up it'll help explain what went wrong.

[mike_w]

Thanks again for the help.

the @ just suppresses function warnings/errors, might want to keep them out for learning, so if you bugger something up it'll help explain what went wrong.

Yup, the ones with the @ in front are the ones I've written error messages for - just haven't got around to doing them all yet!

[Iain]

The MySQL docs aren't as useful as the PHP ones until you make compressed help manual (.chm file) which is easy to do. I've built one recently, get it here -> www.hexus.net/iain/mysql_5_0_3.chm

[mike_w]

Right then, a couple of questions:

1) If I were to have various sections on a website, such as a section for hardware and one for software, would I create one table and put all the articles together, seperating them by creating a category column? Or would it be better to create individual tables for each section? I'm leaning towards a single table, but I'm not really sure.

2) The point (so far as I can tell) of making a site database driven is to seperate the content and the code. However, for the bodies of articles, I still have things such as <p> and <img> tags - is there a better way of storing them in the database?

Thanks for all the help

[aidanjt]

1) I'd keep everything in the same table, after all, if you make two tables they're going to have the same data structure, no point segregating them.
2) you can either make your own tag formats for storing (much like vBulletin), or just allow storage of simple html tags.. whatever works best for you. The latter is less work.

[mike_w]

2) you can either make your own tag formats for storing (much like vBulletin), or just allow storage of simple html tags.. whatever works best for you. The latter is less work.

Hmm... I think I'll go for the HTML tags - it allows me to retain control over the page, as well as not having to use special tag formats all the time.