Retreiving multiple tables of content?

Benny_Venables

I'm fairly new to mysql, but I'm interested in using it to power the content for a site I'm working on dynamically. I have a menu and content on the right, and the only other things that change page to page are an image header and, of course, the title area. Now, I don't know if this works in mysql, but could I, say, put a variable in for the menu, content and titles, then using a php powered link (the sort of index.php?page=news ones) have the page load a table that will automatically fill the menu, content and title areas with mysql stored data.

I'm relly curious to know how to do this since I've tried using php variables, but you need to define every variable in the link, making for massive links and very messy code.

I have no idea how easy this is, but I have mysql, php and phpmyadmin, so hopefully it shouldn't be too hard.

Thanks!

TheDefender

Yes it is not as difficult as it may seem... Post some of your code in the Code Critique forum (or even here) and the regs arouns here can help clean it up to get your desired effect.

Benny_Venables

The problem is I haven't got any php code written so far, even though I've had a few years experience with scripts and mysql databases, I've not ever attempted anything like this, and wasn't even sure if it was possible, therefore I haven't any code written for it yet. What would be the best step to take next?

TheDefender

Storyboard what you are trying to do, and dig into the MySQL and PHP Manuals and start writing some code. Since you are new, chances are good you are going to have troubles, so bring your code to the board here, and let the regs help you out with fixing and tweaking things... You may also consider a trip to your local library for a book or two on writing PHP.

Even if you write scripts that have nothing to do with what you are trying to do, it's a good thing too, because a) it helps for learning; and b) you may not know you will need it later, so it's good to have some snippets lying around.

Benny_Venables

I have "Mysql in 30 days" but I have no idea if it covers what I'm looking to do. How is this easier to do with php variables? I've seen a few sites done that way and wondered if that might be the answer to my problems?

TheDefender

Some people like that book, and some people don't. I personally don't think it's thorough enough... Besides that, the PHP and MySQL manual cover it all pretty well for you... Now to give you an idea of what you are looking for...

Let's say you have a table called "yourtable" with 3 fields: id, firstname and lastname, and there are 3 records:
1 John Doe
2 Jane Doe
3 Mike Jerky

Now, let's say you want to click on a name to pull that specific record.

<?
// Build your connection to your database.  (Change the items in [b]bold[/b] to match your own settings)
$connect = mysql_pconnect("[b]yourhost[/b]", "[b]username[/b]", "[b]password[/b]") or die("Could not connect: ".mysql_error());
mysql_select_db("[b]database[/b]", $connect) or die('Can\'t use the database: '.mysql_error());

// Build your query (Change the item in [b]bold[/b] to match your own settings)
$qry = mysql_query("SELECT * FROM [b]yourtable[/b] ORDER BY lastname ASC, firstname ASC");

// Loop through a WHILE loop to display results
WHILE($gotqry = mysql_fetch_array($qry)){
echo "<a href=\"./details.php?id=".$gotqry['id]."\">".$gotqry['firstname']." ".$gotqry['lastname']."</a><BR />";
}

?>

This will output:
Jane Doe
John Doe
Mike Jerky

OK, now you click on Jane Doe's name, the URL would be like below, and you use the id variable in the URL to fetch Janes' specific record.

http://www.yourdomain.com/details.php?id=2

Your code to display Jane's record info might look something like:

<?
// Build your connection to your database.  (Change the items in [b]bold[/b] to match your own settings)
$connect = mysql_pconnect("[b]yourhost[/b]", "[b]username[/b]", "[b]password[/b]") or die("Could not connect: ".mysql_error());
mysql_select_db("[b]database[/b]", $connect) or die('Can\'t use the database: '.mysql_error());

// Build your query (Change the item in [b]bold[/b] to match your own settings)
$qry = mysql_query("SELECT * FROM [b]yourtable[/b] WHERE id = '".$_GET['id']."'");

// Loop through a WHILE loop to display results
WHILE($gotqry = mysql_fetch_array($qry)){
echo $gotqry['lastname'].", ".$gotqry['firstname'];
}

?>

Nothing fancy, it just outputs the following:
Doe, Jane

When you say you wonder if it is the answer to your problems, what problems do you mean? Everything you describe is basic stuff covered in many different books (including the Teach Yourself in 30 days) and in the MySQL manual and PHP manual, both easily available online. If you don't open books and manuals, then your "problems" will never be solved.