use php to show mysql data

Question

Hello all, I am wondering what the best way is using php to obtain a list of all the rows in the database, and when clicking on a row show the information in more detail, such as a related image etc.

Should I use frames to do this? Are there good examples of this somewhere?

Edit:

I need much simpler instructions, as I am not a programmer and am just starting out. Can any links or examples be recommended?

Answer 1

I use tables and JavaScript to do this.

Data in a SQL database is, by nature, tabular. So I just select the data and create a table. Then, to drill down (when I need do), I provide a JavaScript "more" functionality and use CSS to hide/display the additional data.

Answer 2

I tend to use two separate pages. One to list, which links to the one that shows the detailed record. The one that lists passes an ID parameter on the link (ie. show.php?id=145), as for the show.php page will get that parameter from $_GET['id'].

This is the simplest approach to your problem.

Answer 3

You could begin with the PHP manual. It's rather well organised now. But now that my sarcastic bit's out of the way...

One of the best ways to work with your database (at the moment, your choice is MySQL, but this could change) is to abstract your code from direct interaction with it.

A tool such as ADODB is well worth getting to know and makes the task of "obtain a list of all the rows in the database" rather trivial.

The main advantage of this is that it insulates you somewhat from having to rewrite lots of code if you find you need to migrate your application to a server with a different database running on it.

Better still (imho) would be to look at a framework such as Zend's (well, they do MAKE php afterall) with it's DB abstraction called Zend_Db. This might be overkill for you right now as it appears, from looking at your other questions, that you're quite new to PHP/MySQL development.

Also good: Smarty (for abstracting your presentation from your logic)

Answer 4

If you're not a programmer, but want to use php to show what's inside your mysql tables, perhaps phpMyAdmin is what you're looking for?

Answer 5

If you're building a simple database-driven application, and you're just starting to learn PHP, the approach I'd recommend is to use a framework that generates these for you. Take a look at QCodo (http://www.qcodo.com), and in particular, this tutorial video: http://www.qcodo.com/view.php/demo_1_live

Answer 6

Contrary to other's recommendations, I would not recommend a framework or abstraction level. It will insulate you from understanding how php works and requires that you learn php and the framework structure/process at the same time. An abstraction layer is good practice in a commercial environment, but from the vibe of your question, you don't anticipate moving servers or migrating your db.

I recommend working procedurally (not object-oriented) with the php and mysql until you understand what is going on and how the language works.

To respond to your actual question:

You need to connect to the database: mysql_connect()

You need to select the database you want to work with: mysql_select_db()

You need to define the query: msyql_query()

You need to use a while loop to get the data:

$query=mysql_query("select * from table_name");
    while($row=mysql_fetch_assoc($query)){
         extract($row);
         echo $name of field 1.": ".$name of field 2;
    }

To make each row of output a link to more info rewrite the echo statement like this:

 echo "<a href=\"http://addresstomoreinfo.php?image_id=".$image_id.\"&gt;".$name 
      of field 1.": ".$name of field 2."</a>";

The "name of field" variables represent the column names of your db table and I have made up the layout of the field name, colon, and second field name. How the info is displayed is up to you.

The question mark prepends the name of a variable that is defined in the addresstomoreinfo.php page that will be identified by $var=$_GET['image_id'];

Other php, html, css elements are involved in the big picture of accomplishing this. A good source for begining information is http://www.w3schools.com/ I also live and die by the php manual linked to above

Answer 7

I think you don't really know what you're asking for! :-)

With a suitable framework, database and object abstraction layers, this is trivial (I've done it several times). But they are not trivial to write from scratch and not helpful for learning PHP from the basics. Unless you've done this in other languages.

OTOH, doing it all directly is still a good exercise (as @kevtrout has described), as long as you're willing to re-engineer the code repeatedly (even if you never really do) to develop suitable abstraction. IMO, there is far too much PHP kicking around that has long outgrown such a simple structure.